List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal) throws SQLException;
java.sql.Timestamp value, using the given Calendar object. From source file:com.jagornet.dhcp.db.JdbcIaManager.java
/** * Expire ia./* w w w .ja va2 s . co m*/ * * @param id the id */ protected void expireIA(final Long id) { getJdbcTemplate().update("update identityassoc set state=" + IdentityAssoc.EXPIRED + " where id=?" + " and not exists" + " (select 1 from iaaddress" + " where identityassoc_id=identityassoc.id" + " and validendtime>=?)", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setLong(1, id); java.sql.Timestamp now = new java.sql.Timestamp((new Date()).getTime()); ps.setTimestamp(2, now, Util.GMT_CALENDAR); } }); }
From source file:com.jagornet.dhcp.db.JdbcIaManager.java
/** * Delete expired ia./*www .j a va 2 s . c om*/ * * @param id the id */ protected void deleteExpiredIA(final Long id) { getJdbcTemplate().update( "delete from identityassoc" + " where id=?" + " and not exists (select 1 from iaaddress" + " where identityassoc_id=identityassoc.id" + " and validendtime is not null and validendtime>=?)", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setLong(1, id); java.sql.Timestamp now = new java.sql.Timestamp((new Date()).getTime()); ps.setTimestamp(2, now, Util.GMT_CALENDAR); } }); }
From source file:com.jagornet.dhcp.db.JdbcIaAddressDAO.java
public void create(final IaAddress iaAddr) { /**/*from w ww. j a v a 2 s . co m*/ * Note: see https://issues.apache.org/jira/browse/DERBY-3609 * "Formally, Derby does not support getGeneratedKeys since * DatabaseMetaData.supportsGetGeneratedKeys() returns false. * However, Statement.getGeneratedKeys() is partially implemented, * ... since it will only return a meaningful result when an single * row insert is done with INSERT...VALUES" * * Spring has thus provided a workaround as described here: * http://jira.springframework.org/browse/SPR-5306 */ GeneratedKeyHolder newKey = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement( "insert into iaaddress" + " (ipaddress, starttime, preferredendtime, validendtime," + " state, identityassoc_id)" + " values (?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS); ps.setBytes(1, iaAddr.getIpAddress().getAddress()); java.sql.Timestamp sts = new java.sql.Timestamp(iaAddr.getStartTime().getTime()); ps.setTimestamp(2, sts, Util.GMT_CALENDAR); java.sql.Timestamp pts = new java.sql.Timestamp(iaAddr.getPreferredEndTime().getTime()); ps.setTimestamp(3, pts, Util.GMT_CALENDAR); java.sql.Timestamp vts = new java.sql.Timestamp(iaAddr.getValidEndTime().getTime()); ps.setTimestamp(4, vts, Util.GMT_CALENDAR); ps.setByte(5, iaAddr.getState()); ps.setLong(6, iaAddr.getIdentityAssocId()); return ps; } }, newKey); Number newId = newKey.getKey(); if (newId != null) { iaAddr.setId(newId.longValue()); } }
From source file:com.jagornet.dhcp.db.JdbcIaPrefixDAO.java
public void create(final IaPrefix iaPrefix) { /**/*from w ww. j a v a2 s . c o m*/ * Note: see https://issues.apache.org/jira/browse/DERBY-3609 * "Formally, Derby does not support getGeneratedKeys since * DatabaseMetaData.supportsGetGeneratedKeys() returns false. * However, Statement.getGeneratedKeys() is partially implemented, * ... since it will only return a meaningful result when an single * row insert is done with INSERT...VALUES" * * Spring has thus provided a workaround as described here: * http://jira.springframework.org/browse/SPR-5306 */ GeneratedKeyHolder newKey = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement( "insert into iaprefix" + " (prefixaddress, prefixlength, starttime, preferredendtime," + " validendtime, state, identityassoc_id)" + " values (?, ?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS); ps.setBytes(1, iaPrefix.getIpAddress().getAddress()); ps.setInt(2, iaPrefix.getPrefixLength()); java.sql.Timestamp sts = new java.sql.Timestamp(iaPrefix.getStartTime().getTime()); ps.setTimestamp(3, sts, Util.GMT_CALENDAR); java.sql.Timestamp pts = new java.sql.Timestamp(iaPrefix.getPreferredEndTime().getTime()); ps.setTimestamp(4, pts, Util.GMT_CALENDAR); java.sql.Timestamp vts = new java.sql.Timestamp(iaPrefix.getValidEndTime().getTime()); ps.setTimestamp(5, vts, Util.GMT_CALENDAR); ps.setByte(6, iaPrefix.getState()); ps.setLong(7, iaPrefix.getIdentityAssocId()); return ps; } }, newKey); Number newId = newKey.getKey(); if (newId != null) { iaPrefix.setId(newId.longValue()); } }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testDateTimeTimestampWithCalendar() throws SQLException { Statement stat = conn.createStatement(); stat.execute("create table ts(x timestamp primary key)"); stat.execute("create table t(x time primary key)"); stat.execute("create table d(x date)"); Calendar utcCalendar = new GregorianCalendar(new SimpleTimeZone(0, "Z")); TimeZone old = TimeZone.getDefault(); DateTimeUtils.resetCalendar();//from w ww.j ava 2 s .c o m TimeZone.setDefault(TimeZone.getTimeZone("PST")); try { Timestamp ts1 = Timestamp.valueOf("2010-03-13 18:15:00"); Time t1 = new Time(ts1.getTime()); Date d1 = new Date(ts1.getTime()); // when converted to UTC, this is 03:15, which doesn't actually exist // because of summer time change at that day Timestamp ts2 = Timestamp.valueOf("2010-03-13 19:15:00"); Time t2 = new Time(ts2.getTime()); Date d2 = new Date(ts2.getTime()); PreparedStatement prep; ResultSet rs; prep = conn.prepareStatement("insert into ts values(?)"); prep.setTimestamp(1, ts1, utcCalendar); prep.execute(); prep.setTimestamp(1, ts2, utcCalendar); prep.execute(); prep = conn.prepareStatement("insert into t values(?)"); prep.setTime(1, t1, utcCalendar); prep.execute(); prep.setTime(1, t2, utcCalendar); prep.execute(); prep = conn.prepareStatement("insert into d values(?)"); prep.setDate(1, d1, utcCalendar); prep.execute(); prep.setDate(1, d2, utcCalendar); prep.execute(); rs = stat.executeQuery("select * from ts order by x"); rs.next(); assertEquals("2010-03-14 02:15:00.0", rs.getString(1)); assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp(1, utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString()); assertEquals("2010-03-14 02:15:00.0", rs.getString("x")); assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp("x", utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString()); rs.next(); assertEquals("2010-03-14 03:15:00.0", rs.getString(1)); assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp(1, utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getString("x")); assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp("x", utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString()); rs = stat.executeQuery("select * from t order by x"); rs.next(); assertEquals("02:15:00", rs.getString(1)); assertEquals("18:15:00", rs.getTime(1, utcCalendar).toString()); assertEquals("02:15:00", rs.getTime(1).toString()); assertEquals("02:15:00", rs.getString("x")); assertEquals("18:15:00", rs.getTime("x", utcCalendar).toString()); assertEquals("02:15:00", rs.getTime("x").toString()); rs.next(); assertEquals("03:15:00", rs.getString(1)); assertEquals("19:15:00", rs.getTime(1, utcCalendar).toString()); assertEquals("03:15:00", rs.getTime(1).toString()); assertEquals("03:15:00", rs.getString("x")); assertEquals("19:15:00", rs.getTime("x", utcCalendar).toString()); assertEquals("03:15:00", rs.getTime("x").toString()); rs = stat.executeQuery("select * from d order by x"); rs.next(); assertEquals("2010-03-14", rs.getString(1)); assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate(1).toString()); assertEquals("2010-03-14", rs.getString("x")); assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate("x").toString()); rs.next(); assertEquals("2010-03-14", rs.getString(1)); assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate(1).toString()); assertEquals("2010-03-14", rs.getString("x")); assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate("x").toString()); } finally { TimeZone.setDefault(old); DateTimeUtils.resetCalendar(); } stat.execute("drop table ts"); stat.execute("drop table t"); stat.execute("drop table d"); }
From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java
/** * Insert dhcp lease.//from w w w.j a v a 2 s . co m * * @param lease the lease */ protected void insertDhcpLease(final DhcpLease lease) { getJdbcTemplate().update("insert into dhcplease" + " (ipaddress, duid, iatype, iaid, prefixlen, state," + " starttime, preferredendtime, validendtime," + " ia_options, ipaddr_options)" + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setBytes(1, lease.getIpAddress().getAddress()); ps.setBytes(2, lease.getDuid()); ps.setByte(3, lease.getIatype()); ps.setLong(4, lease.getIaid()); ps.setShort(5, lease.getPrefixLength()); ps.setByte(6, lease.getState()); java.sql.Timestamp sts = new java.sql.Timestamp(lease.getStartTime().getTime()); ps.setTimestamp(7, sts, Util.GMT_CALENDAR); java.sql.Timestamp pts = new java.sql.Timestamp(lease.getPreferredEndTime().getTime()); ps.setTimestamp(8, pts, Util.GMT_CALENDAR); java.sql.Timestamp vts = new java.sql.Timestamp(lease.getValidEndTime().getTime()); ps.setTimestamp(9, vts, Util.GMT_CALENDAR); ps.setBytes(10, encodeOptions(lease.getIaDhcpOptions())); ps.setBytes(11, encodeOptions(lease.getIaAddrDhcpOptions())); } }); }
From source file:com.jagornet.dhcp.db.JdbcLeaseManager.java
public void updateIaAddr(final IaAddress iaAddr) { getJdbcTemplate().update("update dhcplease" + " set state = ?," + ((iaAddr instanceof IaPrefix) ? " prefixlen = ?," : "") + " starttime = ?," + " preferredendtime = ?," + " validendtime = ?" + " where ipaddress = ?", new PreparedStatementSetter() { @Override// w w w.ja v a2 s . com public void setValues(PreparedStatement ps) throws SQLException { int i = 1; ps.setByte(i++, iaAddr.getState()); if (iaAddr instanceof IaPrefix) { ps.setShort(i++, ((IaPrefix) iaAddr).getPrefixLength()); } Date start = iaAddr.getStartTime(); if (start != null) { java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime()); ps.setTimestamp(i++, sts, Util.GMT_CALENDAR); } else { ps.setNull(i++, java.sql.Types.TIMESTAMP); } Date preferred = iaAddr.getPreferredEndTime(); if (preferred != null) { java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime()); ps.setTimestamp(i++, pts, Util.GMT_CALENDAR); } else { ps.setNull(i++, java.sql.Types.TIMESTAMP); } Date valid = iaAddr.getValidEndTime(); if (valid != null) { java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime()); ps.setTimestamp(i++, vts, Util.GMT_CALENDAR); } else { ps.setNull(i++, java.sql.Types.TIMESTAMP); } ps.setBytes(i++, iaAddr.getIpAddress().getAddress()); } }); }
From source file:gobblin.metastore.database.DatabaseJobHistoryStoreV101.java
private void addTaskExecutionInfoToBatch(PreparedStatement upsertStatement, TaskExecutionInfo info) throws SQLException { Preconditions.checkArgument(info.hasTaskId()); Preconditions.checkArgument(info.hasJobId()); int index = 0; upsertStatement.setString(++index, info.getTaskId()); upsertStatement.setString(++index, info.getJobId()); upsertStatement.setTimestamp(++index, info.hasStartTime() ? new Timestamp(info.getStartTime()) : null, getCalendarUTCInstance());//from w w w.ja va 2s . c om upsertStatement.setTimestamp(++index, info.hasEndTime() ? new Timestamp(info.getEndTime()) : null, getCalendarUTCInstance()); upsertStatement.setLong(++index, info.hasDuration() ? info.getDuration() : -1); upsertStatement.setString(++index, info.hasState() ? info.getState().name() : null); upsertStatement.setString(++index, info.hasFailureException() ? info.getFailureException() : null); upsertStatement.setLong(++index, info.hasLowWatermark() ? info.getLowWatermark() : -1); upsertStatement.setLong(++index, info.hasHighWatermark() ? info.getHighWatermark() : -1); upsertStatement.setString(++index, info.hasTable() && info.getTable().hasNamespace() ? info.getTable().getNamespace() : null); upsertStatement.setString(++index, info.hasTable() && info.getTable().hasName() ? info.getTable().getName() : null); upsertStatement.setString(++index, info.hasTable() && info.getTable().hasType() ? info.getTable().getType().name() : null); upsertStatement.addBatch(); }
From source file:at.rocworks.oa4j.logger.dbs.NoSQLJDBC.java
public int storeData(DataList list) { try {/*from ww w . j a v a 2 s . c om*/ Connection conn = dataSourceWrite.getConnection(); if (conn != null) { int i; DataItem item; EventItem event; Object tag; conn.setAutoCommit(false); PreparedStatement stmt; Date t1 = new Date(); stmt = conn.prepareStatement(sqlInsertStmt); for (i = 0; i <= list.getHighWaterMark() && (item = list.getItem(i)) != null; i++) { if (!(item instanceof EventItem)) continue; event = (EventItem) item; ValueItem val = event.getValue(); tag = this.getTagOfDp(event.getDp()); if (tag == null) continue; if (tag instanceof Long) stmt.setLong(1, (Long) tag); else if (tag instanceof String) stmt.setString(1, (String) tag); java.sql.Timestamp ts = new java.sql.Timestamp(event.getTimeMS()); ts.setNanos(event.getNanos()); stmt.setTimestamp(2, ts, cal); Double dval = val.getDouble(); if (dval != null) { stmt.setDouble(3, dval); } else { stmt.setNull(3, Types.DOUBLE); } // value_string stmt.setString(4, val.getString()); // value_timestamp if (val.getTimeMS() != null) stmt.setTimestamp(5, new java.sql.Timestamp(val.getTimeMS()), cal); else stmt.setNull(5, Types.TIMESTAMP); // status, manager, user if (event.hasAttributes()) { stmt.setLong(6, event.getStatus()); stmt.setInt(7, event.getManager()); stmt.setInt(8, event.getUser()); } else { stmt.setNull(6, Types.INTEGER); stmt.setNull(7, Types.INTEGER); stmt.setNull(8, Types.INTEGER); } //JDebug.out.log(Level.FINE, "{0}:{1}/{2} [{3}]", new Object[] {i, element_id.toString(), ts.toString(), item.toString()}); stmt.addBatch(); } try { stmt.executeBatch(); // TODO check result? int[] res = } catch (BatchUpdateException ex) { JDebug.out.log(Level.SEVERE, "Batch exception {0} update count {1}.", new Object[] { ex.getErrorCode(), ex.getUpdateCounts().length }); JDebug.StackTrace(Level.SEVERE, ex); } catch (SQLException ex) { SQLException current = ex; do { JDebug.out.log(Level.SEVERE, "SQL exception {0}.", new Object[] { ex.getErrorCode() }); JDebug.StackTrace(Level.SEVERE, current); } while ((current = current.getNextException()) != null); // for (i = 0; i <= list.getHighWaterMark() && (item = list.getItem(i)) != null; i++) { // JDebug.out.log(Level.INFO, "{0}", item.toJSONObject()); // } } Date t2 = new Date(); stmt.close(); afterInsert(conn); conn.commit(); conn.close(); addServerStats(list.getHighWaterMark(), t2.getTime() - t1.getTime()); return INoSQLInterface.OK; } else { JDebug.StackTrace(Level.SEVERE, "no connection!"); return INoSQLInterface.ERR_REPEATABLE; } } catch (Exception ex) { JDebug.StackTrace(Level.SEVERE, ex); return INoSQLInterface.ERR_REPEATABLE; } }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
public void testDatetimeWithCalendar() throws SQLException { trace("test DATETIME with Calendar"); ResultSet rs;/* ww w . jav a2 s .c o m*/ stat = conn.createStatement(); stat.execute("CREATE TABLE test(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)"); PreparedStatement prep = conn.prepareStatement("INSERT INTO test VALUES(?, ?, ?, ?)"); Calendar regular = Calendar.getInstance(); Calendar other = null; // search a locale that has a _different_ raw offset long testTime = java.sql.Date.valueOf("2001-02-03").getTime(); for (String s : TimeZone.getAvailableIDs()) { TimeZone zone = TimeZone.getTimeZone(s); long rawOffsetDiff = regular.getTimeZone().getRawOffset() - zone.getRawOffset(); // must not be the same timezone (not 0 h and not 24 h difference // as for Pacific/Auckland and Etc/GMT+12) if (rawOffsetDiff != 0 && rawOffsetDiff != 1000 * 60 * 60 * 24) { if (regular.getTimeZone().getOffset(testTime) != zone.getOffset(testTime)) { other = Calendar.getInstance(zone); break; } } } trace("regular offset = " + regular.getTimeZone().getRawOffset() + " other = " + other.getTimeZone().getRawOffset()); prep.setInt(1, 0); prep.setDate(2, null, regular); prep.setTime(3, null, regular); prep.setTimestamp(4, null, regular); prep.execute(); prep.setInt(1, 1); prep.setDate(2, null, other); prep.setTime(3, null, other); prep.setTimestamp(4, null, other); prep.execute(); prep.setInt(1, 2); prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular); prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular); prep.setTimestamp(4, Timestamp.valueOf("2007-08-09 10:11:12.131415"), regular); prep.execute(); prep.setInt(1, 3); prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other); prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other); prep.setTimestamp(4, Timestamp.valueOf("2107-08-09 10:11:12.131415"), other); prep.execute(); prep.setInt(1, 4); prep.setDate(2, java.sql.Date.valueOf("2101-02-03")); prep.setTime(3, java.sql.Time.valueOf("14:05:06")); prep.setTimestamp(4, Timestamp.valueOf("2107-08-09 10:11:12.131415")); prep.execute(); rs = stat.executeQuery("SELECT * FROM test ORDER BY ID"); assertResultSetMeta(rs, 4, new String[] { "ID", "D", "T", "TS" }, new int[] { Types.INTEGER, Types.DATE, Types.TIME, Types.TIMESTAMP }, new int[] { 10, 8, 6, 23 }, new int[] { 0, 0, 0, 10 }); rs.next(); assertEquals(0, rs.getInt(1)); assertTrue(rs.getDate(2, regular) == null && rs.wasNull()); assertTrue(rs.getTime(3, regular) == null && rs.wasNull()); assertTrue(rs.getTimestamp(3, regular) == null && rs.wasNull()); rs.next(); assertEquals(1, rs.getInt(1)); assertTrue(rs.getDate(2, other) == null && rs.wasNull()); assertTrue(rs.getTime(3, other) == null && rs.wasNull()); assertTrue(rs.getTimestamp(3, other) == null && rs.wasNull()); rs.next(); assertEquals(2, rs.getInt(1)); assertEquals("2001-02-03", rs.getDate(2, regular).toString()); assertEquals("04:05:06", rs.getTime(3, regular).toString()); assertFalse(rs.getTime(3, other).toString().equals("04:05:06")); assertEquals("2007-08-09 10:11:12.131415", rs.getTimestamp(4, regular).toString()); assertFalse(rs.getTimestamp(4, other).toString().equals("2007-08-09 10:11:12.131415")); rs.next(); assertEquals(3, rs.getInt("ID")); assertFalse(rs.getTimestamp("TS", regular).toString().equals("2107-08-09 10:11:12.131415")); assertEquals("2107-08-09 10:11:12.131415", rs.getTimestamp("TS", other).toString()); assertFalse(rs.getTime("T", regular).toString().equals("14:05:06")); assertEquals("14:05:06", rs.getTime("T", other).toString()); // checkFalse(rs.getDate(2, regular).toString(), "2101-02-03"); // check(rs.getDate("D", other).toString(), "2101-02-03"); rs.next(); assertEquals(4, rs.getInt("ID")); assertEquals("2107-08-09 10:11:12.131415", rs.getTimestamp("TS").toString()); assertEquals("14:05:06", rs.getTime("T").toString()); assertEquals("2101-02-03", rs.getDate("D").toString()); assertFalse(rs.next()); stat.execute("DROP TABLE test"); }