Spring Data JPA and Querydsl to fetch subset of columns using bean/constructor projection
Solution 1
Looks like custom repository implementation is the way to go for now until something similar available in spring data.
I have gone through http://static.springsource.org/spring-data/data-jpa/docs/current/reference/html/repositories.html#repositories.custom-implementations
Here is my implementation which works. However it would be good to have this method available directly in Spring-Data-JPA
Step 1: Intermediate interface for shared behavior
public interface CustomQueryDslJpaRepository <T, ID extends Serializable>
extends JpaRepository<T, ID>, QueryDslPredicateExecutor<T> {
/**
* Returns a {@link org.springframework.data.domain.Page} of entities matching the given {@link com.mysema.query.types.Predicate}.
* This also uses provided projections ( can be JavaBean or constructor or anything supported by QueryDSL
* @param constructorExpression this constructor expression will be used for transforming query results
* @param predicate
* @param pageable
* @return
*/
Page<T> findAll(FactoryExpression<T> factoryExpression, Predicate predicate, Pageable pageable);
}
Step 2: Implementation of intermediate interface
public class CustomQueryDslJpaRepositoryImpl<T, ID extends Serializable> extends QueryDslJpaRepository<T, ID>
implements CustomQueryDslJpaRepository<T, ID> {
//All instance variables are available in super, but they are private
private static final EntityPathResolver DEFAULT_ENTITY_PATH_RESOLVER = SimpleEntityPathResolver.INSTANCE;
private final EntityPath<T> path;
private final PathBuilder<T> builder;
private final Querydsl querydsl;
public CustomQueryDslJpaRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager) {
this(entityInformation, entityManager, DEFAULT_ENTITY_PATH_RESOLVER);
}
public CustomQueryDslJpaRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager,
EntityPathResolver resolver) {
super(entityInformation, entityManager);
this.path = resolver.createPath(entityInformation.getJavaType());
this.builder = new PathBuilder<T>(path.getType(), path.getMetadata());
this.querydsl = new Querydsl(entityManager, builder);
}
@Override
public Page<T> findAll(FactoryExpression<T> factoryExpression, Predicate predicate, Pageable pageable) {
JPQLQuery countQuery = createQuery(predicate);
JPQLQuery query = querydsl.applyPagination(pageable, createQuery(predicate));
Long total = countQuery.count();
List<T> content = total > pageable.getOffset() ? query.list(factoryExpression) : Collections.<T> emptyList();
return new PageImpl<T>(content, pageable, total);
}
}
Step 3: Create a custom repository factory to replace the default
public class CustomQueryDslJpaRepositoryFactoryBean<R extends JpaRepository<T, I>, T, I extends Serializable>
extends JpaRepositoryFactoryBean<R, T, I> {
protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
return new CustomQueryDslJpaRepositoryFactory(entityManager);
}
private static class CustomQueryDslJpaRepositoryFactory<T, I extends Serializable> extends JpaRepositoryFactory {
private EntityManager entityManager;
public CustomQueryDslJpaRepositoryFactory(EntityManager entityManager) {
super(entityManager);
this.entityManager = entityManager;
}
protected Object getTargetRepository(RepositoryMetadata metadata) {
return new CustomQueryDslJpaRepositoryImpl<>(getEntityInformation(metadata.getDomainType()), entityManager);
}
protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
return CustomQueryDslJpaRepository.class;
}
}
}
Step 4: Use the custom repository factory
Using annotation
@EnableJpaRepositories(repositoryFactoryBeanClass=CustomQueryDslJpaRepositoryFactoryBean.class)
OR using XML
<repositories base-package="com.acme.repository" factory-class="com.acme.CustomQueryDslJpaRepositoryFactoryBean" />
Note: Don't place custom repository interface and implementation in the same directory as base-package. If you are placing then exclude them from scanning otherwise spring will try to create beans for them
Sample usage
public interface UserDemoRepository extends CustomQueryDslJpaRepository<UserDemo, Long>{
}
public class UserDemoService {
@Inject
UserDemoRepository userDemoRepository;
public Page<User> findAll(UserSearchCriteria userSearchCriteria, Pageable pageable) {
QUserDemo user = QUserDemo.userDemo;
return userDemoRepository.findAll(Projections.bean(UserDemo.class, user.id, user.username), UserPredicate.defaultUserSearch(userSearchCriteria), pageable);
}
}
Solution 2
For more recent versions of Spring Data, I couldn't get the accepted answer to work without hitting issues, but found that going down the route from the Spring Data docs, does work by revising that answer as follows:
1. The repository interface
@NoRepositoryBean
public interface QueryDslPredicateAndProjectionExecutor<T, ID extends Serializable>
extends JpaRepository<T, ID>, QueryDslPredicateExecutor<T> {
<PROJ> Page<PROJ> customFindWithProjection(FactoryExpression<PROJ> factoryExpression, Predicate predicate, Pageable pageable);
}
2. The repository implementation
public class QueryDslJpaEnhancedRepositoryImpl<T, ID extends Serializable> extends QueryDslJpaRepository<T, ID>
implements QueryDslPredicateAndProjectionExecutor<T, ID> {
//All instance variables are available in super, but they are private
private static final EntityPathResolver DEFAULT_ENTITY_PATH_RESOLVER = SimpleEntityPathResolver.INSTANCE;
private final EntityPath<T> path;
private final PathBuilder<T> builder;
private final Querydsl querydsl;
public QueryDslJpaEnhancedRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager) {
this(entityInformation, entityManager, DEFAULT_ENTITY_PATH_RESOLVER);
}
public QueryDslJpaEnhancedRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager,
EntityPathResolver resolver) {
super(entityInformation, entityManager, resolver);
this.path = resolver.createPath(entityInformation.getJavaType());
this.builder = new PathBuilder<T>(path.getType(), path.getMetadata());
this.querydsl = new Querydsl(entityManager, builder);
}
@Override
public <PROJ> Page<PROJ> customFindWithProjection(FactoryExpression<PROJ> factoryExpression, Predicate predicate, Pageable pageable) {
JPQLQuery countQuery = createQuery(predicate);
JPQLQuery query = querydsl.applyPagination(pageable, createQuery(predicate));
Long total = countQuery.count();
List<PROJ> content = total > pageable.getOffset() ? query.list(factoryExpression) : Collections.<PROJ>emptyList();
return new PageImpl<PROJ>(content, pageable, total);
}
}
3. Setting the default repository implementation
@EnableJpaRepositories(
repositoryBaseClass=QueryDslJpaEnhancedRepositoryImpl.class,
basePackageClasses=SomeRepository.class)
Solution 3
For the current versions of Spring Data (1.11.1) and QueryDSL (4), you have to change the customFindWithProjection method implementation like this:
@Override
public <PROJ> Page<PROJ> customFindWithProjection(FactoryExpression<PROJ> factoryExpression, Predicate predicate, Pageable pageable) {
final JPQLQuery<?> countQuery = createCountQuery(predicate);
JPQLQuery<PROJ> query = querydsl.applyPagination(pageable, createQuery(predicate).select(factoryExpression));
long total = countQuery.fetchCount();
List<PROJ> content = pageable == null || total > pageable.getOffset() ? query.fetch() : Collections.<PROJ> emptyList();
return new PageImpl<PROJ>(content, pageable, total);
}
The rest of the code remains the same.
Solution 4
As a (albeit very ugly and inefficient) workaround, I simply retrieved a regular Page
containing the entities from my repository and manually mapped them to a projection in the controller like so:
@GetMapping(value = "/columns")
public Page<ColumnProjection> getColumns(@QuerydslPredicate(root = Column.class) final Predicate predicate,
final Pageable pageable) {
Page<Column> filteredColumns = columnRepository.findAll(predicate, pageable);
List<ColumnProjection> filteredColumnProjections = new ArrayList<>();
filteredColumns.forEach(c -> filteredColumnProjections.add(new ColumnProjectionImpl(c)));
return new PageImpl<>(filteredColumnProjections, pageable, filteredColumnProjections.size());
}
Where ColumnProjectionImpl
is a class implementing my ColumnProjection
interface.
This was the easiest solution I could come up with while not having to adapt my existing ColumnRepository.
Solution 5
I've just run into the same issue myself. In short - we have to specify custom repository factory bean, that tell to use our custom repo as another "fragment". So i overrided factory.getRepositoryFragments to include a custom projection predicate implementation(IMHO it resolves the issue in the question No property found for type… custom Spring Data repository).
updated code, based on all previous answers :
1.QuerydslPredicateProjectionRepositoryFactory
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactory;
import org.springframework.data.querydsl.EntityPathResolver;
import org.springframework.data.querydsl.SimpleEntityPathResolver;
import org.springframework.data.repository.core.RepositoryMetadata;
import org.springframework.data.repository.core.support.RepositoryComposition;
import org.springframework.data.repository.core.support.RepositoryFragment;
import javax.persistence.EntityManager;
import java.io.Serializable;
import static org.springframework.data.querydsl.QuerydslUtils.QUERY_DSL_PRESENT;
public class QuerydslPredicateProjectionRepositoryFactory extends JpaRepositoryFactory {
private final EntityManager entityManager;
private EntityPathResolver entityPathResolver;
public QuerydslPredicateProjectionRepositoryFactory(EntityManager entityManager) {
super(entityManager);
this.entityManager = entityManager;
this.entityPathResolver = SimpleEntityPathResolver.INSTANCE;
}
@Override
protected RepositoryComposition.RepositoryFragments getRepositoryFragments(RepositoryMetadata metadata) {
RepositoryComposition.RepositoryFragments fragments = super.getRepositoryFragments(metadata);
boolean isQueryDslRepository = QUERY_DSL_PRESENT
&& QuerydslPredicateProjectionRepository.class.isAssignableFrom(metadata.getRepositoryInterface());
if (isQueryDslRepository) {
JpaEntityInformation<?, Serializable> entityInformation = getEntityInformation(metadata.getDomainType());
Object querydslFragment = getTargetRepositoryViaReflection(QuerydslPredicateProjectionRepositoryImpl.class, entityInformation,
entityManager, entityPathResolver, null);
fragments = fragments.append(RepositoryFragment.implemented(querydslFragment));
}
return fragments;
}
}
2.QuerydslPredicateProjectionRepositoryFactoryBean
import java.io.Serializable;
import javax.persistence.EntityManager;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.core.support.RepositoryFactorySupport;
public class QuerydslPredicateProjectionRepositoryFactoryBean<T extends Repository<S, ID>, S, ID extends Serializable> extends JpaRepositoryFactoryBean<T, S, ID> {
public QuerydslPredicateProjectionRepositoryFactoryBean(Class<? extends T> repositoryInterface) {
super(repositoryInterface);
}
protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
return new QuerydslPredicateProjectionRepositoryFactory(entityManager);
}
}
3.QuerydslPredicateProjectionRepository here we add new methods, that use projections etc...
import com.querydsl.core.types.FactoryExpression;
import com.querydsl.core.types.Predicate;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import java.util.List;
public interface QuerydslPredicateProjectionRepository<T> {
<Projection> Page<Projection> findAll(Predicate predicate, Pageable pageable, FactoryExpression<Projection> factoryExpression);
<Projection> List<Projection> findAll(Predicate predicate, Sort sort, FactoryExpression<Projection> factoryExpression);
<Projection> List<Projection> findAll(Predicate predicate, FactoryExpression<Projection> factoryExpression);
}
4.QuerydslPredicateProjectionRepositoryImpl here we implement the repository interface methods
import com.querydsl.core.QueryResults;
import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.FactoryExpression;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.jpa.JPQLQuery;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.support.CrudMethodMetadata;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.Querydsl;
import org.springframework.data.jpa.repository.support.QuerydslJpaPredicateExecutor;
import org.springframework.data.querydsl.EntityPathResolver;
import org.springframework.data.querydsl.SimpleEntityPathResolver;
import javax.persistence.EntityManager;
import java.util.List;
public class QuerydslPredicateProjectionRepositoryImpl<T> extends QuerydslJpaPredicateExecutor<T> implements QuerydslPredicateProjectionRepository<T> {
private static final EntityPathResolver DEFAULT_ENTITY_PATH_RESOLVER = SimpleEntityPathResolver.INSTANCE;
private final Querydsl querydsl;
public QuerydslPredicateProjectionRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
this(entityInformation, entityManager, DEFAULT_ENTITY_PATH_RESOLVER);
}
public QuerydslPredicateProjectionRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager, EntityPathResolver resolver) {
super(entityInformation, entityManager, resolver, null);
EntityPath<T> path = resolver.createPath(entityInformation.getJavaType());
PathBuilder<T> builder = new PathBuilder<T>(path.getType(), path.getMetadata());
this.querydsl = new Querydsl(entityManager, builder);
}
public QuerydslPredicateProjectionRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager, EntityPathResolver resolver, CrudMethodMetadata metadata) {
super(entityInformation, entityManager, resolver, metadata);
EntityPath<T> path = resolver.createPath(entityInformation.getJavaType());
PathBuilder<T> builder = new PathBuilder<T>(path.getType(), path.getMetadata());
this.querydsl = new Querydsl(entityManager, builder);
}
@Override
public <Projection> List<Projection> findAll(Predicate predicate, FactoryExpression<Projection> factoryExpression) {
return createQuery(predicate).select(factoryExpression).fetch();
}
@Override
public <Projection> List<Projection> findAll(Predicate predicate, Sort sort, FactoryExpression<Projection> factoryExpression) {
JPQLQuery<Projection> query = createQuery(predicate).select(factoryExpression);
querydsl.applySorting(sort, query);
return query.fetch();
}
@Override
public <Projection> Page<Projection> findAll(Predicate predicate, Pageable pageable, FactoryExpression<Projection> factoryExpression) {
JPQLQuery<Projection> query = createQuery(predicate).select(factoryExpression);
querydsl.applyPagination(pageable, query);
querydsl.applySorting(pageable.getSort(), query);
QueryResults<Projection> queryResults = query.fetchResults();
return new PageImpl<>(queryResults.getResults(), pageable, queryResults.getTotal());
}
}
5.Example entity
@Entity
public class Example extends Serializable{
private static final long serialVersionUID = 1L;
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
protected Long id;
@Column
private String name;
@Column
private String surname;
@Column
private Integer year;
public Example() {
}
public Long getId() {return id;}
public void setId(Long id) {this.id = id;}
public String getName() {return name;}
public void setName(String name) {this.name = name;}
public String getSurname() {return surname;}
public void setSurname(String surname) {this.surname= surname;}
public Integer getYear() {return year;}
public void setSurname(Integer year) {this.year= year;}
}
6.Example repository
@Repository
public interface ExampleRepository extends JpaRepository<Example, Long>, QuerydslPredicateProjectionRepository<Example> { }
7.Example usage configuration :
@EnableJpaRepositories(repositoryFactoryBeanClass = QuerydslPredicateProjectionRepositoryFactoryBean.class)
typical usage :
//get list of entities only with year field value set - memory consuming
List<Example> years = repository.findAll(predicate, Projections.fields(Example.class, QExample.example.year));
//get list of tuples - looks nicer - less memory consuming
List<Tuple> years = repository.findAll(predicate, Projections.tuple(QExample.example.year));
//get list of integers - nice :)
List<Integer> years = repository.findAll(predicate, Projections.constructor(Integer.class, QExample.example.year));
Murali
Updated on July 20, 2020Comments
-
Murali almost 4 years
I have an entity class as below:
@Entity public class UserDemo implements Serializable { @Id private Long id; private String username; private String createdBy; @Version private int version; /*** * * Getters and setters */ }
Using Spring Data JPA and Querydsl how do I fetch a page of UserDemo with onlyid
andusername
properties populated? I need to use paging as well as searching. In short I would like to achieve the same result asPage<UserDemo> findAll(Predicate predicate, Pageable pageable);
but with limited field of UserDemo populated.