Unlocking the Power of Spring Data JPA Query Methods

Introduction

In our day-to-day life as Spring Boot developers, we have used Spring Data JPA and written queries in repositories but many developers don’t take the advantages provided by JPA. So in this blog, we are going to look at the features of JPA that can be helpful for writing queries.

Spring Data JPA simplifies access to the database and provides a high-level and object-oriented interface to interact with the database. It also helps to reduce JDBC code and complex SQL queries.

Related read: Go Database/SQL

Features of Spring Data JPA

➡️ Repository Abstraction:- It introduces the concept of a repository which is an interface that defines common data access operations such as create, read, update, and delete.
➡️ Query Method:- It allows you to create query methods by naming them according to specific conventions. It will then automatically generate corresponding SQL queries.
➡️ Custom Queries:- For more complex queries, you can write custom SQL queries using JPQL (Java Persistence Query Language) or native SQL.
➡️ Pagination and Sorting:- It simplifies the pagination and sorting of query results.

Let’s try to implement writing JPA queries in different formats:

🔶 Get All Users and Sort Them According to the Creation Date

➡️ By Using JPA Query Methods

Sort sort = Sort.by(Sort.Direction.DESC, "creationDate");
List<User> users = userRepo.findAll(sort);

➡️ By Using a Custom Query

@Query(“select u from User as u order by u.creationDate desc”)
List<User> findAll();

➡️ By Using Native Query

@Query(nativeQuery = true,value="select u from user a u where order by creation_date desc")
List<Users> findAll();

The above queries will give us all users soring them according to the latest creation date.

🔶 Get Users Whose Name is ‘xyz’

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByName(String name);

In this method, the name represents the field name of the user class.

➡️ By Using a Custom Query

@Query(“select u from User as u where name =?1”)
List<User> findByName(String name);

➡️ By Using Native Query

@Query(nativeQuery = true,value="select u from user a u where u.name =?1")
List<Users> findByName(String name);

🔶 Get Users Whose Name Contains ‘m’ in Them

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByNameContaining(String name);

➡️ By Using a Custom Query

@Query(“select u from User as u where name like %?1%”)
List<User> findByNameContains(String name);

➡️ By Using a Native Query

@Query(nativeQuery = true,value="select u from user a u where u.name like %?1%")
List<Users> findByNameContains(String name);

🔶 Get Users Whose Name Starts with ‘A’

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByNameStartsWith(String name);

➡️ By Using a Custom Query

@Query(“select u from User as u where name like ?1%”)
List<User> findByNameStartsWith (String name);

➡️ By Using a Native Query

@Query(nativeQuery = true,value="select u from user a u where u.name like 1%")
List<Users> findByNameStartsWith (String name);

Similarly, we can use EndsWith which will work like %?1 and if we want to ignore the casing while comparing we can use findByNameContainsIgnoreCase.

🔶 Get Users Whose Age is Greater Than 24

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByAgeGreaterThan(int age);

➡️ By Using a Custom Query

@Query(“select u from User as u where u.age >= ?1”)
List<User> findByAgeGreaterThan(int age);

➡️ By Using a Native Query

@Query(nativeQuery = true,value="select u from user a u where u.age>=?1")
List<Users> findByAgeGreaterThan(int age);

Similarly, we can use LessThan for less than age, LessThanEqual for less than and equal to age, and GreaterThanEquals for greater than and equal to age.

🔶 Get Users Whose Age is Between 20 to 24

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByAgeBetween(int startAge,int endAge);

➡️ By Using a Custom Query

@Query(“select u from User as u where u.age between ?1 and ?2”)
List<User> findByAgeBetween(int startAge,int endAge);

➡️ By Using a Native Query

@Query(nativeQuery = true,value="select u from user a u where u.age between ?1 and ?2 ")
List<Users> findByAgeBetween(int startAge,int endAge);

🔶 Get Users Whose IDs are 1,2,3,4,5,6

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByIdIn(List<Long> ids);

➡️ By Using a Custom Query

@Query(“select u from User as u where u.id in ?1”)
List<User> findByIdIn(List<Long> ids);

➡️ By Using a Native Query

@Query(nativeQuery = true,value="select u from user a u where u.id in ?1 ")
List<Users> findByIdIn(List<Long> ids);

🔶 Get Users Who are Created After 2022-02-01

➡️ By Using JPA Query Methods

List<User> users = userRepo.findByCreationDateAfter(Date date);

➡️ By Using a Custom Query

@Query(“select u from User as u where u.creationDate > ?1”)
List<User> findByCreationDateAfter(Date date);

➡️ By Using a Native Query

@Query(nativeQuery = true,value="select u from user a u where u.creation_date > ?1 ")
List<Users> findByCreationDateAfter(Date date);

In the below examples let’s try to access tables with OneToMany ManyToMany And OneToOne tables using Query methods:

findByCHILDTABLENAMEFIELDNAME(Long Id)

For example, let’s try to access a user whose payment ID user USER – PAYMENT relationship is ONE_MANY:

findByPaymentId(long id)

In this case, we can also use the AND operation and check the payment method type:

findByPaymentIdAndPaymentType(long id,String paymentType)

We can also use a delete query to delete records from the table:

deleteByName(String name)

This query method will delete the record where the name will be matched.