Spring Data JPA - Guide to the @Query Annotation

Introduction

If you have worked with Spring Data JPA for any length of time - you're probably acquainted with derived query methods:

public interface MyRepository extends JpaRepository<Client, Long> {
   List<Client> findByOrganizationName(String name);
}

They are a nifty and quick way to offload the burden of writing queries to Spring Data JPA by simply defining method names.

In this hypothetical scenario - we've defined a JpaRepository for an Entity class, which has an attribute called organizationName. Instead of implementing this method in a service implementing MyRepository - Spring Data JPA generates a query automatically given the name of the method. It'll generate a query that returns a list of all Entity records, with a matching organizationName.

Once the method is called, the following Hibernate request is made:

select 
client0_.id as id1_0_, 
client0_.age as age2_0_, 
client0_.name as name3_0_, 
client0_.organization as organiz4_0_ 
from 
client client0_ where client0_.organization=?

This is an extremely flexible and powerful feature of Spring Data JPA and it allows you to bootstrap queries without writing the queries themselves, or even implementing any handling logic in the back-end.

However, they become very difficult to create when complex queries are required:

public interface PropertyRepository extends JpaRepository<Property, Long> {
   List<Property> findPropertiesByTransactionTypeAndPropertyType(@Param("transaction_type") TransactionType transactionType, @Param("property_type") PropertyType propertyType);
}

And this is for just two parameters. What happens when you want to create a query for 5 parameters?

Also, just how many method variations will you create?

This is the point when you'll most likely want to prefer to write your own queries. This is doable via the @Query annotation.

The @Query annotation is applied at the method-level in JpaRepository interfaces, and pertains to a single method. The language used inside of the annotation depends on your back-end, or you can use the neutral JPQL (for relational databases).

In case of JpaRepository variants, such as MongoRepository, naturally, you'll be writing Mongo queries, while if you're using a relational database - you'll be writing SQL queries.

When the method is called - the query from within the @Query annotation fires and returns the results.

If you'd like to read more about writing MongoDB-native queries, read our Spring Data MongoDB: Guide to the @Query Annotation!

What is JPQL?

JPQL stands for the Java Persistence Query Language. It is defined in the JPA specification and is an object-oriented query language used to perform database operations on persistent entities.

JPA kicks in as the mediator and transpiles JPQL queries to SQL queries for execution.

Some of its features include:

• It is a platform-independent query language.
• It is simple and robust.
• It can be used with any type of relation database.
• It can be declared statically into metadata or can also be dynamically built in code.
• It is case insensitive.

If your database can change or varies from development to production, as long as they're both relational - JPQL works wonders and you can write JPQL queries to create generic logic that can be used over and over again.

If you're not already familiar with JPQL, read our Guide to Understanding JPQL (coming soon!)

If you're using a non-relational database, such as MongoDB - you'll write the queries native to that database.

Again, if you'd like to read more about writing MongoDB-native queries, read our Spring Data MongoDB: Guide to the @Query Annotation!

JPQL Query Structure

JPQL’s syntax is very similar to that of SQL. Since most developers are already familiar with SQL’s syntax, it becomes easy to learn and use JPQL.

The structures of JPQL's SELECT, UPDATE and DELETE queries are:

SELECT ... FROM ...
[WHERE ...]
[GROUP BY ... [HAVING ...]]
[ORDER BY ...]

DELETE FROM ... [WHERE ...]

UPDATE ... SET ... [WHERE ...]

We'll be writing these queries manually in the @Query annotation in a moment.

Understanding the @Query Annotation

The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward.

The @Query annotation supports both native SQL and JPQL. When native SQL is used, the nativeQuery parameter of the annotation should be set to true:

@Query("NATIVE_QUERY...", nativeQuery=true)
List<Entity> findAllByName(String name);

To select all clients from a database, we can use either a native query or JPQL:

@Query("SELECT(*) FROM CLIENT", nativeQuery=true)
List<Client> findAll();

@Query("SELECT client FROM Client client")
List<Client> findAll();

That's it. Your job is done - similar to how derived query methods take care of the job for you - this @Query fires when you call the findAll() method.

Though, if you only want to find all records, it's easier to use a derived query method - that's what they exist for. You'll want to write your own queries when there are dynamic variables you want to pass as parameters to the queries themselves.

Referencing Method Parameters

Both native queries and JPQL queries in the @Query annotation can accept annotated method parameters, which further classify into:

• Position-based parameters
• Named parameters

When using position-based parameters, you have to keep track of the order in which you supply the parameters in:

@Query("SELECT c FROM Client c WHERE c.name = ?1 AND c.age = ?2")
List<Client> findAll(String name, int age);

The first parameter passed to the method is mapped to ?1, the second is mapped to ?2, etc. If you accidentally switch these up - your query will likely throw an exception, or silently produce wrong results.

On the other hand, named parameters are, well, named and can be referenced by name, no matter their position:

@Query("SELECT c FROM Client c WHERE c.name = :name and c.age = :age")
List<Client> findByName(@Param("name") String name, @Param("age") int age);

The name within the @Param annotation is matched to the named parameters in the @Query annotation, so you're free to call your variables however you'd like - but for consistency's sake - it's advised to use the same name.

If you don't supply a matching @Param for a named parameter in the query - an exception is thrown at compile-time:

@Query("SELECT c FROM Client c WHERE c.name = :name and c.age = :age")
List<Client> findByName(@Param("name") String name, @Param("num1") int age);

Results in:

java.lang.IllegalStateException: Using named parameters for method public abstract ClientRepository.findByName(java.lang.String,int) but parameter 'Optional[num1]' not found in annotated query 'SELECT c FROM Client c WHERE c.name = :name and c.age = :age'!

SpEL Expressions with the @Query Annotation

SpEL (Spring Expression Language) is a language that supports querying and the manipulation of an object graph at runtime:

#{expression}

The most useful SpEL expression that can be used in the @Query annotation is #{#entityname}.

As the name indicates, it denotes entity name referenced by the repository you are in. It avoids stating the actual entity name and is resolved as follows:

If the domain type has set the name property on the @Entity annotation, it is used. Otherwise, the simple class name of the domain type is used.

In other words:

// #{#entityName} resolves to "client_entity"
@Entity(name = "client_entity")
public class Client {}

// #{#entityName} resolves to "Client"
@Entity()
public class Client {}

This makes it possible for us to abstract the queries as:

public interface ClientRepository extends JpaRepository<Client, Long> {     
    @Query("select e from #{#entityName} e where e.name = ?1")     
    List<Client> findByName(String name);
}

Modifying Queries

DELETE and UPDATE queries are called modifying queries and must carry an additional annotation: @Modifying. This will trigger the query in the @Query annotation to allow it to perform modifications on the entities, rather than just retrieving data.

Without the additional @Modifying annotation, we'd be facing a InvalidDataAccessApiUsageException, letting us know that the @Query annotation doesn't support DML (Data Manipulation Language) statements:

@Modifying
@Query(“DELETE c FROM Client c WHERE c.name = :name”)
void deleteClientByName(@Param("name") String name);

@Modifying
@Query(“UPDATE Client c WHERE c.id = :id”)
void updateUserById(@Param("id") long id);

Sorting and Paging

Both sorting and paging can be done the same way as for derived queries:

To create Pageable, and Sortable queries, you supply the Pageable parameter to the method, which Spring Data JPA automatically picks up, if you extend the PagingAndSortingRepository interface at least.

JpaRepository already extends PagingAndSortingRepository so the functionality is inherently there:

@Repository
public interface ClientRepository extends JpaRepository<Client, Long> {
    @Query("select e from #{#entityName} e where e.organization = ?1")
    Page<Client> findByOrganization(String name, Pageable pageable);
}

The return type of the method becomes Page<T>, List<T> or Slice<T>, though, Page<T> is the only one that keeps track of all the entities retrieved and the paginated results. The Pageable we're supposed to pass in now is constructed as a PageRequest.of(int page, int size, Sort sort).

We supply the page we're looking for, the size of the pages as well as how the data is sorted inside:

@RestController
public class Controller {

    @Autowired
    private ClientRepository clientRepository;

    @GetMapping(value = "/", produces = MediaType.TEXT_PLAIN_VALUE)
    public ResponseEntity main() {
        clientRepository.save(new Client(1, 22, "David", "StackAbuse"));
        clientRepository.save(new Client(2, 34, "John", "StackAbuse"));
        clientRepository.save(new Client(3, 46, "Melissa", "StackAbuse"));

        Pageable pageRequest = PageRequest.of(0, 10, Sort.by("age").descending());

        Page<Client> clientPage = clientRepository.findByOrganization("StackAbuse", pageRequest);

        List<Client> clientList = clientPage.getContent();
        int pageNum = clientPage.getNumber();
        long numOfClients = clientPage.getTotalElements();
        long totalNumOfPages = clientPage.getTotalPages();

        return ResponseEntity.ok(
                String.format("Clients: %s, \nCurrent page: %s out of %s, \nTotal entities: %s", 
                clientList, 
                pageNum, 
                totalNumOfPages, 
                numOfClients));
    }

If we take a look at the logs, we'll see the Hibernate queries kicking in and how they're transpiled:

2021-08-13 23:16:06.701 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : select client0_.id as id1_0_0_, client0_.age as age2_0_0_, client0_.name as name3_0_0_, client0_.organization as organiz4_0_0_ from client client0_ where client0_.id=?
2021-08-13 23:16:06.727 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into client (age, name, organization, id) values (?, ?, ?, ?)
2021-08-13 23:16:06.732 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : select client0_.id as id1_0_0_, client0_.age as age2_0_0_, client0_.name as name3_0_0_, client0_.organization as organiz4_0_0_ from client client0_ where client0_.id=?
2021-08-13 23:16:06.733 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into client (age, name, organization, id) values (?, ?, ?, ?)
2021-08-13 23:16:06.734 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : select client0_.id as id1_0_0_, client0_.age as age2_0_0_, client0_.name as name3_0_0_, client0_.organization as organiz4_0_0_ from client client0_ where client0_.id=?
2021-08-13 23:16:06.735 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into client (age, name, organization, id) values (?, ?, ?, ?)
2021-08-13 23:16:06.754 DEBUG 4252 --- [nio-8080-exec-1] org.hibernate.SQL                        : select client0_.id as id1_0_, client0_.age as age2_0_, client0_.name as name3_0_, client0_.organization as organiz4_0_ from client client0_ where client0_.organization=? order by client0_.age desc limit ?

Hitting the REST endpoint would result in:

Clients: [Client{id=3, age=46, name='Melissa', organization='StackAbuse'}, Client{id=2, age=34, name='John', organization='StackAbuse'}, Client{id=1, age=22, name='David', organization='StackAbuse'}], 
Current page: 0 out of 1, 
Total entities: 3

Conclusion

Derived query methods are a great query bootstrapping feature of Spring Data JPA but the simplicity comes at the cost of scalability. Although flexible - they're not ideal for scaling up to complex queries.

This is where Spring Data JPA's @Query annotation kicks in.

In this guide, we've taken a look at the @Query annotation and how to utilize it in Spring-based applications to write custom native and JPQL queries for your repositories.

We've explored the parametrization options as well as how to paginate and sort your data.