Spring Boot和JPA：使用可选的范围内条件实现搜索查询

  • Z时代
  • 2024-01-10
  • 分类：问答

Spring Boot" title="Spring Boot">Spring Boot REST服务和MySQL在这里。我有以下Profile实体：

@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基于各种条件进行搜索：

# 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例如：

// 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），因此我在如何编写JPQL查询方面陷入困境ProfileRepository：

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

当然，如果有任何漂亮的小程序库，或者如果Spring Boot / JPA已经为此提供了解决方案，那么我非常高兴！

回答：

您可以通过JpaSpecificationExecutor使用Spring数据来实现具有规范的复杂查询。存储库接口必须扩展该JpaSpecificationExecutor<T>接口，以便我们可以通过创建新Specification<T>对象来指定数据库查询的条件。

诀窍是将Specification接口与结合使用JpaSpecificationExecutor。这是示例：

@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;
        ....
}

然后，我们定义存储库：

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

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

现在，我们要做的是定义我们的规范，该规范将返回Predicate包含查询约束的规范（在示例中，PersonSpecification执行查询的select

* from name =？或（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;
    }
}

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

…

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中存在的完整示例

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

以上是 Spring Boot和JPA：使用可选的范围内条件实现搜索查询 的全部内容， 来源链接： utcz.com/qa/431706.html

回到顶部