List of usage examples for org.springframework.jdbc.core PreparedStatementCreator PreparedStatementCreator
PreparedStatementCreator
From source file:info.raack.appliancelabeler.data.JDBCDatabase.java
private int getIdForEnergyMonitor(final EnergyMonitor energyMonitor) { if (energyMonitor.getId() > 0) { return energyMonitor.getId(); } else {//from ww w. j a v a2 s .c o m final String userId = energyMonitor.getUserId(); final String monitorId = energyMonitor.getMonitorId(); final String monitorType = energyMonitor.getType(); try { return jdbcTemplate.queryForInt(queryForExistingEnergyMonitorId, new Object[] { userId, monitorType, monitorId }); } catch (IncorrectResultSizeDataAccessException e) { // there was no row for this energy monitor - insert a row for it logger.debug( "No energy monitor row for this monitor (user " + userId + ", monitor " + monitorId + ")"); KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(insertForNewEnergyMonitor, new String[] { "id" }); ps.setString(1, userId); ps.setString(2, monitorType); ps.setString(3, monitorId); ps.setLong(4, -1); return ps; } }, keyHolder); int newId = keyHolder.getKey().intValue(); energyMonitor.setId(newId); return newId; } } }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insert(final Pand pand) throws DAOException { try {/*from w w w . j ava 2 s .c o m*/ jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("insert into bag_pand (" + "bag_pand_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel," + "pand_geometrie," + "bouwjaar," + "pand_status," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer" + ") values (?,?,?,?,?,?,?,?,?,?,?,?)"); ps.setLong(1, pand.getIdentificatie()); ps.setInt(2, pand.getAanduidingRecordInactief().ordinal()); ps.setLong(3, pand.getAanduidingRecordCorrectie()); ps.setInt(4, pand.getOfficieel().ordinal()); ps.setString(5, pand.getPandGeometrie()); ps.setInt(6, pand.getBouwjaar()); ps.setString(7, pand.getPandStatus()); ps.setTimestamp(8, new Timestamp(pand.getBegindatumTijdvakGeldigheid().getTime())); if (pand.getEinddatumTijdvakGeldigheid() == null) ps.setNull(9, Types.TIMESTAMP); else ps.setTimestamp(9, new Timestamp(pand.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(10, pand.getInOnderzoek().ordinal()); ps.setDate(11, new Date(pand.getDocumentdatum().getTime())); ps.setString(12, pand.getDocumentnummer()); return ps; } }); } catch (DataAccessException e) { throw new DAOException("Error inserting pand: " + pand.getIdentificatie(), e); } }
From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java
@Override public void updateStep(final String form24Id, final String nexStep) { try {//from www . j ava 2 s .co m final String userName = BuckWaUtils.getUserNameFromContext(); final String fullName = BuckWaUtils.getFullNameFromContext(); final Timestamp currentDate = new java.sql.Timestamp(System.currentTimeMillis()); final String docNo = ExciseHelper.getDocNo(); logger.info(" docNo:" + docNo + " userName:" + userName + " fuulName:" + fullName + " currentDate+" + currentDate); if (ExciseConstants.FORM24_STEP_2.equals(nexStep)) { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("" + " update form24 set step=?,doc_no=?,audit_by=?,audit_by_full_name=?,audit_date=? where form24_id=? " + "", Statement.RETURN_GENERATED_KEYS); ps.setString(1, nexStep); ps.setString(2, docNo); ps.setString(3, userName); ps.setString(4, fullName); ps.setTimestamp(5, currentDate); ps.setLong(6, new Long(form24Id)); return ps; } }); } else { jdbcTemplate.update(" update form24 set step ='" + nexStep + "' where form24_id=" + form24Id); } } catch (BuckWaException ex) { ex.printStackTrace(); //throw ex; } }
From source file:org.ohmage.query.impl.SurveyUploadQuery.java
/** * Creates the prompt response entry in the corresponding table and saves * any attached files, images, videos, etc.. * //from w w w . java 2 s . c o m * @param username * The username of the user saving this prompt response. * * @param client * The name of the device used to generate the response. * * @param surveyResponseId * The unique identifier for this survey response. * * @param fileList * The list of files saved to the disk, which should be a reference * to a list that will be populated by this function. * * @param promptUploadList * The collection of prompt responses to store. * * @param repeatableSetIteration * If these prompt responses were part of a repeatable set, this is * the iteration of that repeatable set; otherwise, null. * * @param bufferedImageMap * The map of image IDs to their contents. * * @param videoContentsMap * The map of video IDs to their contents. * * @param transactionManager * The manager for this transaction. * * @param status * The status of this transaction. * * @throws DataAccessException * There was an error saving the information. */ private void createPromptResponse(final String username, final String client, final Number surveyResponseId, final List<File> fileList, final Collection<Response> promptUploadList, final Integer repeatableSetIteration, final Map<UUID, Image> bufferedImageMap, final Map<String, Video> videoContentsMap, final Map<String, Audio> audioContentsMap, final DataSourceTransactionManager transactionManager, final TransactionStatus status) throws DataAccessException { for (Response response : promptUploadList) { if (response instanceof RepeatableSetResponse) { Map<Integer, Map<Integer, Response>> iterationToResponse = ((RepeatableSetResponse) response) .getResponseGroups(); for (Integer iteration : iterationToResponse.keySet()) { createPromptResponse(username, client, surveyResponseId, fileList, iterationToResponse.get(iteration).values(), iteration, bufferedImageMap, videoContentsMap, audioContentsMap, transactionManager, status); } continue; } final PromptResponse promptResponse = (PromptResponse) response; getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(SQL_INSERT_PROMPT_RESPONSE); ps.setLong(1, surveyResponseId.longValue()); RepeatableSet parent = promptResponse.getPrompt().getParent(); if (parent == null) { ps.setNull(2, java.sql.Types.NULL); ps.setNull(3, java.sql.Types.NULL); } else { ps.setString(2, parent.getId()); ps.setInt(3, repeatableSetIteration); } ps.setString(4, promptResponse.getPrompt().getType().toString()); ps.setString(5, promptResponse.getPrompt().getId()); Object response = promptResponse.getResponse(); if (response instanceof DateTime) { ps.setString(6, DateTimeUtils.getW3cIso8601DateString((DateTime) response, true)); } else if ((promptResponse instanceof MultiChoiceCustomPromptResponse) && (response instanceof Collection)) { JSONArray json = new JSONArray(); for (Object currResponse : (Collection<?>) response) { json.put(currResponse); } ps.setString(6, json.toString()); } else { ps.setString(6, response.toString()); } return ps; } }); if (promptResponse instanceof PhotoPromptResponse) { // Grab the associated image and save it String imageId = promptResponse.getResponse().toString(); // If it wasn't skipped and it was displayed, save the // associated images. if (!JsonInputKeys.PROMPT_SKIPPED.equals(imageId) && !JsonInputKeys.PROMPT_NOT_DISPLAYED.equals(imageId) && !JsonInputKeys.IMAGE_NOT_UPLOADED.equals(imageId)) { // Get the directory to save the image and save it. File originalFile; try { originalFile = bufferedImageMap.get(UUID.fromString(imageId)).saveImage(getDirectory()); } catch (DomainException e) { rollback(transactionManager, status); throw new DataAccessException("Error saving the images.", e); } // Get the image's URL. String url = "file://" + originalFile.getAbsolutePath(); // Insert the image URL into the database. try { getJdbcTemplate().update(SQL_INSERT_IMAGE, new Object[] { username, client, imageId, url }); } catch (org.springframework.dao.DataAccessException e) { transactionManager.rollback(status); throw new DataAccessException("Error executing SQL '" + SQL_INSERT_IMAGE + "' with parameters: " + username + ", " + client + ", " + imageId + ", " + url, e); } } } // Save the video. else if (promptResponse instanceof VideoPromptResponse) { // Make sure the response contains an actual video response. Object responseValue = promptResponse.getResponse(); if (!((responseValue instanceof NoResponse) || (responseValue instanceof NoResponseMedia))) { // Attempt to write it to the file system. try { // Get the current video directory. File currVideoDirectory = VideoDirectoryCache.getDirectory(); // Get the video ID. String responseValueString = responseValue.toString(); // Get the video object. Video video = videoContentsMap.get(responseValueString); // Get the file. File videoFile = new File(currVideoDirectory.getAbsolutePath() + "/" + responseValueString + "." + video.getType()); // Get the video contents. InputStream content = video.getContentStream(); if (content == null) { transactionManager.rollback(status); throw new DataAccessException("The video contents did not exist in the map."); } // Write the video contents to disk. FileOutputStream fos = new FileOutputStream(videoFile); // Write the content to the output stream. int bytesRead; byte[] buffer = new byte[4096]; while ((bytesRead = content.read(buffer)) != -1) { fos.write(buffer, 0, bytesRead); } fos.close(); // Store the file reference in the video list. fileList.add(videoFile); // Get the video's URL. String url = "file://" + videoFile.getAbsolutePath(); // Insert the video URL into the database. try { getJdbcTemplate().update(SQL_INSERT_IMAGE, new Object[] { username, client, responseValueString, url }); } catch (org.springframework.dao.DataAccessException e) { videoFile.delete(); transactionManager.rollback(status); throw new DataAccessException( "Error executing SQL '" + SQL_INSERT_IMAGE + "' with parameters: " + username + ", " + client + ", " + responseValueString + ", " + url, e); } } // If it fails, roll back the transaction. catch (DomainException e) { transactionManager.rollback(status); throw new DataAccessException("Could not get the video directory.", e); } catch (IOException e) { transactionManager.rollback(status); throw new DataAccessException("Could not write the file.", e); } } } else if (promptResponse instanceof AudioPromptResponse) { // Make sure the response contains an actual audio response. Object responseValue = promptResponse.getResponse(); if (!((responseValue instanceof NoResponse) || (responseValue instanceof NoResponseMedia))) { // Attempt to write it to the file system. try { // Get the current audio directory. File currAudioDirectory = AudioDirectoryCache.getDirectory(); // Get the audio ID. String responseValueString = responseValue.toString(); // Get the audio object. Audio audio = audioContentsMap.get(responseValueString); // Get the file. File audioFile = new File(currAudioDirectory.getAbsolutePath() + "/" + responseValueString + "." + audio.getType()); // Get the video contents. InputStream content = audio.getContentStream(); if (content == null) { transactionManager.rollback(status); throw new DataAccessException("The audio contents did not exist in the map."); } // Write the video contents to disk. FileOutputStream fos = new FileOutputStream(audioFile); // Write the content to the output stream. int bytesRead; byte[] buffer = new byte[4096]; while ((bytesRead = content.read(buffer)) != -1) { fos.write(buffer, 0, bytesRead); } fos.close(); // Store the file reference in the video list. fileList.add(audioFile); // Get the video's URL. String url = "file://" + audioFile.getAbsolutePath(); // Insert the video URL into the database. try { getJdbcTemplate().update(SQL_INSERT_IMAGE, new Object[] { username, client, responseValueString, url }); } catch (org.springframework.dao.DataAccessException e) { audioFile.delete(); transactionManager.rollback(status); throw new DataAccessException( "Error executing SQL '" + SQL_INSERT_IMAGE + "' with parameters: " + username + ", " + client + ", " + responseValueString + ", " + url, e); } } // If it fails, roll back the transaction. catch (DomainException e) { transactionManager.rollback(status); throw new DataAccessException("Could not get the video directory.", e); } catch (IOException e) { transactionManager.rollback(status); throw new DataAccessException("Could not write the file.", e); } } } } }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override// w w w . j a va2s .co m public List<FC_Post> suGetPostsBefore(final Date date, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { // "status > 1 and (time_posted::date between ? and ?)", @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement( select(length) + " where time_posted::date < ?" + " offset " + offset + " limit " + limit); ps.setDate(1, date); return ps; } }, mapper); }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override// w ww .ja v a2 s .c om public List<FC_Post> suGetPostsAfter(final Date date, 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( select(length) + " where time_posted::date > ?" + " offset " + offset + " limit " + limit); ps.setDate(1, date); return ps; } }, mapper); }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insert(final Verblijfsobject verblijfsobject) throws DAOException { try {/* w w w. ja va2s .co m*/ transactionTemplate.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus status) { jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("insert into bag_verblijfsobject (" + "bag_verblijfsobject_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel," + "verblijfsobject_geometrie," + "oppervlakte_verblijfsobject," + "verblijfsobject_status," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer," + "bag_nummeraanduiding_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps.setLong(1, verblijfsobject.getIdentificatie()); ps.setInt(2, verblijfsobject.getAanduidingRecordInactief().ordinal()); ps.setLong(3, verblijfsobject.getAanduidingRecordCorrectie()); ps.setInt(4, verblijfsobject.getOfficieel().ordinal()); ps.setString(5, verblijfsobject.getVerblijfsobjectGeometrie()); ps.setInt(6, verblijfsobject.getOppervlakteVerblijfsobject()); ps.setInt(7, verblijfsobject.getVerblijfsobjectStatus().ordinal()); ps.setTimestamp(8, new Timestamp(verblijfsobject.getBegindatumTijdvakGeldigheid().getTime())); if (verblijfsobject.getEinddatumTijdvakGeldigheid() == null) ps.setNull(9, Types.TIMESTAMP); else ps.setTimestamp(9, new Timestamp(verblijfsobject.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(10, verblijfsobject.getInOnderzoek().ordinal()); ps.setDate(11, new Date(verblijfsobject.getDocumentdatum().getTime())); ps.setString(12, verblijfsobject.getDocumentnummer()); ps.setLong(13, verblijfsobject.getHoofdAdres()); return ps; } }); insertGebruikersdoelen(verblijfsobject); insertNevenadressen(TypeAdresseerbaarObject.VERBLIJFSOBJECT, verblijfsobject); insertGerelateerdePanden(verblijfsobject); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting verblijfsobject: " + verblijfsobject.getIdentificatie(), e); } }
From source file:info.raack.appliancedetection.evaluation.data.JDBCDatabase.java
@Override public void saveSimulationGroup(final SimulationGroup group) { KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement( "insert into simulation_groups (start_time, duration, num_appliances, on_concurrency, labels_per_appliance) values (?, ?, ?, ?, ?)", new String[] { "id" }); ps.setLong(1, group.getStartTime().getTime()); ps.setInt(2, group.getDurationInSeconds()); ps.setInt(3, group.getNumAppliances()); ps.setInt(4, group.getOnConcurrency()); ps.setInt(5, group.getLabelsPerOnOff()); return ps; }//from w w w.j a va 2s .c o m }, keyHolder); group.setId(keyHolder.getKey().intValue()); }
From source file:net.freechoice.dao.impl.DaoPost.java
@DBSpec(dialect = Dialect.PostgreSQL) @Override/*from w w w . j av a 2 s.c o m*/ public List<FC_Post> suGetPostsBetween(final Date start, final Date end, final int length, final int offset, final int limit) { return getJdbcTemplate().query(new PreparedStatementCreator() { // "status > 1 and (time_posted::date between ? and ?)", @Override public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException { PreparedStatement ps = arg0.prepareStatement(select(length) + " where time_posted::date between ? and ?" + " offset " + offset + " limit " + limit); ps.setDate(1, start); ps.setDate(2, end); return ps; } }, mapper); }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insert(final Ligplaats ligplaats) throws DAOException { try {//w w w .ja v a 2 s . c om transactionTemplate.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus status) { jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection .prepareStatement("insert into bag_ligplaats (" + "bag_ligplaats_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel," + "ligplaats_status," + "ligplaats_geometrie," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer," + "bag_nummeraanduiding_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?)"); ps.setLong(1, ligplaats.getIdentificatie()); ps.setInt(2, ligplaats.getAanduidingRecordInactief().ordinal()); ps.setLong(3, ligplaats.getAanduidingRecordCorrectie()); ps.setInt(4, ligplaats.getOfficieel().ordinal()); ps.setInt(5, ligplaats.getLigplaatsStatus().ordinal()); ps.setString(6, ligplaats.getLigplaatsGeometrie()); ps.setTimestamp(7, new Timestamp(ligplaats.getBegindatumTijdvakGeldigheid().getTime())); if (ligplaats.getEinddatumTijdvakGeldigheid() == null) ps.setNull(8, Types.TIMESTAMP); else ps.setTimestamp(8, new Timestamp(ligplaats.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(9, ligplaats.getInOnderzoek().ordinal()); ps.setDate(10, new Date(ligplaats.getDocumentdatum().getTime())); ps.setString(11, ligplaats.getDocumentnummer()); ps.setLong(12, ligplaats.getHoofdAdres()); return ps; } }); insertNevenadressen(TypeAdresseerbaarObject.LIGPLAATS, ligplaats); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting ligplaats: " + ligplaats.getIdentificatie(), e); } }