Spring Data JPA - Custom Query Methods

Spring Data JPA allows to create custom query methods from given methods.

 

How does it work?

• The repository interface must extend a Spring Data JPA Repository (JpaRepository, CrudRepository, etc)
• The query method is divided into 2 parts - (find..., exists...) and (By...)
• The custom query method should be built from the reserved keywords.
• Spring Data JPA will then parse the custom query method and create a corresponding JPQL (Java Persistence Query Language) query.
• Method parameters are automatically bound to query parameters.
• Hibernate or any other JPA provider will convert JPQL into SQL and execute it in database

 

Keywords used in query creation

• Full list of keywords can be found in the documentation (https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods)

Keyword	Example	JPQL
Distinct	findDistinctByLastname	select distinct ... where x.lastname = ?1
And	findByLastnameAndFirstname	select ... where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	select ... where x.lastname = ?1 and x.firstname = ?2
Is, Equals	findByFirstname, findByFirstnameIs, findByFirstnameEquals	select ... where x.firstname = ?1
LessThan	findByAgeLessThan	select ... where x.age < ?1
Before	findByStartDateBefore	select ... where x.startDate < ?1
StartingWith	findByFirstnameStartingWith	select ... where x.firstname like ?1
Not	findByLastnameNot	select ... where x.lastname <> ?1
True	findByActiveTrue	select ... where x.active = true

• Queries created by each keyword has their own syntax (parameters, return type), so need to be careful when creating custom queries

 

JPA Named Query

• We can 'name' a query and use it later
• To use named query, we need to use @NamedQuery annotation on an entity.
• The query is written in JPQL format.

// User Entity

@Entity
@NamedQuery(name = "User.findByEmailAddress",
	query = "select u from User u where u.emailAddress = ?1")
public class User {
	// ...
}

// UserRepository

@Query(name = "User.findByEmailAddress")
List<User> findByUsername(@Param("username") String username);

 

@Query annotation

• You can use @Query annotation to create a custom query directly in the repository interface.
• By default, it will use JPQL format.

@Query("SELECT u FROM User u WHERE u.status = 1")
Collection<User> findAllActiveUsers();

• You can set the value of nativeQuery attribute to true to use SQL format. 

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection<User> findAllActiveUsersNative();

 

One of the main advantages of using Spring Data JPA is that we can capture any error when the application loads (not in runtime)