下面列出了java.sql.PreparedStatement#setTimestamp ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Documents a record in the database for the specified class indicating it has been executed.
* @param upgradeClass the name of the upgrade class
* @param startTime the time (in millis) of the execution
* @param endTime the time (in millis) the execution completed
* @throws SQLException a SQLException
* @since 1.2.0
*/
public void installUpgrade(final Class<? extends UpgradeItem> upgradeClass, final long startTime, final long endTime) throws SQLException {
PreparedStatement statement = null;
try {
statement = connection.prepareStatement("INSERT INTO \"INSTALLEDUPGRADES\" (\"UPGRADECLASS\", \"STARTTIME\", \"ENDTIME\") VALUES (?, ?, ?)");
statement.setString(1, upgradeClass.getCanonicalName());
statement.setTimestamp(2, new Timestamp(startTime));
statement.setTimestamp(3, new Timestamp(endTime));
statement.executeUpdate();
connection.commit();
LOGGER.debug("Added: " + upgradeClass.getCanonicalName() + " to UpgradeMetaProcessor table (Starttime: " + startTime + "; Endtime: " + endTime + ")");
} finally {
DbUtil.close(statement);
//DbUtil.close(connection); // do not close connection
}
}
/**
* Updates the end_date and num_links generated of the subjob.
*
* @param jobId the job identification.
* @param subjobId the subjob identification.
* @return true if the data have been updated correctly
* in the DDBB. False otherwise.
* @since 1.0
*/
public boolean updateSubjobEndDate(final int jobId, final int subjobId, final int numLinks){
//Update end_date, num_links of subjob
try {
PreparedStatement preparedStatement = null;
preparedStatement = conn.prepareStatement("UPDATE linksdiscovery_subjob_instances SET end_date = ?, num_links = ? WHERE job_id = ? AND subjob_id = ?");
// parameters start with 1
final java.util.Date today = new java.util.Date();
final java.sql.Timestamp todaySQL = new java.sql.Timestamp(today.getTime());
preparedStatement.setTimestamp(1, todaySQL);
preparedStatement.setInt(2, numLinks);
preparedStatement.setInt(3, jobId);
preparedStatement.setInt(4, subjobId);
preparedStatement.executeUpdate();
} catch (SQLException exception) {
LOGGER.error(MessageCatalog._00024_DATA_ACCESS_FAILURE, exception);
return false;
}
return true;
}
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());
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();
}
public static void prepareReportingInsert(PreparedStatement ps,
String email,
int dashId,
int deviceId,
short pin,
PinType pinType,
long ts,
double value) throws SQLException {
ps.setString(1, email);
ps.setInt(2, dashId);
ps.setInt(3, deviceId);
ps.setShort(4, pin);
ps.setInt(5, pinType.ordinal());
ps.setTimestamp(6, new Timestamp(ts), DateTimeUtils.UTC_CALENDAR);
ps.setDouble(7, value);
}
private void setMofidicationDate(FileEntry fileEntry, Date modificationDate) throws SQLException {
PreparedStatement statement = null;
try {
statement = testDataSource.getDataSource()
.getConnection()
.prepareStatement(MessageFormat.format(UPDATE_MODIFICATION_TIME, FileService.DEFAULT_TABLE_NAME));
statement.setTimestamp(1, new java.sql.Timestamp(modificationDate.getTime()));
statement.setString(2, fileEntry.getId());
statement.executeUpdate();
} finally {
JdbcUtil.closeQuietly(statement);
}
}
protected int insertToTableWithDefaultId(PreparedStatement stmt, int cid, int sid, int qty,
String status, Timestamp time, BigDecimal ask, int tid, boolean isPut) throws SQLException {
Log.getLogWriter().info("gemfirexd - " + (isPut ? "Putting" : "inserting") + " into trade.sellorders with " +
"CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
",TIME:"+ time + ",ASK:" + ask + ",TID:" + tid);
stmt.setInt(1, cid);
stmt.setInt(2, sid);
stmt.setInt(3, qty);
stmt.setBigDecimal(4, ask);
if (testworkaroundFor51519) stmt.setTimestamp(5, time, getCal());
else stmt.setTimestamp(5, time);
if (useDefaultValue) {
stmt.setInt(6, tid);
} else {
stmt.setString(6, status);
stmt.setInt(7, tid);
}
int rowCount = stmt.executeUpdate();
Log.getLogWriter().info("gemfirexd - " + (isPut ? "Put " : "inserted ") + rowCount + " rows into trade.sellorders " +
"CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
",TIME:"+ time + ",ASK:" + ask + ",TID:" + tid);
SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
if (warning != null) {
SQLHelper.printSQLWarning(warning);
}
return rowCount;
}
private long createRuntimeJobExecutionEntry(final JobInstance jobInstance, final Properties jobParameters, final BatchStatus batchStatus,
final Timestamp timestamp) {
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
conn = getConnection();
statement = conn.prepareStatement(dictionary.getCreateJobExecution(), Statement.RETURN_GENERATED_KEYS);
statement.setLong(1, jobInstance.getInstanceId());
statement.setTimestamp(2, timestamp);
statement.setTimestamp(3, timestamp);
statement.setString(4, batchStatus.name());
String propVal = PropertyHelper.propertiesToString(jobParameters);
statement.setObject(5, propVal != null ? propVal.getBytes() : null);
statement.executeUpdate();
if (!conn.getAutoCommit()) {
conn.commit();
}
rs = statement.getGeneratedKeys();
if (rs.next()) {
return rs.getLong(1);
}
return -1;
} catch (final SQLException e) {
throw new PersistenceException(e);
} finally {
cleanupConnection(conn, rs, statement);
}
}
private void updateData(String tableName, int startIndex, int endIndex) throws SQLException {
Connection connection = TestUtil.getConnection();
PreparedStatement ps = connection.prepareStatement("UPDATE " + tableName + " set blobField=?, charField=?," +
"charForBitData=?, clobField=?, dateField=?, decimalField=?, doubleField=?, floatField=?, longVarcharForBitDataField=?, numericField=?," +
"realField=?, smallIntField=?, timeField=?, timestampField=?, varcharField=?, varcharForBitData=?, xmlField=xmlparse(document cast (? as clob) PRESERVE WHITESPACE) where bigIntegerField=?");
for (int i = startIndex; i < endIndex; i++) {
int lessThan10 = i % 10;
ps.setBlob(1,new ByteArrayInputStream(new byte[]{(byte)i,(byte)i,(byte)i,(byte)i}));
ps.setString(2, ""+lessThan10);
ps.setBytes(3, ("" + lessThan10).getBytes());
ps.setClob(4, new StringReader("UPDATE CLOB " + i));
ps.setDate(5, new Date(System.currentTimeMillis()));
ps.setBigDecimal(6, new BigDecimal(lessThan10 + .8));
ps.setDouble(7, i + .88);
ps.setFloat(8, i + .9f);
ps.setBytes(9, ("B" + lessThan10).getBytes());
ps.setBigDecimal(10, new BigDecimal(i));
ps.setFloat(11, lessThan10 * 1111);
ps.setShort(12, (short)i);
ps.setTime(13, new Time(System.currentTimeMillis()));
ps.setTimestamp(14, new Timestamp(System.currentTimeMillis()));
ps.setString(15, "BY" + lessThan10);
ps.setBytes(16, ("" + lessThan10).getBytes());
ps.setClob(17, new StringReader("<xml><sometag>UPDATE XML CLOB " + i + "</sometag></xml>"));
ps.setLong(18, i);
ps.execute();
}
}
public static void writeTimestamp(Timestamp val, int paramIdx, int sqlType,
PreparedStatement s) throws SQLException {
if (null == val) {
s.setNull(paramIdx, sqlType);
} else {
s.setTimestamp(paramIdx, val);
}
}
@Override
public void getField(final PreparedStatement ps, final int idx) throws SQLException
{
if (value == null)
ps.setNull(idx, Types.TIMESTAMP);
else
ps.setTimestamp(idx, value);
}
private void clearOldSubscribers(Date date, Connection conn) {
PreparedStatement stat = null;
try {
stat = conn.prepareStatement(DELETE_OLD_SUBSCRIBERS);
stat.setTimestamp(1, new Timestamp(date.getTime()));
stat.executeUpdate();
} catch (Throwable t) {
processDaoException(t, "Error in clear old tokens", "clearOldSubscribers");
} finally {
closeDaoResources(null, stat);
}
}
private void checkTime(long time) throws Throwable {
PreparedStatement pstmt = con.prepareStatement("insert into #testTimestamp values (?)");
java.sql.Timestamp ts = new java.sql.Timestamp(time);
pstmt.setTimestamp(1, ts);
pstmt.executeUpdate();
pstmt.close();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from #testTimestamp");
rs.next();
java.sql.Timestamp tsres = rs.getTimestamp(1);
assertTrue(ts.equals(tsres));
stmt.executeUpdate("truncate table #testTimestamp");
stmt.close();
}
protected void savePageMetadata(String pageCode, PageMetadata pageMetadata, boolean isAdd, String tableName, Connection conn)
throws ApsSystemException {
if (pageMetadata != null) {
PreparedStatement stat = null;
try {
StringBuilder query = new StringBuilder(isAdd ? ADD_PAGE_METADATA_START : UPDATE_PAGE_METADATA_START);
query.append(tableName).append(isAdd ? ADD_PAGE_METADATA_END : UPDATE_PAGE_METADATA_END);
stat = conn.prepareStatement(query.toString());
int index = 1;
if (isAdd) {
stat.setString(index++, pageCode);
}
stat.setString(index++, pageMetadata.getGroup());
stat.setString(index++, pageMetadata.getTitles().toXml());
stat.setString(index++, pageMetadata.getModel().getCode());
if (pageMetadata.isShowable()) {
stat.setInt(index++, 1);
} else {
stat.setInt(index++, 0);
}
String extraConfig = this.getExtraConfig(pageMetadata);
stat.setString(index++, extraConfig);
Date updatedAt = pageMetadata.getUpdatedAt() != null ? pageMetadata.getUpdatedAt() : new Date();
stat.setTimestamp(index++, updatedAt != null ? new java.sql.Timestamp(updatedAt.getTime()) : null);
if (!isAdd) {
stat.setString(index++, pageCode);
}
stat.executeUpdate();
} catch (Throwable t) {
_logger.error("Error while saving the page metadata record for table {}", tableName, t);
throw new RuntimeException("Error while saving the page metadata record for table " + tableName, t);
} finally {
closeDaoResources(null, stat);
}
}
}
protected void addBatchInsert(PreparedStatement stmt, int oid, int cid, int sid, int qty,
String status, Timestamp time, BigDecimal bid, int tid, boolean isPut) throws SQLException {
JSONObject json = new JSONObject();
String jsonLog ="";
if (SQLTest.hasJSON && ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {
json = getJSONObject(oid,cid,sid,qty,status,time,bid,tid);
jsonLog = ",JSON_DETAILS: " +json.toJSONString();
}
Log.getLogWriter().info( (SQLHelper.isDerbyConn(stmt.getConnection())? "Derby - " :"gemfirexd - " ) + (isPut ? "putting " : "inserting ") + " into trade.buyorders with data OID:" + oid +
",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
stmt.setInt(1, oid);
stmt.setInt(2, cid);
stmt.setInt(3, sid);
stmt.setInt(4, qty);
stmt.setBigDecimal(5, bid);
stmt.setTimestamp(6, time);
stmt.setString(7, status);
stmt.setInt(8, tid);
if (SQLTest.hasJSON && ! SQLHelper.isDerbyConn(stmt.getConnection()) ) { Clob jsonClob = stmt.getConnection().createClob();
jsonClob.setString(1, json.toJSONString());
stmt.setClob(9, jsonClob); }
stmt.addBatch();
}
protected int updateToTableTidListTx(PreparedStatement stmt, int cid,
int sid, int oid, BigDecimal ask, int qty, Timestamp orderTime,
int whichUpdate, int tid, int tid2) throws SQLException {
int rowCount = 0;
switch (whichUpdate) {
case 0:
//"update trade.sellorders set ask = ? where oid= ? and status = 'open' and tid=? ",
Log.getLogWriter().info("updating sellorders table ask to " + ask
+" for oid: " + oid + " and status = 'open' and tid=" + tid);
stmt.setBigDecimal(1, ask);
stmt.setInt(2, oid);
stmt.setInt(3, tid);
rowCount = stmt.executeUpdate();
break;
case 1:
//"update trade.sellorders set ask = ? , qty=? where oid= ? and ask <? and status = 'open' and tid =? ",
Log.getLogWriter().info("updating sellorders table ask to " + ask +
" and qty to " + qty + " for oid: " + oid +
" and ask < " + ask + " and status = 'open' and tid=" + tid);
stmt.setBigDecimal(1, ask);
stmt.setInt(2, qty);
stmt.setInt(3, oid);
stmt.setBigDecimal(4, ask);
stmt.setInt(5, tid);
rowCount = stmt.executeUpdate();
break;
case 2:
// "update trade.sellorders set status = 'filled' where sid = ? and ask<? and status = 'open' and (tid =? or tid=? )",
Log.getLogWriter().info("updating sellorders table status to 'filled' " +
"for sid: " + sid + " and ask < " + ask + " and status = 'open' " +
"and (tid=" + tid + " or tid=" + tid2 + ")" );
stmt.setInt(1, sid);
stmt.setBigDecimal(2, ask);
stmt.setInt(3, tid);
stmt.setInt(4, tid2);
rowCount = stmt.executeUpdate();
break;
case 3:
// "update trade.sellorders set status = 'cancelled' where order_time <? and status = 'open' and tid =? ",
Log.getLogWriter().info("updating sellorders table status to 'cancelled' " +
"for order_time < '" + orderTime + "' and status = 'open' and tid=" +tid);
stmt.setTimestamp(1, orderTime);
stmt.setInt(2, tid);
rowCount = stmt.executeUpdate();
break;
default:
throw new TestException ("Wrong update sql string here");
}
SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
if (warning != null) {
SQLHelper.printSQLWarning(warning);
}
return rowCount;
}
public void setOrderTime(int i, PreparedStatement ps) throws SQLException {
ps.setTimestamp(i,orderTime);
}
@Override
protected PreparedStatement createDeleteStaleResponsesStmt(final Connection c, final int MAX_ROWS) throws SQLException {
PreparedStatement stmt = c.prepareStatement("delete from COP_RESPONSE where response_timeout < ? and not exists (select * from COP_WAIT w where w.correlation_id = COP_RESPONSE.correlation_id LIMIT " + MAX_ROWS + ")");
stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
return stmt;
}
/**
* Determines how to map the given value to the appropriate JDBC data jdbcType and sets the parameter on the
* provided PreparedStatement
*
* @param stmt the PreparedStatement to set the parameter on
* @param attrName the name of the attribute that the parameter is coming from - for logging purposes
* @param parameterIndex the index of the HiveQL parameter to set
* @param parameterValue the value of the HiveQL parameter to set
* @param jdbcType the JDBC Type of the HiveQL parameter to set
* @throws SQLException if the PreparedStatement throws a SQLException when calling the appropriate setter
*/
protected void setParameter(final PreparedStatement stmt, final String attrName, final int parameterIndex, final String parameterValue, final int jdbcType) throws SQLException {
if (parameterValue == null) {
stmt.setNull(parameterIndex, jdbcType);
} else {
try {
switch (jdbcType) {
case Types.BIT:
case Types.BOOLEAN:
stmt.setBoolean(parameterIndex, Boolean.parseBoolean(parameterValue));
break;
case Types.TINYINT:
stmt.setByte(parameterIndex, Byte.parseByte(parameterValue));
break;
case Types.SMALLINT:
stmt.setShort(parameterIndex, Short.parseShort(parameterValue));
break;
case Types.INTEGER:
stmt.setInt(parameterIndex, Integer.parseInt(parameterValue));
break;
case Types.BIGINT:
stmt.setLong(parameterIndex, Long.parseLong(parameterValue));
break;
case Types.REAL:
stmt.setFloat(parameterIndex, Float.parseFloat(parameterValue));
break;
case Types.FLOAT:
case Types.DOUBLE:
stmt.setDouble(parameterIndex, Double.parseDouble(parameterValue));
break;
case Types.DECIMAL:
case Types.NUMERIC:
stmt.setBigDecimal(parameterIndex, new BigDecimal(parameterValue));
break;
case Types.DATE:
stmt.setDate(parameterIndex, new Date(Long.parseLong(parameterValue)));
break;
case Types.TIME:
stmt.setTime(parameterIndex, new Time(Long.parseLong(parameterValue)));
break;
case Types.TIMESTAMP:
stmt.setTimestamp(parameterIndex, new Timestamp(Long.parseLong(parameterValue)));
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGNVARCHAR:
case Types.LONGVARCHAR:
stmt.setString(parameterIndex, parameterValue);
break;
default:
stmt.setObject(parameterIndex, parameterValue, jdbcType);
break;
}
} catch (SQLException e) {
// Log which attribute/parameter had an error, then rethrow to be handled at the top level
getLogger().error("Error setting parameter {} to value from {} ({})", new Object[]{parameterIndex, attrName, parameterValue}, e);
throw e;
}
}
}
/**
*
*
* @param ps
* @param col
* @param value
*
* @throws SQLException
*/
@Override
public void bindValue(final PreparedStatement ps, final int col, final String value) throws SQLException {
ps.setTimestamp(col, new java.sql.Timestamp(Long.parseLong(value)));
}
/**
* sets the value of a timestamp field in the specified column. <br/><br/>
*
* @param pstmt
* prepared statement
* @param timestamp
* timestamp
* @param calendar
* calendar
* @param pos
* number of column of bytes field.
*/
public PreparedStatement setTimestamp(PreparedStatement pstmt, Timestamp timestamp, GregorianCalendar calendar, int pos) throws SQLException
{
pstmt.setTimestamp(pos, timestamp, calendar);
return pstmt;
}