JPA Criteria API Queries (CriteriaBuilder, CriteriaQuery) Create Dynamic Query instead of appending String

Here is an example about how to create a Dynamic Query.

 This is very efficient when you have a method where some parameters may be null. so instead of creating a String builder (or String Buffer) and append the SQL Query, it is better to use the Criteria API Queries.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

public List<Devices> findDevice(String enterpriseId, List<String> modelIds, List<String> deviceIds, Date constructionDate) { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Devices> query = builder.createQuery(Devices.class); Root<Devices> deviceRoot = query.from(Devices.class); List<Predicate> predicateList = new ArrayList<Predicate>(); if(enterpriseId !=null ){ predicateList.add(builder.equal(deviceRoot.get("enterprise").get("enterpriseId"), enterpriseId)); } if(modelIds != null && !modelIds.isEmpty()){ predicateList.add(builder.isTrue(deviceRoot.get("deviceModel").get("modelId").in(modelIds))); } if(deviceIds != null && !deviceIds.isEmpty()){ predicateList.add(builder.isTrue(deviceRoot.get("deviceId").in(deviceIds))); } predicateList.add(builder.greaterThan(deviceRoot.<Date>get("constructionDate"), constructionDate)); query.where(builder.and(predicateList.toArray(new Predicate[predicateList.size()]))); return entityManager.createQuery(query).getResultList(); }

Hope it helped you :) 

JPA 2.0 Criteria Query/Builder DELETE : Typesafe DELETE queries

JPA 2.0 don't provide a dynamic way to write delete queries. In fact we can only write a type safe queries for the SELECT but not for the DELETE and Update.

JPA 2.1 provides this possibility using  CriteriaUpdate and CriteriaDelete. You can have more details in this link.


But actually, as I am obliged to use JPA 2.0 which comes with JBoss eap 6, and waiting for JPA 2.1, I should use the native queries.

So the problem here is that I have multiple lists that I should use in the query and that may be null.

Query query = entityManager.createNativeQuery("DELETE FROM person_record pr WHERE pr.person.person_id IN (select person_id from person p WHERE p.age IN ?1 AND p.job IN  ?2 ");

query.setParameter(1, ageList);
query.setParameter(2, jobList);

Here I am using a SELECT subquery. In fact if I use this query,

DELETE.... Where pr.person.age IN ?1

Hibernate will not accept it and throw an exception.

Problem:

The problem with the first query is that when ageList or the jobList is null, nothing will be returned. So we should put the parameter only if it is not null. We can so create a StringBuilder and put the first part of the request and according to what parameter we have, we create the rest of the query.

Solution: 

But a more safe think to do is to use the Criteria Builder in order to select the rows to delete, then delete the rows using entityManager.remove(entity). This is also very useful if we need to do a Cascade DELETE.

So the first think is to do like this:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery(PersonRecord.class);
Root root = query.from(PersonRecord.class);

List predicateList = new ArrayList();

if(ageList != null && !ageList .isEmpty()){
predicateList.add(builder.isTrue(root.get("person").get("age").in(ageList )));
}
if(jobList!= null && !jobList.isEmpty()){
predicateList.add(builder.isTrue(modelTypeRoot.get("person").get("job").in(jobList)));
}

query.where(builder.and(predicateList.toArray(new Predicate[predicateList.size()])));

List personToDelete= entityManager.createQuery(query).getResultList();

for(PersonRecord item:personToDelete){
       // Make the entity attached again
entityManager.merge(item);
      // Here a CASCADE remove will be done easily
entityManager.remove(item);
}

This is only a temporarely solution till we will get the JBoss EAP with JPA 2.1

JPA Hibernate CriteriaBuilder, Order the returned rows, Limit Result and construct a new Bean in the result with Left Join

Using Criteria Builder, we need sometime to Order the result, and even to Limit the size of the returned rows. We need also to Create a new bean directly in the query with or without a LEFT JOIN. The join in used if the attribute (which is a foreign key) may be null.

To the above mentioned requirement using JPA criterai builder, the code is:

 CriteriaBuilder builder = entityManager.getCriteriaBuilder();
 CriteriaQuery query = builder.createQuery(MyPerson.class);
 Root root = query.from(Person.class);

query.select(builder.construct(MyPerson.class, 
root .get("personName"),
root .get("phone").get("mobilePhone"),
root .join("address", JoinType.LEFT).get("country"),
        root.get("age"),
));

query.orderBy(builder.desc(root.get("age")));

alerts = entityManager.createQuery(query).setMaxResults(MAX_RESULT).getResultList();

That's all, hope it helps you.

Hibernate JPA Change the implicit INNER JOIN with a LEFT JOIN with Criteria Builder and build a new Result bean: Only one LEFT JOIN Query is generated

Here my experience with the Generated Queries using my lovely Hibernate. I was stuck for about four days with one of the generated query, but finally I find the solution.

I was developing a code in order to get Persons from my tables using JPA (Hibernate implementation) and Criteria Builder . Performing the test, I have noticed that Hibernates returns only Persons which have a Phone number (as I have a foreign Key on a Contact table). Hibernate do by default an INNER JOIN, and not a LEFT JOIN.

SO to force Hibernate doing a LEFT JOIN with the criteria Builder (I was creating a dynamic query), I used this code:

Root root = query.from(Person.class);

Join phone = root.join("contact", JoinType.LEFT);
...
query.select(builder.construct(MyPerson.class, 
               root.get("contact").get("phone")

The generated Query where :

SELECT phone2.number, person1.name
FROM person person1
LEFT OUTER JOIN phones phone1 ON person1.contact_id = phone1.id
,phones phone2
WHERE 
person1.contact_id = phone2.id

So Hibernate IGNORES in fact my Left JOIN even if it put it in the query. It use the Phone2 and not Phone1 present in the Left Join.  So the rows with contact null, is not yet returned.

If I build my bean by giving the root only, it will generate many other requests.
query.select(builder.construct(MyPerson.class, root);

It was very hard to finally find the solution which consists of removing the Join and do the join while creating the new bean. So the code should be in fact like this:

Root root = query.from(Person.class);
query.select(builder.construct(MyPerson.class, 
   root.join("contact", JoinType.LEFT).get("phone"),...));

So finally the generated query is exactly what I wanted:

SELECT phone1.number, person1.name
FROM person person1
LEFT OUTER JOIN phones phone1 ON person1.contact_id = phone1.id

Resolve JPA Criteria Builder org.hibernate.QueryException: could not instantiate class from tuple

Using Criteria Builder in JPA, I construct a bean from the query result using this line:

query.select(builder.construct(MyNewBean.class, root));

I give here the root and not root.get("colName"), as I have difficulties in generating queries as I like. I will speak about that later. But now let us focus on the first problem.

While testing my code, I get

org.hibernate.QueryException: could not instantiate class className from tuple
at org.hibernate.transform.AliasToBeanConstructorResultTransformer.transformTuple
I have thought first that I shouldn't give root (as I was used to give root.get("user").get("name"), root.get("phone")). But the problems wasn't really that.
Just after reading again the error, I have found this :
Caused by: java.lang.NullPointerException

OK. The Problem is in my constructor. I do
this.userName = MyEntity.get("user").get("name");

In fact, User refers to  another entity, so when we get the user, it can be NULL. Which will generate the nullPointerException and thus the tuple Error. So before getting the entities attributes make sure that they are not null (No need to check that for the NOT NULL foreign Keys)

if( MyEntity.get("user") != null){
   this.userName = MyEntity.get("user").get("name");
}

An advice is to use also an EAGER fetch if you are sure that you need the parameters (to return to a Web Service Client). So, only one request will be generated. If you use a Lazy join, at every call to get, a new Select will be done which will degrade performance.