OR search with Spring Data Jpa Specification

Here's how to use Spring Data JPA to do a simple OR search and a slightly more complex OR search.

Entity Suppose the Entity associated with the DB table looks like this.

@Data
@Entity
@Table(name = "users")
public class Users {
  @Id
  @Column(name = "user_id")
  private int userId;

  @Column(name = "company_id")
  private int companyId;
}

Repository

public interface UsersRepository extends JpaRepository<Users, Integer> {

}

Specification

public class UsersSpecifications {
  public static Specification<Users> userIdContains(
      int userId) {
    return new Specification<Users>() {
      @Override
      public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> query,
          CriteriaBuilder cb) {
        return cb.equal(root.get("userId"), userId);
      }
    };

  public static Specification<Users> companyIdContains(
      int companyId) {
    return new Specification<Users>() {
      @Override
      public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> query,
          CriteriaBuilder cb) {
        return cb.equal(root.get("companyId"), companyId);
      }
    };
  }
  }
}

Simple OR search

WHERE user_id = 1 OR user_id = 2;

usersRepository.findAll(Specification
            .where(UsersSpecifications.userIdContains(1))
            .or(UsersSpecifications.userIdContains(2)));

A little complicated OR search

WHERE company_id = 3 AND (user_id = 1 OR user_id = 2);

usersRepository.findAll(Specification
            .where(UsersSpecifications.companyIdContains(1))
            .and(UsersSpecifications.userIdContains(1).or(UsersSpecifications.userIdContains(2))));

Recommended Posts

OR search with Spring Data Jpa Specification
Exists using Specification in Spring Data JPA
Dynamically generate queries with Spring Data JPA (multi-word search) (paging support)
Sort by Spring Data JPA (with compound key sort)
Creating a common repository with Spring Data JPA
A memorandum when trying Spring Data JPA with STS
MySQL JSON type Value search with SpringBoot + Spring JPA
[spring] Let's use Spring Data JPA
I tried to get started with Spring Data JPA
Spring Boot Introductory Guide I tried [Accessing Data with JPA]
[How to install Spring Data Jpa]
Spring Data JPA SQL log output
See the behavior of entity update with Spring Boot + Spring Data JPA
Creating REST APIs with Spring JPA Data with REST and Lombok incredibly easy.
Spring with Kotorin --2 RestController and Data Class
Create an or search function with Ransack.
Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)
Until data acquisition with Spring Boot + MyBatis + PostgreSQL
Spring Data JPA save select-insert is only insert
Spring Data JPA Entity cross-reference and its notes
Jackson is unable to JSON serialize hibernateLazyInitializer in Spring Data JPA with error
Sample code for search using QBE (Query by Example) of Spring Data JPA
Spring Boot + Spring Data JPA About multiple table joins
Compatibility of Spring JDBC and MyBatis with Spring Data JDBC (provisional)
Until the use of Spring Data and JPA Part 2
Until the use of Spring Data and JPA Part 1
Make the where clause variable in Spring Data JPA
Spring with Kotorin --- 5. Actuator
Spring Data JDBC Preview
Self-made Validation with Spring
Spring with Kotorin ―― 1. SPRING INITIALIZR
Download with Spring Boot
spring data dynamodb trap
Search AWS S3 resources with Spring Cloud AWS Resource Handling feature
Implement REST API with Spring Boot and JPA (Application Layer)
Implement REST API with Spring Boot and JPA (Infrastructure layer)
Try hitting the zip code search API with Spring Boot
Connect to database with spring boot + spring jpa and CRUD operation
Flow until output table data to view with Spring Boot
Implement REST API with Spring Boot and JPA (domain layer)