Last Updated on August 1, 2020 by jt

If you need to quickly create a JPA-based repository layer, Spring Data JPA is the right choice. You define your repository interface by extending one of the Spring Data JPA Repository interfaces.  At runtime, Spring Data JPA will create your repository implementations with the common CRUD methods. You can then perform CRUD operations without writing a single line of data access code.

But by saying so, Enterprise Applications developed using the Spring Framework often needs to execute complex queries against the database.

In such a scenario, you need to inform Spring Data JPA on what queries you need to execute. You do it using the @Query annotation.

In the post, I will show you how to use @Query annotation to execute custom queries.

The Application

I will use a Spring Boot application with a Book entity.  I will also use Lombok to generate code for the Book entity.

To store entities, I will go with an embedded H2 database.

The Spring Data JPA, Lombok, and H2 dependencies in the pom.xml file is this.

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
   <scope>runtime</scope>
   <version>1.4.199</version>
</dependency>
<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <version>1.18.12</version>
   <scope>provided</scope>
</dependency>

Next, I will add configuration properties in the application.properties file to connect with the embedded H2 database.

The application.properties file is this.

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver

The code of the Book entity is this.

Book.java

package guru.springframework.customquery.domain;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity(name = "Book")
@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private int id;
   @Column(unique=true)
   private String isbn;
   private String title;
   private String author;
   private boolean status;

}

In the preceding code, @Entity(name = "Book") annotation specifies that the class is an entity mapped to a table named Book. The other class-level annotations are Lombok annotations to reduce boilerplate code in this entity class.

Simple @Query

The repository interface extends CrudRepository. Here, I will use the @Query annotation to create a custom query to find all books.

The code of the BookRepository is this.

package guru.springframework.customquery.repository;

import guru.springframework.customquery.domain.Book;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface BookRepository extends CrudRepository<Book, Integer> {
  @Query("SELECT b FROM Book b")
List<Book> findAllBooks();

}

In the preceding code, the findAllBooks() method is annotated with the @Query annotation. This annotation takes a custom query as a string. In this example, the custom query returns all books.

To test this method, I will write a JUnit 5 test.

The BookRepositoryTest class is this.

BookRepositoryTest.java

package guru.springframework.customquery.repository;

import guru.springframework.customquery.domain.Book;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import static org.hamcrest.CoreMatchers.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import java.util.List;

import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.containsInAnyOrder;
import static org.hamcrest.Matchers.hasProperty;

@DataJpaTest
class BookRepositoryTest {
   @Autowired
   private BookRepository bookRepository;
   private Book book1, book2;
   private List<Book> bookList;

   @BeforeEach
   void setUp() {
       Book book1=Book.builder()
               .isbn("0-2169-6768-6")
               .title("I Dare")
               .author("James Cassy")
               .status(true)
               .build();
       Book book2=Book.builder()
               .isbn("0-5395-2414-X")
               .title("I Dare")
               .author("Patrick")
               .status(false)
               .build();
       Book book3=Book.builder()
               .isbn("0-2139-7448-7")
               .title("Time Machine")
               .author("Herbert Neil")
               .status(false)
               .build();
       Book book4=Book.builder()
               .isbn("0-4572-6998-3")
               .title("Time Machine")
               .author("George Wells")
               .status(false)
               .build();

       bookRepository.save(book1);
       bookRepository.save(book2);
       bookRepository.save(book3);
       bookRepository.save(book4);
   }

   @AfterEach
   void tearDown() {
      bookRepository.deleteAll();
   }


   @Test
   void findAllBooks() {
      List<Book> result = bookRepository.findAllBooks();
      assertThat(result, is(notNullValue()));
   }
}

This is an integration test. However, instead of loading the entire application context using @SpringBootTest, I used the @DataJpa annotation. This will load the JPA slice of the application context making the test lightweight and fast.

The setUp() method annotated with @BeforeEach saves Book objects to the database.

The findAllBooks() test method calls the bookRepository.findAllBooks() method.

Finally, the code performs assertions on the returned result using the AssertJ library.

JPQL Select @Query with Index Parameters

One way to pass method parameters to a query is through an index.

Let’s define a custom query using Java Persistence Query Language (JPQL) to find a book for a given title and author.

The code for querying a book with index parameters using JPQL is this.

@Query("SELECT b FROM Book b WHERE b.title = ?1 and b.author = ?2")
Book findBookByTitleAndAuthorIndexJpql(String title, String authorName);

In the preceding code, the title method parameter will be assigned to the query parameter with index 1. Similarly, authorName will be assigned to the query parameter with index 2.

It is important to note that the order of the query parameter indexes and the method parameters must be the same.

Therefore, this code will not work.

@Query("SELECT b FROM Book b WHERE b.title = ?2 and b.author = ?1")
Book findBookByTitleAndAuthorIndexJpql( String authorName, String title);

The code to test the query is this.

@Test
void findBookByTitleAndAuthorIndexJpql() {
   Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorIndexJpql("I Dare", "James Cassy");
   assertThat(actualRetrievedBook, is(notNullValue()));
   assertThat(actualRetrievedBook.getIsbn(), is("0-2169-6768-6"));
}

Native SQL Select @Query with Index Parameters

For a custom Native SQL query, you need to set the nativequery flag to true.

The code to use Native SQL is this.

@Query(value = "SELECT * FROM Book  WHERE title = ?1 and author = ?2",
       nativeQuery = true)
Book findBookByTitleAndAuthorIndexNative(String title, String authorName);

In the preceding code, I’m setting the nativeQuery flag to true. At runtime, the title and authorName method parameters will be assigned to the query parameter with index 1 and 2 respectively.

The test code is this.

@Test
void findBookByTitleAndAuthorIndexNative() {
   Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorIndexNative("I Dare", "James Cassy");
   assertThat(actualRetrievedBook, is(notNullValue()));
   assertThat(actualRetrievedBook.getIsbn(), is("0-2169-6768-6"));
}

JPQL @Query with Named Parameters

Another approach to pass parameters to a query is with named parameters. In this approach, you pass the method parameter values to the query bind parameters.

In order to do so, use the @Param annotation with the name of the bind parameter in the method definition.

The code for querying a book with named parameters is this.

@Query("SELECT b FROM Book b WHERE b.title = :title and b.author= :author")
Book findBookByTitleAndAuthorNamedJpql(@Param("title") String title, @Param("author") String author);

The  @Param annotation in the preceding code binds the names of the query parameters with the method parameters.

The test code is this.

@Test
void findBookByTitleAndAuthorNamedJpql() {
  Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorNamedJpql("I Dare", "James Cassy");
  assertThat(actualRetrievedBook, is(notNullValue()));
  assertThat(actualRetrievedBook.getIsbn(), is("0-2169-6768-6"));
}

Native SQL @Query with Named Parameters

To query with named parameters using Native SQL is similar to JPQL.

The code to query is this.

@Query(value = "SELECT * FROM Book WHERE title = :title and author= :author", nativeQuery = true)
Book findBookByTitleAndAuthorNamedNative(@Param("title") String title, @Param("author") String author);

The test code is this.

@Test
void findBookByTitleAndAuthorNamedNative() {
   Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorNamedNative("I Dare", "James Cassy");
   assertThat(actualRetrievedBook, is(notNullValue()));
   assertThat(actualRetrievedBook.getIsbn(), is("0-2169-6768-6"));
}

Sorting with @Query

To sort query results, in Spring Data JPA provides a special Sort parameter. When you use the Sort parameter, Spring Data JPA will generate the ORDER_BY clause automatically.

The code to perform sorting is this.

@Query("select b from Book b where b.title = ?1")
List<Book> findBookByTitleAndSort(String title, Sort sort);

In the preceding code, the Sort parameter orders the books retrieved by title according to the sorting properties set on it by the client code.

The test code is this.

@Test
void findBookByTitleAndSort() {
   List<Book> actualBookList = bookRepository.findBookByTitleAndSort("Time Machine",Sort.by("author").descending());
   assertThat(actualBookList.size(), is(2));
   assertThat(actualBookList, containsInAnyOrder(
           hasProperty("author", is("George Wells")),
           hasProperty("author", is("Herbert Neil"))
   ));
}

Note: Spring Data JPA does not currently support dynamic sorting for native queries

Spring Expression Language (SpEL) with @Query

Spring Data JPA also supports Spring Expression Language SpEL expressions. An SpEL expression is evaluated against a predefined set of variables. You can replace the actual entity name with entityName expression variable.

The code to demonstrate SpEL expression is this.

@Query("select b from #{#entityName} b where b.title = ?1")
List<Book> findBookByTitleSPEL(String title);

In the preceding code, Spring Data JPA inserts the entityName of the domain type associated with the given repository, Book in our case.

The test code is this.

@Test
void findBookByTitleSPEL() {
   List<Book> actualBookList = bookRepository.findBookByTitleSPEL("I Dare");
   assertThat(actualBookList.size(), is(2));
}

Pagination with @Query

Spring Data JPA provides a specialPageableparameter to paginate query results.

With Pagination, you get a Page object as a result. A Page object is the subset of the complete result.

The code to perform pagination is this.

@Query(value = "SELECT b FROM Book b ORDER BY title")
Page<Book> findAllBooksWithPagination(Pageable pageable);

The test code is this.

@Test
void findAllBooksWithPagination() {
   Page<Book> allBookWithPagination = bookRepository.findAllBooksWithPagination(PageRequest.of(0, 3));
   assertThat(allBookWithPagination.getTotalPages(), is(2));
}

<h2>Summary</h2>

When you are into Enterprise Application Development with the Spring Framework, you will have to deal with complex queries.

For such queries, you cannot always look for the comfort of Derived queries. For example, if you use more than 2-3 query parameters or need to define multiple joins to other entities, you need a more flexible approach.

In such situations, you can use Spring Data JPA’s @Query annotation to specify a custom JPQL or native SQL query.

With @Query Spring Data JPA provides the required JPA code to execute the statement as a JPQL or native SQL query. Your preferred JPA implementation,  such as, Hibernate or EclipseLink, will then execute the query and map the result.

Another advantage of using the Spring Data JPA @Query annotation is related to code manageability.

With @Query, you are binding the queries to the Java method that executes them. By using this approach over annotating the domain class, you free the domain class from storing any persistence specific information. Also, it is convenient to co-locate the queries in the repository interface.

The source code for this post can be found here on GitHub.