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:com.opensymphony.module.propertyset.database.JDBCPropertySet.java

private void setValues(PreparedStatement ps, int type, String key, Object value)
        throws SQLException, PropertyException {
    // Patched by Edson Richter for MS SQL Server JDBC Support!
    String driverName;/*from   w w  w .  j a  v  a  2  s. c om*/

    try {
        driverName = ps.getConnection().getMetaData().getDriverName().toUpperCase();
    } catch (Exception e) {
        driverName = "";
    }

    ps.setNull(1, Types.VARCHAR);
    ps.setNull(2, Types.TIMESTAMP);

    // Patched by Edson Richter for MS SQL Server JDBC Support!
    // Oracle support suggestion also Michael G. Slack
    if ((driverName.indexOf("SQLSERVER") >= 0) || (driverName.indexOf("ORACLE") >= 0)) {
        ps.setNull(3, Types.BINARY);
    } else {
        ps.setNull(3, Types.BLOB);
    }

    ps.setNull(4, Types.FLOAT);
    ps.setNull(5, Types.NUMERIC);
    ps.setInt(6, type);
    ps.setString(7, globalKey);
    ps.setString(8, key);

    switch (type) {
    case PropertySet.BOOLEAN:

        Boolean boolVal = (Boolean) value;
        ps.setInt(5, boolVal.booleanValue() ? 1 : 0);

        break;

    case PropertySet.DATA:

        Data data = (Data) value;
        ps.setBytes(3, data.getBytes());

        break;

    case PropertySet.DATE:

        Date date = (Date) value;
        ps.setTimestamp(2, new Timestamp(date.getTime()));

        break;

    case PropertySet.DOUBLE:

        Double d = (Double) value;
        ps.setDouble(4, d.doubleValue());

        break;

    case PropertySet.INT:

        Integer i = (Integer) value;
        ps.setInt(5, i.intValue());

        break;

    case PropertySet.LONG:

        Long l = (Long) value;
        ps.setLong(5, l.longValue());

        break;

    case PropertySet.STRING:
        ps.setString(1, (String) value);

        break;

    default:
        throw new PropertyException("This type isn't supported!");
    }
}

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

/**
 * Insert new owner info for a domain.//from   w ww .  jav  a 2 s.com
 * @param c 
 *            A connection to the database
 * @param d
 *            A domain to insert on. The domains ID must be correct.
 * @param doi
 *            Owner info to insert.
 * @throws SQLException
 *             If any database problems occur during the insertion process.
 */
private void insertOwnerInfo(Connection c, Domain d, DomainOwnerInfo doi) throws SQLException {
    PreparedStatement s = c.prepareStatement(
            "INSERT INTO ownerinfo " + "( domain_id, created, info ) VALUES ( ?, ?, ?)",
            Statement.RETURN_GENERATED_KEYS);
    s.setLong(1, d.getID());
    s.setTimestamp(2, new Timestamp(doi.getDate().getTime()));
    s.setString(3, doi.getInfo());
    s.executeUpdate();
    doi.setID(DBUtils.getGeneratedID(s));
}

From source file:com.commander4j.db.JDBDespatch.java

public boolean confirm() {
    boolean result = false;
    JDBPalletHistory ph = new JDBPalletHistory(getHostID(), getSessionID());
    long txn = 0;

    logger.debug("confirm2 [" + getDespatchNo() + "]");

    if (getStatus().equals("Confirmed") == true) {
        setErrorMessage("Despatch already confirmed.");
    } else {//from   w ww  .  j ava2s .co  m
        if (isValid(true) == true) {

            // Run in current thread.
            logger.debug("*NON THREADED DESPATCH*");
            while (txn == 0) {
                txn = ph.generateNewTransactionRef();
                if (txn > 0) {
                    logger.debug("Transaction Number = " + String.valueOf(txn));
                    setDespatchDate(com.commander4j.util.JUtility.getSQLDateTime());
                    logger.debug("Confirm Date       = " + String.valueOf(getDespatchDate()));
                    try {

                        // Update Despatch Status
                        logger.debug("Updating Despatch Status");
                        PreparedStatement stmtupdate0;

                        stmtupdate0 = Common.hostList.getHost(getHostID()).getConnection(getSessionID())
                                .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements()
                                        .getSQL("JDBDespatch.setConfirmed"));
                        stmtupdate0.setLong(1, txn);
                        stmtupdate0.setString(2, "Confirmed");
                        stmtupdate0.setTimestamp(3, getDespatchDate());
                        stmtupdate0.setString(4, getDespatchNo());
                        stmtupdate0.setString(5, getDespatchNo());
                        stmtupdate0.execute();
                        stmtupdate0.clearParameters();
                        stmtupdate0.close();

                        // Write FROM Locations to Pallet History
                        logger.debug("Updating Pallet location_id records");
                        PreparedStatement stmtupdate1;

                        stmtupdate1 = Common.hostList.getHost(getHostID()).getConnection(getSessionID())
                                .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements()
                                        .getSQL("JDBPalletHistory.insertFromPallet"));
                        stmtupdate1.setLong(1, txn);
                        stmtupdate1.setString(2, "DESPATCH");
                        stmtupdate1.setString(3, "FROM");
                        stmtupdate1.setTimestamp(4, getDespatchDate());
                        stmtupdate1.setString(5, getUserID());
                        stmtupdate1.setString(6, getDespatchNo());
                        stmtupdate1.execute();
                        stmtupdate1.clearParameters();
                        stmtupdate1.close();

                        // Update Pallet Locations to TO
                        logger.debug("Updating Pallet Locations");
                        PreparedStatement stmtupdate2;

                        stmtupdate2 = Common.hostList.getHost(getHostID()).getConnection(getSessionID())
                                .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements()
                                        .getSQL("JDBPallet.updateLocationIDByDespatchNo"));
                        stmtupdate2.setString(1, getLocationIDTo());
                        stmtupdate2.setString(2, getUserID());
                        stmtupdate2.setTimestamp(3, getDespatchDate());
                        stmtupdate2.setString(4, getDespatchNo());
                        stmtupdate2.execute();
                        stmtupdate2.clearParameters();
                        stmtupdate2.close();

                        // Write TO Locations to Pallet History
                        logger.debug("Writing TO Pallet History records");
                        PreparedStatement stmtupdate3;

                        stmtupdate3 = Common.hostList.getHost(getHostID()).getConnection(getSessionID())
                                .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements()
                                        .getSQL("JDBPalletHistory.insertFromPallet"));
                        stmtupdate3.setLong(1, txn);
                        stmtupdate3.setString(2, "DESPATCH");
                        stmtupdate3.setString(3, "TO");
                        stmtupdate3.setTimestamp(4, getDespatchDate());
                        stmtupdate3.setString(5, getUserID());
                        stmtupdate3.setString(6, getDespatchNo());
                        stmtupdate3.execute();
                        stmtupdate3.clearParameters();
                        stmtupdate3.close();

                        // COMMIT !
                        logger.debug("Commiting updates");
                        Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit();

                        // Refresh data
                        logger.debug("Refreshing data");
                        getDespatchProperties();

                        // Request Interfaces
                        if (getLocationDBTo().isDespatchConfirmationMessageRequired()) {
                            logger.debug("Requesting outbound despatch message");
                            odc = new OutgoingDespatchConfirmation(getHostID(), getSessionID());
                            odc.submit(txn);
                        }

                        if (getLocationDBTo().isDespatchEquipmentTrackingMessageRequired()) {
                            logger.debug("Requesting outbound equipment message");
                            oet = new OutgoingEquipmentTracking(getHostID(), getSessionID());
                            oet.submit(txn);
                        }

                        if (getLocationDBTo().isDespatchPreAdviceMessageRequired()) {
                            logger.debug("Requesting outbound pre-advice message");
                            opa = new OutgoingDespatchPreAdvice(getHostID(), getSessionID());
                            opa.submit(txn);
                        }

                        if (getLocationDBTo().isDespatchEmailRequired()) {
                            logger.debug("Requesting outbound despatch email message");
                            ode = new OutgoingDespatchEmail(getHostID(), getSessionID());
                            ode.submit(txn);
                        }

                        result = true;

                    } catch (SQLException e) {
                        logger.error("Confirm2 error )" + e.getMessage());
                        try {
                            Common.hostList.getHost(getHostID()).getConnection(getSessionID()).rollback();
                            logger.error("Confirm2 failed (rollback success)");
                        } catch (SQLException e1) {
                            logger.error("Confirm2 failed (rollback failure)" + e1.getMessage());
                        }
                    }

                } else {
                    logger.error("Unable to get Transaction Number - retrying");
                }

            }

        }
    }

    return result;
}

From source file:org.dcache.chimera.FsSqlDriver.java

/**
 *
 *  creates a new id for a tag and stores it into t_tags_inodes table.
 *
 * @param uid/*from ww  w .  j  av a2  s  . c  om*/
 * @param gid
 * @param mode
 * @return
 */
long createTagInode(int uid, int gid, int mode) {
    final String CREATE_TAG_INODE_WITHOUT_VALUE = "INSERT INTO t_tags_inodes (imode, inlink, iuid, igid, isize, "
            + "ictime, iatime, imtime, ivalue) VALUES (?,1,?,?,0,?,?,?,NULL)";

    Timestamp now = new Timestamp(System.currentTimeMillis());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int rc = _jdbc.update(con -> {
        PreparedStatement ps = con.prepareStatement(CREATE_TAG_INODE_WITHOUT_VALUE,
                Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, mode | UnixPermission.S_IFREG);
        ps.setInt(2, uid);
        ps.setInt(3, gid);
        ps.setTimestamp(4, now);
        ps.setTimestamp(5, now);
        ps.setTimestamp(6, now);
        return ps;
    }, keyHolder);
    if (rc != 1) {
        throw new JdbcUpdateAffectedIncorrectNumberOfRowsException(CREATE_TAG_INODE_WITHOUT_VALUE, 1, rc);
    }
    return (Long) keyHolder.getKeys().get("itagid");
}

From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java

private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, Integer scale,
        Object inValue) throws SQLException {

    if (inValue instanceof SqlTypeValue) {
        ((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName);
    } else if (inValue instanceof SqlValue) {
        ((SqlValue) inValue).setValue(ps, paramIndex);
    } else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR
            || (sqlType == Types.CLOB && isStringValue(inValue.getClass()))) {
        ps.setString(paramIndex, inValue.toString());
    } else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
        if (inValue instanceof BigDecimal) {
            ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
        } else if (scale != null) {
            ps.setObject(paramIndex, inValue, sqlType, scale);
        } else {// w  w w.  j a  v a2 s .co  m
            ps.setObject(paramIndex, inValue, sqlType);
        }
    } else if (sqlType == Types.DATE) {
        if (inValue instanceof java.util.Date) {
            if (inValue instanceof java.sql.Date) {
                ps.setDate(paramIndex, (java.sql.Date) inValue);
            } else {
                ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
            }
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
        } else {
            ps.setObject(paramIndex, inValue, Types.DATE);
        }
    } else if (sqlType == Types.TIME) {
        if (inValue instanceof java.util.Date) {
            if (inValue instanceof java.sql.Time) {
                ps.setTime(paramIndex, (java.sql.Time) inValue);
            } else {
                ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime()));
            }
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal);
        } else {
            ps.setObject(paramIndex, inValue, Types.TIME);
        }
    } else if (sqlType == Types.TIMESTAMP) {
        if (inValue instanceof java.util.Date) {
            if (inValue instanceof java.sql.Timestamp) {
                ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue);
            } else {
                ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
            }
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
        } else {
            ps.setObject(paramIndex, inValue, Types.TIMESTAMP);
        }
    } else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
        if (isStringValue(inValue.getClass())) {
            ps.setString(paramIndex, inValue.toString());
        } else if (isDateValue(inValue.getClass())) {
            ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
        } else {
            // Fall back to generic setObject call without SQL type specified.
            ps.setObject(paramIndex, inValue);
        }
    } else {
        // Fall back to generic setObject call with SQL type specified.
        ps.setObject(paramIndex, inValue, sqlType);
    }
}

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

/**
 * Update the list of owner info for the given domain, keeping IDs where
 * applicable./*from   w  w w .  ja v a  2  s . c  om*/
 * @param c 
 *            A connection to the database
 * @param d
 *            A domain to update.
 * @throws SQLException
 *             If any database problems occur during the update process.
 */
private void updateOwnerInfo(Connection c, Domain d) throws SQLException {
    List<Long> oldIDs = DBUtils.selectLongList(c, "SELECT ownerinfo_id FROM ownerinfo " + "WHERE domain_id = ?",
            d.getID());
    PreparedStatement s = c.prepareStatement(
            "UPDATE ownerinfo SET " + "created = ?, " + "info = ? " + "WHERE ownerinfo_id = ?");
    for (DomainOwnerInfo doi : d.getAllDomainOwnerInfo()) {
        if (doi.hasID() && oldIDs.remove(doi.getID())) {
            s.setTimestamp(1, new Timestamp(doi.getDate().getTime()));
            DBUtils.setStringMaxLength(s, 2, doi.getInfo(), Constants.MAX_OWNERINFO_SIZE, doi, "owner info");
            s.setLong(3, doi.getID());
            s.executeUpdate();
            s.clearParameters();
        } else {
            insertOwnerInfo(c, d, doi);
        }
    }
    if (oldIDs.size() != 0) {
        String message = "Not allowed to delete ownerinfo " + oldIDs + " on " + d;
        log.debug(message);
        throw new IOFailure(message);
    }
}

From source file:org.dcache.chimera.FsSqlDriver.java

/**
 *
 *  creates a new id for a tag and stores it into t_tags_inodes table.
 *
 * @param uid/*from  w w w  .j  a v a2  s  .co m*/
 * @param gid
 * @param mode
 * @param value
 * @return
 */
long createTagInode(int uid, int gid, int mode, byte[] value) {
    final String CREATE_TAG_INODE_WITH_VALUE = "INSERT INTO t_tags_inodes (imode, inlink, iuid, igid, isize, "
            + "ictime, iatime, imtime, ivalue) VALUES (?,1,?,?,?,?,?,?,?)";

    Timestamp now = new Timestamp(System.currentTimeMillis());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int rc = _jdbc.update(con -> {
        PreparedStatement ps = con.prepareStatement(CREATE_TAG_INODE_WITH_VALUE,
                Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, mode | UnixPermission.S_IFREG);
        ps.setInt(2, uid);
        ps.setInt(3, gid);
        ps.setLong(4, value.length);
        ps.setTimestamp(5, now);
        ps.setTimestamp(6, now);
        ps.setTimestamp(7, now);
        ps.setBinaryStream(8, new ByteArrayInputStream(value), value.length);
        return ps;
    }, keyHolder);
    if (rc != 1) {
        throw new JdbcUpdateAffectedIncorrectNumberOfRowsException(CREATE_TAG_INODE_WITH_VALUE, 1, rc);
    }
    return (Long) keyHolder.getKeys().get("itagid");
}

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

/**
 * @param oldDBConn/* ww  w  .  ja v  a2 s.c  o  m*/
 * @param newDBConn
 */
public static void moveGTPNameToCEText1(final Connection oldDBConn, final Connection newDBConn) {
    String sql = null;
    try {
        IdMapperIFace ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent",
                "CollectingEventID", false);

        Timestamp now = new Timestamp(System.currentTimeMillis());
        PreparedStatement pStmt = newDBConn.prepareStatement(
                "UPDATE collectingeventattribute SET Text3=? WHERE CollectingEventAttributeID=?");

        PreparedStatement pStmt2 = newDBConn.prepareStatement(
                "INSERT INTO collectingeventattribute SET Text3=?, Version=0, DisciplineID=?, TimestampCreated=?, TimestampModified=?",
                Statement.RETURN_GENERATED_KEYS);
        PreparedStatement pStmt3 = newDBConn.prepareStatement(
                "UPDATE collectingevent SET CollectingEventAttributeID=? WHERE CollectingEventID=?");

        int cnt = 0;
        // Query to Create PickList
        sql = "SELECT c.CollectingEventID, g.Name FROM collectingevent AS c "
                + "Inner Join stratigraphy AS s ON c.CollectingEventID = s.StratigraphyID "
                + "Inner Join geologictimeperiod AS g ON s.GeologicTimePeriodID = g.GeologicTimePeriodID ";
        for (Object[] row : BasicSQLUtils.query(oldDBConn, sql)) {
            Integer id = (Integer) row[0];
            Integer newCEId = ceMapper.get(id);
            if (newCEId != null) {
                Vector<Object[]> colList = BasicSQLUtils.query(
                        "SELECT DisciplineID, CollectingEventAttributeID FROM collectingevent WHERE CollectingEventID = "
                                + newCEId);
                Object[] cols = colList.get(0);

                if (cols[1] != null) {
                    pStmt.setString(1, (String) row[1]);
                    pStmt.setInt(2, newCEId);

                    int rv = pStmt.executeUpdate();
                    if (rv != 1) {
                        log.error(String.format("Error updating CEA New Id %d  Old: %d  rv: %d", newCEId, id,
                                rv));
                    }
                } else {
                    Integer disciplineID = (Integer) cols[0];
                    pStmt2.setString(1, (String) row[1]);
                    pStmt2.setInt(2, disciplineID);
                    pStmt2.setTimestamp(3, now);
                    pStmt2.setTimestamp(4, now);

                    int rv = pStmt2.executeUpdate();
                    if (rv == 1) {
                        Integer newCEAId = BasicSQLUtils.getInsertedId(pStmt2);
                        if (newCEAId != null) {
                            pStmt3.setInt(1, newCEAId);
                            pStmt3.setInt(2, newCEId);
                            rv = pStmt3.executeUpdate();
                            if (rv != 1) {
                                log.error(String.format("Error updating CEA New Id %d To CE ID: %d", newCEAId,
                                        newCEId));
                            }
                        } else {
                            log.error("Couldn't get inserted CEAId");
                        }

                    } else {
                        log.error(String.format("Error updating CEA New Id %d  Old: %d  rv: %d", newCEId, id,
                                rv));
                    }
                }
            } else {
                log.error(String.format("No Map for Old CE Id %d", id));
            }
            cnt++;
            if (cnt % 500 == 0) {
                log.debug("Count " + cnt);
            }
        }
        log.debug("Count " + cnt);
        pStmt.close();

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

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

protected void insertNevenadressen(final TypeAdresseerbaarObject objectType,
        final BAGAdresseerbaarObject object) {
    jdbcTemplate.batchUpdate("insert into bag_neven_adres (" + "bag_object_id," + "aanduiding_record_correctie,"
            + "begindatum_tijdvak_geldigheid," + "bag_object_type," + "bag_nummeraanduiding_id"
            + ") values (?,?,?,?,?)", new BatchPreparedStatementSetter() {
                @Override//from w  ww  .j av  a 2s .  c  o m
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, object.getIdentificatie());
                    ps.setLong(2, object.getAanduidingRecordCorrectie());
                    ps.setTimestamp(3, new Timestamp(object.getBegindatumTijdvakGeldigheid().getTime()));
                    ps.setInt(4, objectType.ordinal());
                    ps.setLong(5, object.getNevenAdressen().get(i));
                }

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

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

protected void insertGerelateerdePanden(final List<Verblijfsobject> verblijfsobjecten) {
    final List<GerelateerdPand> batch = new ArrayList<GerelateerdPand>();
    for (Verblijfsobject verblijfsobject : verblijfsobjecten)
        for (Long gerelateerdPand : verblijfsobject.getGerelateerdPanden())
            batch.add(new GerelateerdPand(verblijfsobject, gerelateerdPand));
    jdbcTemplate.batchUpdate(/* www . j a  v  a  2 s  .c o  m*/
            "insert into bag_gerelateerd_pand (" + "bag_verblijfsobject_id," + "aanduiding_record_correctie,"
                    + "begindatum_tijdvak_geldigheid," + "bag_pand_id" + ") values (?,?,?,?)",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, batch.get(i).getIdentificatie());
                    ps.setLong(2, batch.get(i).getAanduidingRecordCorrectie());
                    ps.setTimestamp(3, new Timestamp(batch.get(i).getBegindatumTijdvakGeldigheid().getTime()));
                    ps.setLong(4, batch.get(i).getGerelateerdPand());
                }

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