0

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?

user149408
  • 5,385
  • 4
  • 33
  • 69

1 Answers1

3

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();
Marco Behler
  • 3,627
  • 2
  • 17
  • 19
  • are you referring to http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sgc_array_def (note the chapter)? I’ve updated my question for clarification: I am wondering if this works when used in an in predicate (`lastname IN ('Stauder', 'Brugger', 'Wieser')`). – user149408 Apr 11 '20 at 21:51
  • 1
    You need to refer to this part http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_sql_predicates (IN Predicate) for the right example. Needs IN ( UNNEST(?) ). – fredt Apr 11 '20 at 21:57
  • @fredt this is exactly what I meant, why not make it an answer so I can accept it? – user149408 Apr 11 '20 at 22:00
  • Last question for clarification: do I really need to use these HSQLDB-specific types? Or would it be sufficient to just add the missing `UNNEST(?)` to the code in my question and otherwise leave it as is? – user149408 Apr 12 '20 at 16:36
  • Finished my code and tried it out: apparently only the `UNNEST(?)` is needed, the rest works as written in my question. – user149408 Apr 12 '20 at 23:13