Dynamic spring data jpa repository query with arbitrary AND clauses

Question

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

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

What is the best approach to implement search option with optional fields ?

Answer 1

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

---

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

Where DaoRepository is

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

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:

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
}