Dynamic Spring Data JPA Repository Query with Arbitrary and Clauses

Dynamic spring data jpa repository query with arbitrary AND clauses

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
}

Dynamic query using JpaRepository

I'm posting the solution here.

I didn't use the JPARepository, I added the treatment of my request in the ServiceImpl, directly injecting the EntityManager in this way:
an example taking as filter just unidad (i can add others)

public class PeticionServiceImpl implements PeticionService {

/*..
 */
@Inject
private EntityManager em;

@Override
public List<PeticionDTO> searchFilter( String unidad) {
    String consulta = "select peticion from Peticion peticion ";
    if (unidad != null && !"".equals(unidad)) {
        consulta = consulta + " where ";

        if (unidad != null && !"".equals(unidad)) {
            consulta = consulta
                    + " UPPER(translate(peticion.contacto.poa, 'áéíóúÁÉÍÓÚ', 'aeiouAEIOU')) LIKE UPPER(translate('%"
                    + unidad + "%', 'áéíóúÁÉÍÓÚ', 'aeiouAEIOU')) ";
            filtro = true;
        }
    }
    peticiones = peticionMapper.peticionsToPeticionDTOs((List<Peticion>) em.createQuery(consulta).getResultList());
    return peticiones;
}

}

Spring Data JPA: How to form a conditional query?

For optional field, use this : @Query("select d from data d where (:name is null or d.name=:name) and (:age is null or d.age=:age) and (:date is null or d.date=:date)")

---

Related Topics