How to use projections and specifications with spring data jpa?

Question

I'm not able to use Spring Data JPA projections and specifications together. I have the following setup:

Entity:

@Entity
public class Country {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "NAME", nullable = false)
    private String name;

    @Column(name = "CODE", nullable = false)
    private String code;

    ---getters & setters---

}

Projection Interface:

public interface CountryProjection {
    String getName();
}

Country Specification:

public class CountrySpecification {
    public static Specification<Country> predicateName(final String name) {
        return new Specification<Country>() {
            @Override
            public Predicate toPredicate(Root<Country> eventRoot, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.equal(eventRoot.get(Country_.name), name);
            }
        };
    }
}

Repository:

public interface CountryRepository extends JpaRepository<Country, Long>, JpaSpecificationExecutor<Country> {
    List<CountryProjection> findByName(String name); // works fine
    List<CountryProjection> findAllProjectedBy(); // works fine
    List<CountryProjection> findAllProjectedBy(Specification<Country> specification); //throws Exception as shown below
}

The first two methods findByName and findAllProjectedBy works fine. Whereas the third method findAllProjectedBy(Specification specification) throws the following exception -

Caused by: java.util.NoSuchElementException: null at java.util.ArrayList$Itr.next(ArrayList.java:854) ~[na:1.8.0_102] at java.util.Collections$UnmodifiableCollection$1.next(Collections.java:1042) ~[na:1.8.0_102] at org.springframework.data.jpa.repository.query.CriteriaQueryParameterBinder.bind(CriteriaQueryParameterBinder.java:63) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:100) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:160) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:151) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.invokeBinding(PartTreeJpaQuery.java:218) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.createQuery(PartTreeJpaQuery.java:142) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.doCreateQuery(PartTreeJpaQuery.java:78) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:190) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:118) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482) ~[spring-data-commons-1.12.6.RELEASE.jar:na] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460) ~[spring-data-commons-1.12.6.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) ~[spring-data-commons-1.12.6.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.10.6.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.5.RELEASE.jar:4.3.5.RELEASE] at com.sun.proxy.$Proxy82.findAllProjectedBy(Unknown Source) ~[na:na] at com.mmp.data.jpa.DataJpaApplication.run(DataJpaApplication.java:42) [classes/:na] at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE] ... 11 common frames omitted

How can this be achieved? Any ideas?

Debi

Answer 1

The ability to mix Projections and Specifications are not yet supported. There is a bug tracking this.

Raffinate

Bug is Closed on Apr 20, 2022 – Besant

Answer 2

I found this https://github.com/pramoth/specification-with-projection and it seems to work which does exactly what you're looking for. I've included it in my own project and so far no problems. Big thanks to Pramoth.

Basically you extend JpaSpecificationExecutorWithProjection instead of JpaSpecificationExecutor.

public interface DocumentRepository extends JpaRepository< Country,Long>,JpaSpecificationExecutorWithProjection<Country,Long>

and you get findall() method with projections and specifications

<R> Page<R> findAll(Specification<T> spec, Class<R> projectionClass, Pageable pageable);

Update for database level projections

There is an outdated comment below.

This solution is unfortunate in that it selects everything from DB and than just "map" it to projection.

The last version of this library supports database level projections link to the commit.

Update for the last Spring Boot versions

Until this pull request is not merged, to support the last Spring Boot versions, fixed library sources can be copy pasted directly to the project from here https://github.com/v-ladynev/specification-with-projection-embeded

Scraper

Nevertheless, I think that this would work faster in REST API, because you'll have to send less data in JSON. Am I right? – Bond

@JakubSłowikowski No. And better not use entity classes in scope of REST API. – Kolk

Hi @v.ladynev, thank you so much for adapting to Spring Boot 2.7, it saved my day. I noticed that the methods using EntityGraph are marked as deprecated, and you recommend using annotations instead. I've tried, but can't get it to work. Could you provide an example of usage with Specification + Projections + EntityGraph? I would greatly appreciate your help. – Coumarone

@Coumarone I don't use static entity graphs at all. I use dynamic ones instead https://mcmap.net/q/391804/-how-does-jpa-entitygraph-allow-chosing-the-entity-graph-at-runtime I have to create a custom JpaRepositoryFactory to use specification-with-projection and spring-data-jpa-entity-graph repositories. Still can't use specifications and dynamic entity graphs within one repository. Just don't need it. Probably it is possible. – Kolk

Answer 3

Starting from Spring Data 3.0, mixing projections and specifications is supported. We can use the JpaSpecificationExecutor.findBy method:

Specification<Country> specification = CountrySpecification.predicateName("Austria");
List<CountryProjection> result = countryRepository.findBy(specification, q -> q
        .project("name")                    // query hint (not required)
        .as(CountryProjection .class)       // projection result class
        .all()                              
);

Make sure to check the FetchableFluentQuery class for count, paging, sort and other options.

Stand

the generate SQL still query all the columns – Qadi

This should be the accepted answer, thank you very much for pointing to this simple and elegant solution! – Weaken

The code works but as John mentioned, the query includes all the columns in select clause and that's not the objective for using projections – Dandle

Answer 4

so this issue is still active in spring data github. As @Naso said you can bring another dependency into your project (https://github.com/pramoth/specification-with-projection) Or nothing stops you to make two Entity classes that point to the same table . For example

@Entity
@Table("country")
public class Country {
  String code;
  String name;

}
@Entity
@Table("country")
public class CountryName {

 String name;
}

public interface CountryRepository extends JpaRepository<CountryName, Long>, JpaSpecificationExecutor<Country> {

    List<CountryName> findAllProjectedBy(Specification<Country> specification); //throws Exception as shown below
}

Tenderloin

+1 for pointing out the (often forgotten) possibility to use a separate lightweight Entity as a means of projection – Cherrylchersonese

Issue is Closed on Apr 20, 2022 – Besant

This doesn't work... the specification is still treated as a query parameter... for me at least – Hasa

Answer 5

@esdee: For now, I Created a Custom Repository Implementation where I created a dynamic query where you can create even a native query and map it to a DTO without using projections.

In order to do this you can see this doc:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations

Here is a already example:

Spring Data JPA Custom Repository

Just keep in mind that if you want to make it pageable, in your Custom Repository you have to create also a method to count the rows, that you want to extract in your customFindAll(parameters). The disadgantage is that I rewrote the specifications in native query. But maybe the custom Implmentation can work also with Specification, let me know if it helps.

Regards, C

Cosmo

Answer 6

We gave a try to Pramoth solution (which hasn't moved since 2022). But it does not work with OneToMany associations, as it'll return one entity per tuple.

Finally they added that in Spring Boot 3 (https://github.com/spring-projects/spring-data-jpa/issues/2274)

Since we're still in Spring Boot 2, I just copied the appropriate FetchableFluentQueryBySpecification class and necessary imports (EntityGraphFactory, FluentQuerySupport). Changed jakarta imports for javax ones.

And created a CustomSimpleJpaRepository with the copied method from SimpleJpaRepository :

public <S extends T, R> R findBy(Specification<T> spec, Function<FetchableFluentQuery<S>, R> queryFunction) {

        Assert.notNull(spec, "Specification must not be null!");
        Assert.notNull(queryFunction, "Query function must not be null!");

        Function<Sort, TypedQuery<T>> finder = sort -> {
            return getQuery(spec, getDomainClass(), sort);
        };

        FetchableFluentQuery<R> fluentQuery = new FetchableFluentQueryBySpecification<T, R>(spec, getDomainClass(),
                Sort.unsorted(), null, finder, this::count, this::exists, this.em);

        return queryFunction.apply((FetchableFluentQuery<S>) fluentQuery);
    }

Then enable the Custom class

@Configuration
@EnableJpaRepositories(
    repositoryBaseClass = CustomSimpleJpaRepository.class
)

Finally you can use the findBy method on the repository, with V being your projectionClass reference:

Page<V> entities = repository.findBy(
    specification,
    q -> q.as(projectionClass)
        .page(
            PageRequest
                .of(page, size)
                .withSort(sort))
);

Tobar

Answer 7

There is no solution unless you implement you own repository.

Kangaroo

try to give a detailed information about your solution. add your code ,highlight the keywords – Indira

you can take a look on it thoughts-on-java.org/dto-projections – Kangaroo

Answer 8

Depending on how complex your requirements get, you may end up having to implement a custom repository: https://dzone.com/articles/accessing-the-entitymanager-from-spring-data-jpa

Summarizing the article above, you will need to implement an interface for the custom methods (the interface's name must end with Custom):

public interface ParkrunCourseRepositoryCustom {    
    void refresh(ParkrunCourse parkrunCourse);
}

Then you will need to create a class that implements the interface (the class' name must end with Impl):

import javax.persistence.PersistenceContext;
import javax.persistence.EntityManager;
import com.glenware.springboot.form.ParkrunCourse;
import org.springframework.transaction.annotation.Transactional;
public class ParkrunCourseRepositoryImpl implements ParkrunCourseRepositoryCustom {
    @PersistenceContext
    private EntityManager em;
    @Override
    @Transactional
    public void refresh(ParkrunCourse parkrunCourse) {
        em.refresh(parkrunCourse);
    }
}

Finally, you must implement the interface for the actual repository:

public interface ParkrunCourseRepository extends CrudRepository, ParkrunCourseRepositoryCustom {
}

This will give you full access to the EntityManager, allowing you to implement your queries in whichever way JPA allows.

Brinn

Answer 9

here's is the code snippet i used in my project. (The code is kotlin but can be rewrite with Java with little effort)

    val specification = Specification { root: Root<User>, query: CriteriaQuery<*>, cb: CriteriaBuilder ->
        val themeJoin = root.join<User, Theme>("themes", JoinType.LEFT)
        query.multiselect(root.get<Long>("id").alias("id"),themeJoin.get<String>("title").alias("title"))
        cb.gt(themeJoin.get<Long>("id"),1)
    }

    val builder: CriteriaBuilder = em.criteriaBuilder
    val query = builder.createQuery(Tuple::class.java)
    val root = query.from(User::class.java)
    val predict = specification.toPredicate(root,query,builder)
    query.where(predict)
    return em.createQuery(query).resultList

in the User Entity, there are several OneToMany and ManyToOne properties, since i only query the id of User and the theme title, the jpa only generate single sql join User entity and Theme Entity.

select u1_0.id,t1_0.title from user u1_0 left join theme t1_0 on u1_0.id=t1_0.user_id where t1_0.id>?

Ose

Answer 10

If you use Specification, you can't use in CountryRepository.

CountryRepository cRepository;

cRepository.findAll(Specification<Country> specification);

Uppity

Answer 11

Another way you could solve this is by using the ProxyProjectionFactory. You would have your repository fetch the actual entity and then along the line (maybe in your service layer), map the resultset into the projection type. See below;

public interface CountryRepository extends JpaRepository<Country, Long>, JpaSpecificationExecutor<Country> {  

}

then in your service, you do this;

List<CountryProjection> findAllProjectedBy(Specification<Country> countrySpecification) {
    List<Country> countries = this.countryRepository.findAll(countrySpecification);

    ProxyProjectionFactory pf= new SpelAwareProxyProjectionFactory();
    return countries.stream().map(c->pf.createProjection(CountryProjection.class, c)).collect(Collectors.toList());
}

Hope this helps!

Slippy

This would extract all entity, the purpose of Projection is to avoid this, to reduce the time for extraction of unnecessary fields. I wouldn't use this solution if I need to optimize – Cosmo

@CosminConstantinescu, could you please share a better suggestion? – Slippy