JDBC prepared statements are ideal for backend optimization. The resulting SQL does not need to be re-optimized if you are using a parameterized SQL statement. Single parameters or even parameters by name with a single value are quite simple an easy, and you can find examples all over the internet. But if you would like to pass in an array into a parameter, it may be difficult to find examples.

This is an example of how to do this with an Oracle SQL statement.

First, you will need to create a custom data type in Oracle.

CREATE TYPE t_collection IS TABLE OF NUMBER;

Then in you jdbc client, you will need to use the OraclePreparedStatement class, and some of the other Oracle provided classes.

import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

Define your SQL statement.

select itemid, prompt from responses where itemid in (select value(v) from table(:item) v)

Notice: The (select value(v) from table(:item) v ) is using a nested table, thus the table(). We will pass in the :item as the t_collection type that we created which will be treated as a table.

Now in your method call you would:

// change from a jboss connection to an oracle connection
con = (OracleConnection)((WrappedConnection)getConnection()).getUnderlyingConnection();

// create a statement, apply the filters as parameters, add the item parameter, and execute
statement = (OraclePreparedStatement)con.prepareStatement(RESPONSES);
statement = applyFilter(statement, filter);

String[] items = itemid.split(”,”);
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(”T_COLLECTION”, con);
ARRAY sarray = new ARRAY(desc, con, items);
statement.setARRAYAtName(”item”, sarray);
rs = statement.executeQuery();