Query Proposal Request Object JPA IN

Question

How to write a request to build api criteria for a JPQL query below? I am using JPA 2.2 .

 SELECT * FROM Employee e WHERE e.Parent IN ('John','Raj') ORDER BY e.Parent 

Answer 1

This criteria setting should do the trick:

 CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Employee> q = cb.createQuery(Employee.class); Root<Employee> root = q.from(Employee.class); q.select(root); List<String> parentList = Arrays.asList(new String[]{"John", "Raj"}); Expression<String> parentExpression = root.get(Employee_.Parent); Predicate parentPredicate = parentExpression.in(parentList); q.where(parentPredicate); q.orderBy(cb.asc(root.get(Employee_.Parent)); q.getResultList(); 

I used the overloaded CriteriaQuery.where method here, which takes the predicate Predicate .. an in in this case.

Answer 2

 You can also do this with Criteria API In clause as below: CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Employee> cq = cb.createQuery(Employee.class); Root<Employee> root = cq.from(Employee.class); List<String> parentList = Arrays.asList("John", "Raj" ); In<String> in = cb.in(root.get(Employee_parent)); parentList.forEach(p -> in.value(p)); return entityManager .createQuery(cq.select(root) .where(in).orderBy(cb.asc(root.get(Employee_.Parent))) .getResultList(); 

Make out my GitHub for this and almost all possible examples of criteria.

https://github.com/vaneetkataria/Jpa-Hibernate/blob/master/jdbcToJpaMigration/src/test/java/com/katariasoft/technologies/jpaHibernate/entity/criteria/SingleTableCriteriaFetchTests.java

Answer 3

In order not to puzzle when you need to do it again using the Criteria API - you can create a parent wrapper class for your Dao and put the hepler method there :

 /** * An SQL "WHERE IN" expression alternative. * * @param inList List to search in. * @param pathToEntityField Path to a field in your entity object. * @return A ready predicate-condition to paste into CriteriaQuery.where(). */ @SuppressWarnings("unchecked") private Predicate in(List<?> inList, Expression pathToEntityField) { return pathToEntityField.in(inList); } 

Use WHERE IN then, like:

 query.where(**in**(myList, root.get(MyTypedEntity_.id))); 

Answer 4

I hope this can be helpful. The code is verified when Entry has only one @Id column:

 public static <Entry, Id> List<Entry> getByIds(List<Id> ids, Class<Entry> clazz, EntityManager entityManager) throws CustomDatabaseException { List<Entry> entries; try { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Entry> q = cb.createQuery(clazz); Root<Entry> root = q.from(clazz); EntityType<Entry> e = root.getModel(); CriteriaQuery<Entry> all = q.where(root.get(e.getId(e.getIdType().getJavaType()).getName()).in(ids)); TypedQuery<Entry> allQuery = entityManager.createQuery(all); entries = allQuery.getResultList(); } catch (NoResultException nre) { entries = Collections.emptyList() } catch (Exception e) { throw new CustomDatabaseException(e); } return entries; }