{"id":6331,"date":"2020-07-22T07:00:26","date_gmt":"2020-07-22T11:00:26","guid":{"rendered":"http:\/\/springframework.guru\/?p=6331"},"modified":"2020-08-01T08:08:25","modified_gmt":"2020-08-01T12:08:25","slug":"spring-data-jpa-query","status":"publish","type":"post","link":"https:\/\/springframework.guru\/spring-data-jpa-query\/","title":{"rendered":"Spring Data JPA @Query"},"content":{"rendered":"

If you need to quickly create a JPA-based repository layer, Spring Data JPA<\/a> is the right choice. You define your repository interface by extending one of the Spring Data JPA Repository interfaces.\u00a0 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.<\/p>\n

But by saying so, Enterprise Applications developed using the Spring Framework<\/a> often needs to execute complex queries against the database.<\/p>\n

In such a scenario, you need to inform Spring Data JPA on what queries you need to execute. You do it using the @Query<\/code> annotation.<\/p>\n

In the post, I will show you how to use @Query<\/code> annotation to execute custom queries.<\/p>\n

The Application<\/h2>\n

I will use a Spring Boot application with a Book<\/code> entity.\u00a0 I will also use Lombok to generate code for the Book<\/code> entity.<\/p>\n

To store entities, I will go with an embedded H2<\/a> database.<\/p>\n

The Spring Data JPA, Lombok, and H2 dependencies in the pom.xml<\/code> file is this.<\/p>\n

<dependency>\r\n   <groupId>org.springframework.boot<\/groupId>\r\n   <artifactId>spring-boot-starter-data-jpa<\/artifactId>\r\n<\/dependency>\r\n<dependency>\r\n   <groupId>com.h2database<\/groupId>\r\n   <artifactId>h2<\/artifactId>\r\n   <scope>runtime<\/scope>\r\n   <version>1.4.199<\/version>\r\n<\/dependency>\r\n<dependency>\r\n   <groupId>org.projectlombok<\/groupId>\r\n   <artifactId>lombok<\/artifactId>\r\n   <version>1.18.12<\/version>\r\n   <scope>provided<\/scope>\r\n<\/dependency><\/pre>\n
Next, I will add configuration properties in the application.properties<\/code> file to connect with the embedded H2 database.<\/p>\n
The application.properties<\/code> file is this.<\/p>\n
spring.datasource.url=jdbc:h2:mem:testdb\r\nspring.datasource.driverClassName=org.h2.Driver\r\n<\/pre>\n
The code of the Book<\/code> entity is this.<\/p>\n
Book.java<\/strong><\/p>\n
package guru.springframework.customquery.domain;\r\n\r\nimport lombok.AllArgsConstructor;\r\nimport lombok.Builder;\r\nimport lombok.Data;\r\nimport lombok.NoArgsConstructor;\r\n\r\nimport javax.persistence.*;\r\n\r\n@Entity(name = \"Book\")\r\n@Builder\r\n@Data\r\n@NoArgsConstructor\r\n@AllArgsConstructor\r\npublic class Book {\r\n   @Id\r\n   @GeneratedValue(strategy = GenerationType.AUTO)\r\n   private int id;\r\n   @Column(unique=true)\r\n   private String isbn;\r\n   private String title;\r\n   private String author;\r\n   private boolean status;\r\n\r\n}\r\n<\/pre>\n
In the preceding code, @Entity(name = \"Book\")<\/code> annotation specifies that the class is an entity mapped to a table named Book. The other class-level annotations are Lombok<\/a> annotations to reduce boilerplate code in this entity class.<\/p>\n
Simple @Query<\/h2>\n
The repository interface extends CrudRepository<\/a>. Here, I will use the @Query<\/code> annotation to create a custom query to find all books.<\/p>\n
The code of the BookRepository<\/code> is this.<\/p>\n
package guru.springframework.customquery.repository;\r\n\r\nimport guru.springframework.customquery.domain.Book;\r\nimport org.springframework.data.jpa.repository.Query;\r\nimport org.springframework.data.repository.CrudRepository;\r\nimport org.springframework.stereotype.Repository;\r\n\r\n@Repository\r\npublic interface BookRepository extends CrudRepository<Book, Integer> {\r\n  @Query(\"SELECT b FROM Book b\")\r\nList<Book> findAllBooks();\r\n\r\n}\r\n<\/pre>\n
In the preceding code, the findAllBooks()<\/code> method is annotated with the @Query<\/code> annotation. This annotation takes a custom query as a string. In this example, the custom query returns all books.<\/p>\n
To test this method, I will write a JUnit 5<\/a> test.<\/p>\n
The BookRepositoryTest<\/code> class is this.<\/p>\n
BookRepositoryTest.java<\/strong><\/p>\n
package guru.springframework.customquery.repository;\r\n\r\nimport guru.springframework.customquery.domain.Book;\r\nimport org.junit.jupiter.api.AfterEach;\r\nimport org.junit.jupiter.api.BeforeEach;\r\nimport org.junit.jupiter.api.Test;\r\nimport static org.hamcrest.CoreMatchers.*;\r\nimport org.springframework.beans.factory.annotation.Autowired;\r\nimport org.springframework.boot.test.context.SpringBootTest;\r\nimport org.springframework.data.domain.Page;\r\nimport org.springframework.data.domain.PageRequest;\r\nimport org.springframework.data.domain.Sort;\r\nimport java.util.List;\r\n\r\nimport static org.hamcrest.MatcherAssert.assertThat;\r\nimport static org.hamcrest.Matchers.containsInAnyOrder;\r\nimport static org.hamcrest.Matchers.hasProperty;\r\n\r\n@DataJpaTest\r\nclass BookRepositoryTest {\r\n   @Autowired\r\n   private BookRepository bookRepository;\r\n   private Book book1, book2;\r\n   private List<Book> bookList;\r\n\r\n   @BeforeEach\r\n   void setUp() {\r\n       Book book1=Book.builder()\r\n               .isbn(\"0-2169-6768-6\")\r\n               .title(\"I Dare\")\r\n               .author(\"James Cassy\")\r\n               .status(true)\r\n               .build();\r\n       Book book2=Book.builder()\r\n               .isbn(\"0-5395-2414-X\")\r\n               .title(\"I Dare\")\r\n               .author(\"Patrick\")\r\n               .status(false)\r\n               .build();\r\n       Book book3=Book.builder()\r\n               .isbn(\"0-2139-7448-7\")\r\n               .title(\"Time Machine\")\r\n               .author(\"Herbert Neil\")\r\n               .status(false)\r\n               .build();\r\n       Book book4=Book.builder()\r\n               .isbn(\"0-4572-6998-3\")\r\n               .title(\"Time Machine\")\r\n               .author(\"George Wells\")\r\n               .status(false)\r\n               .build();\r\n\r\n       bookRepository.save(book1);\r\n       bookRepository.save(book2);\r\n       bookRepository.save(book3);\r\n       bookRepository.save(book4);\r\n   }\r\n\r\n   @AfterEach\r\n   void tearDown() {\r\n      bookRepository.deleteAll();\r\n   }\r\n\r\n\r\n   @Test\r\n   void findAllBooks() {\r\n      List<Book> result = bookRepository.findAllBooks();\r\n      assertThat(result, is(notNullValue()));\r\n   }\r\n}\r\n<\/pre>\n
This is an integration test. However, instead of loading the entire application context using @SpringBootTest<\/code>, I used the @DataJpa<\/code> annotation. This will load the JPA slice of the application context making the test lightweight and fast.<\/p>\n
The setUp()<\/code> method annotated with @BeforeEach<\/code> saves Book<\/code> objects to the database.<\/p>\n
The findAllBooks()<\/code> test method calls the bookRepository.findAllBooks()<\/code> method.<\/p>\n
Finally, the code performs assertions on the returned result using the AssertJ<\/code> library.<\/p>\n
JPQL Select @Query with Index Parameters<\/h2>\n
One way to pass method parameters to a query is through an index.<\/p>\n
Let\u2019s define a custom query using Java Persistence Query Language (JPQL) to find a book for a given title and author.<\/p>\n
The code for querying a book with index parameters using JPQL is this.<\/p>\n
@Query(\"SELECT b FROM Book b WHERE b.title = ?1 and b.author = ?2\")\r\nBook findBookByTitleAndAuthorIndexJpql(String title, String authorName);<\/pre>\n
In the preceding code, the title<\/code> method parameter will be assigned to the query parameter with index 1. Similarly, authorName<\/code> will be assigned to the query parameter with index 2.<\/p>\n
It is important to note that the order of the query parameter indexes and the method parameters must be the same.<\/p>\n
Therefore, this code will not work.<\/p>\n
@Query(\"SELECT b FROM Book b WHERE b.title = ?2 and b.author = ?1\")\r\nBook findBookByTitleAndAuthorIndexJpql( String authorName, String title);\r\n<\/pre>\n
The code to test the query is this.<\/p>\n
@Test\r\nvoid findBookByTitleAndAuthorIndexJpql() {\r\n   Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorIndexJpql(\"I Dare\", \"James Cassy\");\r\n   assertThat(actualRetrievedBook, is(notNullValue()));\r\n   assertThat(actualRetrievedBook.getIsbn(), is(\"0-2169-6768-6\"));\r\n}\r\n<\/pre>\n
Native SQL Select @Query with Index Parameters<\/h2>\n
For a custom Native SQL query, you need to set the nativequery<\/code> flag to true.<\/p>\n
The code to use Native SQL is this.<\/p>\n
@Query(value = \"SELECT * FROM Book  WHERE title = ?1 and author = ?2\",\r\n       nativeQuery = true)\r\nBook findBookByTitleAndAuthorIndexNative(String title, String authorName);<\/pre>\n
In the preceding code, I\u2019m setting the nativeQuery<\/code> flag to true. At runtime, the title<\/code> and authorName<\/code> method parameters will be assigned to the query parameter with index 1 and 2 respectively.<\/p>\n
The test code is this.<\/p>\n
@Test\r\nvoid findBookByTitleAndAuthorIndexNative() {\r\n   Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorIndexNative(\"I Dare\", \"James Cassy\");\r\n   assertThat(actualRetrievedBook, is(notNullValue()));\r\n   assertThat(actualRetrievedBook.getIsbn(), is(\"0-2169-6768-6\"));\r\n}\r\n<\/pre>\n
JPQL @Query with Named Parameters<\/h2>\n
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.<\/p>\n
In order to do so, use the @Param<\/code> annotation with the name of the bind parameter in the method definition.<\/p>\n
The code for querying a book with named parameters is this.<\/p>\n
@Query(\"SELECT b FROM Book b WHERE b.title = :title and b.author= :author\")\r\nBook findBookByTitleAndAuthorNamedJpql(@Param(\"title\") String title, @Param(\"author\") String author);<\/pre>\n
The\u00a0 @Param<\/code> annotation in the preceding code binds the names of the query parameters with the method parameters.<\/p>\n
The test code is this.<\/p>\n
@Test\r\nvoid findBookByTitleAndAuthorNamedJpql() {\r\n  Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorNamedJpql(\"I Dare\", \"James Cassy\");\r\n  assertThat(actualRetrievedBook, is(notNullValue()));\r\n  assertThat(actualRetrievedBook.getIsbn(), is(\"0-2169-6768-6\"));\r\n}<\/pre>\n
Native SQL @Query with Named Parameters<\/h2>\n
To query with named parameters using Native SQL is similar to JPQL.<\/p>\n
The code to query is this.<\/p>\n
@Query(value = \"SELECT * FROM Book WHERE title = :title and author= :author\", nativeQuery = true)\r\nBook findBookByTitleAndAuthorNamedNative(@Param(\"title\") String title, @Param(\"author\") String author);\r\n<\/pre>\n
The test code is this.<\/p>\n
@Test\r\nvoid findBookByTitleAndAuthorNamedNative() {\r\n   Book actualRetrievedBook = bookRepository.findBookByTitleAndAuthorNamedNative(\"I Dare\", \"James Cassy\");\r\n   assertThat(actualRetrievedBook, is(notNullValue()));\r\n   assertThat(actualRetrievedBook.getIsbn(), is(\"0-2169-6768-6\"));\r\n}\r\n<\/pre>\n
Sorting with @Query<\/h2>\n
To sort query results, in Spring Data JPA provides a special Sort<\/code> parameter. When you use the Sort<\/code> parameter, Spring Data JPA will generate the ORDER_BY<\/code> clause automatically.<\/p>\n
The code to perform sorting is this.<\/p>\n
@Query(\"select b from Book b where b.title = ?1\")\r\nList<Book> findBookByTitleAndSort(String title, Sort sort);\r\n<\/pre>\n
In the preceding code, the Sort<\/code> parameter orders the books retrieved by title according to the sorting properties set on it by the client code.<\/p>\n
The test code is this.<\/p>\n
@Test\r\nvoid findBookByTitleAndSort() {\r\n   List<Book> actualBookList = bookRepository.findBookByTitleAndSort(\"Time Machine\",Sort.by(\"author\").descending());\r\n   assertThat(actualBookList.size(), is(2));\r\n   assertThat(actualBookList, containsInAnyOrder(\r\n           hasProperty(\"author\", is(\"George Wells\")),\r\n           hasProperty(\"author\", is(\"Herbert Neil\"))\r\n   ));\r\n}\r\n<\/pre>\n
Note<\/strong>: Spring Data JPA does not currently support dynamic sorting for native queries<\/p>\n
Spring Expression Language (SpEL) with @Query<\/h2>\n
Spring Data JPA also supports Spring Expression Language SpEL expressions. An SpEL<\/code> expression is evaluated against a predefined set of variables. You can replace the actual entity name with entityName<\/code> expression variable.<\/p>\n
The code to demonstrate SpEL expression is this.<\/p>\n
@Query(\"select b from #{#entityName} b where b.title = ?1\")\r\nList<Book> findBookByTitleSPEL(String title);\r\n<\/pre>\n
In the preceding code, Spring Data JPA inserts the entityName<\/code> of the domain type associated with the given repository, Book in our case.<\/p>\n
The test code is this.<\/p>\n
@Test\r\nvoid findBookByTitleSPEL() {\r\n   List<Book> actualBookList = bookRepository.findBookByTitleSPEL(\"I Dare\");\r\n   assertThat(actualBookList.size(), is(2));\r\n}\r\n<\/pre>\n
Pagination with @Query<\/h2>\n
Spring Data JPA provides a specialPageable<\/code>parameter to paginate query results.<\/p>\n
With Pagination, you get a Page<\/code> object as a result. A Page<\/code> object is the subset of the complete result.<\/p>\n
The code to perform pagination is this.<\/p>\n
@Query(value = \"SELECT b FROM Book b ORDER BY title\")\r\nPage<Book> findAllBooksWithPagination(Pageable pageable);\r\n\r\n<\/pre>\n
The test code is this.<\/p>\n
@Test\r\nvoid findAllBooksWithPagination() {\r\n   Page<Book> allBookWithPagination = bookRepository.findAllBooksWithPagination(PageRequest.of(0, 3));\r\n   assertThat(allBookWithPagination.getTotalPages(), is(2));\r\n}\r\n\r\n<\/pre>\n
<h2>Summary<\/h2><\/p>\n
When you are into Enterprise Application Development with the Spring Framework, you will have to deal with complex queries.<\/p>\n
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.<\/p>\n
In such situations, you can use Spring Data JPA\u2019s @Query<\/code> annotation to specify a custom JPQL or native SQL query.<\/p>\n
With @Query<\/code> Spring Data JPA provides the required JPA code to execute the statement as a JPQL or native SQL query. Your preferred JPA implementation,\u00a0 such as, Hibernate or EclipseLink, will then execute the query and map the result.<\/p>\n
Another advantage of using the Spring Data JPA @Query<\/code> annotation is related to code manageability.<\/p>\n
With @Query<\/code>, 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.<\/p>\n
The source code for this post can be found here on GitHub<\/a>.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0 At runtime, Spring Data JPA will create your repository implementations with the common CRUD methods. You can then perform CRUD operations without writing […]Continue reading<\/a><\/p>\n","protected":false},"author":111,"featured_media":4656,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_lmt_disableupdate":"","_lmt_disable":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[21,104,105,1],"tags":[281,284,282,283,29,120],"class_list":["post-6331","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spring","category-spring-boot","category-spring-data","category-uncategorized","tag-query","tag-java-persistence-query-language","tag-jpql","tag-native-sql","tag-spring-boot","tag-spring-data-jpa"],"jetpack_publicize_connections":[],"aioseo_notices":[],"modified_by":"jt","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/springframework.guru\/wp-content\/uploads\/2017\/07\/Spring-5ver02bweb.jpg","jetpack_shortlink":"https:\/\/wp.me\/p5BZrZ-1E7","_links":{"self":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts\/6331"}],"collection":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/users\/111"}],"replies":[{"embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/comments?post=6331"}],"version-history":[{"count":11,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts\/6331\/revisions"}],"predecessor-version":[{"id":6447,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/posts\/6331\/revisions\/6447"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/media\/4656"}],"wp:attachment":[{"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/media?parent=6331"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/categories?post=6331"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/springframework.guru\/wp-json\/wp\/v2\/tags?post=6331"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}