Customize Query in Runtime With JPA

In Spring Boot, we can provide a solution to this problem using a CriteriaBuilder class for our  EntityManager. In this post, I show you how to do it easily on your own.

To do this, I created a project that can be found here. 

In this program, we can make a REST request to the URL http://localhost:8080/get where I pass the following parameters, all optional:

• Customer ID: idCustomer 
• Client: nameCustomer 
• Customer Address: addressCustomer 
• Creation date of registration: CreatedDate. The date must be sent in the Spanish format, ie "yyyy-MM-dd," for example: 31/01/2018.
• Condition previous field: dateCondition. It has to be one of three strings: "greater,""less," and "equal." If you don't put any condition or put an invalid condition, it will use "greater."

Search URLs could be:

• http://localhost: 8080/get?CreatedDate=21.01.2018&dateCondition=equal
• http://localhost:8080/get?CreatedDate =21.01.2018 & dateCondition=greater
• http://localhost: 8080/get?nameCustomer=Smith & CreatedDate=01.21.2018

The program uses a database H2 to create a simple table of customers with next fields: id, name, address, email, and created_date. The table is filled after with the data that we can see in the file data.sql.

To make our custom QUERY, first, I create an interface in CustomersRepository, extending from  JpaRepository. In this interface, we define the function getData, as shown in the following code:

public  interface  CustomersRepository  extends  JpaRepository < CustomersEntity , Integer > {

public  List < CustomersEntity >  getData ( HashMap < String , Object >  conditions );
}

The function getData receives a HashMap where we will be putting the search conditions. So if we find customers whose client code is equal to 1, add the 'id' key and '1.'

If you want the name to be 'Smith,' we would add this item to HashMap: 

hm . put ( " name " , " Smith " );

And so on with all fields or desired conditions.

Once we have created our repository, we need to create a class that we call  CustomersRepositoryImpl. It is very important that it be called as our repository interface by adding the ending  impl (implementation). In this class, we must have a function equal to that defined in the repository because it is the function that Spring Boot executes when we call the function defined in the interface.

This is the code that allows you to customize our query:

public class CustomersRepositoryImpl{
@PersistenceContext
private EntityManager entityManager;

public List<CustomersEntity> getData(HashMap<String, Object> conditions)
{
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<CustomersEntity> query= cb.createQuery(CustomersEntity.class);
Root<CustomersEntity> root = query.from(CustomersEntity.class);

List<Predicate> predicates = new ArrayList<>();
conditions.forEach((field,value) ->
{
switch (field)
{
case "id":
predicates.add(cb.equal (root.get(field), (Integer)value));
break;
case "name":
predicates.add(cb.like(root.get(field),"%"+(String)value+"%"));
break;
case "address":
predicates.add(cb.like(root.get(field),"%"+(String)value+"%"));
break;
case "created":
String dateCondition=(String) conditions.get("dateCondition");
switch (dateCondition)
{
case GREATER_THAN:
predicates.add(cb.greaterThan(root.<Date>get(field),(Date)value));
break;
case LESS_THAN:
predicates.add(cb.lessThan(root.<Date>get(field),(Date)value));
break;
case EQUAL:
predicates.add(cb.equal(root.<Date>get(field),(Date)value));
                        break;
}
break;
}
});
query.select(root).where(predicates.toArray(new Predicate[predicates.size()]));
return entityManager.createQuery(query).getResultList(); 
}
}

As you can see, the first is to inject a reference to the object EntityManager labeled @PersistenceContext. In the function on theEntityManager, we create an object CriteriaBuilder, and for this purpose, we created a CriteriaQuery where we will be putting the different conditions of our Query. To find the pillars on which the query needs an object  Root, we will create from the previous objectCriteriaQuery.

Now, we create a list ofPredicate objects. In that list, we will include the conditions of our query.

Based on the object, CriteriaQuery will go calling the desired function as the criterion to apply. Thus, if we set a condition where the field is equal to a value, the function equal() will be called, passing, as the first parameter, the Expression object that refers to the entity field and then the desired value. The object Expression is created by taking the Root object defined above with the name of the column on which the condition is established.

If you want to add a condition where a field is like a text, the function like() will be called. If you want the field to have a higher value than the parameter, send greaterThan () and so on.

If the field is of type Date, you must specify the data type of the field, as shown in the code root.<Date>get(field), because otherwise, it does not correctly parse the date.

Highlight the name of the field defined in our logical entity and it does not have to be the same as the column in the database. For example, the date field in the project entity instance is created with the following statements:

@Column(name="created_date")
@Temporal(TemporalType.DATE)
Date created;

This is so that, in the database, the column was called created_date. But, all references to the entity will be made through the name created, and that is why when we seek the field name in Root object, I use the field createdand not the field created_date. Otherwise, it would give an error.

Once we have the conditions of the consultation established, we only need to prepare the query by calling the select function, which we will first indicate using  Root with the entity. Then, we will further prepare the query by calling the conditions established in the Predicate list, which we will have to convert previously to anArray. This is done with the sentence: query.select(root).where(predicates.toArray(new Predicate[predicates.size()]));

Now, we execute the select and collect the results in an object List with the commandentityManager.createQuery(query).getResultList().

Now, we will have our Query custom work! 

Remember: you can read more articles in Spanish about Spring and JPA at www.profesor-p.com!