How can I use Spring's pagination (using Pageable) while writing a dynamic query using QueryDSL?
Step 1: Annotate the entity class with
@QueryEntity
@Entity
@QueryEntity
public class Country {}
This seems to have been addressed already since the question shows Q
classes.
Step 2: Have the repository interface extend
QueryDslPredicateExecutor
public interface CountryRepository
extends PagingAndSortingRepository<Country, Long>
, QueryDslPredicateExecutor<Country> {
}
Step 3: Invoke the
Page<T> findAll(Predicate query, Pageable page)
method provided byQueryDslPredicateExecutor
public Page<Country> getCountries(String country, Optional<String> status, Pageable page) {
QCountry root = QCountry.country;
BooleanExpression query = root.codeLeft.country.equalsIgnoreCase(country);
query = query.and(root.codeRight.country.equalsIgnoreCase(country));
if (status.isPresent()) {
query = query.and(root.active.status.equalsIgnoreCase(status));
}
return countryRepository.findAll(query, page);
}
Righto
Updated on June 18, 2022Comments
-
Righto almost 2 years
I am trying to use pagination with QueryDSL - using the com.mysema.querydsl package.
All my Querydsl query types look like this -
@Generated("com.mysema.query.codegen.EntitySerializer") public class QCountry extends EntityPathBase<Country> {...}
Currently, my repository implementation class looks something like this -
@Override public Page<Country> findPaginatedCountries(String country, Optional<String> status, Pageable pageable) { QCountry qCountry= QCountry.someObject; QActiveCountry qActiveCountry = QActiveCountry.activeCountry; JPAQuery jpaQuery = new JPAQuery(entityManager); QueryBase queryBase = jpaQuery.from(qCountry).innerJoin(qActiveCountry).fetch() .where(qCountry.codeLeft.country.upper().eq(country.toUpperCase())) .where(qCountry.codeRight.country.upper().eq(country.toUpperCase())); if(status.isPresent()){ queryBase = queryBase.where(qActiveCountry.id(qCountry.active.id)) .where(qActiveCountry.status.upper().eq(status.get().toUpperCase())); } .......}
Now, I want this dynamic query to return a paginated response. I want to use Spring's pagination to do that and not manually set offset, size etc.
I know I can use QueryDslRepositorySupport class - as implemented here - https://github.com/keke77/spring-data-jpa-sample/blob/master/spring-data-jpa/src/main/java/com/gmind7/bakery/employee/EmployeeRepositoryImpl.java
Sample code from the above link -
@Override public Page<Employees> QFindByOfficeCode(long officeCode, Pageable pageable) { //JPAQuery query = new JPAQuery(em); JPQLQuery query = from(QEmployees.employees).where(QEmployees.employees.officeCode.eq(officeCode)); query = super.getQuerydsl().applyPagination(pageable, query); SearchResults<Employees> entitys = query.listResults(QEmployees.employees); return new PageImpl<Employees>(entitys.getResults(), pageable, entitys.getTotal()); }
However, to do that -
- I need to pass JPQLQuery object to the applyPagination method. How can I do that without changing my code (Ofcourse, the repository class will extend QueryDslRepositorySupport class). Currently, I am using JPAQuery as you can see.
OR
- I probably need to change my QueryDSL types by having them extend EntityPath instead of EntityPathBase so that I can use JPQLQuery.from() to generate the query and then use the applyPagination method, which requires a JPQLQuery object. However, my Q classes are extending EntityPathBase class instead. Should I be use com.querydsl package instead of com.mysemsa.querydsl package to generate query types?
OR
- Other option is to use the following - http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/querydsl/QueryDslPredicateExecutor.html#findAll-com.querydsl.core.types.Predicate-org.springframework.data.domain.Pageable-
Code snippet below -
Page<T> page = QueryDslPredicateExecutor.findAll(org.springframework.data.querydsl.Predicate predicate, Pageable pageable)
However, I am making joins between two tables and then filtering results with a where clause (as you can see above in my code). How can I pass a predicate object in the findAll method above? Not sure how to include a join in it.
Please let me know if the problem is not clear, I can add more details.
EDIT: There is a many to one relationship between Country and ActiveCountry. Country class has an ActiveCountry reference. And we have to do a join between both ids. Is is possible that Country can have null ActiveCountry. Therefore, we want an inner join - only non null values for active country
@ManyToOne @JoinColumn(name="id") ActiveCountry active;
-
Righto about 7 yearsThanks for your reply. The issue however is to be able to join two tables while building a Predicate or BooleanExpression. Can you edit your response on how to do that as well? Thanks
-
manish about 7 years@Righto, please add the shortest possible code for the entity classes to your question. It is difficult to form a query without understanding the relationship between classes.
-
Righto about 7 yearsSo basically, there is a many to one relationship between Country and ActiveCountry.
-
manish about 7 years@Righto, please see the updated answer based on your edit for the question. The join is automatically managed by JPA so you don't need to think of it as a SQL join when writing the DSL query.
-
Righto about 7 yearsBut I do want to ignore null fields. It is possible that some fields are null. I would only want non-null fields in the join
-
Righto about 7 yearsI do take it constructively. I have clearly stated "innerjoin" in my code. I would appreciate people here who have the patience to have a continued dialogue instead of getting angry and threatening like you just did. Anyway, thanks for your help.
-
wmakley almost 4 yearsWhen I follow this example, Hibernate generates cross joins for no reason. I need to control the entire query...