From Java Persistence with Spring Data and Hibernate by Catalin Tudose

This article delves into using Spring Data JPA to access databases.

Take 35% off Java Persistence with Spring Data and Hibernate by entering fcctudose2 into the discount box at checkout at

This article assumes knowledge of the main Spring Data modules.

We’ll focus on Spring Data JPA here, as it is used to interact with databases. Spring Data JPA is largely used as an alternative to access databases from Java programs. It provides a new layer of abstraction on top of a JPA provider (e.g. Hibernate), taking control of configuration and transaction management. Let’s analyze here its capabilities in depth. We can still define and manage our entities using JPA and Hibernate, but we’ll provide Spring Data JPA as an alternative to interact with them.

Defining query methods with Spring Data JPA

Let’s imagine that we have an auction system called CaveatEmptor, and we want to add functionality. We’ll extend the User class by adding the fields email, level, and active. A user may have different levels, which will allow him or her to execute particular actions (for example, bidding above some amount). A user may be active or may be retired (i.e. previously active in the CaveatEmptor auction system). This is important information that the CaveatEmptor application needs to keep about its user. The source code demonstrated from can be found in the springdatajpa2 folder.

Listing 1 The modified User class

 Path: Ch04/springdatajpa2/src/main/java/com/manning/javapersistence/springdatajpa/model/
 @Table(name = "USERS")
 public class User {
     private Long id;
     private String username;
     private LocalDate registrationDate;
     private String email;
     private int level;
     private boolean active;
     public User() {
     public User(String username) {
         this.username = username;
     public User(String username, LocalDate registrationDate) {
         this.username = username;
         this.registrationDate = registrationDate;
     //getters and setters

We’ll start to add new methods to the UserRepository interface and use them inside newly created tests.

The UserRepository interface will extend JpaRepository, which extends PagingAndSortingRepository, which, in turn, extends CrudRepository.

CrudRepository provides basic CRUD functionality. PagingAndSortingRepository offers convenient methods to do sorting and pagination of the records (to be addressed later in the chapter). JpaRepository offers JPA-related methods, as flushing the persistence context and delete records in a batch. Additionally, JpaRepository overwrites a few methods from CrudRepository, as findAll, findAllById, or saveAll, to return List instead of Iterable.

We’ll also add a series of query methods to the UserRepository interface that will look like this:

Listing 2 The UserRepository interface with new methods

 Path: Ch04/springdatajpa2/src/main/java/com/manning/javapersistence/springdatajpa/repositories/
 public interface UserRepository extends JpaRepository<User, Long> {
    User findByUsername(String username);
    List<User> findAllByOrderByUsernameAsc();
    List<User> findByRegistrationDateBetween(LocalDate start, LocalDate end);
 List<User> findByUsernameAndEmail(String username, String email);
 List<User> findByUsernameOrEmail(String username, String email);
 List<User> findByUsernameIgnoreCase(String username);
 List<User> findByLevelOrderByUsernameDesc(int level);
 List<User> findByLevelGreaterThanEqual(int level);
 List<User> findByUsernameContaining(String text);
 List<User> findByUsernameLike(String text);
 List<User> findByUsernameStartingWith(String start);
 List<User> findByUsernameEndingWith(String end);
 List<User> findByActive(boolean active);
 List<User> findByRegistrationDateIn(Collection<LocalDate> dates);
 List<User> findByRegistrationDateNotIn(Collection<LocalDate> dates);

The purpose of the query methods is to retrieve information from the database. Spring Data JPA provides a query builder mechanism that will create the behavior of the repository methods based on their names. We’ll analyze later the modifying queries, now we dive into the queries having as purpose to find information. This query mechanism removes prefixes and suffixes as find...By, get...By, query...By, read...By, count...By from the name of the method and parses what is left.

You can declare methods containing expressions as Distinct to set a distinct clause, operators as LessThan, GreaterThan, Between, and Like or compound conditions with And or Or. You can apply static ordering with the OrderBy clause in the name of the query method referencing a property and providing a sorting direction (Asc or Desc). You can use IgnoreCase for properties supporting such a clause. For deleting rows, you have to replace find with delete in the names of the methods. Also, Spring Data JPA will look at the return type of the method. If you want to find a User and return it in an Optional container, the method return type will be Optional<User>. A full list of possible return types, together with detailed explanations, may be found at

The names of the methods need to follow the rules to determine the resulting query.  If the method naming is wrong (for example, the entity property does not match in the query method), you will get an error while the application context is loaded.

Table 1 describes the keywords that Spring Data JPA supports and how each method name is transposed in JPQL.

Table 1 Keywords usage in Spring Data JPA and generated JPQL



Generated JPQL

Is, Equals




… where e.username = ?1



… where e.username = ?1 and e.registrationdate = ?2



… where e.username = ?1 or e.registrationdate = ?2



… where e.registrationdate < ?1



… where e.registrationdate <= ?1



… where e.registrationdate > ?1



… where e.registrationdate >= ?1



… where e.registrationdate between ?1 and ?2



… where e.registrationdate = ?1 order by e.username desc



… where e.username like ?1



… where e.username not like ?1



… where e.registrationdate < ?1



… where e.registrationdate > ?1

Null, IsNull


… where e.registrationdate is null

NotNull, IsNotNull


… where e.registrationdate is not null



… where e.username <> ?1



(Collection<LocalDate> dates)

… where e.registrationdate in ?1



(Collection<LocalDate> dates)

… where e.registrationdate not in ?1



… where = true



… where = false



… where e.username like ?1%



… where e.username like %?1



… where e.username like %?1%



.. where UPPER(e.username) = UPPER(?1)

As a base class for all future tests, we’ll write the SpringDataJpaApplicationTests abstract class.

Listing 3 The SpringDataJpaApplicationTests abstract class

 Path: Ch04/springdatajpa2/src/test/java/com/manning/javapersistence/springdatajpa/
 @SpringBootTest                                                             #A
 @TestInstance(TestInstance.Lifecycle.PER_CLASS)                             #B
 abstract class SpringDataJpaApplicationTests {
     @Autowired                                                              #C
     UserRepository userRepository;                                          #C
     @BeforeAll                                                              #D
     void beforeAll() {                                                      #D
         userRepository.saveAll(generateUsers());                            #D
     }                                                                       #D
     private static List<User> generateUsers() {                             #E
         List<User> users = new ArrayList<>();
        User john = new User("john", LocalDate.of(2020, Month.APRIL, 13));
         john.setEmail("[email protected]");
         //create and set a total of 10 users
         //add a total of 10 users to the list
         return users;
     @AfterAll                                                               #F
     void afterAll() {                                                       #F
         userRepository.deleteAll();                                         #F
     }                                                                       #F

#A The @SpringBootTest annotation, added by Spring Boot to the initially created class, will tell Spring Boot to search the main configuration class (the @SpringBootApplication annotated class, for instance) and create the ApplicationContext to be used in the tests. It is important to understand that the @SpringBootApplication annotation added by Spring Boot to the class containing the main method will enable the Spring Boot auto-configuration mechanism, and will enable a scan on the package where the application is located, as well as allowing for the registering of extra beans in the context.

#B Using the @TestInstance(TestInstance.Lifecycle.PER_CLASS) annotation, we ask JUnit 5 to create one single instance of the test class and reuse it for all test methods. This will allow us to make the @BeforeAll and @AfterAll annotated methods non-static and to directly use inside them the auto-wired UserRepository instance field.

#C We auto-wire a UserRepository instance. This auto-wiring is possible due to the @SpringBootApplication annotation, which enables a scan on the package where the application is located and registers the beans in the context.

#D The @BeforeAll annotated method will be executed once before executing all tests from a class that extends SpringDataJpaApplicationTests. This method will not be static.

#E The @TestInstance(TestInstance.Lifecycle.PER_CLASS) annotation forces the creation of a single instance of the test class. It will save the list of users created by the generateUsers method to the database.

#F The @AfterAll annotated method will be executed once, after executing all tests from a class that extends SpringDataJpaApplicationTests. This method will not be static. The @TestInstance(TestInstance.Lifecycle.PER_CLASS) annotation forces the creation of a single instance of the test class.

The next tests will extend this class and use the already populated database. To test the methods that now belong to UserRepository, we’ll create the FindUsersUsingQueriesTest class and follow the same recipe for writing tests: call the repository method and verify its results.

Listing 4 The FindUsersUsingQueriesTest class

 Path: Ch04/springdatajpa2/src/test/java/com/manning/javapersistence/springdatajpa/
 public class FindUsersUsingQueriesTest extends SpringDataJpaApplicationTests {
     void testFindAll() {
         List<User> users = userRepository.findAll();
         assertEquals(10, users.size());
     void testFindUser() {
         User beth = userRepository.findByUsername("beth");
         assertEquals("beth", beth.getUsername());
     void testFindAllByOrderByUsernameAsc() {
         List<User> users = userRepository.findAllByOrderByUsernameAsc();
         assertAll(() -> assertEquals(10, users.size()),
                 () -> assertEquals("beth", users.get(0).getUsername()),
                 () -> assertEquals("stephanie",
                        users.get(users.size() - 1).getUsername()));
     void testFindByRegistrationDateBetween() {
         List<User> users = userRepository.findByRegistrationDateBetween(
                 LocalDate.of(2020, Month.JULY, 1),
                 LocalDate.of(2020, Month.DECEMBER, 31));
         assertEquals(4, users.size());
     //more tests

Limiting query results, sorting, and paging

The first and top keywords (used equivalently) can limit the results of query methods. The top and first keywords may be followed by an optional numeric value to indicate the maximum result size to be returned. If this numeric value is missing, the result size will be 1.

Pageable is an interface for pagination information. In practice, we use the PageRequest class that implements it. This one can specify the page number, the page size, and the sorting criterion.

We’ll add the following methods to the UserRepository interface:

Listing 5 Limiting query results, sorting, and paging in the UserRepository interface

 Path: Ch04/springdatajpa2/src/main/java/com/manning/javapersistence/springdatajpa/repositories/
 User findFirstByOrderByUsernameAsc();
 User findTopByOrderByRegistrationDateDesc();
 Page<User> findAll(Pageable pageable);
 List<User> findFirst2ByLevel(int level, Sort sort);
 List<User> findByLevel(int level, Sort sort);
 List<User> findByActive(boolean active, Pageable pageable);

We’ll write the following tests to verify how these newly added methods work:

Listing 6 Testing limiting query results, sorting and paging

 Path: Ch04/springdatajpa2/src/test/java/com/manning/javapersistence/springdatajpa/
 public class FindUsersSortingAndPagingTest extends                        
              SpringDataJpaApplicationTests {                               
     void testOrder() {
         User user1 = userRepository.findFirstByOrderByUsernameAsc();        #A
         User user2 = userRepository.findTopByOrderByRegistrationDateDesc(); #A
         Page<User> userPage = userRepository.findAll(PageRequest.of(1, 3)); #B
         List<User> users = userRepository.findFirst2ByLevel(2,              #C
                                 "registrationDate"));     #C
                 () -> assertEquals("beth", user1.getUsername()),           
                 () -> assertEquals("julius", user2.getUsername()),         
                 () -> assertEquals(2, users.size()),     
                 () -> assertEquals(3, userPage.getSize()),                 
                 () -> assertEquals("beth", users.get(0).getUsername()),    
                 () -> assertEquals("marion", users.get(1).getUsername())   
     void testFindByLevel() {
         Sort.TypedSort<User> user = Sort.sort(User.class);                  #D
         List<User> users = userRepository.findByLevel(3,                    #E
          ;        #E
                 () -> assertEquals(2, users.size()),             
                 () -> assertEquals("james", users.get(0).getUsername())    
     void testFindByActive() {
         List<User> users = userRepository.findByActive(true,                #F
                    PageRequest.of(1, 4,"registrationDate")));      #F
                 () -> assertEquals(4, users.size()),           
                 () -> assertEquals("burk", users.get(0).getUsername())    

#A The first test will find the first user by ascending order of the username and the first user by descending order of the registration date.

#B Find all users, split them into pages, and return page number 1 of size 3 (the page numbering starts with 0).

#C Find the first 2 users with level 2, ordered by registration date.

#D The second test will define a sorting criterion on the User class. Sort.TypedSort extends Sort and can use method handles to define properties to sort by.

#E We find the users of level 3 and sort by registration date, descending.

#F The third test will find the active users sorted by registration date, split them into pages, and return page number 1 of size 4 (the page numbering starts with 0).

If you want to learn more about the book, check it out on Manning’s liveBook platform here.