CODEWITHSUNDEEP
jdbchsqldb
user149408
user149408

Reputation: 5901

Does HSQLDB support setArray for in predicates in queries?

Suppose you want to issue a query such as the following to a HSQLDB instance over a JDBC connection:

select * from employees where lastname in ('Stauder', 'Brugger', 'Wieser');

Challenge: the list of names varies (the number of values as well as the values themselves), and we want to use a PreparedStatement.

According to this answer, the proper way to do this would be:

PreparedStatement statement = connection.prepareStatement(
    "select * from employees where lastname in (?)");
/* modify the following line to determine the values dynamically */
Array array = statement.getConnection().createArrayOf("VARCHAR",
    new Object[]{"Stauder", "Brugger", "Wieser"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();

However, comments indicate that not all JDBC drivers support this. The HSQLDB docs say that setArray() is supported as of version 2.0, but it is unclear to me if that means the example above will work, specifically using this construct with an in predicate. Can anyone shed some light on this?

Upvotes: 0

Views: 141

Answers (1)

Marco Behler
Marco Behler

Reputation: 3724

Take straight from the HSQLDB Documentation, this is how you should do it. (Major props to @fredt, as he actually had the complete right answer).

 String sql = "select * from employees where lastname in ( UNNEST(?) )";
 PreparedStatement ps = connection.prepareStatement(sql)
 Object[] data = new Object[]{"Stauder", "Brugger", "Wieser"};
 // default types defined in org.hsqldb.types.Type can be used
 org.hsqldb.types.Type type = org.hsqldb.types.Type.SQL_VARCHAR_DEFAULT;
 JDBCArrayBasic array = new JDBCArrayBasic(data, type);
 ps.setArray(1, array);
 ResultSet rs = ps.executeQuery();

Upvotes: 3

Related Questions