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:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

protected void deleteNevenadressen(final TypeAdresseerbaarObject objectType,
        final BAGAdresseerbaarObject object) {
    jdbcTemplate.batchUpdate(//from   w ww . j  ava  2s  .c om
            "delete from bag_neven_adres" + " where bag_object_id = ?" + " and bag_object_type = ?"
                    + " and aanduiding_record_correctie = ?" + " and begindatum_tijdvak_geldigheid = ?",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, object.getIdentificatie());
                    ps.setInt(2, objectType.ordinal());
                    ps.setLong(3, object.getAanduidingRecordCorrectie());
                    ps.setTimestamp(4, new Timestamp(object.getBegindatumTijdvakGeldigheid().getTime()));
                }

                @Override
                public int getBatchSize() {
                    return object.getNevenAdressen().size();
                }
            });
}

From source file:nl.nn.adapterframework.jdbc.JdbcFacade.java

protected void applyParameters(PreparedStatement statement, ParameterValueList parameters)
        throws SQLException, SenderException {
    // statement.clearParameters();

    /*/*from   ww  w  . j a v a2s  .  c o  m*/
          // getParameterMetaData() is not supported on the WebSphere java.sql.PreparedStatement implementation.
          int senderParameterCount = parameters.size();
          int statementParameterCount = statement.getParameterMetaData().getParameterCount();
          if (statementParameterCount<senderParameterCount) {
             throw new SenderException(getLogPrefix()+"statement has more ["+statementParameterCount+"] parameters defined than sender ["+senderParameterCount+"]");
          }
    */

    for (int i = 0; i < parameters.size(); i++) {
        ParameterValue pv = parameters.getParameterValue(i);
        String paramType = pv.getDefinition().getType();
        Object value = pv.getValue();
        //      log.debug("applying parameter ["+(i+1)+","+parameters.getParameterValue(i).getDefinition().getName()+"], value["+parameterValue+"]");

        if (Parameter.TYPE_DATE.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.DATE);
            } else {
                statement.setDate(i + 1, new java.sql.Date(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_DATETIME.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIMESTAMP);
            } else {
                statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_TIMESTAMP.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIMESTAMP);
            } else {
                statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_TIME.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIME);
            } else {
                statement.setTime(i + 1, new java.sql.Time(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_XMLDATETIME.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.TIMESTAMP);
            } else {
                statement.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
            }
        } else if (Parameter.TYPE_NUMBER.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.NUMERIC);
            } else {
                statement.setDouble(i + 1, ((Number) value).doubleValue());
            }
        } else if (Parameter.TYPE_INTEGER.equals(paramType)) {
            if (value == null) {
                statement.setNull(i + 1, Types.INTEGER);
            } else {
                statement.setInt(i + 1, (Integer) value);
            }
        } else if (Parameter.TYPE_INPUTSTREAM.equals(paramType)) {
            if (value instanceof FileInputStream) {
                FileInputStream fis = (FileInputStream) value;
                long len = 0;
                try {
                    len = fis.getChannel().size();
                } catch (IOException e) {
                    log.warn(getLogPrefix() + "could not determine file size", e);
                }
                statement.setBinaryStream(i + 1, fis, (int) len);
            } else if (value instanceof ByteArrayInputStream) {
                ByteArrayInputStream bais = (ByteArrayInputStream) value;
                long len = bais.available();
                statement.setBinaryStream(i + 1, bais, (int) len);
            } else {
                throw new SenderException(getLogPrefix() + "unknown inputstream [" + value.getClass()
                        + "] for parameter [" + pv.getDefinition().getName() + "]");
            }
        } else if ("string2bytes".equals(paramType)) {
            statement.setBytes(i + 1, ((String) value).getBytes());
        } else if ("bytes".equals(paramType)) {
            statement.setBytes(i + 1, (byte[]) value);
        } else {
            statement.setString(i + 1, (String) value);
        }
    }
}

From source file:nl.nn.adapterframework.jdbc.JdbcTransactionalStorage.java

public IMessageBrowsingIterator getIterator(Date startTime, Date endTime, boolean forceDescending)
        throws ListenerException {
    Connection conn;//w  w  w.  j  a  v  a 2  s . c  om
    try {
        conn = getConnection();
    } catch (JdbcException e) {
        throw new ListenerException(e);
    }
    try {
        String query;
        if (startTime == null && endTime == null) {
            query = selectListQuery;
        } else {
            query = getSelectListQuery(getDbmsSupport(), startTime, endTime, forceDescending);
        }
        if (log.isDebugEnabled())
            log.debug("preparing selectListQuery [" + query + "]");
        PreparedStatement stmt = conn.prepareStatement(query);
        if (startTime == null && endTime == null) {
            applyStandardParameters(stmt, false, false);
        } else {
            int paramPos = applyStandardParameters(stmt, true, false);
            if (startTime != null) {
                stmt.setTimestamp(paramPos++, new Timestamp(startTime.getTime()));
            }
            if (endTime != null) {
                stmt.setTimestamp(paramPos++, new Timestamp(endTime.getTime()));
            }
        }
        ResultSet rs = stmt.executeQuery();
        return new ResultSetIterator(conn, rs);
    } catch (SQLException e) {
        throw new ListenerException(e);
    }
}

From source file:dk.netarkivet.harvester.datamodel.HarvestDefinitionDBDAO.java

/**
 * Update an existing harvest definition with new info.
 *
 * @param hd/*from   w  w  w  .j a va 2  s.c o  m*/
 *            An updated harvest definition
 * @see HarvestDefinitionDAO#update(HarvestDefinition)
 */
public synchronized void update(HarvestDefinition hd) {
    ArgumentNotValid.checkNotNull(hd, "HarvestDefinition hd");
    if (hd.getOid() == null || !exists(hd.getOid())) {
        final String message = "Cannot update non-existing " + "harvestdefinition '" + hd.getName() + "'";
        log.debug(message);
        throw new PermissionDenied(message);
    }
    HarvestDefinition preHD = null;
    if (hd instanceof FullHarvest) {
        preHD = ((FullHarvest) hd).getPreviousHarvestDefinition();
    }

    Connection c = HarvestDBConnection.get();
    PreparedStatement s = null;
    try {
        c.setAutoCommit(false);
        s = c.prepareStatement("UPDATE harvestdefinitions SET " + "name = ?, " + "comments = ?, "
                + "numevents = ?, " + "submitted = ?," + "isactive = ?," + "edition = ?, audience = ? "
                + "WHERE harvest_id = ? AND edition = ?");
        DBUtils.setName(s, 1, hd, Constants.MAX_NAME_SIZE);
        DBUtils.setComments(s, 2, hd, Constants.MAX_COMMENT_SIZE);
        s.setInt(3, hd.getNumEvents());
        s.setTimestamp(4, new Timestamp(hd.getSubmissionDate().getTime()));
        s.setBoolean(5, hd.getActive());
        long nextEdition = hd.getEdition() + 1;
        s.setLong(6, nextEdition);
        s.setString(7, hd.getAudience());
        s.setLong(8, hd.getOid());
        s.setLong(9, hd.getEdition());

        int rows = s.executeUpdate();
        // Since the HD exists, no rows indicates bad edition
        if (rows == 0) {
            String message = "Somebody else must have updated " + hd + " since edition " + hd.getEdition()
                    + ", not updating";
            log.debug(message);
            throw new PermissionDenied(message);
        }
        s.close();
        if (hd instanceof FullHarvest) {
            FullHarvest fh = (FullHarvest) hd;
            s = c.prepareStatement(
                    "UPDATE fullharvests SET " + "previoushd = ?, " + "maxobjects = ?, " + "maxbytes = ?, "
                            + "maxjobrunningtime = ?, " + "isindexready = ? " + "WHERE harvest_id = ?");
            if (preHD != null) {
                s.setLong(1, preHD.getOid());
            } else {
                s.setNull(1, Types.BIGINT);
            }
            s.setLong(2, fh.getMaxCountObjects());
            s.setLong(3, fh.getMaxBytes());
            s.setLong(4, fh.getMaxJobRunningTime());
            s.setBoolean(5, fh.getIndexReady());
            s.setLong(6, fh.getOid());

            rows = s.executeUpdate();
            log.debug(rows + " fullharvests records updated");
        } else if (hd instanceof PartialHarvest) {
            PartialHarvest ph = (PartialHarvest) hd;
            s = c.prepareStatement(
                    "UPDATE partialharvests SET " + "schedule_id = " + "    (SELECT schedule_id FROM schedules "
                            + "WHERE schedules.name = ?), " + "nextdate = ? " + "WHERE harvest_id = ?");
            s.setString(1, ph.getSchedule().getName());
            DBUtils.setDateMaybeNull(s, 2, ph.getNextDate());
            s.setLong(3, ph.getOid());
            rows = s.executeUpdate();
            log.debug(rows + " partialharvests records updated");
            s.close();
            // FIXME The updates to harvest_configs table should be done
            // in method removeDomainConfiguration(), and not here.
            // The following deletes ALL harvest_configs entries for
            // this PartialHarvest, and creates the entries for the
            // PartialHarvest again!!
            createHarvestConfigsEntries(c, ph, ph.getOid());
        } else {
            String message = "Harvest definition " + hd + " has unknown class " + hd.getClass();
            log.warn(message);
            throw new ArgumentNotValid(message);
        }
        saveExtendedFieldValues(c, hd);

        c.commit();
        hd.setEdition(nextEdition);
    } catch (SQLException e) {
        throw new IOFailure("SQL error while updating harvest definition " + hd + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        DBUtils.closeStatementIfOpen(s);
        DBUtils.rollbackIfNeeded(c, "updating", hd);
        HarvestDBConnection.release(c);
    }
}

From source file:org.bytesoft.openjtcc.supports.logger.DbTransactionLoggerImpl.java

@Override
public void beginTransaction(TransactionArchive transaction) {
    Connection connection = null;
    PreparedStatement stmt = null;
    try {/* w w  w .  j  a  v a 2s.co m*/
        connection = this.getConnection();

        StringBuilder ber = new StringBuilder();
        ber.append("insert into tcc_transaction(");
        ber.append("application, endpoint, global_tx_id, status");
        ber.append(", status_trace, coordinator, created_time, deleted");
        ber.append(") values (?, ?, ?, ?, ?, ?, ?, ?)");
        stmt = connection.prepareStatement(ber.toString());

        TransactionContext transactionContext = transaction.getTransactionContext();
        TransactionStatus transactionStatus = transaction.getTransactionStatus();
        XidImpl globalXid = transactionContext.getGlobalXid();
        boolean coordinator = transactionContext.isCoordinator();
        TerminalKey terminalKey = transactionContext.getTerminalKey();

        int index = 1;
        stmt.setString(index++, terminalKey.getApplication());
        stmt.setString(index++, terminalKey.getEndpoint());
        stmt.setString(index++, ByteUtils.byteArrayToString(globalXid.getGlobalTransactionId()));
        stmt.setInt(index++, transactionStatus.getInnerStatus());
        stmt.setInt(index++, transactionStatus.getInnerStatusTrace());
        stmt.setBoolean(index++, coordinator);
        stmt.setTimestamp(index++, new Timestamp(transactionContext.getCreatedTime()));
        stmt.setBoolean(index++, false);

        stmt.executeUpdate();
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        closeStatement(stmt);
        this.releaseConnection(connection);
    }
}

From source file:edu.ku.brc.specify.toycode.RegPivot.java

/**
 * @param hash/*w ww  . j  a va  2 s  .  c o  m*/
 * @param recordType
 * @param pStmt
 * @param dbFieldTypes
 * @param dbFieldNames
 * @param inxToName
 * @throws SQLException
 */
private void writeHash(final HashMap<String, HashMap<String, Object>> hash, final Integer recordType,
        final PreparedStatement pStmt, final Vector<Integer> dbFieldTypes, final Vector<String> dbFieldNames,
        final HashMap<Integer, String> inxToName) throws SQLException {
    int totalCnt = hash.size();
    int cnt = 0;

    for (String idKey : hash.keySet()) {
        cnt++;
        if (cnt % 500 == 0)
            System.out.println(cnt + " / " + totalCnt);

        HashMap<String, Object> nameToVals = hash.get(idKey);

        if (recordType != null) {
            pStmt.setInt(dbFieldNames.size() + 1, (Integer) recordType);
        }

        for (int i = 0; i < dbFieldNames.size(); i++) {
            int fInx = i + 1;
            String name = inxToName.get(i);
            Object value = nameToVals.get(name);

            pStmt.setObject(fInx, null);

            int typ = dbFieldTypes.get(i);

            if (value != null) {
                if (value instanceof Integer) {
                    pStmt.setInt(fInx, (Integer) value);

                } else if (value instanceof String) {
                    pStmt.setString(fInx, (String) value);
                } else if (value instanceof Timestamp) {
                    pStmt.setTimestamp(fInx, (Timestamp) value);
                } else {
                    System.err.println("Unhandled class: " + value.getClass().getName());
                }
            } else {
                pStmt.setObject(fInx, null);
            }
        }
        pStmt.executeUpdate();
    }

}

From source file:fr.aliacom.obm.common.contact.ContactDaoJdbcImpl.java

private Set<Integer> findRemovalCandidates(String sql, Date d) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;/*from  w w w.  ja va 2s .  c om*/
    Connection con = null;

    Set<Integer> l = new HashSet<Integer>();
    try {
        con = obmHelper.getConnection();
        ps = con.prepareStatement(sql);

        int idx = 1;
        ps.setTimestamp(idx++, new Timestamp(d.getTime()));
        rs = ps.executeQuery();

        while (rs.next()) {
            l.add(rs.getInt(1));
        }
    } finally {
        obmHelper.cleanup(con, ps, rs);
    }
    return l;
}

From source file:nl.nn.adapterframework.jdbc.XmlQuerySender.java

private void applyParameters(PreparedStatement statement, Vector columns) throws SQLException {
    Iterator iter = columns.iterator();
    int var = 1;
    while (iter.hasNext()) {
        Column column = (Column) iter.next();
        if (column.getParameter() != null) {
            if (column.getParameter() instanceof String) {
                log.debug(/*from   ww  w  .  j  a  va 2 s  .co  m*/
                        "parm [" + var + "] is a String with value [" + column.getParameter().toString() + "]");
                statement.setString(var, (String) column.getParameter());
                var++;
            }
            if (column.getParameter() instanceof Integer) {
                log.debug("parm [" + var + "] is an Integer with value [" + column.getParameter().toString()
                        + "]");
                statement.setInt(var, Integer.parseInt(column.getParameter().toString()));
                var++;
            }
            if (column.getParameter() instanceof Float) {
                log.debug(
                        "parm [" + var + "] is a Float with value [" + column.getParameter().toString() + "]");
                statement.setFloat(var, Float.parseFloat(column.getParameter().toString()));
                var++;
            }
            if (column.getParameter() instanceof Timestamp) {
                log.debug("parm [" + var + "] is a Timestamp with value [" + column.getParameter().toString()
                        + "]");
                statement.setTimestamp(var, (Timestamp) column.getParameter());
                var++;
            }
        }
    }
}

From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * @param oldDBConn/*from   ww  w .j a v  a 2  s  .  c  o m*/
 * @param newDBConn
 * @param disciplineID
 * @return
 */
public static boolean convertKUFishObsData(final Connection oldDBConn, final Connection newDBConn) {
    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdMapperIFace coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog",
            "CollectionObjectCatalogID", false);

    PreparedStatement pStmt1 = null;
    PreparedStatement pStmt2 = null;
    PreparedStatement pStmt3 = null;
    try {
        pStmt1 = newDBConn.prepareStatement(
                "INSERT INTO collectionobjectattribute (Remarks, CollectionMemberID, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pStmt2 = newDBConn.prepareStatement(
                "UPDATE collectionobjectattribute SET Remarks=? WHERE CollectionObjectAttributeID = ?");

        pStmt3 = newDBConn.prepareStatement(
                "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?");

        String sql = " SELECT BiologicalObjectID, Text1, TimestampCreated, TimestampModified FROM observation WHERE Text1 IS NOT NULL AND LENGTH(Text1) > 0";
        Statement stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int oldCOId = rs.getInt(1);
            Integer newCOId = coMapper.get(oldCOId);
            if (newCOId != null) {
                sql = "SELECT CollectionObjectAttributeID, CollectionMemberID FROM collectionobject WHERE CollectionObjectID = "
                        + newCOId;
                Object[] row = BasicSQLUtils.getRow(sql);
                if (row == null || row.length == 0) {
                    log.error("Couldn't get record for  newCOId " + newCOId);
                    continue;
                }

                Integer newCOAId = (Integer) row[0];
                Integer collMemId = (Integer) row[1];

                if (newCOAId != null) // Do Update
                {
                    pStmt2.setString(1, rs.getString(2));
                    pStmt2.setInt(2, newCOAId);
                    pStmt2.executeUpdate();

                } else // Do Insert
                {
                    pStmt1.setString(1, rs.getString(2));
                    pStmt1.setInt(2, collMemId);
                    pStmt1.setTimestamp(3, rs.getTimestamp(3));
                    pStmt1.setTimestamp(4, rs.getTimestamp(4));
                    pStmt1.setInt(5, 1);
                    pStmt1.executeUpdate();
                    newCOAId = BasicSQLUtils.getInsertedId(pStmt1);
                }

                pStmt3.setInt(1, newCOAId);
                pStmt3.setInt(2, newCOId);
                pStmt3.executeUpdate();

            } else {
                log.error("No mapped CO for Obs.BiologicalObjectID " + oldCOId);
            }
        }
        rs.close();
        stmt.close();

        return true;

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

    } finally {
        try {
            if (pStmt1 != null)
                pStmt1.close();
            if (pStmt2 != null)
                pStmt2.close();
            if (pStmt3 != null)
                pStmt3.close();

        } catch (Exception ex) {
        }
    }

    return false;
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

protected void deleteGerelateerdePanden(final Verblijfsobject verblijfsobject) {
    jdbcTemplate.update(new PreparedStatementCreator() {
        @Override//from   w ww  . j ava2 s . c  o m
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement("delete from bag_gerelateerd_pand"
                    + " where bag_verblijfsobject_id = ?" + " and aanduiding_record_correctie = ?"
                    + " and begindatum_tijdvak_geldigheid = ?");
            ps.setLong(1, verblijfsobject.getIdentificatie());
            ps.setLong(2, verblijfsobject.getAanduidingRecordCorrectie());
            ps.setTimestamp(3, new Timestamp(verblijfsobject.getBegindatumTijdvakGeldigheid().getTime()));
            return ps;
        }
    });
}