criteria-api

How to write Subqueries with In-Expressions in JPA 2.0, Criteria API?

by

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

Distinct results from Spring Data JPA Specification that uses join

by

Use the query parameter in your toPredicate method to invoke the distinct method. Sample below: public Predicate toPredicate(Root<Contact> root, CriteriaQuery<?> query, CriteriaBuilder cb) { final Predicate appPredicate = root.join(Contact_.managedApplications) .get(ManagedApplication_.managedApplicationId).in(appIds); query.distinct(true); …

JPA Criteria API with multiple parameters

by

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

Spring Data JPA: Creating Specification Query Fetch Joins

by

Specification class: public class MatchAllWithSymbol extends Specification<Gene> { private String symbol; public CustomSpec (String symbol) { this.symbol = symbol; } @Override public Predicate toPredicate(Root<Gene> root, CriteriaQuery<?> query, CriteriaBuilder cb) { //This part allow to use this specification in pageable queries //but you must be aware that the results will be paged in //application memory! Class … Read more

Really dynamic JPA CriteriaBuilder

by

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

JPA Criteria API – How to add JOIN clause (as general sentence as possible)

by

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