Spring Boot &JPA:使用可选的范围条件实现搜索查询 [英] Spring Boot & JPA: Implementing search queries with optional, ranged criteria

问题描述

这是一个 SSCCE，展示研究，不是骗子，而且是正题！！！

This is an SSCCE, shows research, isn't a dupe and is on topic!!!

Spring Boot REST 服务和 MySQL 在这里.我有以下 Profile 实体:

Spring Boot REST service and MySQL here. I have the following Profile entity:

@Entity
@Table(name = "profiles")
public class Profile extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "profile_given_name")
    private String givenName;

    @Column(name = "profile_surname")
    private String surname;

    @Column(name = "profile_is_male")
    private Integer isMale;

    @Column(name = "profile_height_meters", columnDefinition = "DOUBLE")
    private BigDecimal heightMeters;

    @Column(name = "profile_weight_kilos", columnDefinition = "DOUBLE")
    private BigDecimal weightKilos;

    @Column(name = "profile_dob")
    private Date dob;

    // Getters, setters & ctor down here
}

我还有一个 ProfileController 并且我想公开一个 GET 端点，它提供了一种非常灵活/健壮的方式来根据各种条件搜索 Profiles:

I also have a ProfileController and I want to expose a GET endpoint that provides a really flexible/robust way to search for Profiles based on a large range of criteria:

# Search for women between 1.2 and 1.8 meters tall.
GET /v1/profiles?isMale=0&heightMeters={"gt": 1.2, "lt": 1.8}

# Search for men born after Jan 1, 1990 who weigh less than 100 kg.
GET /v1/profiles?isMale=1&dob={"gt" : "1990-01-01 00:00:00"}&weightKilos={"lt": 100.0}

等

这是我的控制器:

@RestController
@RequestMapping("/v1/profiles")
public class ProfileResource {
  @Autowired
  ProfileRepository profileRepository;

  @GetMapping
  public ResponseEntity<Set<Profile>> searchProfiles(@RequestParam(value = "isMale", required = false) String isMaleVal,
                                              @RequestParam(value = "heightMeters", required = false) String heightMetersVal,
                                              @RequestParam(value = "weightKilos", required = false) String weightKilosVal,
                                              @RequestParam(value = "dob", required = false) String dobVal) {

      Integer isMaleVal;
      BooleanCriteria isMaleCriteria;
      if(isMaleVal != null) {
        // Parse the value which could either be "0" for female, "1" for male or something like
        // ?isMale={0,1} to indicate

        // BooleanCriteria would store which values male, female or both) to include in the search
      }

      BigDecimal heighMeters;
      BigDecimalCriteria heightCriteria;
      if(heightMetersVal != null) {
        // Parse the value which like in the examples could be something like:
        // ?heightMeters={"gt" : "1.0"}

        // BigDecimalCriteria stores range information
      }

      BigDecimal heighMeters;
      BigDecimalCriteria weightCriteria;
      if(weightKilosVal != null) {
        // Parse the value which like in the examples could be something like:
        // ?weightKilos={"eq" : "100.5"}

        // BigDecimalCriteria stores range information
      }

      // Ditto for DOB and DateCriteria

      // TODO: How to pack all of these "criteria" POJOs into a
      // CrudRepository/JPQL query against the "profiles" table?
      Set<Profile> profiles = profileRepository.searchProfiles(
        isMaleCriteria, heightCriteria, weightCriteria, dobCriteria);
    }
}

我对 BigDecimalCriteria 的想法是这样的:

My thinking for, say, BigDecimalCriteria would be something like:

// Basically it just stores the (validated) search criteria that comes in over the wire
// on the controller method
public class BigDecimalCriteria {
  private BigDecimal lowerBound;
  private Boolean lowerBoundInclusive;
  private BigDecimal upperBound;
  private Boolean upperBoundInclusive;

  // Getters, setters, ctors, etc.
}

由于所有这些搜索条件都是可选的(因此可以是 null)，我一直在思考如何在 ProfileRepository 中编写 JPQL 查询:

Since all of these search criteria are optional (and thus can be null), I'm stuck on how to write the JPQL query in the ProfileRepository:

public interface ProfileRepository extends CrudRepository<Profile,Long> {
  @Query("???")
  public Set<Profile> searchProfiles();
}

我如何为 ProfileRepository#searchProfiles 实现 @Query(...) 以启用我的所有搜索条件(给定所有允许的范围和要搜索的条件值)，并允许任何条件为空/可选?

How can I implement the @Query(...) for ProfileRepository#searchProfiles in such a way that enables all of my search criteria (given all the permissible ranges and criteria values to search for), and allows any criteria to be null/optional?

当然，如果有任何漂亮的小库或者 Spring Boot/JPA 已经有解决方案，我会全力以赴！

Of course, if there are any nifty little libraries or if Spring Boot/JPA already has a solution for this, I'm all ears!

推荐答案

你可以在 spring 数据中通过 JpaSpecificationExecutor 实现复杂的有规范的查询.存储库接口必须扩展 JpaSpecificationExecutor 接口，以便我们可以通过创建新的 Specification 对象来指定数据库查询的条件.

You can achieve complex queries with specifications by JpaSpecificationExecutor in spring data. Repository interface must extend the JpaSpecificationExecutor<T> interface so we can specify the conditions of our database queries by creating new Specification<T> objects.

诀窍在于将 Specification 接口与 JpaSpecificationExecutor 结合使用.这是示例:

The trick is in the use of the Specification interface in combination with a JpaSpecificationExecutor. here is the example:

@Entity
@Table(name = "person")
public class Person {

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

 @Column(name = "name")
 private String name;

 @Column(name = "surname")
 private String surname;

 @Column(name = "city")
 private String city;

 @Column(name = "age")
 private Integer age;

        ....

}

然后我们定义我们的存储库:

Then we define our repository:

public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {

}

如您所见，我们扩展了另一个接口 JpaSpecificationExecutor.该接口定义了通过规范类执行搜索的方法.

As you can see we have extended another interface the JpaSpecificationExecutor. This interface defines the methods to perform the search via a Specification class.

我们现在要做的是定义我们的规范，该规范将返回包含查询约束的Predicate(在示例中，PersonSpecification 正在执行查询选择* 来自姓名 = ? 或(姓氏 = ? 和年龄 = ?) 的人):

What we have to do now is to define our specification that will return the Predicate containing the constraints for the query (in the example the PersonSpecification is performing the query select * from person where name = ? or (surname = ? and age = ?) ):

public class PersonSpecification implements Specification<Person> {

    private Person filter;

    public PersonSpecification(Person filter) {
        super();
        this.filter = filter;
    }

    public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> cq,
            CriteriaBuilder cb) {

        Predicate p = cb.disjunction();

        if (filter.getName() != null) {
            p.getExpressions()
                    .add(cb.equal(root.get("name"), filter.getName()));
        }

        if (filter.getSurname() != null && filter.getAge() != null) {
            p.getExpressions().add(
                    cb.and(cb.equal(root.get("surname"), filter.getSurname()),
                            cb.equal(root.get("age"), filter.getAge())));
        }

        return p;
    }
}

现在是时候使用它了.以下代码片段展示了如何使用我们刚刚创建的规范:

Now it is time to use it. The following code fragment shows how to use the Specification we just created:

...

Person filter = new Person();
filter.setName("Mario");
filter.setSurname("Verdi");
filter.setAge(25);

Specification<Person> spec = new PersonSpecification(filter);

List<Person> result = repository.findAll(spec);

这里是 github 中的完整示例

Here is full example present in github

您还可以使用规范创建任何复杂的查询

Also you can create any complex queries using Specification

这篇关于Spring Boot &JPA:使用可选的范围条件实现搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！