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

问题描述

我正在使用 Spring数据jpa存储库，需要为搜索功能提供不同的字段。在搜索之前输入字段是可选的。我有5个字段说 EmployeeNumber ，名称，已婚，专业和 DateOfBirth 。

这里我只需要查询用户和其他字段给定的值应该被忽略.Ex，

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 字段应该被忽略，几乎所有可能的查询都需要开发。在此案例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 with QueryDslPredicateExecutor< T> 。

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 ，您需要指定你的pom.xml中的 QueryDSL APT Maven插件。

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：

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