Java 带有任意 AND 子句的动态 spring 数据 jpa 存储库查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28874135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic spring data jpa repository query with arbitrary AND clauses
提问by Mr.Chowdary
I'm using Spring data jpa repositories, Got a requirement to give search feature with different fields. Entering fields before search is optional.I have 5 fields say EmployeeNumber, Name, Married,Professionand DateOfBirth.
Here i need to query only with the given values by user and other fields should be ignored.Ex,
我正在使用Spring data jpa repositories，需要提供具有不同字段的搜索功能。在搜索之前输入字段是可选的。我有 5 个字段EmployeeNumber，例如Name、Married、Profession和DateOfBirth。
这里我只需要查询用户给定的值，其他字段应该被忽略。例如，
Input : EmployeeNumber: ,Name:St,Married: ,Professsion:IT,DateOfBirth:
Query : Select * from Employee e where Name like 'St%' and Profession like 'IT%';
Input : EmployeeNumber:10,Name: ,Married: ,Professsion:IT,DateOfBirth:
Query : Select * from Employee e where EmployeeNumber like '10%' and Profession like 'IT%';
So here we are considering values entered and querying. In this case, Spring data is having a limitation as mentioned in this post(Not scalable and all possible queries should be written)
I'm using Querydsl, but still the problem exists as nullfields should be ignored and almost all possible queries need to be developed. In this case 31 queries.
what if search fields are 6,7,8...??
所以这里我们正在考虑输入和查询的值。在这种情况下，春季数据是具有限制中提到的这篇文章（不可扩展，所有可能出现的问题，应书面）我使用的Querydsl，但仍然存在问题的null领域应该被忽视，需要开发的几乎所有可能出现的问题. 在这case 31 queries。如果搜索字段是什么6,7,8...？
What is the best approach to implement search option with optional fields ?
使用可选字段实现搜索选项的最佳方法是什么？
采纳答案by EpicPandaForce
Please note that there might be changes to be done to use the new major version of QueryDSL (4.x) and querydsl-jpa
请注意，使用 QueryDSL (4.x) 和querydsl-jpa的新主要版本可能需要进行一些更改
In one of our projects, we used QueryDSLwith QueryDslPredicateExecutor<T>.
在我们的一个项目中，我们使用QueryDSL了QueryDslPredicateExecutor<T>.
public Predicate createPredicate(DataEntity dataEntity) {
QDataEntity qDataEntity = QDataEntity.dataEntity;
BooleanBuilder booleanBuilder = new BooleanBuilder();
if (!StringUtils.isEmpty(dataEntity.getCnsiConsumerNo())) {
booleanBuilder
.or(qDataEntity.cnsiConsumerNo.contains(dataEntity.getCnsiConsumerNo()));
}
if (!StringUtils.isEmpty(dataEntity.getCnsiMeterNo())) {
booleanBuilder.or(qDataEntity.cnsiMeterNo.contains(dataEntity.getCnsiMeterNo()));
}
return booleanBuilder.getValue();
}
And we could use this in the repositories:
我们可以在存储库中使用它：
@Repository
public interface DataEntityRepository
extends DaoRepository<DataEntity, Long> {
Where DaoRepositoryis
哪里DaoRepository是
@NoRepositoryBean
public interface DaoRepository<T, K extends Serializable>
extends JpaRepository<T, K>,
QueryDslPredicateExecutor<T> {
}
Because then, you can use repository predicate methods.
因为那时，您可以使用存储库谓词方法。
Iterable<DataEntity> results = dataEntityRepository.findAll(dataEntityPredicateCreator.createPredicate(dataEntity));
To get QClasses, you need to specify the QueryDSL APT Maven pluginin your pom.xml.
要获取QClasses，您需要在 pom.xml 中指定QueryDSL APT Maven 插件。
<build>
<plugins>
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>maven-apt-plugin</artifactId>
<version>1.0.4</version>
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources</outputDirectory>
<processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
Dependencies are
依赖是
<!-- querydsl -->
<dependency>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-core</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>
Or for Gradle:
或者对于 Gradle：
sourceSets {
generated
}
sourceSets.generated.java.srcDirs = ['src/main/generated']
configurations {
querydslapt
}
dependencies {
// other deps ....
compile "com.mysema.querydsl:querydsl-jpa:3.6.3"
compile "com.mysema.querydsl:querydsl-apt:3.6.3:jpa"
}
task generateQueryDSL(type: JavaCompile, group: 'build', description: 'Generates the QueryDSL query types') {
source = sourceSets.main.java
classpath = configurations.compile + configurations.querydslapt
options.compilerArgs = [
"-proc:only",
"-processor", "com.mysema.query.apt.jpa.JPAAnnotationProcessor"
]
destinationDir = sourceSets.generated.java.srcDirs.iterator().next()
}
compileJava {
dependsOn generateQueryDSL
source generateQueryDSL.destinationDir
}
compileGeneratedJava {
dependsOn generateQueryDSL
classpath += sourceSets.main.runtimeClasspath
}
回答by iamiddy
You can use Specifications that Spring-data gives you out of the box. and be able to use criteria API to build queries programmatically.To support specifications you can extend your repository interface with the JpaSpecificationExecutor interface
您可以使用 Spring-data 开箱即用的规范。并且能够使用标准 API 以编程方式构建查询。为了支持规范，您可以使用 JpaSpecificationExecutor 接口扩展您的存储库接口
public interface CustomerRepository extends SimpleJpaRepository<T, ID>, JpaSpecificationExecutor {
}
The additional interface(JpaSpecificationExecutor ) carries methods that allow you to execute Specifications in a variety of ways.
附加接口 (JpaSpecificationExecutor) 带有允许您以多种方式执行规范的方法。
For example, the findAll method will return all entities that match the specification:
例如， findAll 方法将返回所有符合规范的实体：
List<T> findAll(Specification<T> spec);
The Specification interface is as follows:
规范界面如下：
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
CriteriaBuilder builder);
}
Okay, so what is the typical use case? Specifications can easily be used to build an extensible set of predicates on top of an entity that then can be combined and used with JpaRepository without the need to declare a query (method) for every needed combination. Here's an example: Example 2.15. Specifications for a Customer
好的，那么典型的用例是什么？规范可以很容易地用于在实体之上构建一组可扩展的谓词，然后可以将其组合并与 JpaRepository 一起使用，而无需为每个需要的组合声明查询（方法）。这是一个示例：示例 2.15。客户规格
public class CustomerSpecs {
public static Specification<Customer> isLongTermCustomer() {
return new Specification<Customer>() {
public Predicate toPredicate(
Root<Customer> root, CriteriaQuery<?> query,
CriteriaBuilder builder) {
LocalDate date = new LocalDate().minusYears(2);
return builder.lessThan(root.get('dateField'), date);
}
};
}
public static Specification<Customer> hasSalesOfMoreThan(MontaryAmount value) {
return new Specification<Customer>() {
public Predicate toPredicate(
Root<T> root, CriteriaQuery<?> query,
CriteriaBuilder builder) {
// build query here
}
};
}
}
You expressed some criteria on a business requirement abstraction level and created executable Specifications. So a client might use a Specification as follows:
您在业务需求抽象级别表达了一些标准并创建了可执行规范。因此，客户端可能会使用以下规范：
List customers = customerRepository.findAll(isLongTermCustomer());
You can also combine Specification Example 2.17. Combined Specifications
您还可以结合规范示例 2.17。组合规格
MonetaryAmount amount = new MonetaryAmount(200.0, Currencies.DOLLAR);
List<Customer> customers = customerRepository.findAll(
where(isLongTermCustomer()).or(hasSalesOfMoreThan(amount)));
As you can see, Specifications offers some glue-code methods to chain and combine Specifications. Thus extending your data access layer is just a matter of creating new Specification implementations and combining them with ones already existing.
如您所见，规范提供了一些粘合代码方法来链接和组合规范。因此，扩展您的数据访问层只是创建新的规范实现并将它们与现有的结合起来的问题。
And you can Create Complex Specifications, here is an example
你可以创建复杂的规范，这是一个例子
public class WorkInProgressSpecification {
public static Specification<WorkInProgress> findByCriteria(final SearchCriteria searchCriteria) {
return new Specification<WorkInProgress>() {
@Override
public Predicate toPredicate(
Root<WorkInProgress> root,
CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<Predicate>();
if (searchCriteria.getView() != null && !searchCriteria.getView().isEmpty()) {
predicates.add(cb.equal(root.get("viewType"), searchCriteria.getView()));
}
if (searchCriteria.getFeature() != null && !searchCriteria.getFeature().isEmpty()) {
predicates.add(cb.equal(root.get("title"), searchCriteria.getFeature()));
}
if (searchCriteria.getEpic() != null && !searchCriteria.getEpic().isEmpty()) {
predicates.add(cb.equal(root.get("epic"), searchCriteria.getEpic()));
}
if (searchCriteria.getPerformingGroup() != null && !searchCriteria.getPerformingGroup().isEmpty()) {
predicates.add(cb.equal(root.get("performingGroup"), searchCriteria.getPerformingGroup()));
}
if (searchCriteria.getPlannedStartDate() != null) {
System.out.println("searchCriteria.getPlannedStartDate():" + searchCriteria.getPlannedStartDate());
predicates.add(cb.greaterThanOrEqualTo(root.<Date>get("plndStartDate"), searchCriteria.getPlannedStartDate()));
}
if (searchCriteria.getPlannedCompletionDate() != null) {
predicates.add(cb.lessThanOrEqualTo(root.<Date>get("plndComplDate"), searchCriteria.getPlannedCompletionDate()));
}
if (searchCriteria.getTeam() != null && !searchCriteria.getTeam().isEmpty()) {
predicates.add(cb.equal(root.get("agileTeam"), searchCriteria.getTeam()));
}
return cb.and(predicates.toArray(new Predicate[] {}));
}
};
}
}
Here is the JPA Respositories docs
回答by Robert Niestroj
From Spring Data JPA 1.10 there is another option for this is Query By Example.
Your repository should implement apart from JpaRepositoryalso the QueryByExampleExecutorinterface where you get methods like:
从 Spring Data JPA 1.10 开始，还有另一个选项是Query By Example。您的仓库应该实行分开JpaRepository也是QueryByExampleExecutor界面，你会得到类似的方法：
<S extends T> Iterable<S> findAll(Example<S> example)
Then you create the Exampleto search for like:
然后创建示例以搜索类似：
Employee e = new Employee();
e.setEmployeeNumber(getEmployeeNumberSomewherFrom());
e.setName(getNameSomewhereFrom());
e.setMarried(getMarriedSomewhereFrom());
e.setProfession(getProfessionSomewhereFrom());
e.setDateOfBirth(getDateOfBirthSomewhereFrom());
and then:
进而：
employeeRepository.findAll(Example.of(e));
If some parameters are null they wont be taken in to WHERE clause so you get dynamic queries.
如果某些参数为空，它们将不会被纳入 WHERE 子句，因此您将获得动态查询。
To refine matching of String attributes take a look at ExampleMatcher's
要细化 String 属性的匹配，请查看ExampleMatcher's
An ExampleMatcherwhich does a case-insensitive likeis for example:
一个ExampleMatcher不区分大小写like的例子是：
ExampleMatcher matcher = ExampleMatcher.matching().
withMatcher("profession", ExampleMatcher.GenericPropertyMatcher.of(ExampleMatcher.StringMatcher.CONTAINING).ignoreCase());
QBE Examples: https://github.com/spring-projects/spring-data-examples/tree/master/jpa/query-by-example
QBE 示例：https: //github.com/spring-projects/spring-data-examples/tree/master/jpa/query-by-example
