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.cloudera.sqoop.TestIncrementalImport.java

/**
 * Insert rows with id = [low, hi) into tableName with
 * the timestamp column set to the specified ts.
 *//*from  w ww .j  a  v  a 2  s  .  c om*/
private void insertIdTimestampRows(String tableName, int low, int hi, Timestamp ts) throws SQLException {
    LOG.info("Inserting id rows in [" + low + ", " + hi + ") @ " + ts);
    SqoopOptions options = new SqoopOptions();
    options.setConnectString(SOURCE_DB_URL);
    HsqldbManager manager = new HsqldbManager(options);
    Connection c = manager.getConnection();
    PreparedStatement s = null;
    try {
        s = c.prepareStatement("INSERT INTO " + tableName + " VALUES(?,?)");
        for (int i = low; i < hi; i++) {
            s.setInt(1, i);
            s.setTimestamp(2, ts);
            s.executeUpdate();
        }

        c.commit();
    } finally {
        s.close();
    }
}

From source file:com.buckwa.dao.impl.excise4.Form23DaoImpl.java

@Override
public void create(final Form23 form23) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final StringBuilder sql = new StringBuilder();
    sql.append("INSERT INTO `form23`").append(
            " (`form23_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,"
                    + "`totalScrap`,`part4flag`,`part4fullName`,`part4Date`,"
                    + "`part5flag`,`part5licenseNo`,`part5licenseDate`,`part5billingNo`,`part5billingDate`,`part5amount`,`part5Date`,"
                    + "`part6flag`,`part6Date`,`step`)")
            .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'0')");

    logger.info("SQL : " + sql.toString());

    String user = "";
    try {/*from w  ww.  j  av a2s  .c om*/
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;

    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            Timestamp currentDate = new Timestamp(System.currentTimeMillis());
            Industry industry = form23.getIndustry();
            Factory factory = form23.getFactory();

            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, industry.getIndustryId());
            if (factory.getFactoryId() != null) {
                ps.setLong(2, factory.getFactoryId());
            } else {
                ps.setNull(2, java.sql.Types.BIGINT);
            }
            ps.setTimestamp(3, currentDate);
            ps.setString(4, userName);
            ps.setTimestamp(5, currentDate);
            ps.setString(6, userName);
            ps.setBigDecimal(7, form23.getTotalScrap());

            ps.setString(8, form23.getPart4flag());
            ps.setString(9, form23.getPart4fullName());
            ps.setTimestamp(10, currentDate);
            ps.setString(11, form23.getPart5flag());
            ps.setString(12, form23.getPart5licenseNo());
            ps.setTimestamp(13, getDateFormString(form23.getPart5licenseDate()));//part5licenseDate
            ps.setString(14, form23.getPart5billingNo());
            ps.setTimestamp(15, getDateFormString(form23.getPart5billingDate()));//part5billingDate
            ps.setBigDecimal(16, form23.getPart5amount());
            ps.setTimestamp(17, currentDate);//part5Date
            ps.setString(18, form23.getPart6flag());//part5Date
            ps.setTimestamp(19, currentDate);//part6Date

            return ps;
        }

    }, keyHolder);

    final Long returnidform23 = keyHolder.getKey().longValue();
    form23.setForm23Id(returnidform23);
    form23.setStep("0");
    logger.info("returnidform23 : " + returnidform23);

    //ID PRODUCT
    List<Product> products = form23.getProductList();
    if (products != null) {
        final StringBuilder psql = new StringBuilder();
        psql.append(
                "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
                .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

        logger.info("SQL : " + psql.toString());

        for (final Product p : products) {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    Timestamp currentDate = new Timestamp(System.currentTimeMillis());
                    PreparedStatement ps = connection.prepareStatement(psql.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, returnidform23);
                    ps.setString(2, p.getSeq());
                    ps.setString(3, p.getProductName());
                    ps.setString(4, p.getSize());
                    ps.setString(5, p.getBandColor());
                    ps.setString(6, p.getBackgroudColor());
                    ps.setString(7, p.getLicenseNo());
                    ps.setBigDecimal(8, p.getGrossnumber200());
                    ps.setBigDecimal(9, p.getGrossnumber400());
                    ps.setBigDecimal(10, p.getCorkScrap());
                    ps.setBigDecimal(11, p.getTotalScrap());

                    ps.setTimestamp(12, currentDate);
                    ps.setString(13, userName);
                    ps.setTimestamp(14, currentDate);
                    ps.setString(15, userName);
                    return ps;
                }

            }, keyHolder);

            long returnidproduct = keyHolder.getKey().longValue();
            p.setProcuctId(returnidproduct);
            logger.info("returnidproduct : " + returnidproduct);
        }
    }
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void create(final Form24 form24) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final StringBuilder sql = new StringBuilder();
    sql.append(//  ww  w  .  j av  a 2s . co  m
            "INSERT INTO `form24`(`form24_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,step,industry_name,tax_no,factory_name) ")
            .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?)");

    logger.info("SQL : " + sql.toString());

    String user = "";
    try {
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;

    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            Timestamp currentDate = new Timestamp(System.currentTimeMillis());
            final Industry industry = form24.getIndustry();
            final Factory factory = form24.getFactory();

            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, industry.getIndustryId());
            if (factory.getFactoryId() != null) {
                ps.setLong(2, factory.getFactoryId());
            } else {
                ps.setNull(2, java.sql.Types.BIGINT);
            }
            ps.setTimestamp(3, currentDate);
            ps.setString(4, userName);
            ps.setTimestamp(5, currentDate);
            ps.setString(6, userName);
            ps.setString(7, form24.getStep());
            ps.setString(8, industry.getIndustryName());
            ps.setString(9, industry.getTaxNo());
            ps.setString(10, factory.getFactoryName());
            return ps;
        }

    }, keyHolder);

    final Long returnidform24 = keyHolder.getKey().longValue();
    form24.setForm24Id(returnidform24);
    logger.info("returnidform24 : " + returnidform24);

    //ID PRODUCT
    List<Product> products = form24.getProductList();
    if (products != null) {
        final StringBuilder psql = new StringBuilder();
        psql.append(
                "INSERT INTO `form24_product`(`form24_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
                .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

        logger.info("SQL : " + psql.toString());

        for (final Product p : products) {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    Timestamp currentDate = new Timestamp(System.currentTimeMillis());
                    PreparedStatement ps = connection.prepareStatement(psql.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, returnidform24);
                    ps.setString(2, p.getSeq());
                    ps.setString(3, p.getProductName());
                    ps.setString(4, p.getSize());
                    ps.setString(5, p.getBandColor());
                    ps.setString(6, p.getBackgroudColor());
                    ps.setString(7, p.getLicenseNo());
                    ps.setBigDecimal(8, p.getGrossnumber200());
                    ps.setBigDecimal(9, p.getGrossnumber400());
                    ps.setBigDecimal(10, p.getCorkScrap());
                    ps.setBigDecimal(11, p.getTotalScrap());

                    ps.setTimestamp(12, currentDate);
                    ps.setString(13, userName);
                    ps.setTimestamp(14, currentDate);
                    ps.setString(15, userName);
                    return ps;
                }

            }, keyHolder);

            long returnidproduct = keyHolder.getKey().longValue();
            p.setProcuctId(returnidproduct);
            logger.info("returnidproduct : " + returnidproduct);
        }
    }
}

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

public List<AccountOperation> getAccountOperationsForUser(int user_id, Date date, int count)
        throws SQLException {
    Connection connection = getConnection();
    List<AccountOperation> result = new ArrayList<AccountOperation>();
    PreparedStatement statement = null;
    try {/*from  w  ww  .j  a  va  2 s .c om*/
        statement = connection.prepareStatement(GET_ALL_ACCOUNTOPERATIONS_FOR_USER_STATEMENT);
        statement.setTimestamp(1, new Timestamp(date.getTime()));
        statement.setInt(2, user_id);
        statement.setInt(3, user_id);
        statement.setInt(4, user_id);
        statement.setInt(5, count); // how many records
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            AccountOperation item = new AccountOperation();
            item.setId(rs.getLong(1));
            item.setUser_id(rs.getInt(2));
            item.setType(rs.getInt(3));
            item.setAmount(rs.getLong(4));
            item.setDate(rs.getTimestamp(5));
            item.setSource_user_id(rs.getInt(6));
            item.setDestination_user_id(rs.getInt(7));
            item.setSourceUserName(rs.getString(8));
            item.setDestinationUserName(rs.getString(9));
            result.add(item);
        }
    } catch (SQLException e) {
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
    return result;
}

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

public List<DepositWithdrawal> getDepositsWithdrawalsForUser(int user_id, DepositWithdrawal.OperationType type,
        Date date, int page, int pageSize) throws SQLException {
    Connection connection = getConnection();
    List<DepositWithdrawal> result = new ArrayList<DepositWithdrawal>();
    PreparedStatement statement = null;
    try {/* w  ww.j a  va  2s .  co  m*/
        statement = connection.prepareStatement(GET_ALL_DEPOSITWITHDRAWAL_FOR_USER_STATEMENT);
        statement.setTimestamp(1, new Timestamp(date.getTime()));
        statement.setInt(2, user_id);
        if (type == OperationType.DEPOSIT) {
            statement.setInt(3, ACCOUNT_OPERATION_DEPOSIT);
        } else {
            statement.setInt(3, ACCOUNT_OPERATION_WITHDRAWAL);
        }
        if (page <= 0)
            page = 1;
        statement.setInt(4, (page - 1) * (pageSize - 1)); // the offset
        statement.setInt(5, pageSize); // how many records
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            DepositWithdrawal item = new DepositWithdrawal();
            item.setId(rs.getLong(1));
            item.setUser_id(rs.getInt(2));
            item.setType(type);
            item.setAmount(rs.getLong(4));
            item.setDate(rs.getTimestamp(5));
            result.add(item);
        }
    } catch (SQLException e) {
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
    return result;
}

From source file:mitll.xdata.dataset.kiva.ingest.KivaIngest.java

public static int executePreparedStatement(PreparedStatement statement, List<String> types, List<String> values)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss Z");
    try {//from   w w  w  .ja  v  a  2s .  c o m
        for (int i = 0; i < types.size(); i++) {
            String type = TYPE_TO_DB.get(types.get(i).toUpperCase());
            String value = values.get(i);
            if (value != null && value.trim().length() == 0) {
                value = null;
            }
            if (type.equalsIgnoreCase("INT")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.INTEGER);
                } else {
                    statement.setInt(i + 1, Integer.parseInt(value, 10));
                }
            } else if (type.equalsIgnoreCase("DOUBLE")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.DOUBLE);
                } else {
                    statement.setDouble(i + 1, Double.parseDouble(value));
                }
            } else if (type.equalsIgnoreCase("BOOLEAN")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.BOOLEAN);
                } else {
                    statement.setBoolean(i + 1, Boolean.parseBoolean(value));
                }
            } else if (type.equalsIgnoreCase("VARCHAR")) {
                statement.setString(i + 1, value);
            } else if (type.equalsIgnoreCase("TIMESTAMP")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.TIMESTAMP);
                } else {
                    statement.setTimestamp(i + 1, new Timestamp(sdf.parse(value).getTime()));
                }
            }
        }
    } catch (Throwable e) {
        System.out.println("types = " + types);
        System.out.println("values = " + values);
        System.out.println("types.size() = " + types.size());
        System.out.println("values.size() = " + values.size());
        e.printStackTrace();
        System.out.println(e.getMessage());
        throw new Exception(e);
    }
    return statement.executeUpdate();
}

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

/**
 * @param oldDBConn//from   ww w.  jav a 2 s .c om
 * @param newDBConn
 * @param disciplineID
 * @return
 */
public static boolean convertKUFishCruiseDataOld(final Connection oldDBConn, final Connection newDBConn,
        final int disciplineID) {
    PreparedStatement pStmt1 = null;
    PreparedStatement pStmt2 = null;
    try {
        Timestamp now = new Timestamp(System.currentTimeMillis());
        pStmt1 = newDBConn.prepareStatement(
                "INSERT INTO collectingtrip (CollectingTripName, DisciplineID, TimestampCreated, Version) VALUES(?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pStmt2 = newDBConn.prepareStatement(
                "INSERT INTO collectingevent (CollectingTripID, DisciplineID, stationFieldNumber, Method, StartTime, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?,?,?,?)");

        String sql = "SELECT Text1, Text2, Number1, TimestampCreated, TimestampModified FROM stratigraphy";
        Vector<Object[]> rows = BasicSQLUtils.query(oldDBConn, sql);
        for (Object[] row : rows) {
            pStmt1.setString(1, "Cruise");
            pStmt1.setInt(2, disciplineID);
            pStmt1.setTimestamp(3, now);
            pStmt1.setInt(4, 0);
            pStmt1.execute();

            Integer intsertId = BasicSQLUtils.getInsertedId(pStmt1);
            String vessel = (String) row[0];
            String cruiseName = (String) row[1];
            Integer number = row[2] != null ? ((Double) row[2]).intValue() : null;

            pStmt2.setInt(1, intsertId);
            pStmt2.setInt(2, disciplineID);
            pStmt2.setString(3, vessel);
            pStmt2.setString(4, cruiseName);
            if (number != null) {
                pStmt2.setInt(5, number);
            }
            pStmt2.setTimestamp(6, (Timestamp) row[3]);
            pStmt2.setTimestamp(7, (Timestamp) row[4]);
            pStmt2.setInt(8, 0);

            pStmt2.execute();
        }
        return true;

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

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

        } catch (Exception ex) {
        }
    }

    return false;
}

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

public int removeOldRecord() {
    Connection conn = null;/*from   w  w  w .  ja  v a  2  s. co m*/
    PreparedStatement stmt = null;
    int affected = 0;
    long ts = System.currentTimeMillis() - auditDbRetentionMs;
    try {
        logger.info("Start to remove old records per timestamp={} with retentionMs={}", ts, auditDbRetentionMs);

        conn = getConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(String.format(DELETE_METRICS_SQL, dataTableName));

        Timestamp dbTs = new Timestamp(ts);
        stmt.setTimestamp(1, dbTs);
        affected = stmt.executeUpdate();
        conn.commit();

        REMOVED_RECORDS_COUNTER.mark(affected);
        logger.info("Removed count={} old records per timestamp={} with retentionMs={}", affected, ts,
                auditDbRetentionMs);
    } catch (Exception e) {
        logger.warn("Got exception to remove old records", e);
        FAILED_TO_REMOVE_COUNTER.mark();
        rollback(conn);
    } finally {
        closeDbResource(null, stmt, conn);
    }

    return affected;
}

From source file:com.cloudera.sqoop.TestIncrementalImport.java

public void testModifyWithTimestamp() throws Exception {
    // Create a table with data in it; import it.
    // Then modify some existing rows, and verify that we only grab
    // those rows.

    final String TABLE_NAME = "modifyTimestamp";
    Timestamp thePast = new Timestamp(System.currentTimeMillis() - 100);
    createTimestampTable(TABLE_NAME, 10, thePast);

    List<String> args = getArgListForTable(TABLE_NAME, false, false);
    createJob(TABLE_NAME, args);/*from ww  w.  j  a  v  a2 s.c  o  m*/
    runJob(TABLE_NAME);
    assertDirOfNumbers(TABLE_NAME, 10);

    // Modify a row.
    long importWasBefore = System.currentTimeMillis();
    Thread.sleep(50);
    long rowsAddedTime = System.currentTimeMillis() - 5;
    assertTrue(rowsAddedTime > importWasBefore);
    assertTrue(rowsAddedTime < System.currentTimeMillis());
    SqoopOptions options = new SqoopOptions();
    options.setConnectString(SOURCE_DB_URL);
    HsqldbManager manager = new HsqldbManager(options);
    Connection c = manager.getConnection();
    PreparedStatement s = null;
    try {
        s = c.prepareStatement("UPDATE " + TABLE_NAME + " SET id=?, last_modified=? WHERE id=?");
        s.setInt(1, 4000); // the first row should have '4000' in it now.
        s.setTimestamp(2, new Timestamp(rowsAddedTime));
        s.setInt(3, 0);
        s.executeUpdate();
        c.commit();
    } finally {
        s.close();
    }

    // Import only the new row.
    clearDir(TABLE_NAME);
    runJob(TABLE_NAME);
    assertSpecificNumber(TABLE_NAME, 4000);
}

From source file:net.pms.dlna.DLNAMediaDatabase.java

public boolean isDataExists(String name, long modified) {
    boolean found = false;
    Connection conn = null;/*  w w w.j  a v a 2 s .c om*/
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
        conn = getConnection();
        stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
        stmt.setString(1, name);
        stmt.setTimestamp(2, new Timestamp(modified));
        rs = stmt.executeQuery();
        while (rs.next()) {
            found = true;
        }
    } catch (SQLException se) {
        logger.error(null, se);
        return false;
    } finally {
        close(rs);
        close(stmt);
        close(conn);
    }
    return found;
}