Example usage for java.sql PreparedStatement setDate

Introduction

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

Prototype

void setDate(int parameterIndex, java.sql.Date x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.

Usage

From source file:org.apache.phoenix.end2end.index.IndexExpressionIT.java

protected void helpTestUpdate(boolean localIndex) throws Exception {
    String dataTableName = MUTABLE_INDEX_DATA_TABLE;
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {//from w ww.j a v  a2s  .c o m
        conn.setAutoCommit(false);
        populateDataTable(conn, dataTableName);

        // create an expression index
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName
                + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
                + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )"
                + " INCLUDE (long_col1, long_col2)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();

        // update index pk column and covered column
        String upsert = "UPSERT INTO " + fullDataTableName
                + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        stmt = conn.prepareStatement(upsert);
        stmt.setString(1, "varchar1");
        stmt.setString(2, "char1");
        stmt.setInt(3, 1);
        stmt.setLong(4, 1l);
        stmt.setBigDecimal(5, new BigDecimal(0.5));
        stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
        stmt.setString(7, "a.varchar_updated");
        stmt.setLong(8, 101);
        stmt.executeUpdate();
        conn.commit();

        // verify only one row was updated in the data table
        String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from "
                + fullDataTableName;
        ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());

        // verify that the rows in the index table are also updated
        rs = conn.createStatement().executeQuery("SELECT " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());
        conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
    } finally {
        conn.close();
    }
}

---

From source file:org.apache.phoenix.end2end.DateTimeIT.java

@Test
public void testDateSubtractionCompareDate() throws Exception {
    String tablename = generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM " + tablename + " WHERE organization_id = ? and date - 1 >= ?";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {/*from ww  w.  j av a 2 s. c  o  m*/
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("F", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}

---

From source file:org.apache.phoenix.end2end.DateTimeIT.java

@Test
public void testDateAddCompareDate() throws Exception {
    String tablename = generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM " + tablename + " WHERE organization_id = ? and date + 1 >= ?";
    Connection conn = DriverManager.getConnection(url);
    try {/*  ww  w. ja  v a 2 s.  co m*/
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 8 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("E", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("F", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}

---

From source file:org.apache.phoenix.end2end.DateTimeIT.java

@Test
public void testDateSubtractionCompareNumber() throws Exception {
    String tablename = generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM " + tablename + " WHERE organization_id = ? and ? - \"DATE\" > 3";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {//from  ww w .  j av  a2s .  c  o m
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 6 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}

---

From source file:org.apache.phoenix.end2end.DateTimeIT.java

@Test
public void testDateSubtractionLongToDecimalCompareNumber() throws Exception {
    String tablename = generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM " + tablename + " WHERE organization_id = ? and ? - \"DATE\" - 1.5 > 3";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {/*from   www  .j a v  a  2s  .  c om*/
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("C", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}

---

From source file:org.openmrs.util.databasechange.CreateCodedOrderFrequencyForDrugOrderFrequencyChangeset.java

private void insertUniqueFrequencies(JdbcConnection connection, Set<String> uniqueFrequencies)
        throws CustomChangeException, SQLException, DatabaseException {
    PreparedStatement insertOrderFrequencyStatement = null;
    Boolean autoCommit = null;/*from ww  w .ja  va  2 s .  c o  m*/
    try {
        autoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        insertOrderFrequencyStatement = connection.prepareStatement("insert into order_frequency "
                + "(concept_id, creator, date_created, retired, uuid) values (?, ?, ?, ?, ?)");

        Date date = new Date(new java.util.Date().getTime());

        for (String frequency : uniqueFrequencies) {
            if (StringUtils.isBlank(frequency)) {
                continue;
            }

            Integer conceptIdForFrequency = UpgradeUtil.getConceptIdForUnits(frequency);
            if (conceptIdForFrequency == null) {
                throw new CustomChangeException("No concept mapping found for frequency: " + frequency);
            }

            Integer orderFrequencyId = UpgradeUtil.getOrderFrequencyIdForConceptId(
                    connection.getUnderlyingConnection(), conceptIdForFrequency);
            if (orderFrequencyId != null) {
                //a single concept is mapped to more than one text or there is an order frequency already
                continue;
            }

            //Generating UUID for order frequency. Generated UUIDs will be the same if concepts UUIDs are the same.
            String uuid = UpgradeUtil.getConceptUuid(connection.getUnderlyingConnection(),
                    conceptIdForFrequency);
            uuid += "-6925ebb0-7c69-11e3-baa7-0800200c9a66"; //Adding random value for order frequency
            uuid = UUID.nameUUIDFromBytes(uuid.getBytes()).toString();

            insertOrderFrequencyStatement.setInt(1, conceptIdForFrequency);
            insertOrderFrequencyStatement.setInt(2, 1);
            insertOrderFrequencyStatement.setDate(3, date);
            insertOrderFrequencyStatement.setBoolean(4, false);
            insertOrderFrequencyStatement.setString(5, uuid);

            insertOrderFrequencyStatement.executeUpdate();
            insertOrderFrequencyStatement.clearParameters();
        }
        connection.commit();
    } catch (DatabaseException e) {
        handleError(connection, e);
    } catch (SQLException e) {
        handleError(connection, e);
    } finally {
        if (autoCommit != null) {
            connection.setAutoCommit(autoCommit);
        }
        if (insertOrderFrequencyStatement != null) {
            insertOrderFrequencyStatement.close();
        }
    }
}

---

From source file:com.tremolosecurity.provisioning.core.providers.BasicDB.java

private PreparedStatement updateField(User user, Connection con, StringBuffer b, String attrName, String userID,
        int userIDnum) throws SQLException {
    b.setLength(0);/*from  ww  w .  j a  v  a  2s  .co m*/
    b.append("UPDATE ").append(this.userTable).append(" SET ");
    this.getFieldName(attrName, b).append("=? WHERE ");
    this.getFieldName(this.userPrimaryKey, b).append("=?");
    PreparedStatement ps = con.prepareStatement(b.toString());
    ps.setString(1, user.getAttribs().get(attrName).getValues().get(0));
    if (userIDnum != -1) {
        ps.setInt(2, userIDnum);
    } else {

        Attribute.DataType tat = user.getAttribs().get(attrName).getDataType();
        switch (tat) {
        case string:
            ps.setString(2, userID);
            break;
        case intNum:
            ps.setInt(2, Integer.parseInt(userID));
            break;
        case longNum:
            ps.setLong(2, Long.parseLong(userID));
            break;

        case date:
            ps.setDate(2, new Date(ISODateTimeFormat.date().parseDateTime(userID).getMillis()));
            break;
        case timeStamp:
            ps.setTimestamp(2, new Timestamp(ISODateTimeFormat.dateTime().parseDateTime(userID).getMillis()));
            break;
        }

        ps.setString(2, userID);
    }
    ps.executeUpdate();
    return ps;
}

---

From source file:org.kuali.kfs.gl.batch.dataaccess.impl.LedgerPreparedStatementCachingDaoJdbc.java

public void insertEntry(final Entry entry, final Timestamp currentTimestamp) {
    new InsertingJdbcWrapper<Entry>() {
        @Override//from w  w w.j  ava 2s . co m
        protected void populateStatement(PreparedStatement preparedStatement) throws SQLException {
            preparedStatement.setInt(1, entry.getUniversityFiscalYear());
            preparedStatement.setString(2, entry.getChartOfAccountsCode());
            preparedStatement.setString(3, entry.getAccountNumber());
            preparedStatement.setString(4, entry.getSubAccountNumber());
            preparedStatement.setString(5, entry.getFinancialObjectCode());
            preparedStatement.setString(6, entry.getFinancialSubObjectCode());
            preparedStatement.setString(7, entry.getFinancialBalanceTypeCode());
            preparedStatement.setString(8, entry.getFinancialObjectTypeCode());
            preparedStatement.setString(9, entry.getUniversityFiscalPeriodCode());
            preparedStatement.setString(10, entry.getFinancialDocumentTypeCode());
            preparedStatement.setString(11, entry.getFinancialSystemOriginationCode());
            preparedStatement.setString(12, entry.getDocumentNumber());
            preparedStatement.setInt(13, entry.getTransactionLedgerEntrySequenceNumber());
            preparedStatement.setString(14, entry.getTransactionLedgerEntryDescription());
            preparedStatement.setBigDecimal(15, entry.getTransactionLedgerEntryAmount().bigDecimalValue());
            preparedStatement.setString(16, entry.getTransactionDebitCreditCode());
            preparedStatement.setDate(17, entry.getTransactionDate());
            preparedStatement.setString(18, entry.getOrganizationDocumentNumber());
            preparedStatement.setString(19, entry.getProjectCode());
            preparedStatement.setString(20, entry.getOrganizationReferenceId());
            preparedStatement.setString(21, entry.getReferenceFinancialDocumentTypeCode());
            preparedStatement.setString(22, entry.getReferenceFinancialSystemOriginationCode());
            preparedStatement.setString(23, entry.getReferenceFinancialDocumentNumber());
            preparedStatement.setDate(24, entry.getFinancialDocumentReversalDate());
            preparedStatement.setString(25, entry.getTransactionEncumbranceUpdateCode());
            preparedStatement.setDate(26, entry.getTransactionPostingDate());
            preparedStatement.setTimestamp(27, currentTimestamp);
        }
    }.execute(Entry.class);
}

---

From source file:org.apache.phoenix.end2end.DateTimeIT.java

@Test
public void testProjectedDateTimestampUnequal() throws Exception {
    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE IF NOT EXISTS " + tableName
            + " (k1 INTEGER PRIMARY KEY, dates DATE, timestamps TIMESTAMP)";
    conn.createStatement().execute(ddl);
    // Differ by date
    String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_DATE('2004-02-04 00:10:10'),"
            + "TO_TIMESTAMP('2006-04-12 00:10:10'))";
    conn.createStatement().execute(dml);
    // Differ by time
    dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_DATE('2004-02-04 00:10:10'), "
            + "TO_TIMESTAMP('2004-02-04 15:10:20'))";
    conn.createStatement().execute(dml);
    // Differ by nanoseconds
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)");
    stmt.setInt(1, 3);/*from  w  w w. j a  va 2  s  . co m*/
    stmt.setDate(2, new Date(1000));
    Timestamp ts = new Timestamp(1000);
    ts.setNanos(100);
    stmt.setTimestamp(3, ts);
    stmt.execute();
    // Equality
    dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_DATE('2004-02-04 00:10:10'), "
            + "TO_TIMESTAMP('2004-02-04 00:10:10'))";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName);
    assertTrue(rs.next());
    assertEquals(false, rs.getBoolean(1));
    assertTrue(rs.next());
    assertEquals(false, rs.getBoolean(1));
    assertTrue(rs.next());
    assertEquals(false, rs.getBoolean(1));
    assertTrue(rs.next());
    assertEquals(true, rs.getBoolean(1));
    assertFalse(rs.next());
}

---

From source file:org.apache.phoenix.end2end.DateTimeIT.java

@Test
public void testProjectedDateUnsignedTimestampCompare() throws Exception {
    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE IF NOT EXISTS " + tableName
            + " (k1 INTEGER PRIMARY KEY, dates DATE, timestamps UNSIGNED_TIMESTAMP)";
    conn.createStatement().execute(ddl);
    // Differ by date
    String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_DATE('2004-02-04 00:10:10'),"
            + "TO_TIMESTAMP('2006-04-12 00:10:10'))";
    conn.createStatement().execute(dml);
    // Differ by time
    dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_DATE('2004-02-04 00:10:10'), "
            + "TO_TIMESTAMP('2004-02-04 15:10:20'))";
    conn.createStatement().execute(dml);
    // Differ by nanoseconds
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)");
    stmt.setInt(1, 3);/*from w  w w.jav a2  s  .c om*/
    stmt.setDate(2, new Date(1000));
    Timestamp ts = new Timestamp(1000);
    ts.setNanos(100);
    stmt.setTimestamp(3, ts);
    stmt.execute();
    // Equality
    dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_DATE('2004-02-04 00:10:10'), "
            + "TO_TIMESTAMP('2004-02-04 00:10:10'))";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName);
    assertTrue(rs.next());
    assertEquals(false, rs.getBoolean(1));
    assertTrue(rs.next());
    assertEquals(false, rs.getBoolean(1));
    assertTrue(rs.next());
    assertEquals(false, rs.getBoolean(1));
    assertTrue(rs.next());
    assertEquals(true, rs.getBoolean(1));
    assertFalse(rs.next());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• »