Blog

Introduction

•  Spring Data JPA is a layer built on top of JPA that provides features out of the box to ease data access from the database.
• The repository interface in Spring Data JPA allows us to define the data access from the database.
• Spring Data JPA allows us two ways to query data from the database. defining query with query derivation and defining manual query.
• In this article, we will learn about query derivation.

Query Derivation

• Query derivation is a very powerful feature that allows us to just name the method in a certain way and Spring Data JPA does the job to generate Query for it.
• For example, if we have PermissionRepository that extends from JPARepository in order to get the permission entity from the type we can just define the method findByType, and whenever we access this method Spring Data JPA will generate a query for it.

public interface PermissionRepository extends JpaRepository<Permission, Integer> {

    Permission findByType(String type);
}

Generated query

• Query creation is the combination of subject and predicate. The subject generally starts with keywords such as find..by, count..by, read..by, etc.
• List of keywords: Subject Keyword, Predicate Keywords

Use Case

Table / Entity

@Entity
@Table(name="ACCOUNTS")
public class Account {
    @Id @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "accounts_seq")
    @SequenceGenerator(name = "accounts_seq", sequenceName = "accounts_seq", allocationSize = 1)
    @Column(name = "user_id")
    private int userId;
    private String username;
    private String password;
    private String email;
    private Timestamp createdOn;
    private Timestamp lastLogin;
  
    // setters getters
}

FindByUsername

• We can add a property such as a username with FindBy

accountRepository.findByUsername("sam");

=> Hibernate: select a1_0.user_id,a1_0.created_on,a1_0.email,a1_0.last_login,a1_0.password,a1_0.permissions_id,a1_0.username from accounts a1_0 where a1_0.username=?

CountByUsername/CountByPermission

• Similarly, we can use a username with CountBy.

accountRepository.countByUsername("sam");

=> Hibernate: select count(a1_0.user_id) from accounts a1_0 where a1_0.username=?


accountRepository.countByPermission(getPermission());

=> Hibernate: select p1_0.id,p1_0.type from permissions p1_0 where p1_0.type=?

CountByPermissionAndCreatedOnGreaterThan

• We can define methods with predicates keywords such as AND, GREATER_THAN.

accountRepository.countByPermissionAndCreatedOnGreaterThan(getPermission(),new Timestamp(parsedDate.getTime()));

=> Hibernate: select count(a1_0.user_id) from accounts a1_0 where a1_0.permissions_id=? and a1_0.created_on>?

• Find more info in the docs.

Conclusion

• Query derivation is a very powerful feature that provides an abstraction to query databases without writing a query.
• But before going too far with this we should know writing complex queries with query derivation will be challenging, in such cases writing manual queries is better.

Before You Leave

• Upgrade your Java skills with Grokking the Java Interview
• If you want to upskill your Java skills, you should definitely check out
  [NEW] Master Spring Boot 3 & Spring Framework 6 with Java
  [ 38 hrs content, 4.7/5 stars, 6+ students already enrolled]