春季靴子JPA:使用可选的范围内条件实施搜索查询 [英] Spring Boot & JPA: Implementing search queries with optional, ranged criteria
问题描述
这是 SSCCE ，它显示了研究成果，不是骗人的话题！/strong>
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的想法是:
// 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!
推荐答案
您可以通过JpaSpecificationExecutor在spring数据中使用规范来实现复杂的查询.
存储库接口必须扩展JpaSpecificationExecutor<T>接口，以便我们可以通过创建新的Specification<T>对象来指定数据库查询的条件.
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.
诀窍是将规范接口与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;
....
}
然后我们定义存储库:
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正在执行查询select * from name =?或(surname)的人员=?和age =?)):
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
这篇关于春季靴子JPA:使用可选的范围内条件实施搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
