The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions. Show
Join the Persistence Hub! All applications need to execute queries to retrieve data from their database. With JPA and Hibernate, you can write JPQL, native SQL queries or Criteria Queries and you can use all of them with Spring Data JPA. In most cases, Spring Data just makes it a little bit easier. Spring Data JPA acts as a layer on top of JPA, and it offers you 2 ways to define your query:
Both options work great, and you should be familiar with them. In this article, I will focus on derived queries, and I will dive deeper into defining a custom query in a future article. Example modelBut before we do that, let’s take a quick look at the domain model that we will use in all of the examples. It consists of an Author and a Book entity with a between them. Spring Data often gets praised for its derived query feature. As long as your method name starts with find…By, read…By, query…By, count…By, or get…By and follows the right pattern, Spring Data generates the required JPQL query. That might sound like you will never need to write your own queries again. But that’s not the case. It’s a great way to define simple queries. But as soon as you need to use more than 2 query parameters or your query gets at least a little bit complex, you should use a custom query. That’s either because the query name gets really complicated to write and read or because you exceed the capabilities of the method name parser. That said, let’s now take a look Spring Data JPA’s derived query feature. Simple derived query with parametersLet’s start with a simple example of a query that loads Author entities with a given firstName. public interface AuthorRepository extends JpaRepository<Author, Long> { }As you can see, the definition of a derived query is pretty simple and self-explaining. I started the name of the method with findBy and then referenced the entity attributes for which I want to filter in the WHERE clause by its name. And then I define a method parameter with the same name as the referenced entity attribute. You can then use this query by injecting an instance of the AuthorRepository and calling the findByFirstName method with the firstName you want to search for. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }When you run this test case and activate the logging of SQL statements, you can see the generated SQL statement in your log file. 2019-04-16 10:38:22.523 DEBUG 24408 --- [ main] org.hibernate.SQL :
Derived queries with multiple parametersYou can extend this method to search for Author entities with a given firstName and lastName by combining them with And. Spring Data JPA, of course, also allows you to concatenate multiple checks using an Or clause. public interface AuthorRepository extends JpaRepository<Author, Long> { }As expected, when you call this repository method, Spring Data JPA and Hibernate generate an SQL query with a WHERE clause that filters the result based on the first_name and last_name columns. 2019-04-16 10:38:22.661 DEBUG 24408 --- [ main] org.hibernate.SQL :
Traverse associations in derived queriesIf you want to filter for an attribute of an associated entity, you can traverse managed relationships by referencing the attribute that maps the association followed by the attribute of the related entity. The following code snippet shows an example in which I reference the books attribute on the Author entity to traverse the mapped association and then reference the title attribute of the associated Book entity. That create a query that returns all authors who have written a book with a given title. public interface AuthorRepository extends JpaRepository<Author, Long> { }When you call this query method, Hibernate generates an SQL query that joins the author and the book table and compares the value in the title column with the provided bind parameter value in the WHERE clause. 2019-04-16 10:37:31.200 DEBUG 20024 --- [ main] org.hibernate.SQL :
Other comparison operatorsIf you just reference an entity attribute in your method name, Spring Data JPA will generate a simple equals comparison. You can also specify different comparison operations by using one of the following keywords together with the name of your entity attribute:
Here is a simple example that selects an Author entity which firstName contains the String “thor” while ignoring its case. public interface AuthorRepository extends JpaRepository<Author, Long> { }When you call this method on the AuthorRepository, Spring Data JPA and Hibernate generate an SQL query that converts the provided String and the value in the first_name column to upper case and creates a LIKE expression to check if the first_name contains the provided String. 2019-04-16 10:38:22.693 DEBUG 24408 - [ main] org.hibernate.SQL : Order the results of a derived queryYou can, of course, also order your query results. In JPQL, this would require an ORDER BY clause in your query. With Spring Data JPA, you just need to add the words OrderBy to your query followed by the name of the entity attribute and the abbreviations ASC or DESC for your preferred order. The following example uses this feature to retrieve all Book entities whose title contains a provided String in the ascending order of their title. public interface BookRepository extends JpaRepository<Book, Long> { }When you call this method on the BookRepository, Spring Data JPA and Hibernate generate an SQL statement with the expected ORDER BY clause. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }0 If you require dynamic ordering, you can add a parameter of type Sort to your query method. This is one of the special parameters supported by Spring Data JPA, and it triggers the generation of an ORDER BY clause. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }1 You then need to instantiate a Sort object and specify the ordering the entity attributes that shall be used to generate the ORDER BY clause. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }2 When you execute the test case, the findByTitleContains generates the same SQL statement as the previous method. But this time, you define the order dynamically,to only return the first 5 records. and you can adjust it at runtime. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }0 Limiting the number of resultsUsing Hibernate or any other JPA implementation, you can limit the number of returned records on the Query interface. With Spring Data JPA, you can do the same by adding the keywords Top or First followed by a number between the find and By keywords. When you call the findFirst5ByTitleOrderByTitleAsc method on the BookRepository, Spring Data JPA and Hibernate generate a query that returns the first 5 Book entities whose title contains the given String. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }4 As you might have expected, the generated SQL statement contains a LIMIT clause to return the first 5 records. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }5 Paginate the results of a derived queryAnd after we had a look at ordering and limiting the number of returned records, we also need to talk about pagination. Spring Data JPA provides another special parameter for it. You just need to add a parameter of type Pageable to your query method definition and change the return type to Page<YourEntity>. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }6 The Pageable interface makes it very easy to step through the pages. You just define which page number you want to retrieve and how many records should be on a page. That’s it. Spring Data JPA takes care of the rest. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }7 As expected, the generated SQL query contains a LIMIT clause and it would also contain an OFFSET clause, if you don’t request the first page. @RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class TestQueryMethod { }8 ConclusionSpring Data JPA just provides a relatively small usability layer on top of JPA, but it offers several features that make working with JPA much easier. The derived query feature, which I showed you in this tutorial, is an excellent example of that. Sure, you could write all these queries yourself, but you don’t have to. As long as your method name doesn’t get too long or complicated, I recommend to let Spring Data JPA generate the required JPQL statement and to take care of the query execution. As a rule of thumb, as long as your query doesn’t need more than 2 parameters, a derived query is the easiest approach. If your query requires more than 2 parameters or you can’t express it in a short and simple method name, you should define the query yourself. I will show you how to do that in one of my next articles. How to query a table in Spring Boot?@Query("SELECT * FROM Student ORDER BY age") Optional<Student> findSortedStudentByAge(); Native: If you want to use this native query in the Spring Boot project then we have to take the help of @Query Annotation and we have to set an attribute nativeQuery=true in Query annotation to mark the query as native. What is the difference between find first and find top?If findFirst() or findTop() is appended by a number, then the records are retrieved up to that number. An important learning point is that we can use either findFirst() or findTop(). It's a matter of personal choice. There are no differences between findFirst() and findTop(). How do you use limits in JPA?Spring Data JPA version 3.2 provides a new type named Limit to limit the number of results returned from a query. It has two static methods – of() and unlimited(). The of() method accepts an integer as an argument. It limits query results to the given maximum size. How do I find the top 10 values in SQL?For example, TOP(10) would return the top 10 rows from the full result set. Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set. |