带有任意 AND 子句的动态 spring 数据 jpa 存储库查询 [英] Dynamic spring data jpa repository query with arbitrary AND clauses

问题描述

我正在使用 Spring 数据 jpa 存储库，需要提供具有不同字段的搜索功能.在搜索之前输入字段是可选的.我有 5 个字段，分别是 EmployeeNumber、Name、Married、Profession 和 出生日期.
这里我只需要查询用户给定的值，其他字段应该被忽略.例如，

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,Profession and DateOfBirth.
Here i need to query only with the given values by user and other fields should be ignored.Ex,

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%';  

所以这里我们考虑输入和查询的值.在这种情况下，Spring 数据有一个限制，如 这篇文章(不可扩展，应该编写所有可能的查询)我正在使用 Querydsl，但问题仍然存在，因为 null 字段应该被忽略，并且几乎所有可能的查询都需要开发.在这个 case 31 个查询.如果搜索字段是 6,7,8... 呢??

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 null fields 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 (4.x) 和 querydsl-jpa

Please note that there might be changes to be done to use the new major version of QueryDSL (4.x) and querydsl-jpa

在我们的一个项目中，我们使用了 QueryDSL 和 QueryDslPredicateExecutor.

In one of our projects, we used QueryDSL with 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> {

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));

要获取QClasses，需要指定QueryDSL APT Maven 插件 在你的 pom.xml 中.

To get QClasses, you need to specify the QueryDSL APT Maven plugin in your pom.xml.

  <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>

依赖是

    <!-- 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>

或者对于 Gradle:

Or for 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
}

这篇关于带有任意 AND 子句的动态 spring 数据 jpa 存储库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！