Spring data JPA where clause

1. Overview

This article is about to learn spring data JPA where clause, In SQL or NoSQL where clause use for filter the records from the table, for example, we some records in Employee table but we want only those employee whose designation is DEVELOPER in that case we use the WHERE clause.

In this article, we will learn WHERE clause using the query method, @Query annotation and native query. We will learn the different combination of where clause like multiple AND and OR conditions with where,  where with between, paging and dynamic where conditions with examples.

If you are totally new with spring data JPA then here is Spring data JPA tutorial which will help you to learn more about Spring Data JPA. To apply filters on the data we can use LIKE or IN query inside WHERE clause here are articles about Spring JPA Like Query and Spring JPA In query.

2.1 Spring JPA where query

Here we have written the same query with three different approaches,

1. Using the method approach, Spring automatically generates the query based on method naming convention.
2. @Query annotation we have written query and as arguments passed the parameters.
3. @Query annotation, we have marked nativeQuery as a true, so Query considers as a native query.

@Repository
@Transactional
public interface EmployeeDAO extends CrudRepository<Employee,Long> {
    List<Employee> findByEmployeeName(@Param("name") String name);      // using method
    @Query("SELECT e from Employee e where e.employeeName =:name ")       // using @query
    List<Employee> findByName(@Param("name") String name);
    @Query(value = "SELECT e from Employee e where e.employeeName =:name ", nativeQuery = true)    @ using @query with native
    List<Employee> findByNameNative(@Param("name") String name);
}

2.2 Spring JPA where multiple conditions

Here where cause with the multiple conditions and between those conditions AND operators are there, so it will fetch only those data whose both the condition are satisfied.

// where with AND
   List<Employee> findByEmployeeNameAndEmployeeRole(@Param("name")String name,@Param("role") String role);
   @Query("SELECT e from Employee e where e.employeeName =:name AND e.employeeRole =:role")
   List<Employee> findByNameAndRole(@Param("name") String name,@Param("role")String role);
   @Query(value = "SELECT * from Employee e where e.employeeName =:name AND e.employeeRole = :role ", nativeQuery = true)
   List<Employee> findByNameAndRoleNative(@Param("name") String name, @Param("role")String role);

2.3 Spring JPA where multiple conditions OR

Here, WHERE cause with multiple conditions but with OR operator so it will fetch those records whose both or any of one condition will be satisfied.

// where with OR
   List<Employee> findByEmployeeNameOrEmployeeRole(@Param("name")String name,@Param("role") String role);
   @Query("SELECT e from Employee e where e.employeeName =:name or e.employeeRole =:role")
   List<Employee> findByNameOrRole(@Param("name") String name,@Param("role")String role);
   @Query(value = "SELECT * from Employee e where e.employeeName =:name or e.employeeRole = :role ", nativeQuery = true)
   List<Employee> findByNameOrRoleNative(@Param("name") String name, @Param("role")String role);

2.4 Spring JPA where with between query

Here, WHERE clause with BETWEEN criteria, Query will return those records which field value between those values.

• @Temporal(TemporalType.DATE) indicate that consider the only DATE without time. If you want to consider criteria including time with passing parameter then remove @Temporal(TemporalType.DATE) from the method.

// where with BETWEEN
  List<Employee> findByJoinedDateBetween(@Temporal(TemporalType.DATE) @Param("startDate") Date startDate,@Temporal(TemporalType.DATE) @Param("endDate")Date endDate);
  @Query("SELECT e from Employee e where e.joinedDate between :startDate and :endDate")
  List<Employee> findByJoinedDate(@Temporal(TemporalType.DATE) @Param("startDate") Date startDate, @Temporal(TemporalType.DATE) @Param("endDate") Date endDate);
  @Query(value = "SELECT * from Employee e where e.joinedDate between :startDate and :endDate", nativeQuery = true)
  List<Employee> findByJoinedDateBetweenNative(@Temporal(TemporalType.DATE) @Param("startDate") Date startDate,@Temporal(TemporalType.DATE) @Param("endDate")Date endDate);

2.5 Spring JPA where with Paging

For performance, Lasy loading we generally fetch records with paging means we will not fetch all the records from table but we just fetch records based on user navigation or requirements for that, we need to pass Pageableinterface in method name as last parameters and other things will be managed by spring JPA.

• for nativeQuery, pass Pageable as a parameter but comment out /*#pageable*/ in the query and other things will be managed by spring

// where with Paging
    Page<Employee> findByEmployeeName(String name, Pageable pageable );
    @Query("SELECT e from Employee e where e.employeeName = :name ")
    Page<Employee> findByName(@Param("name") String name, Pageable pageable);
    @Query(value = "SELECT * from Employee e where e.employeeName = :name /*#pageable*/ ",  nativeQuery = true)
    Page<Employee> findByNameNative(@Param("name") String name, Pageable pageable);

2.6 Spring JPA dynamic where

So many times, We need to generate a dynamic query based on user input, for example when we apply multiple filters on the UI and based on user selection we need to generate a query and give the results.

Here we have extends JpaSpecificationExecutor and in findAll method we have extends Specification interface have toPredicate() based on those predicates the dynamic query will be generated.

findByCriteria(null) : It will return all the employee

findByCriteria("Harry"): It will return only those employee whose name is Harry.

If are looking for more details explanation in then here is an article for generating dynamic in spring JPA with examples.

import com.javadeveloperzone.model.Employee;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public interface EmployeeDAO extends CrudRepository<Employee,Long>,JpaSpecificationExecutor<Employee>{
public List<Employee> findByCriteria(String employeeName){
        return employeeDAO.findAll(new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if(employeeName!=null) {
                    predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName)));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        });
    }
}

2.7 More clause which we can use with where

3. Conclusion

In this article, We learn about different examples of where clause with spring JPA, If haven’t created Rest Service with Spring JPA then here is an article for Spring Rest Service with JPA with MySQL and want to create an example with No-SQL database then here is an article for Spring boot Rest Service with MongoDB Example. We have developed source code with spring boot and you can also use spring JPA without spring boot application.

4. References

• Spring JPA documentation
• Java TemporalType Documentation

5. Source Code

• Git : spring-boot-jpa-where-query