Dynamic spring data jpa repository query with arbitrary AND clauses
Solution 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
}
Solution 2
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 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
Mr.Chowdary
An Enthusiast Programmer in Java and Big Data Technologies. Cloudera Certified Developer for Apache Hadoop
Updated on July 14, 2022Comments
-
Mr.Chowdary almost 2 years
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 sayEmployeeNumber
,Name
,Married
,Profession
andDateOfBirth
.
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 asnull
fields should be ignored and almost all possible queries need to be developed. In thiscase 31 queries
. what if search fields are6,7,8...
??What is the best approach to implement search option with optional fields ?