Java Code Examples for java.sql.PreparedStatement#setDate()

private ResultSet getNonUniqQuery5(Connection conn, int whichQuery,  
    String symbol, Date since, boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + nonUniqSelect[whichQuery]);
    stmt = conn.prepareStatement(nonUniqSelect[whichQuery]);      
    Log.getLogWriter().info("data used in query symbol " + symbol 
        + " and since: " + since);
    stmt.setString(1, symbol);
    stmt.setDate(2, since);
    rs = stmt.executeQuery();
  } catch (SQLException se) {
    SQLHelper.printSQLException(se);
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 

@Test
public void testDateAddCompareDate() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and date + 1 >= ?"; 
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 8 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, 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();
    }
}
 

@Test
public void testTruncateNotTraversableToFormScanKey() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and TRUNC(date,'DAY') <= ?"; 
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = toDate("2013-01-01 00:00:00");
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate, 0.5);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * 0.25)));
        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();
    }
}
 

@Test
public void testDateInList() throws Exception {
    String query = "SELECT entity_id FROM " + tableName + " WHERE a_date IN (?,?) AND a_integer < 4";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setDate(1, new Date(0));
        statement.setDate(2, date);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW1, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

private ResultSet getNonUniqQuery0(Connection conn, int whichQuery,  
    Date since, boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + nonUniqSelect[whichQuery]);
    stmt = conn.prepareStatement(nonUniqSelect[whichQuery]);      
    Log.getLogWriter().info("data used in query -- since: " + since);
    stmt.setDate(1, since);
    rs = stmt.executeQuery();
  } catch (SQLException se) {
    SQLHelper.printSQLException(se);
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 

@Override
public void inserir(Venda venda) throws Exception {
    Conexao c = new Conexao();
    String sql = "INSERT INTO TBVENDA (CODIGOCLIENTE, DATAVENDA, VALORTOTAL, SITUACAO) VALUES (?, ?, ?, ?)";
    PreparedStatement ps = c.getConexao().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    ps.setInt(1, venda.getCliente().getCodigo());
    ps.setDate(2, new Date(venda.getDataVenda().getTime()));
    ps.setDouble(3, venda.getValorTotal());
    ps.setInt(4, venda.getSituacao().getId());
    ps.execute();

    ResultSet rs = ps.getGeneratedKeys();
    rs.next();
    int idVenda = rs.getInt(1);

    for (ItemVenda iv : venda.getItens()) {
        sql = "INSERT INTO TBITEMVENDA (CODIGOPRODUTO, CODIGOVENDA, QUANTIDADE, VALORUNITARIO) VALUES (?, ?, ?, ?)";
        ps = c.getConexao().prepareStatement(sql);
        ps.setInt(1, iv.getProduto().getCodigo());
        ps.setInt(2, idVenda);
        ps.setInt(3, iv.getQuantidade());
        ps.setDouble(4, iv.getValorUnitario());
        ps.execute();

        if (venda.getSituacao() == Situacao.FINALIZADA) {
            ProdutoDAO produtoDAO = new ProdutoDAO();
            produtoDAO.saidaEstoque(c, iv.getProduto().getCodigo(), iv.getQuantidade());
        }
    }
    c.confirmar();
}
 

private void updateData(String tableName, int startIndex, int endIndex) throws SQLException {
  Connection connection = TestUtil.getConnection();
  PreparedStatement ps = connection.prepareStatement("UPDATE " + tableName + " set blobField=?, charField=?," +
      "charForBitData=?, clobField=?, dateField=?, decimalField=?, doubleField=?, floatField=?, longVarcharForBitDataField=?, numericField=?," +
      "realField=?, smallIntField=?, timeField=?, timestampField=?, varcharField=?, varcharForBitData=?, xmlField=xmlparse(document cast (? as clob) PRESERVE WHITESPACE) where bigIntegerField=?");
 
  for (int i = startIndex; i < endIndex; i++) {
    int lessThan10 = i % 10;

    ps.setBlob(1,new ByteArrayInputStream(new byte[]{(byte)i,(byte)i,(byte)i,(byte)i}));
    ps.setString(2, ""+lessThan10);
    ps.setBytes(3, ("" + lessThan10).getBytes());
    ps.setClob(4, new StringReader("UPDATE CLOB " + i));
    ps.setDate(5, new Date(System.currentTimeMillis()));
    ps.setBigDecimal(6, new BigDecimal(lessThan10 + .8));
    ps.setDouble(7, i + .88);
    ps.setFloat(8, i + .9f);
    ps.setBytes(9, ("B" + lessThan10).getBytes());
    ps.setBigDecimal(10, new BigDecimal(i));
    ps.setFloat(11, lessThan10 * 1111);
    ps.setShort(12, (short)i);
    ps.setTime(13, new Time(System.currentTimeMillis()));
    ps.setTimestamp(14, new Timestamp(System.currentTimeMillis()));
    ps.setString(15, "BY" + lessThan10);
    ps.setBytes(16, ("" + lessThan10).getBytes());
    ps.setClob(17, new StringReader("<xml><sometag>UPDATE XML CLOB " + i + "</sometag></xml>"));
    ps.setLong(18, i);
    ps.execute();
  }
}
 

@Test
public void testNullValueEqualityScan() throws Exception {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(),PTSDB_NAME,null, ts-2);

    // Insert all rows at ts
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(true);
    PreparedStatement stmt = conn.prepareStatement("upsert into PTSDB VALUES ('', '', ?, 0.5)");
    stmt.setDate(1, D1);
    stmt.execute();
    conn.close();

    // Comparisons against null are always false.
    String query = "SELECT HOST,DATE FROM PTSDB WHERE HOST='' AND INST=''";
    url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

private static void initPtsdbTableValues2(long ts, Date d) throws Exception {
    ensureTableCreated(getUrl(),PTSDB2_NAME,null, ts-2);

    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts; // Insert at timestamp 0
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(true);
    PreparedStatement stmt = conn.prepareStatement("upsert into "+PTSDB2_NAME+"(inst,date,val2) VALUES (?, ?, ?)");
    stmt.setString(1, "a");
    stmt.setDate(2, d);
    stmt.setDouble(3, 101.3);
    stmt.execute();
    stmt.setString(1, "a");
    stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
    stmt.setDouble(3, 99.7);
    stmt.execute();
    stmt.setString(1, "a");
    stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
    stmt.setDouble(3, 105.3);
    stmt.execute();
    stmt.setString(1, "b");
    stmt.setDate(2, d);
    stmt.setDouble(3, 88.5);
    stmt.execute();
    stmt.setString(1, "b");
    stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
    stmt.setDouble(3, 89.7);
    stmt.execute();
    stmt.setString(1, "b");
    stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
    stmt.setDouble(3, 94.9);
    stmt.execute();
    conn.close();
}
 

/**
 * Updates the Relying Party if exists, if not, then creates a new Relying
 * Party
 *
 * @param rpdo
 */
public void update(OpenIDUserRPDO rpdo, int tenantId) {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;

    try {
        if (isUserRPExist(connection, rpdo, tenantId)) {
            // we should update the entry
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.UPDATE_USER_RP);

            prepStmt.setString(1, rpdo.getUserName());
            prepStmt.setInt(2, tenantId);
            prepStmt.setString(3, rpdo.getRpUrl());
            prepStmt.setString(4, rpdo.isTrustedAlways() ? "TRUE" : "FALSE");
            prepStmt.setDate(5, new java.sql.Date(rpdo.getLastVisit().getTime()));
            prepStmt.setInt(6, rpdo.getVisitCount() + 1);
            prepStmt.setString(7, rpdo.getDefaultProfileName());
            prepStmt.execute();
            connection.commit();
        } else {
            // we should create the entry
            if(log.isDebugEnabled()) {
                log.debug("Failed to update RP: " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName() + ". " +
                        "Entry does not exist in the database.");
            }
        }
    } catch (SQLException e) {
        log.error("Failed to update RP:  " + rpdo.getRpUrl() + " for user: " +
                rpdo.getUserName() + " Error while accessing the database", e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
}
 

/**
 *  Sets the SQL attribute of the CopyDate object
 *
 * @param  pStatement        The new SQL value
 * @exception  SQLException  Description of Exception
 * @since                    October 7, 2002
 */
@Override
public void setSQL(PreparedStatement pStatement) throws SQLException {
	if (!field.isNull()) {
	    if (inBatchUpdate){
                  pStatement.setDate(fieldSQL, new java.sql.Date(((DateDataField) field).getDate().getTime()));
	    }else{
	        dateValue.setTime(((DateDataField) field).getDate().getTime());
	        pStatement.setDate(fieldSQL, dateValue);
	    }
	} else {
		pStatement.setNull(fieldSQL, java.sql.Types.DATE);
	}
}
 

@Test
public void testDateInList() throws Exception {
    String query = "SELECT entity_id FROM ATABLE WHERE a_date IN (?,?) AND a_integer < 4";
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setDate(1, new Date(0));
        statement.setDate(2, date);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW1, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

public void setSince(int i, PreparedStatement ps) throws SQLException  {
  ps.setDate(i,since);
}
 

private void testDeleteAllFromTable(boolean autoCommit) throws SQLException {
    Connection con = null;
    try {
        con = DriverManager.getConnection(getUrl());
        con.setAutoCommit(autoCommit);

        Statement stm = con.createStatement();
        stm.execute("CREATE TABLE IF NOT EXISTS web_stats (" +
                "HOST CHAR(2) NOT NULL," +
                "DOMAIN VARCHAR NOT NULL, " +
                "FEATURE VARCHAR NOT NULL, " +
                "DATE DATE NOT NULL, \n" + 
                "USAGE.CORE BIGINT," +
                "USAGE.DB BIGINT," +
                "STATS.ACTIVE_VISITOR INTEGER " +
                "CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE))");
        stm.close();

        PreparedStatement psInsert = con
                .prepareStatement("UPSERT INTO web_stats(HOST, DOMAIN, FEATURE, DATE, CORE, DB, ACTIVE_VISITOR) VALUES(?,?, ? , ?, ?, ?, ?)");
        psInsert.setString(1, "AA");
        psInsert.setString(2, "BB");
        psInsert.setString(3, "CC");
        psInsert.setDate(4, new Date(0));
        psInsert.setLong(5, 1L);
        psInsert.setLong(6, 2L);
        psInsert.setLong(7, 3);
        psInsert.execute();
        psInsert.close();
        if (!autoCommit) {
            con.commit();
        }
        
        con.createStatement().execute("DELETE FROM web_stats");
        if (!autoCommit) {
            con.commit();
        }
        
        ResultSet rs = con.createStatement().executeQuery("SELECT /*+ NO_INDEX */ count(*) FROM web_stats");
        assertTrue(rs.next());
        assertEquals(0, rs.getLong(1));
    } finally {
        try {
            con.close();
        } catch (Exception ex) {
        }
    }
}
 

public void setTradeDate(int i, PreparedStatement ps) throws SQLException  {
  ps.setDate(i,tradeDate);
}
 

protected static void initTableValues(byte[][] splits, long ts) throws Exception {
    ensureTableCreated(getUrl(),PTSDB_NAME, splits, ts-2);

    // Insert all rows at ts
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(true);
    PreparedStatement stmt = conn.prepareStatement(
            "upsert into " +
            "PTSDB(" +
            "    INST, " +
            "    HOST," +
            "    DATE," +
            "    VAL)" +
            "VALUES (?, ?, ?, ?)");
    stmt.setString(1, "abc");
    stmt.setString(2, "abc-def-ghi");
    stmt.setDate(3, new Date(System.currentTimeMillis()));
    stmt.setBigDecimal(4, new BigDecimal(.5));
    stmt.execute();

    ensureTableCreated(getUrl(),BTABLE_NAME, splits, ts-2);
    conn.setAutoCommit(false);

    // Insert all rows at ts
    stmt = conn.prepareStatement(
            "upsert into " +
            "BTABLE(" +
            "    A_STRING, " +
            "    A_ID," +
            "    B_STRING," +
            "    A_INTEGER," +
            "    B_INTEGER," +
            "    C_INTEGER," +
            "    D_STRING," +
            "    E_STRING)" +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    stmt.setString(1, "abc");
    stmt.setString(2, "111");
    stmt.setString(3, "x");
    stmt.setInt(4, 1);
    stmt.setInt(5, 10);
    stmt.setInt(6, 1000);
    stmt.setString(7, null);
    stmt.setString(8, "0123456789");
    stmt.execute();

    stmt.setString(1, "abcd");
    stmt.setString(2, "222");
    stmt.setString(3, "xy");
    stmt.setInt(4, 2);
    stmt.setNull(5, Types.INTEGER);
    stmt.setNull(6, Types.INTEGER);
    stmt.execute();

    stmt.setString(3, "xyz");
    stmt.setInt(4, 3);
    stmt.setInt(5, 10);
    stmt.setInt(6, 1000);
    stmt.setString(7, "efg");
    stmt.execute();

    stmt.setString(3, "xyzz");
    stmt.setInt(4, 4);
    stmt.setInt(5, 40);
    stmt.setNull(6, Types.INTEGER);
    stmt.setString(7, null);
    stmt.execute();

    String ddl = "create table VarcharKeyTest" +
        "   (pk varchar not null primary key)";
    createTestTable(getUrl(), ddl, splits, ts-2);
    stmt = conn.prepareStatement(
            "upsert into " +
            "VarcharKeyTest(pk) " +
            "VALUES (?)");
    stmt.setString(1, "   def");
    stmt.execute();
    stmt.setString(1, "jkl   ");
    stmt.execute();
    stmt.setString(1, "   ghi   ");
    stmt.execute();

    conn.commit();
    conn.close();
}
 

@Override
public String generateResult() throws Exception {
    StringBuilder data = new StringBuilder();
    PreparedStatement stmt = getConnection().prepareStatement(sql);
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            String param = params[i];
            if (param.startsWith("%")) {
                switch (param.charAt(1)) {
                case 'B':
                    stmt.setBoolean(i + 1, Boolean.parseBoolean(param.substring(2)));
                    break;
                case 'b':
                    stmt.setByte(i + 1, Byte.parseByte(param.substring(2)));
                    break;
                case 's':
                    stmt.setShort(i + 1, Short.parseShort(param.substring(2)));
                    break;
                case 'i':
                    stmt.setInt(i + 1, Integer.parseInt(param.substring(2)));
                    break;
                case 'l':
                    stmt.setLong(i + 1, Long.parseLong(param.substring(2)));
                    break;
                case 'f':
                    stmt.setFloat(i + 1, Float.parseFloat(param.substring(2)));
                    break;
                case 'd':
                    stmt.setDouble(i + 1, Double.parseDouble(param.substring(2)));
                    break;
                case 'n':
                    stmt.setBigDecimal(i + 1, new java.math.BigDecimal(param.substring(2)));
                    break;
                case 'D':
                    stmt.setDate(i + 1, java.sql.Date.valueOf(param.substring(2)));
                    break;
                case 't':
                    stmt.setTime(i + 1, java.sql.Time.valueOf(param.substring(2)));
                    break;
                case 'T':
                    stmt.setTimestamp(i + 1, java.sql.Timestamp.valueOf(param.substring(2)));
                    break;
                default:
                    throw new IllegalArgumentException("Unknown type prefix " + param);
                }
            }
            else
                stmt.setString(i + 1, param);
        }
    }
    ResultSet rs;
    try {
        rs = stmt.executeQuery();
        if (executeTwice()) {
            rs.close();
            rs = stmt.executeQuery();
        }
    }
    catch (Exception ex) {
        if (error == null)
            forgetConnection();
        throw ex;
    }
    ResultSetMetaData md = rs.getMetaData();
    for (int i = 1; i <= md.getColumnCount(); i++) {
        if (i > 1) data.append('\t');
        data.append(md.getColumnLabel(i));
    }
    data.append('\n');
    while (rs.next()) {
        for (int i = 1; i <= md.getColumnCount(); i++) {
            if (i > 1) data.append('\t');
            data.append(rs.getString(i));
        }
        data.append('\n');
    }
    stmt.close();
    return data.toString();
}
 

protected void insertObjectDo(Object value, PreparedStatement statement, int index) throws SQLException {
    statement.setDate(index, (Date) value);
}
 

private static void initTableValues(String tablename, Connection conn, String tenantId) throws Exception {
    PreparedStatement stmt = conn.prepareStatement(
            "upsert into " + tablename +
                    " (" +
                    "    ORGANIZATION_ID, " +
                    "    \"DATE\", " +
                    "    FEATURE, " +
                    "    UNIQUE_USERS, " +
                    "    TRANSACTIONS, " +
                    "    CPU_UTILIZATION, " +
                    "    DB_UTILIZATION, " +
                    "    REGION, " +
                    "    IO_TIME)" +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    stmt.setString(1, tenantId);
    stmt.setDate(2, D1);
    stmt.setString(3, F1);
    stmt.setInt(4, 10);
    stmt.setLong(5, 100L);
    stmt.setBigDecimal(6, BigDecimal.valueOf(0.5));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.2));
    stmt.setString(8, R2);
    stmt.setNull(9, Types.BIGINT);
    stmt.execute();

    stmt.setString(1, tenantId);
    stmt.setDate(2, D2);
    stmt.setString(3, F1);
    stmt.setInt(4, 20);
    stmt.setLong(5, 200);
    stmt.setBigDecimal(6, BigDecimal.valueOf(1.0));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.4));
    stmt.setString(8, null);
    stmt.setLong(9, 2000);
    stmt.execute();

    stmt.setString(1, tenantId);
    stmt.setDate(2, D3);
    stmt.setString(3, F1);
    stmt.setInt(4, 30);
    stmt.setLong(5, 300);
    stmt.setBigDecimal(6, BigDecimal.valueOf(2.5));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.6));
    stmt.setString(8, R1);
    stmt.setNull(9, Types.BIGINT);
    stmt.execute();

    stmt.setString(1, tenantId);
    stmt.setDate(2, D4);
    stmt.setString(3, F2);
    stmt.setInt(4, 40);
    stmt.setLong(5, 400);
    stmt.setBigDecimal(6, BigDecimal.valueOf(3.0));
    stmt.setBigDecimal(7, BigDecimal.valueOf(0.8));
    stmt.setString(8, R1);
    stmt.setLong(9, 4000);
    stmt.execute();

    stmt.setString(1, tenantId);
    stmt.setDate(2, D5);
    stmt.setString(3, F3);
    stmt.setInt(4, 50);
    stmt.setLong(5, 500);
    stmt.setBigDecimal(6, BigDecimal.valueOf(3.5));
    stmt.setBigDecimal(7, BigDecimal.valueOf(1.2));
    stmt.setString(8, R2);
    stmt.setLong(9, 5000);
    stmt.execute();

    stmt.setString(1, tenantId);
    stmt.setDate(2, D6);
    stmt.setString(3, F1);
    stmt.setInt(4, 60);
    stmt.setLong(5, 600);
    stmt.setBigDecimal(6, BigDecimal.valueOf(4.0));
    stmt.setBigDecimal(7, BigDecimal.valueOf(1.4));
    stmt.setString(8, null);
    stmt.setNull(9, Types.BIGINT);
    stmt.execute();
}
 

@Before
public void initTable() throws Exception {
    long ts = nextTimestamp();
    createTestTable(getUrl(), TO_NUMBER_TABLE_DDL, null, ts-2);
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(false);
    
    PreparedStatement stmt = conn.prepareStatement(
            "upsert into " + TO_NUMBER_TABLE_NAME +
            "    (a_id, " +
            "    a_string," +
            "    b_string," +
            "    a_date," +
            "    a_time," +
            "    a_timestamp)" +
            "VALUES (?, ?, ?, ?, ?, ?)");
    
    stmt.setInt(1, 1);
    stmt.setString(2, "   1");
    stmt.setString(3, "   1");
    row1Date = new Date(System.currentTimeMillis() - 1000);
    row1Time = new Time(System.currentTimeMillis() - 1000);
    row1Timestamp = new Timestamp(System.currentTimeMillis() + 10000);
    stmt.setDate(4, row1Date);
    stmt.setTime(5, row1Time);
    stmt.setTimestamp(6, row1Timestamp);
    stmt.execute();
    
    stmt.setInt(1, 2);
    stmt.setString(2, " 2.2");
    stmt.setString(3, " 2.2");
    row2Date = new Date(System.currentTimeMillis() - 10000);
    row2Time = new Time(System.currentTimeMillis() - 1234);
    row2Timestamp = new Timestamp(System.currentTimeMillis() + 1234567);
    stmt.setDate(4, row2Date);
    stmt.setTime(5, row2Time);
    stmt.setTimestamp(6, row2Timestamp);
    stmt.execute();
    
    stmt.setInt(1, 3);
    stmt.setString(2, "$3.3");
    stmt.setString(3, "$3.3");
    row3Date = new Date(System.currentTimeMillis() - 100);
    row3Time = new Time(System.currentTimeMillis() - 789);
    row3Timestamp = new Timestamp(System.currentTimeMillis() + 78901);
    stmt.setDate(4, row3Date);
    stmt.setTime(5, row3Time);
    stmt.setTimestamp(6, row3Timestamp);
    stmt.execute();
    
    conn.commit();
    conn.close();
}