List of usage examples for org.springframework.jdbc.core PreparedStatementCreator PreparedStatementCreator
PreparedStatementCreator
From source file:org.ohmage.query.impl.UserCampaignQueries.java
@Override public void createUserCampaignMask(final CampaignMask mask) throws DataAccessException { // Create the transaction. DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("Creating an observer."); try {// w w w . ja va 2 s . c o m // Begin the transaction. PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource()); TransactionStatus status = transactionManager.getTransaction(def); // Campaign mask creation SQL. final String campaignMaskSql = "INSERT INTO campaign_mask(" + "assigner_user_id, " + "assignee_user_id, " + "campaign_id, " + "mask_id, " + "creation_time) " + "VALUES (" + "(SELECT id FROM user WHERE username = ?), " + "(SELECT id FROM user WHERE username = ?), " + "(SELECT id FROM campaign WHERE urn = ?), " + "?, " + "?)"; // Campaign mask creation statement with parameters. PreparedStatementCreator maskCreator = new PreparedStatementCreator() { /* * (non-Javadoc) * @see org.springframework.jdbc.core.PreparedStatementCreator#createPreparedStatement(java.sql.Connection) */ @Override public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(campaignMaskSql, new String[] { "id" }); ps.setString(1, mask.getAssignerUserId()); ps.setString(2, mask.getAssigneeUserId()); ps.setString(3, mask.getCampaignId()); ps.setString(4, mask.getId().toString()); ps.setLong(5, mask.getCreationTime().getMillis()); return ps; } }; // The auto-generated key for the observer. KeyHolder maskKeyHolder = new GeneratedKeyHolder(); // Create the observer. try { getJdbcTemplate().update(maskCreator, maskKeyHolder); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException("Error executing SQL '" + campaignMaskSql + "' with parameters: " + mask.getAssignerUserId() + ", " + mask.getAssigneeUserId() + ", " + mask.getCampaignId() + ", " + mask.getId().toString() + ", " + mask.getCreationTime().getMillis(), e); } // Get the mask's DB ID. long key = maskKeyHolder.getKey().longValue(); // Create each of the masks. final String campaignMaskPromptIdSql = "INSERT INTO campaign_mask_survey_prompt_map(" + "campaign_mask_id, " + "survey_id, " + "prompt_id)" + "VALUES (?, ?, ?)"; // Get the survey IDs from the mask. Map<String, Set<String>> promptIds = mask.getSurveyPromptMap(); // Create the list of parameters for each of the survey IDs. List<Object[]> maskPromptIdParameters = new ArrayList<Object[]>(promptIds.size()); // Cycle through the survey IDs building the parameters list. for (String surveyId : promptIds.keySet()) { for (String promptId : promptIds.get(surveyId)) { maskPromptIdParameters.add(new Object[] { key, surveyId, promptId }); } } // Add the mask survey IDs. getJdbcTemplate().batchUpdate(campaignMaskPromptIdSql, maskPromptIdParameters); // Commit the transaction. try { transactionManager.commit(status); } catch (TransactionException e) { transactionManager.rollback(status); throw new DataAccessException("Error while committing the transaction.", e); } } catch (TransactionException e) { throw new DataAccessException("Error while attempting to rollback the transaction.", e); } }
From source file:net.solarnetwork.node.dao.jdbc.JdbcSettingDao.java
private void storeSettingInternal(final String key, final String ttype, final String value, final int flags) { final String type = (ttype == null ? "" : ttype); final Timestamp now = new Timestamp(System.currentTimeMillis()); // to avoid bumping modified date column when values haven't changed, we are careful here // to compare before actually updating getJdbcTemplate().query(new PreparedStatementCreator() { @Override/*from w w w. j a v a2s . co m*/ public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement queryStmt = con.prepareStatement(sqlGet, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT); queryStmt.setString(1, key); queryStmt.setString(2, type); return queryStmt; } }, new ResultSetExtractor<Object>() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { String oldValue = rs.getString(1); if (!value.equals(oldValue)) { rs.updateString(1, value); rs.updateTimestamp(2, now); rs.updateRow(); } } else { rs.moveToInsertRow(); rs.updateString(1, value); rs.updateTimestamp(2, now); rs.updateString(3, key); rs.updateString(4, type); rs.updateInt(5, flags); rs.insertRow(); } return null; } }); }
From source file:net.freechoice.dao.impl.DaoPost.java
@Override public List<FC_Post> getPopularPosts(final int length, final Timestamp offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { @Override//from w w w. ja v a2 s. c om public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(selectFromPost(length, "where is_valid = true and status > 1 " + " and time_posted < ?", NUM_READ_DESC, limit)); ps.setTimestamp(1, offset); return ps; } }, mapper); }
From source file:com.buckwa.dao.impl.excise4.Form23DaoImpl.java
@Override public void create(final Form23 form23) { KeyHolder keyHolder = new GeneratedKeyHolder(); final StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO `form23`").append( " (`form23_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`," + "`totalScrap`,`part4flag`,`part4fullName`,`part4Date`," + "`part5flag`,`part5licenseNo`,`part5licenseDate`,`part5billingNo`,`part5billingDate`,`part5amount`,`part5Date`," + "`part6flag`,`part6Date`,`step`)") .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'0')"); logger.info("SQL : " + sql.toString()); String user = ""; try {/*from ww w .j av a2 s .c o m*/ user = BuckWaUtils.getUserNameFromContext(); } catch (BuckWaException e) { e.printStackTrace(); } final String userName = user; jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { Timestamp currentDate = new Timestamp(System.currentTimeMillis()); Industry industry = form23.getIndustry(); Factory factory = form23.getFactory(); PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS); ps.setLong(1, industry.getIndustryId()); if (factory.getFactoryId() != null) { ps.setLong(2, factory.getFactoryId()); } else { ps.setNull(2, java.sql.Types.BIGINT); } ps.setTimestamp(3, currentDate); ps.setString(4, userName); ps.setTimestamp(5, currentDate); ps.setString(6, userName); ps.setBigDecimal(7, form23.getTotalScrap()); ps.setString(8, form23.getPart4flag()); ps.setString(9, form23.getPart4fullName()); ps.setTimestamp(10, currentDate); ps.setString(11, form23.getPart5flag()); ps.setString(12, form23.getPart5licenseNo()); ps.setTimestamp(13, getDateFormString(form23.getPart5licenseDate()));//part5licenseDate ps.setString(14, form23.getPart5billingNo()); ps.setTimestamp(15, getDateFormString(form23.getPart5billingDate()));//part5billingDate ps.setBigDecimal(16, form23.getPart5amount()); ps.setTimestamp(17, currentDate);//part5Date ps.setString(18, form23.getPart6flag());//part5Date ps.setTimestamp(19, currentDate);//part6Date return ps; } }, keyHolder); final Long returnidform23 = keyHolder.getKey().longValue(); form23.setForm23Id(returnidform23); form23.setStep("0"); logger.info("returnidform23 : " + returnidform23); //ID PRODUCT List<Product> products = form23.getProductList(); if (products != null) { final StringBuilder psql = new StringBuilder(); psql.append( "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ") .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)"); logger.info("SQL : " + psql.toString()); for (final Product p : products) { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { Timestamp currentDate = new Timestamp(System.currentTimeMillis()); PreparedStatement ps = connection.prepareStatement(psql.toString(), Statement.RETURN_GENERATED_KEYS); ps.setLong(1, returnidform23); ps.setString(2, p.getSeq()); ps.setString(3, p.getProductName()); ps.setString(4, p.getSize()); ps.setString(5, p.getBandColor()); ps.setString(6, p.getBackgroudColor()); ps.setString(7, p.getLicenseNo()); ps.setBigDecimal(8, p.getGrossnumber200()); ps.setBigDecimal(9, p.getGrossnumber400()); ps.setBigDecimal(10, p.getCorkScrap()); ps.setBigDecimal(11, p.getTotalScrap()); ps.setTimestamp(12, currentDate); ps.setString(13, userName); ps.setTimestamp(14, currentDate); ps.setString(15, userName); return ps; } }, keyHolder); long returnidproduct = keyHolder.getKey().longValue(); p.setProcuctId(returnidproduct); logger.info("returnidproduct : " + returnidproduct); } } }
From source file:net.solarnetwork.node.dao.jdbc.AbstractJdbcDao.java
/** * Persist a domain object, without using auto-generated keys. * //from w ww. ja v a 2 s . c o m * @param obj * the domain object to persist * @param sqlInsert * the SQL insert statement to use * @return the primary key created for the domain object */ protected Long storeDomainObjectWithoutAutogeneratedKeys(final T obj, final String sqlInsert) { Object result = getJdbcTemplate().execute(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sqlInsert); setStoreStatementValues(obj, ps); return ps; } }, new PreparedStatementCallback<Object>() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { ps.execute(); int count = ps.getUpdateCount(); if (count == 1 && ps.getMoreResults()) { ResultSet rs = ps.getResultSet(); if (rs.next()) { return rs.getObject(1); } } return null; } }); if (result instanceof Long) { return (Long) result; } else if (result instanceof Number) { return Long.valueOf(((Number) result).longValue()); } if (log.isWarnEnabled()) { log.warn("Unexpected (non-number) primary key returned: " + result); } return null; }
From source file:net.freechoice.dao.impl.DaoUser.java
/** check whether this name has been registered * @param login name of a user, must be unique * MUST be checked before using /* w w w.ja v a 2 s . c om*/ * net.freechoice.helper.util.StringUtil.isloginNameLegal() */ @Warning(values = { "Injection" }) @Override public boolean isLoginNameUnique(final String name) { return 0 == getJdbcTemplate().query(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement( "select count(1) from fc_user " + " where is_valid = true and name_login = ?"); ps.setString(1, name); return ps; } }, INT_EXTRACTOR); }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) // @Deprecated//from w w w .j av a 2s.co m @Override public List<FC_Post> getPostsBetween(final Date start, final Date end, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement(selectFromPost(length, " where is_valud = true and status > 1 and (time_posted::date between ? and ?)", TIME_DESCEND, offset, limit)); ps.setDate(1, start); ps.setDate(2, end); return ps; } }, mapper); }
From source file:nl.ordina.bag.etl.dao.AbstractBAGMutatiesDAO.java
@Override public void insert(final BAGMutatie mutatie) throws DAOException { try {//from ww w .ja va 2 s . c o m jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { try { PreparedStatement ps = connection.prepareStatement("insert into bag_mutatie (" + "id," + "tijdstip_verwerking," + "volgnr_verwerking," + "object_type," + "mutatie_product" + ") values ((select nvl(max(id),0) + 1 from bag_mutatie),?,?,?,?)"); ps.setTimestamp(1, Utils.toTimestamp(mutatie.getTijdstipVerwerking())); ps.setLong(2, mutatie.getVolgnrVerwerking()); ps.setInt(3, mutatie.getObjectType().ordinal()); ps.setString(4, XMLMessageBuilder.getInstance(MutatieProduct.class) .handle(new JAXBElement<MutatieProduct>(new QName( "http://www.kadaster.nl/schemas/bag-verstrekkingen/extract-producten-lvc/v20090901", "Mutatie-product"), MutatieProduct.class, mutatie.getMutatieProduct()))); return ps; } catch (JAXBException e) { throw new DAOException(e); } } }); } catch (DataAccessException e) { throw new DAOException(e); } }
From source file:org.ohmage.query.impl.SurveyUploadQuery.java
@Override public List<Integer> insertSurveys(final String username, final String client, final String campaignUrn, final List<SurveyResponse> surveyUploadList, final Map<UUID, Image> bufferedImageMap, final Map<String, Video> videoContentsMap, final Map<String, Audio> audioContentsMap) throws DataAccessException { List<Integer> duplicateIndexList = new ArrayList<Integer>(); int numberOfSurveys = surveyUploadList.size(); // The following variables are used in logging messages when errors occur SurveyResponse currentSurveyResponse = null; PromptResponse currentPromptResponse = null; String currentSql = null;//ww w .ja va 2 s . c o m List<File> fileList = new LinkedList<File>(); // Wrap all of the inserts in a transaction DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("survey upload"); DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource()); TransactionStatus status = transactionManager.getTransaction(def); // begin transaction // Use a savepoint to handle nested rollbacks if duplicates are found Object savepoint = status.createSavepoint(); try { // handle TransactionExceptions for (int surveyIndex = 0; surveyIndex < numberOfSurveys; surveyIndex++) { try { // handle DataAccessExceptions final SurveyResponse surveyUpload = surveyUploadList.get(surveyIndex); currentSurveyResponse = surveyUpload; currentSql = SQL_INSERT_SURVEY_RESPONSE; KeyHolder idKeyHolder = new GeneratedKeyHolder(); // First, insert the survey getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(SQL_INSERT_SURVEY_RESPONSE, Statement.RETURN_GENERATED_KEYS); String locationString = null; Location location = surveyUpload.getLocation(); if (location != null) { try { locationString = location.toJson(false, LocationColumnKey.ALL_COLUMNS) .toString(); } catch (JSONException e) { throw new SQLException(e); } catch (DomainException e) { throw new SQLException(e); } } ps.setString(1, surveyUpload.getSurveyResponseId().toString()); ps.setString(2, username); ps.setString(3, campaignUrn); ps.setLong(4, surveyUpload.getTime()); ps.setString(5, surveyUpload.getTimezone().getID()); ps.setString(6, surveyUpload.getLocationStatus().toString()); ps.setString(7, locationString); ps.setString(8, surveyUpload.getSurvey().getId()); try { ps.setString(9, surveyUpload .toJson(false, false, false, false, true, true, true, true, true, false, false, true, true, true, true, false, false) .toString()); } catch (JSONException e) { throw new SQLException("Couldn't create the JSON.", e); } catch (DomainException e) { throw new SQLException("Couldn't create the JSON.", e); } ps.setString(10, client); ps.setTimestamp(11, new Timestamp(System.currentTimeMillis())); try { ps.setString(12, surveyUpload.getLaunchContext().toJson(true).toString()); } catch (JSONException e) { throw new SQLException("Couldn't create the JSON.", e); } try { ps.setString(13, PreferenceCache.instance() .lookup(PreferenceCache.KEY_DEFAULT_SURVEY_RESPONSE_SHARING_STATE)); } catch (CacheMissException e) { throw new SQLException("Error reading from the cache.", e); } return ps; } }, idKeyHolder); savepoint = status.createSavepoint(); final Number surveyResponseId = idKeyHolder.getKey(); // the primary key on the survey_response table for the // just-inserted survey currentSql = SQL_INSERT_PROMPT_RESPONSE; // Now insert each prompt response from the survey Collection<Response> promptUploadList = surveyUpload.getResponses().values(); createPromptResponse(username, client, surveyResponseId, fileList, promptUploadList, null, bufferedImageMap, videoContentsMap, audioContentsMap, transactionManager, status); } catch (DataIntegrityViolationException dive) { // a unique index exists only on the survey_response table if (isDuplicate(dive)) { LOGGER.debug("Found a duplicate survey upload message for user " + username); duplicateIndexList.add(surveyIndex); status.rollbackToSavepoint(savepoint); } else { // Some other integrity violation occurred - bad!! All // of the data to be inserted must be validated before // this query runs so there is either missing validation // or somehow an auto_incremented key has been duplicated. LOGGER.error("Caught DataAccessException", dive); logErrorDetails(currentSurveyResponse, currentPromptResponse, currentSql, username, campaignUrn); for (File f : fileList) { f.delete(); } rollback(transactionManager, status); throw new DataAccessException(dive); } } catch (org.springframework.dao.DataAccessException dae) { // Some other database problem happened that prevented // the SQL from completing normally. LOGGER.error("caught DataAccessException", dae); logErrorDetails(currentSurveyResponse, currentPromptResponse, currentSql, username, campaignUrn); for (File f : fileList) { f.delete(); } rollback(transactionManager, status); throw new DataAccessException(dae); } } // Finally, commit the transaction transactionManager.commit(status); LOGGER.info("Completed survey message persistence"); } catch (TransactionException te) { LOGGER.error("failed to commit survey upload transaction, attempting to rollback", te); rollback(transactionManager, status); for (File f : fileList) { f.delete(); } logErrorDetails(currentSurveyResponse, currentPromptResponse, currentSql, username, campaignUrn); throw new DataAccessException(te); } LOGGER.info( "Finished inserting survey responses and any associated images into the database and the filesystem."); return duplicateIndexList; }
From source file:org.ohmage.query.impl.AuditQueries.java
@Override public void createAudit(final RequestServlet.RequestType requestType, final String uri, final String client, final String requestId, final String deviceId, final Map<String, String[]> parameters, final Map<String, String[]> extras, final String response, final long receivedMillis, final long respondMillis) throws DataAccessException { if (requestType == null) { throw new IllegalArgumentException("The request type is required and cannot be null."); } else if (uri == null) { throw new IllegalArgumentException("The request URI is required and cannot be null."); } else if (response == null) { throw new IllegalArgumentException("The response is required and cannot be null."); }/* www .j av a2 s .c om*/ // Create the transaction. DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("Creating a request audit."); try { // Begin the transaction. PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource()); TransactionStatus status = transactionManager.getTransaction(def); // Create a key holder that will be responsible for referencing // which row was just inserted. KeyHolder keyHolder = new GeneratedKeyHolder(); // Insert the audit entry. try { getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(SQL_INSERT_AUDIT, new String[] { "id" }); ps.setString(1, requestType.name().toLowerCase()); ps.setString(2, uri); ps.setString(3, client); ps.setString(4, requestId); ps.setString(5, deviceId); ps.setString(6, response); ps.setLong(7, receivedMillis); ps.setLong(8, respondMillis); return ps; } }, keyHolder); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException("Error while executing SQL '" + SQL_INSERT_AUDIT + "' with parameters: " + requestType.name().toLowerCase() + ", " + uri + ", " + client + ", " + deviceId + ", " + response + ", " + receivedMillis + ", " + respondMillis, e); } // Add all of the parameters. if (parameters != null) { for (String key : parameters.keySet()) { for (String value : parameters.get(key)) { try { getJdbcTemplate().update(SQL_INSERT_PARAMETER, keyHolder.getKey().longValue(), key, value); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException( "Error while executing SQL '" + SQL_INSERT_PARAMETER + "' with parameters: " + keyHolder.getKey().longValue() + ", " + key + ", " + value, e); } } } } // Add all of the extras. if (extras != null) { for (String key : extras.keySet()) { for (String value : extras.get(key)) { try { getJdbcTemplate().update(SQL_INSERT_EXTRA, keyHolder.getKey().longValue(), key, value); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException( "Error while executing SQL '" + SQL_INSERT_EXTRA + "' with parameters: " + keyHolder.getKey().longValue() + ", " + key + ", " + value, e); } } } } // Commit the transaction. try { transactionManager.commit(status); } catch (TransactionException e) { transactionManager.rollback(status); throw new DataAccessException("Error while committing the transaction.", e); } } catch (TransactionException e) { throw new DataAccessException("Error while attempting to rollback the transaction.", e); } }