Using JDBC Inlists with Arrays
JDBC does not directly support inlists meaning when you have a query like this
SELECT val
FROM inlist_test_table
WHERE id IN (?)
you can pass only one scalar value to the PreparedStatement. There is no option to pass multiple values neither as Collection nor as Java array. Meaning if you want to pass two values you have to rewrite the query to
SELECT val
FROM inlist_test_table
WHERE id IN (?, ?)
and so forth. This is very inconvenient. It also not very efficient for server side statement caches. This can result in additional parsing overhead on the database server side. Some people use NamedParameterJdbcTemplate from Spring JDBC. However NamedParameterJdbcTemplate requires parsing and rewriting the query every time the query is executed with a different number of elements in the inlist. Additionally Oracle limits the number of bind values in an inlist to 1000 (ORA-01795).
There is however an easy solution using SQL arrays. The query can be rewritten to
SELECT val
FROM inlist_test_table
WHERE id = ANY(?)
and then a java.sql.Array can be passed to the PreparedStatement. This works with the following databases
- H2
- HSQLDB
- PostgreS
- Oracle
With Oracle a slightly different syntax has to be used
SELECT val
FROM inlist_test_table
WHERE id = ANY(SELECT column_value FROM TABLE(?))
Also HSQLDB requires a different syntax and there are some constraints on datatypes. See this stackoverflow discussion for more information.
SELECT val
FROM inlist_test_table
WHERE id IN(UNNEST(?))
There is an additional caveat with Oracle in that Oracle does not support anonymous arrays, instead custom array types have to be created. Additionally the Oracle JDBC driver only supports creating JDBC arrays using proprietary APIs. If you are using Spring JdbcTemplate then the SqlOracleArrayValue class from ferstl/spring-jdbc-oracle does the binding for you and can be used as a bind parameter.
jdbcOperations.queryForObject(SQL,
new SqlOracleArrayValue("MYARRAYTYPE", array),
Integer.class);