Example usage for java.sql PreparedStatement setTimestamp

List of usage examples for java.sql PreparedStatement setTimestamp

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setTimestamp

Introduction

In this page you can find the example usage for java.sql PreparedStatement setTimestamp.

Prototype

void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

From source file:org.apache.lucene.store.jdbc.JdbcDirectory.java

/**
 * Delets all the file entries that are marked to be deleted, and they were marked
 * "delta" time ago (base on database time, if possible by dialect).
 *///w  w  w  .j a  va2 s .  co  m
public void deleteMarkDeleted(long delta) throws IOException {
    long currentTime = System.currentTimeMillis();
    if (dialect.supportsCurrentTimestampSelection()) {
        String timestampSelectString = dialect.getCurrentTimestampSelectString();
        if (dialect.isCurrentTimestampSelectStringCallable()) {
            currentTime = ((Long) jdbcTemplate.executeCallable(timestampSelectString,
                    new JdbcTemplate.CallableStatementCallback() {
                        public void fillCallableStatement(CallableStatement cs) throws Exception {
                            cs.registerOutParameter(1, java.sql.Types.TIMESTAMP);
                        }

                        public Object readCallableData(CallableStatement cs) throws Exception {
                            Timestamp timestamp = cs.getTimestamp(1);
                            return new Long(timestamp.getTime());
                        }
                    })).longValue();
        } else {
            currentTime = ((Long) jdbcTemplate.executeSelect(timestampSelectString,
                    new JdbcTemplate.ExecuteSelectCallback() {
                        public void fillPrepareStatement(PreparedStatement ps) throws Exception {
                            // nothing to do here
                        }

                        public Object execute(ResultSet rs) throws Exception {
                            rs.next();
                            Timestamp timestamp = rs.getTimestamp(1);
                            return new Long(timestamp.getTime());
                        }
                    })).longValue();
        }
    }
    final long deleteBefore = currentTime - delta;
    jdbcTemplate.executeUpdate(table.sqlDeletaMarkDeleteByDelta(),
            new JdbcTemplate.PrepateStatementAwareCallback() {
                public void fillPrepareStatement(PreparedStatement ps) throws Exception {
                    ps.setBoolean(1, true);
                    ps.setTimestamp(2, new Timestamp(deleteBefore));
                }
            });
}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public User createUser(User user) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statementUser = null;
    PreparedStatement statementAccount = null;
    try {/* w w w.  j  ava  2 s  .  com*/
        statementUser = connection.prepareStatement(CREATE_USER_STATEMENT);
        connection.setAutoCommit(false);

        statementUser.setString(1, user.getUserName());
        statementUser.setString(2, user.getEmail());
        statementUser.setString(3, user.getPassword());
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(user.getOpen_date().getTime());
        statementUser.setTimestamp(4, sqlDate);
        statementUser.setInt(5, user.getReceiveNewsletter());
        statementUser.setInt(6, user.getConfirmEmailFlag());
        statementUser.setString(7, user.getConfirmEmailRandom());
        java.sql.Timestamp sqlDate1 = new java.sql.Timestamp(user.getConfirmEmailDate().getTime());
        statementUser.setTimestamp(8, sqlDate1);
        statementUser.execute();

        ResultSet rs = statementUser.getGeneratedKeys();
        if (rs.next()) {
            int id = rs.getInt(1);
            user.setId(id);
        }
        statementAccount = connection.prepareStatement(CREATE_ACCOUNT_STATEMENT);
        statementAccount.setInt(1, user.getId());
        statementAccount.setLong(2, 0L);
        statementAccount.execute();
        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            connection.rollback();
        }
        throw e;
    } finally {
        if (statementUser != null) {
            statementUser.close();
        }
        if (statementAccount != null) {
            statementAccount.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
    return user;
}

From source file:com.adanac.module.blog.dao.MessageDao.java

public Integer save(final String visitorIp, final Date createDate, final String message, final String username,
        final Integer referenceMessageId) {
    return execute(new TransactionalOperation<Integer>() {
        @Override//  w w  w  .jav  a  2  s. c om
        public Integer doInConnection(Connection connection) {
            try {
                PreparedStatement statement = null;
                if (referenceMessageId == null) {
                    statement = connection.prepareStatement(
                            "insert into messages (visitor_ip,city,message,"
                                    + "create_date,username) values (?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                } else {
                    statement = connection.prepareStatement(
                            "insert into messages (visitor_ip,city,message,"
                                    + "create_date,username,reference_message_id) values (?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                }
                statement.setString(1, visitorIp);
                statement.setString(2,
                        Configuration.isProductEnv() ? HttpApiHelper.getCity(visitorIp) : "?");
                statement.setString(3, message);
                Date finalCommentDate = createDate;
                if (createDate == null) {
                    finalCommentDate = new Date();
                }
                statement.setTimestamp(4, new Timestamp(finalCommentDate.getTime()));
                statement.setString(5, username);
                if (referenceMessageId != null) {
                    statement.setInt(6, referenceMessageId);
                }
                int result = statement.executeUpdate();
                if (result > 0) {
                    ResultSet resultSet = statement.getGeneratedKeys();
                    if (resultSet.next()) {
                        return resultSet.getInt(1);
                    }
                }
            } catch (SQLException e) {
                error("save messages failed ...", e);
            }
            return null;
        }
    });
}

From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java

/**
 * /*from  ww  w.j av a 2 s . c om*/
 */
public void buildColRels() {
    int cnt = 0;
    try {
        Calendar cal = Calendar.getInstance();
        Timestamp ts = new Timestamp(cal.getTime().getTime());

        String common = "TimestampCreated, Version, CreatedByAgentID";
        String rlStr = String.format(
                "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)",
                common);
        PreparedStatement rlStmt = dbConn.prepareStatement(rlStr);

        String sql = "SELECT c1.CollectionObjectID, c2.CollectionObjectID FROM collectionobject AS c1 "
                + "Inner Join collectionobject AS c2 ON c1.FieldNumber = c2.FieldNumber WHERE c1.CollectionID =  4 AND c2.CollectionID <>  4";
        Statement stmt = dbConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int id1 = rs.getInt(1);
            int id2 = rs.getInt(2);

            rlStmt.setInt(1, 1); // ColRelType
            rlStmt.setInt(2, id1);
            rlStmt.setInt(3, id2);
            rlStmt.setTimestamp(4, ts);
            rlStmt.setInt(5, 1);
            rlStmt.setInt(6, 1);
            rlStmt.executeUpdate();

            cnt++;
            if (cnt % 1000 == 0) {
                System.out.println("Col Obj Rel: " + cnt);
            }

        }

        rs.close();
        rlStmt.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public void updateUser(User user) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    try {/* w ww.  j  a va2  s .  com*/
        statement = connection.prepareStatement(UPDATE_USER_STATEMENT);
        connection.setAutoCommit(true);
        statement.setString(1, user.getEmail());
        statement.setString(2, user.getPassword());
        statement.setInt(3, user.getReceiveNewsletter());
        statement.setInt(4, user.getConfirmEmailFlag());
        statement.setString(5, user.getConfirmEmailRandom());
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(user.getConfirmEmailDate().getTime());
        statement.setTimestamp(6, sqlDate);
        statement.setInt(7, user.getId());
        statement.execute();
    } catch (SQLException e) {
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

From source file:com.uber.stream.kafka.chaperone.collector.reporter.DbAuditReporter.java

/**
 * metrics is JSON string, that cannot use 'INSERT ... ON DUPLICATE KEY UPDATE'
 * but test shows that the rows have very good time locality, thus mysql cache helps
 * to de select-check-update operation./*w w  w.j  a v  a  2  s. co  m*/
 */
private void addOrUpdateRecord(PreparedStatement selectStmt, PreparedStatement updateStmt,
        PreparedStatement insertStmt, Map<Long, Map<String, TimeBucket>> buffer) throws SQLException {
    for (Map<String, TimeBucket> buckets : buffer.values()) {
        for (TimeBucket bucket : buckets.values()) {
            if (bucket.count == 0) {
                continue;
            }

            // do select-check-update for
            long beginTsInMs = TimeUnit.SECONDS.toMillis(bucket.timeBeginInSec);
            long endTsInMs = TimeUnit.SECONDS.toMillis(bucket.timeEndInSec);

            Timestamp beginDbTs = new Timestamp(beginTsInMs);
            Timestamp endDbTs = new Timestamp(endTsInMs);

            // search by primary key, only one can return if existing
            selectStmt.setTimestamp(1, beginDbTs);
            selectStmt.setTimestamp(2, endDbTs);
            selectStmt.setString(3, bucket.topicName);
            selectStmt.setString(4, bucket.hostName);
            selectStmt.setString(5, bucket.datacenter);
            selectStmt.setString(6, bucket.tier);
            ResultSet rs = null;

            try {
                rs = selectStmt.executeQuery();
                if (rs.next()) {
                    String metricsInStr = rs.getString("metrics");
                    JSONObject oldMetricsInJSON = JSON.parseObject(metricsInStr);
                    long count = oldMetricsInJSON.getLong("count");
                    double meanLatency = oldMetricsInJSON.getDouble("mean");
                    double p95Latency = oldMetricsInJSON.getDouble("mean95");

                    // be back-compatible with old version AuditMsg
                    long totalBytes = oldMetricsInJSON.getLongValue("totalBytes");
                    long noTimeCount = oldMetricsInJSON.getLongValue("noTimeCount");
                    long malformedCount = oldMetricsInJSON.getLongValue("malformedCount");
                    double p99Latency = oldMetricsInJSON.getDoubleValue("mean99");
                    double maxLatency = oldMetricsInJSON.getDoubleValue("maxLatency");

                    JSONObject newMetricsInJSON = new JSONObject();
                    newMetricsInJSON.put("totalBytes", totalBytes + bucket.totalBytes);
                    newMetricsInJSON.put("count", count + bucket.count);
                    newMetricsInJSON.put("noTimeCount", noTimeCount + bucket.noTimeCount);
                    newMetricsInJSON.put("malformedCount", malformedCount + bucket.malformedCount);

                    newMetricsInJSON.put("mean",
                            (meanLatency * count + bucket.totalLatency) / (count + bucket.count));
                    newMetricsInJSON.put("mean95", Math.max(p95Latency, bucket.latency95));
                    newMetricsInJSON.put("mean99", Math.max(p99Latency, bucket.latency99));
                    newMetricsInJSON.put("maxLatency", Math.max(maxLatency, bucket.maxLatency));

                    logger.debug(
                            "Update existing bucket with beginTs={}, endTs={}, topicName={}, hostName={}, "
                                    + "tier={}, oldMetrics={}, newMetrics={}",
                            beginDbTs, endDbTs, bucket.topicName, bucket.hostName, bucket.tier,
                            oldMetricsInJSON.toString(), newMetricsInJSON.toString());

                    updateStmt.setString(1, newMetricsInJSON.toString());
                    updateStmt.setTimestamp(2, beginDbTs);
                    updateStmt.setTimestamp(3, endDbTs);
                    updateStmt.setString(4, bucket.topicName);
                    updateStmt.setString(5, bucket.hostName);
                    updateStmt.setString(6, bucket.datacenter);
                    updateStmt.setString(7, bucket.tier);
                    updateStmt.executeUpdate();
                    UPDATED_RECORDS_COUNTER.mark();
                } else {
                    insertStmt.setTimestamp(1, beginDbTs);
                    insertStmt.setTimestamp(2, endDbTs);
                    insertStmt.setString(3, bucket.topicName);
                    insertStmt.setString(4, bucket.getMetricsAsJSON());
                    insertStmt.setString(5, bucket.hostName);
                    insertStmt.setString(6, bucket.datacenter);
                    insertStmt.setString(7, bucket.tier);
                    insertStmt.executeUpdate();
                    INSERTED_RECORDS_COUNTER.mark();
                }

                if (beginTsInMs > latestTSSeenLastInsert) {
                    latestTSSeenLastInsert = beginTsInMs;
                }
                if (beginTsInMs < earliestTSSeenLastInsert) {
                    earliestTSSeenLastInsert = beginTsInMs;
                }
                REPORTED_COUNTER.mark();
            } finally {
                closeResultset(rs);
            }
        }
    }
}

From source file:com.mtgi.analytics.JdbcBehaviorEventPersisterImpl.java

public void persist(final Queue<BehaviorEvent> events) {

    getJdbcTemplate().execute(new ConnectionCallback() {

        public Object doInConnection(Connection con) throws SQLException, DataAccessException {

            //if this connection is behavior tracking, suspend tracking.
            //we don't generate more events while persisting.
            BehaviorTrackingConnectionProxy bt = null;
            for (Connection c = con; bt == null
                    && c instanceof ConnectionProxy; c = ((ConnectionProxy) c).getTargetConnection()) {
                if (c instanceof BehaviorTrackingConnectionProxy) {
                    bt = (BehaviorTrackingConnectionProxy) c;
                    bt.suspendTracking();
                }//w  w  w. j  ava2 s. c  om
            }

            try {
                boolean doBatch = supportsBatchUpdates(con);
                EventDataElementSerializer dataSerializer = new EventDataElementSerializer(xmlFactory);

                PreparedStatement[] idStmt = { null };
                PreparedStatement insert = con.prepareStatement(insertSql);
                try {

                    //keep track of statements added to the batch so that we can time our
                    //flushes.
                    int batchCount = 0;

                    for (BehaviorEvent next : events) {

                        //event may already have an ID assigned if any
                        //of its child events has been persisted.
                        assignIds(next, con, idStmt);

                        //populate identifying information for the event into the insert statement.
                        insert.setLong(1, (Long) next.getId());

                        BehaviorEvent parent = next.getParent();
                        nullSafeSet(insert, 2, parent == null ? null : parent.getId(), Types.BIGINT);

                        insert.setString(3, next.getApplication());
                        insert.setString(4, next.getType());
                        insert.setString(5, next.getName());
                        insert.setTimestamp(6, new java.sql.Timestamp(next.getStart().getTime()));
                        insert.setLong(7, next.getDurationNs());

                        //set optional context information on the event.
                        nullSafeSet(insert, 8, next.getUserId(), Types.VARCHAR);
                        nullSafeSet(insert, 9, next.getSessionId(), Types.VARCHAR);
                        nullSafeSet(insert, 10, next.getError(), Types.VARCHAR);

                        //convert event data to XML
                        String data = dataSerializer.serialize(next.getData(), true);
                        nullSafeSet(insert, 11, data, Types.VARCHAR);

                        if (doBatch) {
                            insert.addBatch();
                            if (++batchCount >= batchSize) {
                                insert.executeBatch();
                                batchCount = 0;
                            }
                        } else {
                            insert.executeUpdate();
                        }
                    }

                    //flush any lingering batch inserts through to the server.
                    if (batchCount > 0)
                        insert.executeBatch();

                } finally {
                    closeStatement(insert);
                    closeStatement(idStmt[0]);
                }

                return null;

            } finally {
                if (bt != null)
                    bt.resumeTracking();
            }
        }

    });
}

From source file:com.concursive.connect.web.modules.documents.dao.FileItemList.java

/**
 * Description of the Method/*from  ww  w .j  a v a2 s.c  o m*/
 *
 * @param pst Description of Parameter
 * @return Description of the Returned Value
 * @throws SQLException Description of Exception
 */
private int prepareFilter(PreparedStatement pst) throws SQLException {
    int i = 0;
    if (linkModuleId > -1) {
        pst.setInt(++i, linkModuleId);
    }
    if (linkItemId > -1) {
        pst.setInt(++i, linkItemId);
    }
    if (folderId > -1) {
        pst.setInt(++i, folderId);
    }
    if (owner != -1) {
        pst.setInt(++i, owner);
    }
    if (alertRangeStart != null) {
        pst.setTimestamp(++i, alertRangeStart);
    }
    if (alertRangeEnd != null) {
        pst.setTimestamp(++i, alertRangeEnd);
    }
    if (forProjectUser > -1) {
        pst.setInt(++i, forProjectUser);
        pst.setBoolean(++i, true);
        pst.setBoolean(++i, true);
    }
    if (defaultFile != Constants.UNDEFINED) {
        pst.setBoolean(++i, defaultFile == Constants.TRUE);
    }
    if (enabled != Constants.UNDEFINED) {
        pst.setBoolean(++i, enabled == Constants.TRUE);
    }
    if (filename != null) {
        pst.setString(++i, filename);
    }
    if (ignoreId > -1) {
        pst.setInt(++i, ignoreId);
    }
    if (featuredFilesOnly != Constants.UNDEFINED) {
        pst.setBoolean(++i, featuredFilesOnly == Constants.TRUE);
    }
    if (publicProjectFiles != Constants.UNDEFINED) {
        pst.setBoolean(++i, true);
    }
    if (projectCategoryId != -1) {
        pst.setInt(++i, projectCategoryId);
    }
    if (modifiedYearMonth != null) {
        pst.setTimestamp(++i, startOfCurrentMonth);
        pst.setTimestamp(++i, startOfNextMonth);
    }
    return i;
}

From source file:com.agiletec.plugins.jpcontentfeedback.aps.system.services.contentfeedback.comment.CommentDAO.java

protected int buildStatement(ICommentSearchBean searchBean, PreparedStatement stat) throws SQLException {
    int pos = 1;/*from w  w w.  j a v  a  2  s . c o  m*/
    if (searchBean != null) {
        String username = searchBean.getUsername();
        if (username != null && username.length() > 0) {
            stat.setString(pos++, username);
        }
        int status = searchBean.getStatus();
        if (status != 0) {
            stat.setInt(pos++, status);
        }
        String comment = searchBean.getComment();
        if (comment != null && comment.length() > 0) {
            stat.setString(pos++, this.searchLikeString(comment));
        }

        Date fromDate = searchBean.getCreationFROMDate();
        if (fromDate != null) {
            Calendar data = new GregorianCalendar();
            data.setTime(fromDate);
            data.set(GregorianCalendar.MILLISECOND, 0);
            data.set(GregorianCalendar.SECOND, 0);
            data.set(GregorianCalendar.MINUTE, 0);
            data.set(GregorianCalendar.HOUR, 0);
            stat.setTimestamp(pos++, new Timestamp(data.getTimeInMillis()));
        }
        Date toDate = searchBean.getCreationTODate();
        if (toDate != null) {
            Calendar data = new GregorianCalendar();
            data.setTime(toDate);
            data.set(GregorianCalendar.MILLISECOND, 999);
            data.set(GregorianCalendar.SECOND, 59);
            data.set(GregorianCalendar.MINUTE, 59);
            data.set(GregorianCalendar.HOUR_OF_DAY, 23);
            stat.setTimestamp(pos++, new Timestamp(data.getTimeInMillis()));
        }

        String contentId = searchBean.getContentId();
        if (contentId != null && contentId.length() > 0) {
            stat.setString(pos++, contentId);
        }

    }
    return pos;
}

From source file:at.bestsolution.persistence.java.Util.java

public static void setValue(PreparedStatement pstmt, int parameterIndex, TypedValue value) throws SQLException {
    if (value.value == null) {
        int sqlType;
        switch (value.type) {
        case INT:
            sqlType = Types.INTEGER;
            break;
        case DOUBLE:
            sqlType = Types.DECIMAL;
            break;
        case FLOAT:
            sqlType = Types.FLOAT;
            break;
        case BOOLEAN:
            sqlType = Types.BOOLEAN;
            break;
        case LONG:
            sqlType = Types.BIGINT;
            break;
        case STRING:
            sqlType = Types.VARCHAR;
            break;
        case BLOB:
            sqlType = Types.BLOB;
            break;
        case CLOB:
            sqlType = Types.CLOB;
            break;
        case TIMESTAMP:
            sqlType = Types.TIMESTAMP;
            break;
        default://from ww  w .ja  va2 s .  com
            sqlType = Types.OTHER;
            break;
        }
        pstmt.setNull(parameterIndex, sqlType);
    } else {
        switch (value.type) {
        case INT:
            pstmt.setInt(parameterIndex, ((Number) value.value).intValue());
            break;
        case DOUBLE:
            pstmt.setDouble(parameterIndex, ((Number) value.value).doubleValue());
            break;
        case FLOAT:
            pstmt.setDouble(parameterIndex, ((Number) value.value).doubleValue());
            break;
        case BOOLEAN:
            pstmt.setBoolean(parameterIndex, Boolean.TRUE.equals(value.value));
            break;
        case LONG:
            pstmt.setLong(parameterIndex, ((Number) value.value).longValue());
            break;
        case STRING:
            pstmt.setString(parameterIndex, (String) value.value);
            break;
        case TIMESTAMP:
            if (value.value instanceof Timestamp) {
                pstmt.setTimestamp(parameterIndex, (Timestamp) value.value);
            } else {
                pstmt.setTimestamp(parameterIndex, new Timestamp(((Date) value.value).getTime()));
            }
            break;
        case UNKNOWN:
            pstmt.setObject(parameterIndex, value.value);
            break;
        default:
            throw new IllegalStateException("Unknown type");
        }
    }
}