We are using JPA via jakarta.jakartaee-api as a dependency in our project, so that we can use the CriteriaBuilder/CriteriaQuery features. An array of Predicates is used to create multiple conditions. One of the Predicates is a used to build an "IN" clause.
<dependency>
<groupId>jakarta.platform</groupId>
<artifactId>jakarta.jakartaee-api</artifactId>
<version>11.0.0-M4</version>
<scope>provided</scope>
</dependency>
...
predicates = new Predicate[] {
...
criteriaBuilder.in(root.get("zone").in(zoneList))
...
}
criteriaQuery.where(predicates);
The list "zoneList" contains two elements.
The generated SQL part for the "IN" clause contains IN (NULL)
, and therefore no rows are found in the DB.
AND (
(
t0.zone IN (?, ?)
) IN (NULL)
)
Why is JPA adding IN (NULL)
in the generated SQL?
Here's the complete Java code
public List<DBEntity> report() {
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<DBEntity> criteriaQuery = cb.createQuery(DBEntity.class);
Root<DBEntity> root = criteriaQuery.from(DBEntity.class);
Predicate[] predicates = new Predicate[] {
cb.in(root.get("zone").in(Arrays.asList("Zone 1")))
};
criteriaQuery.where(predicates);
criteriaQuery.select(root);
TypedQuery<DBEntity> query = em.createQuery(criteriaQuery);
return query.getResultList();
}
server in use: openliberty : 0.9.3
The DB driver is postgresql:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.4</version>
</dependency>
The solution to create a Predicate object for an "IN CLAUSE" is as follow:
Predicate[] predicates = new Predicate[] {
root.get("zone").in(Arrays.asList("Zone 1"))
};