DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Using AUTHID Parameter in Oracle PL/SQL
  • Minimizing Latency in Kafka Streaming Applications That Use External API or Database Calls
  • Spring Microservice Tip: Abstracting the Database Hostname With Environment Variable
  • A Guide to Enhanced Debugging and Record-Keeping

Trending

  • Google Cloud Document AI Basics
  • Unlocking AI Coding Assistants Part 3: Generating Diagrams, Open API Specs, And Test Data
  • Integrating Security as Code: A Necessity for DevSecOps
  • Event-Driven Architectures: Designing Scalable and Resilient Cloud Solutions
  1. DZone
  2. Data Engineering
  3. Databases
  4. Customize Query in Runtime With JPA

Customize Query in Runtime With JPA

Learn more about how you can customize a query at runtime with JPA.

By 
Jesus J. Puente user avatar
Jesus J. Puente
·
Feb. 26, 19 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
21.1K Views

Join the DZone community and get the full member experience.

Join For Free

Starting from the CriteriaQuery object, the desired function will be called based on the criteria to be applied. In this way, if we want to establish a condition that a field is equal to a value, we will call the functionequal(), passing as the first parameter of the expression that refers to the field of the entity and then the desired value. The object Expression will be created simply by taking from the object Root previously defined and the name of the column on which the condition will be established.

There are times when the fields to restrict a query may vary at runtime. In that case, if you are using JPA, you can't use a  @Querystatement in our repository, because we don't know the fields on which the conditions will apply. 

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.

Using Lambdas and Streams to make the code cleaner and simpler, we go through the HashMap and add the defined conditions to the list of Predicates.

Starting from the CriteriaQuery object, the desired function will be called according to the criteria to be applied. In this way, if we want to establish as a condition that a field is equal to a value, we will call the functionequal(), passing as the first parameter the object Expression that refers to the field of the entity and the desired value. The object Expression will be created simply by taking from the object Root previously defined and the name of the column on which the condition will be established.

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!

Database Object (computer science) Spring Framework

Published at DZone with permission of Jesus J. Puente. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Using AUTHID Parameter in Oracle PL/SQL
  • Minimizing Latency in Kafka Streaming Applications That Use External API or Database Calls
  • Spring Microservice Tip: Abstracting the Database Hostname With Environment Variable
  • A Guide to Enhanced Debugging and Record-Keeping

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!