Dynamic spring data jpa repository query with arbitrary AND clauses
Solution 1:
You can use Specifications that Spring-data gives you out of the box. and be able to use criteria API to build queries programmatically.To support specifications you can extend your repository interface with the JpaSpecificationExecutor interface
public interface CustomerRepository extends SimpleJpaRepository<T, ID>, JpaSpecificationExecutor {
}
The additional interface(JpaSpecificationExecutor ) carries methods that allow you to execute Specifications in a variety of ways.
For example, the findAll method will return all entities that match the specification:
List<T> findAll(Specification<T> spec);
The Specification interface is as follows:
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
CriteriaBuilder builder);
}
Okay, so what is the typical use case? Specifications can easily be used to build an extensible set of predicates on top of an entity that then can be combined and used with JpaRepository without the need to declare a query (method) for every needed combination. Here's an example: Example 2.15. Specifications for a Customer
public class CustomerSpecs {
public static Specification<Customer> isLongTermCustomer() {
return new Specification<Customer>() {
public Predicate toPredicate(
Root<Customer> root, CriteriaQuery<?> query,
CriteriaBuilder builder) {
LocalDate date = new LocalDate().minusYears(2);
return builder.lessThan(root.get('dateField'), date);
}
};
}
public static Specification<Customer> hasSalesOfMoreThan(MontaryAmount value) {
return new Specification<Customer>() {
public Predicate toPredicate(
Root<T> root, CriteriaQuery<?> query,
CriteriaBuilder builder) {
// build query here
}
};
}
}
You expressed some criteria on a business requirement abstraction level and created executable Specifications. So a client might use a Specification as follows:
List customers = customerRepository.findAll(isLongTermCustomer());
You can also combine Specification Example 2.17. Combined Specifications
MonetaryAmount amount = new MonetaryAmount(200.0, Currencies.DOLLAR);
List<Customer> customers = customerRepository.findAll(
where(isLongTermCustomer()).or(hasSalesOfMoreThan(amount)));
As you can see, Specifications offers some glue-code methods to chain and combine Specifications. Thus extending your data access layer is just a matter of creating new Specification implementations and combining them with ones already existing.
And you can Create Complex Specifications, here is an example
public class WorkInProgressSpecification {
public static Specification<WorkInProgress> findByCriteria(final SearchCriteria searchCriteria) {
return new Specification<WorkInProgress>() {
@Override
public Predicate toPredicate(
Root<WorkInProgress> root,
CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<Predicate>();
if (searchCriteria.getView() != null && !searchCriteria.getView().isEmpty()) {
predicates.add(cb.equal(root.get("viewType"), searchCriteria.getView()));
}
if (searchCriteria.getFeature() != null && !searchCriteria.getFeature().isEmpty()) {
predicates.add(cb.equal(root.get("title"), searchCriteria.getFeature()));
}
if (searchCriteria.getEpic() != null && !searchCriteria.getEpic().isEmpty()) {
predicates.add(cb.equal(root.get("epic"), searchCriteria.getEpic()));
}
if (searchCriteria.getPerformingGroup() != null && !searchCriteria.getPerformingGroup().isEmpty()) {
predicates.add(cb.equal(root.get("performingGroup"), searchCriteria.getPerformingGroup()));
}
if (searchCriteria.getPlannedStartDate() != null) {
System.out.println("searchCriteria.getPlannedStartDate():" + searchCriteria.getPlannedStartDate());
predicates.add(cb.greaterThanOrEqualTo(root.<Date>get("plndStartDate"), searchCriteria.getPlannedStartDate()));
}
if (searchCriteria.getPlannedCompletionDate() != null) {
predicates.add(cb.lessThanOrEqualTo(root.<Date>get("plndComplDate"), searchCriteria.getPlannedCompletionDate()));
}
if (searchCriteria.getTeam() != null && !searchCriteria.getTeam().isEmpty()) {
predicates.add(cb.equal(root.get("agileTeam"), searchCriteria.getTeam()));
}
return cb.and(predicates.toArray(new Predicate[] {}));
}
};
}
}
Here is the JPA Respositories docs
Solution 2:
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
}
Solution 3:
From Spring Data JPA 1.10 there is another option for this is Query By Example.
Your repository should implement apart from JpaRepository also the QueryByExampleExecutor interface where you get methods like:
<S extends T> Iterable<S> findAll(Example<S> example)
Then you create the Example to search for like:
Employee e = new Employee();
e.setEmployeeNumber(getEmployeeNumberSomewherFrom());
e.setName(getNameSomewhereFrom());
e.setMarried(getMarriedSomewhereFrom());
e.setProfession(getProfessionSomewhereFrom());
e.setDateOfBirth(getDateOfBirthSomewhereFrom());
and then:
employeeRepository.findAll(Example.of(e));
If some parameters are null they wont be taken in to WHERE clause so you get dynamic queries.
To refine matching of String attributes take a look at ExampleMatcher's
An ExampleMatcher which does a case-insensitive like is for example:
ExampleMatcher matcher = ExampleMatcher.matching().
withMatcher("profession", ExampleMatcher.GenericPropertyMatcher.of(ExampleMatcher.StringMatcher.CONTAINING).ignoreCase());
QBE Examples: https://github.com/spring-projects/spring-data-examples/tree/master/jpa/query-by-example