EclipseLink - How to use an empty collection in an IN statement?

Question

Is it possible to use an empty collection in an IN statement?

List<Integer> idList = new ArrayList<Integer>(); Query query = em.createQuery("SELECT e FROM T e WHERE e.id IN ?1").setParameter(1, idList); List results = query.getResultList(); 

gives:

 Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 48 Error Code: 0 Call: SELECT id, other_id FROM t_table WHERE (id IN ()) Query: ReadAllQuery(referenceClass=T sql="SELECT id, other_id FROM t_table WHERE (id IN ?)") 

This:

 List<Integer> idList = new ArrayList<Integer>(); CriteriaBuilder cb = EntityManagerProvider.getEmf().getCriteriaBuilder(); CriteriaQuery<T> c = cb.createQuery(T.class); Root<T> t_class = c.from(T.class); Expression<String> exp = t_class.get("id"); Predicate predicate = exp.in(idList); c.select(t_class).where(predicate); TypedQuery<T> q = em.createQuery(c); List results = q.getResultList(); 

gives:

 Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 48 Error Code: 0 Call: SELECT id, other_id FROM t_table WHERE (id IN ()) Query: ReadAllQuery(referenceClass=T sql="SELECT id, other_id FROM t_table WHERE (id IN ())") 

Entering zero explication in IN:

 Query query = em.createQuery("SELECT e FROM T e WHERE e.id IN (null)"); 

gives:

  java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing the query [SELECT e FROM T e WHERE e.id IN (null)], line 1, column 33: unexpected token [null]. Internal Exception: NoViableAltException( 54@ [709:9: (itemNode= scalarOrSubSelectExpression ( COMMA itemNode= scalarOrSubSelectExpression )* | subqueryNode= subquery )]) 

However, using your own query works fine:

 List<Integer> idList = new ArrayList<Integer>(); Query query = em.createNativeQuery("SELECT * FROM t_table WHERE t_table.id IN (null)"); List results = query.getResultList(); 

Obviously, I do not want to use my own queries. Of course, I can do if-else checks before creating a query and create another query for an empty collection, or in most cases not create it at all, because in simple queries there will be no results. But I have to change the OpenJPA provider (which accepts empty collections) to EclipseLink in a large project, and sometimes requests contain several collections in different INs, so if-elsing will not be so simple.

Answer 1

Just the answer: you cannot use an empty array or list and set it as a parameter when using the IN query. You can create your query using line concentration, and if the list is empty / size () == 0, you do not add the IN part to the query string or set the parameter.

There is no difference in the resulting query, if you have an empty collection in the IN parameter, you can simply remove this condition. In addition, your goal is not to find results. However, logically you can catch this before executing the request in your example:

"SELECT * FROM t_table WHERE t_table.id IN (null)"

=>, which may be needed: a) ALL the results are valid =>, then you can simply delete the condition where b) NO results are valid => then you do not need to execute the request and can simply return the empty t_table list to your method.

Sebastian

Answer 2

Try release 2.4.

Also try snapping zero,

.. in (: arg)

or pass a list with a null value in it

.. in: list