PreparedStatement question in Java against Oracle

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 w开发者_如何学Pythonhere 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,

---

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().

---

Oracle does handle bind parameters in the IN clause, but expects each parameter to bind a single value of a type compatible with the expression before the IN keyword. What you often want is a variable length IN-list, and that isn't supported right away. However, an expr IN (subquery) variant of the IN clause together with unnesting of an Array will do the trick.

Oracle does not support anonymous array type, so you will need to define a named array type in the database, e.g.

create type NUM_LIST as table of number(10);

Make sure, that your connection is an OracleConnection. Use the table() function to unnest the input array. And use the createOracleArray() method (supported since Oracle 11.2) instead of the standard createArrayOf() method (which is not supported by the Oracle JDBC driver):

PreparedStatement statement = connection.prepareStatement("select pokemon from pokemonTable where capacity in (select * from table(?))");
Array array = ((OracleConnection)statement.getConnection()).createOracleArray("NUM_LIST", new int[]{1,2,3});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();

You may alternatively define NUM_LIST as a varray instead of table. But then you need a cast(? as NUM_LIST) inside the table() function.

---

Using IN lists

It's perfectly possible to create IN lists with JDBC like x IN (?, ?, ?). You're going to have to repeat the parameter markers ? manually, as suggested e.g. by BalusC and keep in mind:

• There's a limit of 1000 elements to IN lists, so you'll have to connect them with OR after that limit: x IN (?, ?, ..., ?) OR x IN (?, ?, ..., ?)

• If you have too many bind variables, using "inline values" can be better for the performance of individual queries, or you use VARRAY or TABLE types

• To prevent cursor cache contention problems by producing too many distinct sql strings and thus SQL_ID's which leads to many hard parses, I advise to again either use VARRAY or TABLE types if you have high numbers of parameters, or at least to use this little hack I call IN list padding, where you repeat the ? a power of 2 times, e.g.

• x IN (?)

• x IN (?, ?)

• x IN (?, ?, ?, ?)

• x IN (?, ?, ?, ?, ?, ?, ?, ?)

  That will reduce the number of distinct SQL_ID from O(N) to O(log(N)) at the price of having to repeat the bind value many times for large numbers, see again VARRAY or TABLE types

Using a dynamic SQL builder

It may be an option to use an off-the-shelf dynamic SQL builder for this query instead of rolling your own, which already handles all of the above transparently, e.g. jOOQ or others like the criteria API. Even Spring's JdbcTemplate has workarounds built in.

(Disclaimer: I work for the company behind jOOQ)

Using VARRAY or TABLE types

Others have pointed out the usage of VARRAY or TABLE types, but beware this approach can have a significant performance penalty for small array numbers. In some unrepresentative benchmarks, I've found that the IN list can still outperform the array for sizes < 100 (measure yourself). Also, there's a possibility of getting very wrong cardinality estimates, which I also documented in the above article