What is Jpa Spesification and Why Should We Use It?

Creating dynamic SQL queries is important in software development. There are a few ways to create queries. However some ways to do this is not the best way for some reasons.

In this article, we will learn how to create dynamic queries using JPA Spesification. Also we will learn why we decided to use this approach.

We can access to database using SQL queries to retrieve and modify the data. Using @Query annotation is a method to write SQL queries. However If we try to create dynamic queries, we need to manupilate query string. This approach can cause a security vulnerability called SQL Injection. Jpa Spesification provide a library for creating dynamic queries. Therefore we are able to build SQL queries programmatically and more securely. Let’s start writing some dynamic queries and try to build them using Spesification.

What is SQL Injection?

SQL injection is a web security vulnerability that allows an attacker to interfere with the queries. An attacker can add another query into the application’s query to manipulate it. This can allow an attacker to view data that they are not normally able to access.

Set Up Entities

Assume that we have two tables named User and Account. We need to join theese tables and filter by some fields conditionally.

@Data
@Entity
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "account_number", unique = true)
    private String accountNumber;

    @Enumerated(EnumType.ORDINAL)
    @Column(name = "account_status")
    private AccountStatus accountStatus;

    @ManyToOne
    private User user;

}

User Entity

@Data
@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    private String email;

    @Enumerated(EnumType.ORDINAL)
    @Column(name = "user_status")
    private UserStatus userStatus;

    @OneToMany(targetEntity = Account.class)
    private List accounts;

}

Create Scenarios and Queries with Spesification

In the first scenario we need to retrieve users by name and user-status.

Firstly I will write a query and try to manipulate the query according to conditions.

@Query(select * from user where name like %:name% and user_status=:userStatus)
List<User> findUsersByNameAndUserStatus(@Param("name") String name,
                                        @Param("user_status") UserStatus userStatus
);

It is not a dynamic query. If paramaters are null, this query will retrieve the data which has paramaters with null value from the database.

Let’s start building with Spesification.

Inside the UserRepository;

default List<User> findUsers(UserFilteringRequestDTO filteringRequestDTO) {
    String name = filteringRequestDTO.getName();
    UserStatus userStatus = filteringRequestDTO.getUserStatus();
    Specification<User> specification = Specification.where(null);

    if (name != null && !name.isEmpty()) {
        specification = specification.and(hasName(name));
    }

    if (userStatus != null) {
        specification = specification.and(hasUserStatus(userStatus));
    }

    return findAll(specification);
}

default Specification<User> hasUserStatus(UserStatus userStatus) {
    return (root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("user_status"), userStatus);
}

default  Specification<User> hasName(String name) {
    return (root, query, criteriaBuilder) -> criteriaBuilder.like(root.get("name"), name);
}

We create a dynamic query by checking the name and userStatus in the FindUsers method. If they are null, we do not create “and” spesification for the “where” clause. And inside the hasUserStatus method we built a criteria by writing this “criteriaBuilder.equal(root.get(“user_status”), userStatus)” piece of code. This is the actual meaning of this code to create “user_status=:userStatus”. We reference the user_status column in the User entity by using “(root.get(“user_status”)”.

Finally inside the hasName method we built a citeria by writing this “criteriaBuilder.like(root.get(“name”), name” piece of code. This is the actual meaning of this code to create “name like %:name”. We reference the name column in the User entity by using “(root.get(“name”)”.

If name is null, the query would be like that “select * from user where and user_status=:userStatus”.

If userStatus is null, the query would be like that “select * from user where name like %:name%”.

So we created a dynamic query.

In the second scenario I need to access accounts by user’s email.

Firstly I will write a query and try to manupilate the query through conditions.

@Query(select * from account a left join user u on a.user_id=u.id where email=:email)
List<Account> findAccountByUserEmail(@Param("email") Srting email);

Let’s start building with Spesification.

Inside the AccountRepository;

default List<Account> findAccounts(String email) {
    Specification<Account> specification = Specification.where(null);

    if (email != null && !email.isEmpty()) {
        specification = specification.and(hasEmail(email));
    }

    return findAll(specification);
}

default Specification<Account> hasEmail(String email) {
    return (root, query, criteriaBuilder) -> {
        Join<Account, User> join = root.join("user");
        return criteriaBuilder.equal(join.get("email"), email);
    };
}

We create a dynamic query by checking email. If It is null, we do not create “and” spesification for the “where” clause.

And inside the hasEmail method we joined user and account tables using this “Join<Account, User> join = root.join(“user”)“ piece of code to filter account data by email.

Finally inside the hasEmail method we built a criteria by writing this “criteriaBuilder.equal(join.get(“email”), email” piece of code. This is the actual meaning of this code to create “email =:email”. We reference the email column in the User entity by using “(root.get(“email “)”.

If email is null, the query would be like that “select * from account”.

So we created a dynamic query.

Conclusion

Although writing SQL Queries in @Query annotation may looks straightforward, there are some reasons not to do so. The main reason for prefering the Spesification approach is security. By using specification, we save our application from SQL Inection attacks. Another reason why this approach is preferred is that dynamic queries can be easily created. Using the specification, we easily create dynamic queries instead of modifying SQL Queries inside the @Query annotation.