I stumbled upon this issue as well. I would ultimately like to execute the following JPQL: SELECT COUNT(u) FROM ( SELECT DISTINCT u FROM User u JOIN u.roles r WHERE r.id IN (1) ) But this wasn’t possible, also not with criteria API. Research taught that this was just a design limitation in JPA. The … Read more
You can pass an array of predicates to the CriteriaBuilder, deciding on equal or like as you go. For this, build a list and pack the contents of the list into an array in a single and statement. Like this: final List<Predicate> predicates = new ArrayList<Predicate>(); for (final Entry<String, String> e : myPredicateMap.entrySet()) { final … Read more
Concept is to construct array of javax.persistence.Predicate which contains only predicates we want to use: Example entity to be queried: @Entity public class A { @Id private Long id; String someAttribute; String someOtherAttribute; … } Query itself: //some parameters to your method String param1 = “1”; String paramNull = null; CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaQuery … Read more
If I understand well, you want to Join ScheduleRequest with User and apply the in clause to the userName property of the entity User. I’d need to work a bit on this schema. But you can try with this trick, that is much more readable than the code you posted, and avoids the Join part … Read more
Instead of root.join(…) you can use root.fetch(…) which returns Fetch<> object. Fetch<> is descendant of Join<> but it can be used in similar manner. You just need to cast Fetch<> to Join<> it should work for EclipseLink and Hibernate … Join<MyEntity, RelatedEntity> join = (Join<MyEntity, RelatedEntity>)root.fetch(“relatedEntity”); …
Maybe the following extract from the Chapter 23 – Using the Criteria API to Create Queries of the Java EE 6 tutorial will throw some light (actually, I suggest reading the whole Chapter 23): Querying Relationships Using Joins For queries that navigate to related entity classes, the query must define a join to the related … Read more
I had the same issue and it was fixed by putting the Model and Model_ class into the same package.
One of the JPA ways for getting only particular columns is to ask for a Tuple object. In your case you would need to write something like this: CriteriaQuery<Tuple> cq = builder.createTupleQuery(); // write the Root, Path elements as usual Root<EntityClazz> root = cq.from(EntityClazz.class); cq.multiselect(root.get(EntityClazz_.ID), root.get(EntityClazz_.VERSION)); //using metamodel List<Tuple> tupleResult = em.createQuery(cq).getResultList(); for (Tuple t … Read more
Below is the pseudo-code for using sub-query using Criteria API. CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<EMPLOYEE> from = criteriaQuery.from(EMPLOYEE.class); Path<Object> path = from.get(“compare_field”); // field to map with sub-query from.fetch(“name”); from.fetch(“id”); CriteriaQuery<Object> select = criteriaQuery.select(from); Subquery<PROJECT> subquery = criteriaQuery.subquery(PROJECT.class); Root fromProject = subquery.from(PROJECT.class); subquery.select(fromProject.get(“requiredColumnName”)); // field to map with main-query subquery.where(criteriaBuilder.and(criteriaBuilder.equal(“name”,name_value),criteriaBuilder.equal(“id”,id_value))); select.where(criteriaBuilder.in(path).value(subquery)); … Read more