List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
java.sql.Timestamp value. From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void delete(final Verblijfsobject verblijfsobject) { try {/* w w w.ja v a 2 s. com*/ deleteGebruikersdoelen(verblijfsobject); deleteNevenadressen(TypeAdresseerbaarObject.VERBLIJFSOBJECT, verblijfsobject); deleteGerelateerdePanden(verblijfsobject); jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("delete from bag_verblijfsobject" + " where bag_verblijfsobject_id = ?" + " and aanduiding_record_correctie = ?" + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?"); ps.setLong(1, verblijfsobject.getIdentificatie()); ps.setLong(2, verblijfsobject.getAanduidingRecordCorrectie()); ps.setTimestamp(3, new Timestamp(verblijfsobject.getBegindatumTijdvakGeldigheid().getTime())); if (verblijfsobject.getEinddatumTijdvakGeldigheid() == null) ps.setNull(4, Types.TIMESTAMP); else ps.setTimestamp(4, new Timestamp(verblijfsobject.getEinddatumTijdvakGeldigheid().getTime())); return ps; } }); } catch (DataAccessException e) { throw new DAOException(e); } }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void delete(final Nummeraanduiding nummeraanduiding) { try {/*from ww w .j a va2 s. c o m*/ jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("delete from bag_nummeraanduiding" + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?" + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?"); ps.setLong(1, nummeraanduiding.getIdentificatie()); ps.setLong(2, nummeraanduiding.getAanduidingRecordCorrectie()); ps.setTimestamp(3, new Timestamp(nummeraanduiding.getBegindatumTijdvakGeldigheid().getTime())); if (nummeraanduiding.getEinddatumTijdvakGeldigheid() == null) ps.setNull(4, Types.TIMESTAMP); else ps.setTimestamp(4, new Timestamp(nummeraanduiding.getEinddatumTijdvakGeldigheid().getTime())); return ps; } }); } catch (DataAccessException e) { throw new DAOException(e); } }
From source file:org.dcache.chimera.FsSqlDriver.java
/** * * creates an entry in t_inodes table with initial values. * for optimization, initial value of reference count may be defined. * for newly created files , file size is zero. For directories 512. * * @param id//from ww w . jav a 2 s. com * @param uid * @param gid * @param mode * @param nlink */ Stat createInode(String id, int type, int uid, int gid, int mode, int nlink, long size) { Timestamp now = new Timestamp(System.currentTimeMillis()); KeyHolder keyHolder = new GeneratedKeyHolder(); _jdbc.update(con -> { PreparedStatement ps = con.prepareStatement( "INSERT INTO t_inodes (ipnfsid,itype,imode,inlink,iuid,igid,isize,iio," + "ictime,iatime,imtime,icrtime,igeneration) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); ps.setString(1, id); ps.setInt(2, type); ps.setInt(3, mode & UnixPermission.S_PERMS); ps.setInt(4, nlink); ps.setInt(5, uid); ps.setInt(6, gid); ps.setLong(7, size); ps.setInt(8, _ioMode); ps.setTimestamp(9, now); ps.setTimestamp(10, now); ps.setTimestamp(11, now); ps.setTimestamp(12, now); ps.setLong(13, 0); return ps; }, keyHolder); Stat stat = new Stat(); stat.setIno((Long) keyHolder.getKeys().get("inumber")); stat.setId(id); stat.setCrTime(now.getTime()); stat.setGeneration(0); stat.setSize(size); stat.setATime(now.getTime()); stat.setCTime(now.getTime()); stat.setMTime(now.getTime()); stat.setUid(uid); stat.setGid(gid); stat.setMode(mode & UnixPermission.S_PERMS | type); stat.setNlink(nlink); stat.setDev(17); stat.setRdev(13); return stat; }
From source file:se.technipelago.weather.chart.Generator.java
private CategoryDataset createRainDataset(final Date begin, final Date end) throws SQLException { final DefaultCategoryDataset dataset = new DefaultCategoryDataset(); PreparedStatement stmt = null; ResultSet result = null;//w w w . j a v a 2 s.c o m Calendar cal = Calendar.getInstance(); cal.setTime(begin); int y1 = cal.get(Calendar.YEAR); int m1 = cal.get(Calendar.MONTH); cal.setTime(end); int y2 = cal.get(Calendar.YEAR); int m2 = cal.get(Calendar.MONTH); long spanDays = (end.getTime() - begin.getTime()) / 1000 / 60 / 60 / 24; DateFormat fmt = spanDays < 40 ? DAY : M; try { final java.sql.Timestamp sqlBegin = new java.sql.Timestamp(begin.getTime()); final java.sql.Timestamp sqlEnd = new java.sql.Timestamp(end.getTime()); if (spanDays < 100) { stmt = conn.prepareStatement( "SELECT date_format(ts, '%Y-%m-%d') AS day, SUM(rain) AS rain FROM archive WHERE ts BETWEEN ? AND ? GROUP BY 1"); } else { stmt = conn.prepareStatement( "SELECT date_format(ts, '%Y-%m-15') AS day, SUM(rain) AS rain FROM archive WHERE ts BETWEEN ? AND ? GROUP BY 1"); } stmt.setTimestamp(1, sqlBegin); stmt.setTimestamp(2, sqlEnd); result = stmt.executeQuery(); while (result.next()) { final String ymd = result.getString(1); final Date day = YMD.parse(ymd); final float rain = result.getFloat(2); dataset.addValue(rain, "Rain", fmt.format(day)); } } catch (ParseException e) { log.log(Level.SEVERE, "Failed to parse rain date", e); } finally { if (result != null) { try { result.close(); } catch (SQLException e) { log.log(Level.SEVERE, "Failed to close ResultSet", e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { log.log(Level.WARNING, "Failed to close select statement", ex); } } } return dataset; }
From source file:dk.netarkivet.harvester.datamodel.RunningJobsInfoDBDAO.java
/** * Store frontier report data to the persistent storage. * @param report the report to store//w w w. j a v a 2s . com * @param filterId the id of the filter that produced the report * @param jobId The ID of the job responsible for this report * @return the update count */ public int storeFrontierReport(String filterId, InMemoryFrontierReport report, Long jobId) { ArgumentNotValid.checkNotNull(report, "report"); ArgumentNotValid.checkNotNull(jobId, "jobId"); Connection c = HarvestDBConnection.get(); PreparedStatement stm = null; try { // First drop existing rows try { c.setAutoCommit(false); stm = c.prepareStatement("DELETE FROM frontierReportMonitor" + " WHERE jobId=? AND filterId=?"); stm.setLong(1, jobId); stm.setString(2, filterId); stm.executeUpdate(); c.commit(); } catch (SQLException e) { String message = "SQL error dropping records for job ID " + jobId + " and filterId " + filterId + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); return 0; } finally { DBUtils.closeStatementIfOpen(stm); DBUtils.rollbackIfNeeded(c, "storeFrontierReport delete", jobId); } // Now batch insert report lines try { c.setAutoCommit(false); stm = c.prepareStatement("INSERT INTO frontierReportMonitor(" + FR_COLUMN.getColumnsInOrder() + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); for (FrontierReportLine frl : report.getLines()) { stm.setLong(FR_COLUMN.jobId.rank(), jobId); stm.setString(FR_COLUMN.filterId.rank(), filterId); stm.setTimestamp(FR_COLUMN.tstamp.rank(), new Timestamp(report.getTimestamp())); stm.setString(FR_COLUMN.domainName.rank(), frl.getDomainName()); stm.setLong(FR_COLUMN.currentSize.rank(), frl.getCurrentSize()); stm.setLong(FR_COLUMN.totalEnqueues.rank(), frl.getTotalEnqueues()); stm.setLong(FR_COLUMN.sessionBalance.rank(), frl.getSessionBalance()); stm.setDouble(FR_COLUMN.lastCost.rank(), frl.getLastCost()); stm.setDouble(FR_COLUMN.averageCost.rank(), correctNumericIfIllegalAverageCost(frl.getAverageCost())); stm.setString(FR_COLUMN.lastDequeueTime.rank(), frl.getLastDequeueTime()); stm.setString(FR_COLUMN.wakeTime.rank(), frl.getWakeTime()); stm.setLong(FR_COLUMN.totalSpend.rank(), frl.getTotalSpend()); stm.setLong(FR_COLUMN.totalBudget.rank(), frl.getTotalBudget()); stm.setLong(FR_COLUMN.errorCount.rank(), frl.getErrorCount()); // URIs are to be truncated to 1000 characters // (see SQL scripts) DBUtils.setStringMaxLength(stm, FR_COLUMN.lastPeekUri.rank(), frl.getLastPeekUri(), MAX_URL_LENGTH, frl, "lastPeekUri"); DBUtils.setStringMaxLength(stm, FR_COLUMN.lastQueuedUri.rank(), frl.getLastQueuedUri(), MAX_URL_LENGTH, frl, "lastQueuedUri"); stm.addBatch(); } int[] updCounts = stm.executeBatch(); int updCountTotal = 0; for (int count : updCounts) { updCountTotal += count; } c.commit(); return updCountTotal; } catch (SQLException e) { String message = "SQL error writing records for job ID " + jobId + " and filterId " + filterId + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); return 0; } finally { DBUtils.closeStatementIfOpen(stm); DBUtils.rollbackIfNeeded(c, "storeFrontierReport insert", jobId); } } finally { HarvestDBConnection.release(c); } }
From source file:com.concursive.connect.web.modules.documents.dao.FileItem.java
/** * Description of the Method/*from w ww . ja va 2 s . c o m*/ * * @param db Description of the Parameter * @param thisVersion Description of the Parameter * @return Description of the Return Value * @throws SQLException Description of the Exception */ public boolean updateVersion(Connection db, FileItemVersion thisVersion) throws SQLException { // Set the master record subject = thisVersion.getSubject(); clientFilename = thisVersion.getClientFilename(); filename = thisVersion.getFilename(); version = thisVersion.getVersion(); size = thisVersion.getSize(); enteredBy = thisVersion.getEnteredBy(); modifiedBy = thisVersion.getModifiedBy(); comment = thisVersion.getComment(); modified = thisVersion.getModified(); // Update the master record int i = 0; PreparedStatement pst = db.prepareStatement("UPDATE project_files " + "SET subject = ?, client_filename = ?, filename = ?, version = ?, " + "size = ?, modifiedby = ?, modified = ?, comment = ?, featured_file = ? " + "WHERE item_id = ? "); pst.setString(++i, subject); pst.setString(++i, clientFilename); pst.setString(++i, filename); pst.setDouble(++i, version); pst.setInt(++i, size); pst.setInt(++i, modifiedBy); pst.setTimestamp(++i, modified); pst.setString(++i, comment); pst.setBoolean(++i, featuredFile); pst.setInt(++i, this.getId()); pst.execute(); pst.close(); return true; }
From source file:se.technipelago.weather.chart.Generator.java
private XYDataset createHistoryDataset(final Date begin, final Date end, final String column, final String label) throws SQLException { final TimeSeriesCollection dataset = new TimeSeriesCollection(); PreparedStatement stmt = null; ResultSet result = null;//from www .j a v a2 s .c o m long spanDays = (end.getTime() - begin.getTime()) / 1000 / 60 / 60 / 24; try { final java.sql.Timestamp sqlBegin = new java.sql.Timestamp(begin.getTime()); final java.sql.Timestamp sqlEnd = new java.sql.Timestamp(end.getTime()); if (spanDays < 100) { stmt = conn.prepareStatement("SELECT ts, " + column + " FROM archive WHERE " + column + " IS NOT NULL AND ts BETWEEN ? AND ? ORDER BY ts"); } else if (spanDays < 1000) { stmt = conn.prepareStatement("SELECT date_format(ts, '%Y-%m-%d %H:00:00') AS day, AVG(" + column + ") AS value FROM archive WHERE " + column + " IS NOT NULL AND ts BETWEEN ? AND ? GROUP BY 1"); } else { stmt = conn.prepareStatement("SELECT date_format(ts, '%Y-%m-%d') AS day, AVG(" + column + ") AS value FROM archive WHERE " + column + " IS NOT NULL AND ts BETWEEN ? AND ? GROUP BY 1"); } stmt.setTimestamp(1, sqlBegin); stmt.setTimestamp(2, sqlEnd); result = stmt.executeQuery(); final TimeSeries s1 = new TimeSeries(label, FixedMillisecond.class); while (result.next()) { final java.sql.Timestamp ts = result.getTimestamp(1); final long timestamp = ts.getTime(); s1.add(new FixedMillisecond(timestamp), result.getFloat(2)); } dataset.addSeries(s1); } finally { if (result != null) { try { result.close(); } catch (SQLException e) { log.log(Level.SEVERE, "Failed to close ResultSet", e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { log.log(Level.WARNING, "Failed to close select statement", ex); } } } return dataset; }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insertOpenbareRuimten(final List<OpenbareRuimte> openbareRuimten) throws DAOException { try {//from w w w . j a va 2 s. co m jdbcTemplate.batchUpdate("insert into bag_openbare_ruimte (" + "bag_openbare_ruimte_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "openbare_ruimte_naam," + "officieel," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "openbare_ruimte_type," + "bron_documentdatum," + "bron_documentnummer," + "openbareruimte_status," + "bag_woonplaats_id," + "verkorte_openbare_ruimte_naam" + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setLong(1, openbareRuimten.get(i).getIdentificatie()); ps.setInt(2, openbareRuimten.get(i).getAanduidingRecordInactief().ordinal()); ps.setLong(3, openbareRuimten.get(i).getAanduidingRecordCorrectie()); ps.setString(4, openbareRuimten.get(i).getOpenbareRuimteNaam()); ps.setInt(5, openbareRuimten.get(i).getOfficieel().ordinal()); ps.setTimestamp(6, new Timestamp( openbareRuimten.get(i).getBegindatumTijdvakGeldigheid().getTime())); if (openbareRuimten.get(i).getEinddatumTijdvakGeldigheid() == null) ps.setNull(7, Types.TIMESTAMP); else ps.setTimestamp(7, new Timestamp( openbareRuimten.get(i).getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(8, openbareRuimten.get(i).getInOnderzoek().ordinal()); ps.setInt(9, openbareRuimten.get(i).getOpenbareRuimteType().ordinal()); ps.setDate(10, new Date(openbareRuimten.get(i).getDocumentdatum().getTime())); ps.setString(11, openbareRuimten.get(i).getDocumentnummer()); ps.setInt(12, openbareRuimten.get(i).getOpenbareruimteStatus().ordinal()); ps.setLong(13, openbareRuimten.get(i).getGerelateerdeWoonplaats()); ps.setString(14, openbareRuimten.get(i).getVerkorteOpenbareRuimteNaam()); } @Override public int getBatchSize() { return openbareRuimten.size(); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting openbare ruimten", e); } }
From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaDropDao.java
/** * Populates the river_tag_trends table//w w w.jav a 2s .c om * * @param drops * @param dropIndex * @param riverDropChannelList * @throws Exception */ private void insertRiverTagTrends(List<Drop> drops, Map<Long, Integer> dropIndex, List<Map<String, Long>> riverDropChannelList) throws Exception { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd H:00:00"); Map<String, RiverTagTrend> trendsData = new HashMap<String, RiverTagTrend>(); for (Map<String, Long> entry : riverDropChannelList) { Long dropletId = entry.get("dropletId"); Long riverId = entry.get("riverId"); River river = new River(); river.setId(riverId); Drop drop = drops.get(dropIndex.get(dropletId)); String datePublishedStr = dateFormat.format(drop.getDatePublished()); Date datePublished = dateFormat.parse(datePublishedStr); // Tags if (drop.getTags() != null) { for (Tag tag : drop.getTags()) { String hash = MD5Util.md5Hex(riverId.toString(), datePublishedStr, tag.getTag(), tag.getType()); RiverTagTrend tagTrend = trendsData.remove(hash); if (tagTrend == null) { tagTrend = new RiverTagTrend(); tagTrend.setRiver(river); tagTrend.setDatePublished(datePublished); tagTrend.setTag(tag.getTag()); tagTrend.setTagType(tag.getType()); tagTrend.setHash(hash); tagTrend.setCount(1L); } else { Long count = new Long(tagTrend.getCount() + 1L); tagTrend.setCount(count); } trendsData.put(hash, tagTrend); } } // Places if (drop.getPlaces() != null) { for (Place place : drop.getPlaces()) { String hash = MD5Util.md5Hex(riverId.toString(), datePublishedStr, place.getPlaceName(), "place"); RiverTagTrend tagTrend = trendsData.remove(hash); if (tagTrend == null) { tagTrend = new RiverTagTrend(); tagTrend.setRiver(river); tagTrend.setDatePublished(datePublished); tagTrend.setTag(place.getPlaceName()); tagTrend.setTagType("place"); tagTrend.setHash(hash); tagTrend.setCount(1L); } else { Long count = new Long(tagTrend.getCount() + 1L); tagTrend.setCount(count); } trendsData.put(hash, tagTrend); } } } if (trendsData.keySet().isEmpty()) return; // Check for existing trends String sql = "SELECT `id`, `hash` FROM `river_tag_trends` WHERE `hash` IN (:hashes)"; MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("hashes", trendsData.keySet()); // List of trend IDs whose count is to be updated final List<long[]> trendCountUpdate = new ArrayList<long[]>(); for (Map<String, Object> row : namedJdbcTemplate.queryForList(sql, params)) { String hash = (String) row.get("hash"); long trendId = ((Number) row.get("id")).longValue(); RiverTagTrend tagTrend = trendsData.remove(hash); long[] counters = { trendId, tagTrend.getCount() }; trendCountUpdate.add(counters); } // Update existing counters if (!trendCountUpdate.isEmpty()) { sql = "UPDATE `river_tag_trends` SET `count` = `count` + ? WHERE `id` = ?"; jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { long[] updateIndex = trendCountUpdate.get(i); ps.setLong(1, updateIndex[1]); ps.setLong(2, updateIndex[0]); } public int getBatchSize() { return trendCountUpdate.size(); } }); } if (trendsData.isEmpty()) { return; } Sequence sequence = sequenceDao.findById("river_tag_trends"); final long startKey = sequenceDao.getIds(sequence, trendsData.size()); // SQL to update the river_tag_trends table sql = "INSERT INTO river_tag_trends(`id`, `hash`, `river_id`, `date_pub`, `tag`, " + "`tag_type`, `count`) VALUES(?, ?, ?, ?, ?, ?, ?)"; final List<Entry<String, RiverTagTrend>> tagTrendsList = new ArrayList<Entry<String, RiverTagTrend>>(); tagTrendsList.addAll(trendsData.entrySet()); jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { long id = startKey + i; Entry<String, RiverTagTrend> entry = tagTrendsList.get(i); RiverTagTrend tagTrend = entry.getValue(); ps.setLong(1, id); ps.setString(2, entry.getKey()); ps.setLong(3, tagTrend.getRiver().getId()); ps.setTimestamp(4, new java.sql.Timestamp(tagTrend.getDatePublished().getTime())); ps.setString(5, tagTrend.getTag()); ps.setString(6, tagTrend.getTagType()); ps.setLong(7, tagTrend.getCount()); } public int getBatchSize() { return tagTrendsList.size(); } }); }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insertWoonplaatsen(final List<Woonplaats> woonplaatsen) throws DAOException { try {/*ww w. j a va 2 s . c o m*/ jdbcTemplate.batchUpdate("insert into bag_woonplaats (" + "bag_woonplaats_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "woonplaats_naam," + "woonplaats_geometrie," + "officieel," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer," + "woonplaats_status" + ") values (?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setLong(1, woonplaatsen.get(i).getIdentificatie()); ps.setInt(2, woonplaatsen.get(i).getAanduidingRecordInactief().ordinal()); ps.setLong(3, woonplaatsen.get(i).getAanduidingRecordCorrectie()); ps.setString(4, woonplaatsen.get(i).getWoonplaatsNaam()); ps.setString(5, woonplaatsen.get(i).getWoonplaatsGeometrie()); ps.setInt(6, woonplaatsen.get(i).getOfficieel().ordinal()); ps.setTimestamp(7, new Timestamp(woonplaatsen.get(i).getBegindatumTijdvakGeldigheid().getTime())); if (woonplaatsen.get(i).getEinddatumTijdvakGeldigheid() == null) ps.setNull(8, Types.TIMESTAMP); else ps.setTimestamp(8, new Timestamp( woonplaatsen.get(i).getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(9, woonplaatsen.get(i).getInOnderzoek().ordinal()); ps.setDate(10, new Date(woonplaatsen.get(i).getDocumentdatum().getTime())); ps.setString(11, woonplaatsen.get(i).getDocumentnummer()); ps.setInt(12, woonplaatsen.get(i).getWoonplaatsStatus().ordinal()); } @Override public int getBatchSize() { return woonplaatsen.size(); } }); } catch (DataAccessException e) { throw new DAOException("Error inserting woonplaatsen", e); } }