Java Code Examples for java.sql.PreparedStatement#setTimestamp()

/**
 * 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
    }
}
 

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 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();
}
 

/**
 * 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;
}
 

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);
  }
}
 

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 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);
        }
    }
}
 

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();
}
 

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);
	}
}
 

@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 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();
  }
}
 

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);
    }
}
 

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 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 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;		
}
 

@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;
        }
    }
}
 

public void setOrderTime(int i, PreparedStatement ps) throws SQLException  {
  ps.setTimestamp(i,orderTime);
}
 

/**
 *
 *
 * @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;
}