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?

Answer 1

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

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);

Answer 3

@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

Answer 4

There is no solution unless you implement you own repository.

Answer 5

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
}

Answer 6

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.

Answer 7

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

CountryRepository cRepository;

cRepository.findAll(Specification<Country> specification);

Answer 8

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!