PreparedStatement question in Java against Oracle.

Question

Hi everyone,

I'm working on the modification of some code to use preparedStatement instead of normal Statement, for security and performance reason.

Our application is currently storing information into an embedded derby database, but we are going to move soon to Oracle.

I've found two things that I need your help guys about Oracle and Prepared Statement :

1- I've found this document saying that Oracle doesn't handle bind parameters into IN clauses, so we cannot supply a query like :

Select pokemon from pokemonTable where capacity in (?,?,?,?)

Is that true ? Is there any workaround ? ... Why ?

2- We have some fields which are of type TIMESTAMP. So with our actual Statement, the query looks like this :

Select raichu from pokemonTable where evolution = TO_TIMESTAMP('2500-12-31 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF')

What should be done for a prepared Statement ? Should I put into the array of parameters : 2500-12-31 or TO_TIMESTAMP('2500-12-31 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF') ?

Thanks for your help, I hope my questions are clear !

Regards,

Answer 1

I'm a bit surprised to see this document. It is true that you cannot set an array/collection like follows (and this is regardless of the database / JDBC driver used):

String sql = "SELECT col FROM tbl WHERE id IN (?)";
statement = connection.prepareStatement(sql);
statement.setArray(1, arrayOfValues); // Fail.

But the in the document mentioned query ought to work. I can tell this from experience with at least Oracle 10g XE in combination with ojdbc14.jar. I suspect that either the author of the document confused things, or it actually concerns a different (older?) version of the DB and/or JDBC driver.

The following ought to work regardless of the JDBC driver used (although you're dependent on the DB used how many items the IN clause can contain, Oracle (yes, again) has a limit of around 1000 items):

private static final String SQL_FIND = "SELECT id, name, value FROM data WHERE id IN (%s)";

public List<Data> find(Set<Long> ids) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Data> list = new ArrayList<Data>();
    String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));

    try{
        connection = database.getConnection();
        statement = connection.prepareStatement(sql);
        setValues(statement, ids.toArray());
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            Data data = new Data();
            data.setId(resultSet.getLong("id"));
            data.setName(resultSet.getString("name"));
            data.setValue(resultSet.getInt("value"));
            list.add(data);
        }
    } finally {
        close(connection, statement, resultSet);
    }

    return list;
}

public static String preparePlaceHolders(int length) {
    StringBuilder builder = new StringBuilder();
    for (int i = 0; i < length;) {
        builder.append("?");
        if (++i < length) {
            builder.append(",");
        }
    }
    return builder.toString();
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

With regard to the TIMESTAMP question, just use PreparedStatement#setTimestamp().