SQLGrammarException when setting empty set as SQL IN parameter

Question

My code causes a SQLGrammarException when I set an empty set into a 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 make this agnostic of whether the set (or any collection submitted) is populated or not?

Answer 1

The problem here is that SQL syntax doesn't allow empty IN clause. So in your case, barSet should not be empty. But you can simply add null to the collection before passing it to the query:

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

You can read about this trick here: SQL In Clause with Zero to Many Parameters