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

The following examples show how to use java.sql.PreparedStatement#setDate() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
@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();
    }
}
 
Example 2
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;
}
 
Example 3
@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();
    }
}
 
Example 4
Source Project: phoenix   File: InQueryIT.java    License: Apache License 2.0 6 votes vote down vote up
@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();
    }
}
 
Example 5
Source Project: phoenix   File: ProductMetricsIT.java    License: Apache License 2.0 6 votes vote down vote up
@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();
    }
}
 
Example 6
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;
}
 
Example 7
@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();
}
 
Example 8
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();
  }
}
 
Example 9
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();
}
 
Example 10
Source Project: carbon-identity   File: OpenIDUserRPDAO.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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);
    }
}
 
Example 11
/**
 *  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);
	}
}
 
Example 12
@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();
    }
}
 
Example 13
Source Project: phoenix   File: DeleteIT.java    License: Apache License 2.0 4 votes vote down vote up
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) {
        }
    }
}
 
Example 14
@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();
}
 
Example 15
Source Project: phoenix   File: ProductMetricsIT.java    License: Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 16
protected void insertObjectDo(Object value, PreparedStatement statement, int index) throws SQLException {
    statement.setDate(index, (Date) value);
}
 
Example 17
@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();
}
 
Example 18
Source Project: phoenix   File: VariableLengthPKIT.java    License: Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 19
public void setTradeDate(int i, PreparedStatement ps) throws SQLException  {
  ps.setDate(i,tradeDate);
}
 
Example 20
public void setSince(int i, PreparedStatement ps) throws SQLException  {
  ps.setDate(i,since);
}