SQLGrammarException when setting an empty set as an SQL IN parameter

Question

My code calls SQLGrammarExceptionwhen I set an empty set to the SQL IN parameter:

Query query = this.entMngr.createNativeQuery("SELECT foo_id, first, last FROM foo WHERE bar IN :barSet");
//barSet is a Set<Integer>
query.setParameter("barSet", barSet);
//this throws exception
List<Object> nativeList =  query.getResultList();

Everything works when the set is not empty. How can I do this agnostic about whether the collection is full (or any collection presented)?

Answer 1

The problem is that the SQL syntax does not allow an empty statement IN. Therefore, in your case barSetit should not be empty. But you can just add nullto the collection before passing it to the request:

barSet.add(null);
query.setParameter("barSet", barSet);

Here you can read about this trick: SQL In Clause with a null parameter

Answer 2

Query.setParameterList() https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Query.html#setParameterList(java.lang.String, java.util.Collection).

.

Query query = this.entMngr.createNativeQuery("SELECT foo_id, first, last FROM foo WHERE bar IN :barSet");
//barSet is a Set<Integer>
query.setParameterList("barSet", barSet); // as barSet is a Collection.
//this throws exception
List<Object> nativeList =  query.getResultList();