Examples with PreparedStatement java.sql.PreparedStatement used on opensource projects

Example 16 with PreparedStatement

use of java.sql.PreparedStatement in project hive by apache.

the class TestJdbcDriver2 method testPrepareStatement.

@Test
public void testPrepareStatement() {
    String sql = "FROM (SELECT 1 FROM " + tableName + " where   'not?param?not?param' <> 'not_param??not_param' and ?=? " + " and 1=? and 2=? and 3.0=? and 4.0=? and 'test\\'string\"'=? and 5=? and ?=? " + " and date '2012-01-01' = date ?" + " and timestamp '2012-04-22 09:00:00.123456789' = timestamp ?" + " ) t SELECT '2011-03-25' ddate,'China',true bv, 10 num LIMIT 1";
    // executed twice: once with the typed ps setters, once with the generic setObject
    try {
        try (PreparedStatement ps = createPreapredStatementUsingSetXXX(sql);
            ResultSet res = ps.executeQuery()) {
            assertPreparedStatementResultAsExpected(res);
        }
        try (PreparedStatement ps = createPreapredStatementUsingSetObject(sql);
            ResultSet res = ps.executeQuery()) {
            assertPreparedStatementResultAsExpected(res);
        }
    } catch (Exception e) {
        e.printStackTrace();
        fail(e.toString());
    }
    // set nothing for prepared sql
    Exception expectedException = null;
    try (PreparedStatement ps = con.prepareStatement(sql);
        ResultSet ignored = ps.executeQuery()) {
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the un-setted sql statement should throw exception", expectedException);
    // set some of parameters for prepared sql, not all of them.
    expectedException = null;
    try (PreparedStatement ps = con.prepareStatement(sql)) {
        ps.setBoolean(1, true);
        ps.setBoolean(2, true);
        try (ResultSet ignored = ps.executeQuery()) {
        }
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException);
    // set the wrong type parameters for prepared sql.
    expectedException = null;
    try (PreparedStatement ps = con.prepareStatement(sql)) {
        // wrong type here
        ps.setString(1, "wrong");
        try (ResultSet res = ps.executeQuery()) {
            assertFalse("ResultSet was not empty", res.next());
        }
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException);
    // setObject to the yet unknown type java.util.Date
    expectedException = null;
    try (PreparedStatement ps = con.prepareStatement(sql)) {
        ps.setObject(1, new Date());
        try (ResultSet ignored = ps.executeQuery()) {
        }
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Setting to an unknown type should throw an exception", expectedException);
}

Example 17 with PreparedStatement

use of java.sql.PreparedStatement in project hive by apache.

the class TestJdbcDriver2 method testPrepareSetTimestamp.

@Test
public void testPrepareSetTimestamp() throws SQLException, ParseException {
    String sql = String.format("SELECT * FROM %s WHERE c17 = ?", dataTypeTableName);
    try (PreparedStatement ps = con.prepareStatement(sql)) {
        Timestamp timestamp = Timestamp.valueOf("2012-04-22 09:00:00.123456789");
        ps.setTimestamp(1, timestamp);
        // Ensure we find the single row which matches our timestamp (where field 1 has value 1)
        try (ResultSet resultSet = ps.executeQuery()) {
            assertTrue(resultSet.next());
            assertEquals(1, resultSet.getInt(1));
            assertFalse(resultSet.next());
        }
    }
}

Example 18 with PreparedStatement

use of java.sql.PreparedStatement in project hive by apache.

the class cbo_rp_TestJdbcDriver2 method createPreapredStatementUsingSetXXX.

private PreparedStatement createPreapredStatementUsingSetXXX(String sql) throws SQLException {
    PreparedStatement ps = con.prepareStatement(sql);
    //setBoolean
    ps.setBoolean(1, true);
    //setBoolean
    ps.setBoolean(2, true);
    //setShort
    ps.setShort(3, Short.valueOf("1"));
    //setInt
    ps.setInt(4, 2);
    //setFloat
    ps.setFloat(5, 3f);
    //setDouble
    ps.setDouble(6, Double.valueOf(4));
    //setString
    ps.setString(7, "test'string\"");
    //setLong
    ps.setLong(8, 5L);
    //setByte
    ps.setByte(9, (byte) 1);
    //setByte
    ps.setByte(10, (byte) 1);
    //setString
    ps.setString(11, "2012-01-01");
    //setTimestamp
    ps.setTimestamp(12, Timestamp.valueOf("2012-04-22 09:00:00.123456789"));
    ps.setMaxRows(2);
    return ps;
}

Example 19 with PreparedStatement

use of java.sql.PreparedStatement in project tomcat by apache.

the class JDBCRealm method getPassword.

/**
     * Get the password for the specified user.
     * @param username The user name
     * @return the password associated with the given principal's user name.
     */
@Override
protected synchronized String getPassword(String username) {
    // Look up the user's credentials
    String dbCredentials = null;
    // Number of tries is the number of attempts to connect to the database
    // during this login attempt (if we need to open the database)
    // This needs rewritten with better pooling support, the existing code
    // needs signature changes since the Prepared statements needs cached
    // with the connections.
    // The code below will try twice if there is a SQLException so the
    // connection may try to be opened again. On normal conditions (including
    // invalid login - the above is only used once.
    int numberOfTries = 2;
    while (numberOfTries > 0) {
        try {
            // Ensure that we have an open database connection
            open();
            PreparedStatement stmt = credentials(dbConnection, username);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    dbCredentials = rs.getString(1);
                }
                dbConnection.commit();
                if (dbCredentials != null) {
                    dbCredentials = dbCredentials.trim();
                }
                return dbCredentials;
            }
        } catch (SQLException e) {
            // Log the problem for posterity
            containerLog.error(sm.getString("jdbcRealm.exception"), e);
        }
        // Close the connection so that it gets reopened next time
        if (dbConnection != null) {
            close(dbConnection);
        }
        numberOfTries--;
    }
    return null;
}

Example 20 with PreparedStatement

use of java.sql.PreparedStatement in project tomcat by apache.

the class DataSourceRealm method credentials.

/**
     * Return a PreparedStatement configured to perform the SELECT required
     * to retrieve user credentials for the specified username.
     *
     * @param dbConnection The database connection to be used
     * @param username User name for which credentials should be retrieved
     * @return the prepared statement
     * @exception SQLException if a database error occurs
     */
private PreparedStatement credentials(Connection dbConnection, String username) throws SQLException {
    PreparedStatement credentials = dbConnection.prepareStatement(preparedCredentials);
    credentials.setString(1, username);
    return (credentials);
}