JPA Part 8: Derived Queries, JPQL, Pagination and the N+1 Problem

So far the repository was empty, and the service used the methods JpaRepository already has (save, findById, ...):

@Repository
public interface UserDetailsRepository extends JpaRepository<UserDetails, Long> {
}

The entity for this part:

@Table(name = "user_details")
@Entity
public class UserDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long userId;

    @Column(name = "user_name")
    private String name;

    private String phone;
    // getters and setters
}

Derived queries

A derived query is generated from the method name. You only declare the method; Spring Data parses the name (in its PartTree class) and builds the query.

List<UserDetails> findUserDetailsByName(String userName);

becomes

select ... from user_details ud1_0 where ud1_0.user_name=?

The name follows a convention: find … By, then conditions on entity field names (name, not the column user_name). Whatever sits between find and By (here UserDetails) is just descriptive. Derived queries are for reading and deleting (also count…, exists…); inserting and updating is done with save().

// AND
List<UserDetails> findUserDetailsByNameAndPhone(String userName, String phone);

// OR
List<UserDetails> findUserDetailsByNameAndPhoneOrUserId(String userName, String phone, Long id);

// IN
List<UserDetails> findUserDetailsByNameIsIn(List<String> userNames);

// LIKE: you pass the pattern, e.g. "b%"
List<UserDetails> findUserDetailsByNameLike(String pattern);

Other common keywords: Between, LessThan, GreaterThan, StartingWith, EndingWith, Containing, IsNull, Not, OrderBy...Asc/Desc.

Delete needs @Transactional:

@Transactional
void deleteByName(String userName);

A derived delete first loads the matching entities and then removes each one, and removing requires a transaction. Without @Transactional: "No EntityManager with actual transaction available for current thread - cannot reliably process 'remove' call".

Pagination and sorting

Two types do this: Pageable (which page, how big, optionally sorted) and Sort. Add one as the last parameter:

List<UserDetails> findUserDetailsByNameStartingWith(String userName, Pageable page);

public List<UserDetails> findByNameDerived(String name) {
    Pageable pageable = PageRequest.of(0, 5);   // page 0 (the first), 5 rows per page
    return userDetailsRepository.findUserDetailsByNameStartingWith(name, pageable);
}

For information about the pages, return a Page:

Page<UserDetails> findUserDetailsByNameStartingWith(String userName, Pageable page);

Page<UserDetails> userDetailsPage = userDetailsRepository.findUserDetailsByNameStartingWith(name, PageRequest.of(0, 5));
List<UserDetails> userDetailsList = userDetailsPage.getContent();
System.out.println("total pages: " + userDetailsPage.getTotalPages());
System.out.println("is first page: " + userDetailsPage.isFirst());
System.out.println("is last page: " + userDetailsPage.isLast());

A Page runs an extra count query to know the total.

Pagination with sorting:

Pageable pageable = PageRequest.of(0, 5, Sort.by("name").descending());

Sorting only, with a Sort parameter:

List<UserDetails> findUserDetailsByNameStartingWith(String userName, Sort sort);

userDetailsRepository.findUserDetailsByNameStartingWith(name, Sort.by("name").descending());

Sort.by takes several fields: Sort.by("name", "phone").ascending() sorts by name, and by phone where names are equal. For a different direction per field:

Sort sort = Sort.by(Sort.Order.asc("name"), Sort.Order.desc("phone"));

JPQL

For queries too complex for a method name, write JPQL (Java Persistence Query Language) with @Query. It looks like SQL but works on entities and their fields, not tables and columns, which keeps it database-independent.

@Query("SELECT u FROM UserDetails u WHERE u.name = :userFirstName")
List<UserDetails> findByUserName(@Param("userFirstName") String userName);

• UserDetails is the entity name, not the table name; u is an alias, and SELECT u returns whole entities.
• u.name is the entity field, not the column user_name.
• :userFirstName is a named parameter, bound by @Param("userFirstName").
• The return type can be a List or a single object. If the method returns one object but the query finds several rows, it fails: "Query did not return a unique result: 2 results were returned".

Joins

With a one-to-one userAddress field on UserDetails:

@Query("SELECT ud FROM UserDetails ud JOIN ud.userAddress ad WHERE ud.name = :userFirstName")
List<UserDetails> findUserDetailsWithAddress(@Param("userFirstName") String userName);

You join along the field (ud.userAddress); no ON clause is needed, since JPA knows the foreign key from the mapping.

To select particular fields, get Object[] rows:

@Query("SELECT ud.name, ad.country FROM UserDetails ud JOIN ud.userAddress ad WHERE ud.name = :userFirstName")
List<Object[]> findUserDetailsWithAddress(@Param("userFirstName") String userName);

List<UserDTO> output = new ArrayList<>();
for (Object[] val : dbOutput) {
    output.add(new UserDTO((String) val[0], (String) val[1]));
}

or let JPQL build the DTO directly with a constructor expression (the fully qualified class name):

@Query("SELECT new com.example.jpa.dto.UserDTO(ud.name, ad.country) " +
       "FROM UserDetails ud JOIN ud.userAddress ad WHERE ud.name = :userFirstName")
List<UserDTO> findUserDetailsWithAddress(@Param("userFirstName") String userName);

The same works for one-to-many (JOIN ud.userAddressList ad), and for chains of tables, just like SQL:

@Query("SELECT a FROM A a JOIN a.bList b JOIN b.cList c WHERE c.someProperty = :someValue")
List<A> findAWithBAndC(@Param("someValue") String someValue);

The N+1 problem

Now a user has many addresses:

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "user_id")   // FK in the user_address table
private List<UserAddress> userAddressList = new ArrayList<>();

A query that returns several users, followed by using each user's addresses:

@Query("SELECT ud FROM UserDetails ud JOIN ud.userAddressList ad")
List<UserDetails> findUsersWithAddresses();

With 7 users, the log shows 8 queries:

select ... from user_details ud1_0 join user_address ual1_0 on ...          -- 1 query for the users
select ... from user_address ual1_0 where ual1_0.user_id=?                   -- then 1 per user
select ... from user_address ual1_0 where ual1_0.user_id=?
...

1 query for the N parents plus N queries for their children: N+1. With thousands of users, that is thousands of queries.

Doesn't EAGER fix it? No. With findById, eager loading uses one join query because there is only one parent. But when a query returns many parents, Hibernate still loads the parents first and then each parent's children in a separate query. findAll() with EAGER behaved the same: one query for the users, then one per user.

Three fixes:

1. JOIN FETCH (JPQL): load the children in the same query.

@Query("SELECT ud FROM UserDetails ud JOIN FETCH ud.userAddressList ad WHERE ud.name = :userFirstName")
List<UserDetails> findUserDetailsWithAddress(@Param("userFirstName") String userName);

One query. (Hibernate 6 also removes the duplicate parents such a join produces.)

2. @BatchSize: load the children of several parents at a time.

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@BatchSize(size = 10)
@JoinColumn(name = "user_id")
private List<UserAddress> userAddressList;

select ... from user_details ud1_0
select ... from user_address ual1_0 where ual1_0.user_id in (?,?,?,?,?,?,?,?,?,?)

Not one query, but N/10 + 1 instead of N + 1.

3. @EntityGraph: tell a repository method (handy for derived ones) which associations to load together.

@EntityGraph(attributePaths = "userAddressList")
List<UserDetails> findUsersBy();

One query, with a left join to the addresses.

@Modifying

@Query expects a SELECT. With UPDATE or DELETE it fails:

Query executed via 'getResultList()' or 'getSingleResult()' must be a 'select' query

Add @Modifying, and a transaction, since it changes the database:

@Modifying
@Transactional
@Query("DELETE FROM UserDetails ud WHERE ud.name = :userFirstName")
void deleteByUserName(@Param("userFirstName") String userName);

Such bulk queries go straight to the database: they do not cascade (deleting users that still have addresses fails on the foreign key) and they bypass the persistence context.

Flush and clear

• flush: write the persistence context's pending changes to the database; the context still holds the entities.
• clear: empty the persistence context, so later reads must go to the database.

Why this matters:

@Transactional
public void deleteByUserName(String name) {
    userDetailsRepository.findById(1L).get();         // user 1 is now in the persistence context
    userDetailsRepository.deleteByUserName(name);      // bulk delete, straight to the DB
    Optional<UserDetails> output = userDetailsRepository.findById(1L);
    System.out.println("output present: " + output.isPresent());
}

This prints output present: true: the row is gone from the database, but the second findById is answered from the persistence context, which still holds user 1. The fix:

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query("DELETE FROM UserDetails ud WHERE ud.name = :userFirstName")
void deleteByUserName(@Param("userFirstName") String userName);

flushAutomatically writes pending changes before the bulk query runs; clearAutomatically empties the context after it. Now the second findById queries the database and prints false.

Pagination and sorting in JPQL

Exactly as with derived queries: add a Pageable or Sort parameter.

@Query("SELECT ud FROM UserDetails ud WHERE ud.name = :userFirstName")
List<UserDetails> findUserDetails(@Param("userFirstName") String userName, Pageable pageable);

userDetailsRepository.findUserDetails(name, PageRequest.of(1, 5));

@NamedQuery

If the same query is needed in several places, define it once, on the entity, with a name:

@Table(name = "user_details")
@Entity
@NamedQuery(
    name = "findByUserName",
    query = "SELECT u FROM UserDetails u WHERE u.name = :userFirstName"
)
public class UserDetails { ... }

and refer to it by name wherever it is used:

@Query(name = "findByUserName")
List<UserDetails> findUsers(@Param("userFirstName") String userName);

A change to the query is then made in one place.

Next: JPQL is portable but cannot express everything. Part 9 covers native SQL queries, and the Criteria API for queries assembled in code at run time.

Check yourself

1. What is a derived query, and which part of findUserDetailsByNameAndPhone does Spring use?
2. Do derived query names use field names or column names?
3. Why does a derived deleteByName need @Transactional?
4. How do you ask for the second page of 5 rows, sorted by name descending? What extra information does a Page give?
5. How do you sort by name ascending and phone descending?
6. How is JPQL different from SQL?
7. A JPQL method returns a single UserDetails, but two rows match. What happens?
8. Give two ways to get a DTO from a JPQL join.
9. Explain the N+1 problem. Why does EAGER not solve it?
10. Give the three fixes for N+1 and how many queries each produces.
11. What does @Modifying do? What does a bulk JPQL delete not do?
12. After a bulk delete, findById still returns the deleted row. Why, and how do you fix it?

Answers

1. A query generated from the method name. ByNameAndPhone: where name = ? and phone = ?.
2. Entity field names.
3. It loads the entities and removes each one, and removing needs a transaction.
4. PageRequest.of(1, 5, Sort.by("name").descending()). Total pages and elements, whether it is the first or last page.
5. Sort.by(Sort.Order.asc("name"), Sort.Order.desc("phone")).
6. It uses entity names and fields instead of tables and columns, so it is database-independent.
7. IncorrectResultSizeDataAccessException: "Query did not return a unique result: 2 results were returned".
8. Select the fields as Object[] and map them yourself, or use SELECT new com.example.UserDTO(...).
9. One query for N parents, then one per parent for its children. With many parents, eager loading still loads each parent's children separately.
10. JOIN FETCH: 1. @EntityGraph: 1. @BatchSize(size = 10): 1 + N/10.
11. It allows UPDATE/DELETE (and insert) queries in @Query. It does not cascade and does not update the persistence context.
12. The entity is still in the persistence context. @Modifying(flushAutomatically = true, clearAutomatically = true).