具有任意 AND 子句的动态 spring 数据 jpa 存储库查询

2022-01-18 00:00:00 spring java spring-data spring-data-jpa querydsl

我正在使用 Spring data jpa repositories，需要提供具有不同字段的搜索功能.在搜索之前输入字段是可选的.我有 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
}

相关文章