How do you create custom queries with JPA?

Table of Contents

• Introduction
• Methods to Create Custom Queries in JPA
  • 1. Using JPQL (Java Persistence Query Language)
    • Example of a JPQL Query
    • Explanation:
  • 2. Using Native SQL Queries
    • Example of a Native SQL Query
    • Explanation:
  • 3. Using Spring Data JPA Custom Query Methods
    • a. Using Derived Queries (Method Names)
    • b. Using the @Query Annotation for Custom Queries
    • c. Using Pagination with @Query
  • 4. Named Queries
    • Example of a Named Query
• Conclusion

Introduction

Java Persistence API (JPA) provides powerful tools to interact with databases through object-relational mapping (ORM). While JPA offers basic CRUD functionality, creating custom queries allows developers to retrieve and manipulate data more flexibly and efficiently. In this guide, we’ll cover the different ways to create custom queries with JPA, including using Java Persistence Query Language (JPQL), native SQL, and Spring Data JPA repository methods.

Methods to Create Custom Queries in JPA

1. Using JPQL (Java Persistence Query Language)

JPQL is an object-oriented query language that allows you to create queries against the database using entity classes and their properties rather than table names and columns. It closely resembles SQL but operates at the object level.

Example of a JPQL Query

Let’s say we have a Person entity and want to find people by their name:

To create a custom JPQL query, you can use the EntityManager's createQuery() method:

Explanation:

• SELECT p FROM Person p is a JPQL query that retrieves Person entities.
• WHERE p.name = :name is the filter condition, which binds the name parameter.

You can also use createQuery() with custom results:

2. Using Native SQL Queries

Sometimes, JPQL might not support certain database-specific features, and in such cases, you can use native SQL queries. JPA allows you to execute raw SQL directly by using the createNativeQuery() method.

Example of a Native SQL Query

For instance, to retrieve all people over a certain age using native SQL:

Explanation:

• createNativeQuery() executes a raw SQL query instead of a JPQL query.
• The result of the query can still be mapped to an entity class, as shown by the Person.class argument in the method.

You can also retrieve non-entity data (e.g., aggregates) using native SQL:

3. Using Spring Data JPA Custom Query Methods

Spring Data JPA provides an even more convenient way to define custom queries through repository methods. You can create queries using method names or the @Query annotation for more complex scenarios.

a. Using Derived Queries (Method Names)

Spring Data JPA allows you to define queries based on the naming convention of repository methods. The query is automatically generated by the framework.

Example of a derived query to find people by name:

Spring Data JPA automatically generates a query like SELECT p FROM Person p WHERE p.name = :name.

b. Using the @Query Annotation for Custom Queries

For more complex queries, you can use the @Query annotation to define a custom JPQL or SQL query directly on the repository method.

You can also use native SQL in @Query:

c. Using Pagination with @Query

Spring Data JPA also allows you to add pagination and sorting to custom queries. For example:

In this example, the Pageable parameter allows you to request a specific page of results along with sorting options.

4. Named Queries

JPA also supports named queries, which are pre-defined queries that are defined at the entity level using the @Query annotation or in the orm.xml file. These queries can then be referenced by their name in the EntityManager.

Example of a Named Query

In the Person entity, you can define a named query like this:

You can then use this named query in your code:

Conclusion

Creating custom queries in JPA allows you to execute complex and flexible database operations efficiently. Whether you’re using JPQL for object-oriented queries, native SQL for database-specific operations, or Spring Data JPA repository methods for automatic query generation, JPA offers several ways to work with the database. By leveraging the right approach for your use case, you can ensure your application performs optimally while maintaining clean, readable code.