CODEWITHSUNDEEP
javahibernatespring-data-jpa
Anand
Anand

Reputation: 391

How to write distinct SQL clause in JPA Repository interface

I am trying to use JpaRepository interface to build up a distinct clause. I tried almost all possible combinations of writing findDistinctBy... but failed. In many resources/links problem is solved by using @Query annotation, but due to some limitations i want to use spring data only.

Sample Entity class with just one column for which i want distinct values:

@Entity
@Table(name="data")
public class Data implements Serializable 
{

    private static final long serialVersionUID = -6967098988952450546L;

    @Id
    @Column(name="id")
    private Integer id;

    // constructor, getters and setters

Repository interface:

@Repository
public interface DataRepository extends JpaRepository<Data, Integer> 
{
    public List<Integer> findDistinctById();
}

Error Trace:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List com.example.demo.repository.DataRepository.findDistinctById()! Method public abstract java.util.List com.example.demo.repository.DataRepository.findDistinctById() expects at least 1 arguments but only found 0. This leaves an operator of type SIMPLE_PROPERTY for property id unbound.
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1803) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:636) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    ... 18 common frames omitted
Caused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List com.example.demo.repository.DataRepository.findDistinctById()! Method public abstract java.util.List com.example.demo.repository.DataRepository.findDistinctById() expects at least 1 arguments but only found 0. This leaves an operator of type SIMPLE_PROPERTY for property id unbound.
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:103) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:106) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:211) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:574) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:567) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[na:1.8.0_221]
    at java.util.Iterator.forEachRemaining(Iterator.java:116) ~[na:1.8.0_221]
    at java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1051) ~[na:1.8.0_221]
    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:1.8.0_221]
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:482) ~[na:1.8.0_221]
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472) ~[na:1.8.0_221]
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708) ~[na:1.8.0_221]
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:1.8.0_221]
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499) ~[na:1.8.0_221]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:569) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:559) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at java.util.Optional.map(Optional.java:215) ~[na:1.8.0_221]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:559) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:332) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:297) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:212) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.util.Lazy.get(Lazy.java:94) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:300) ~[spring-data-commons-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:121) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1862) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1799) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    ... 28 common frames omitted
Caused by: java.lang.IllegalStateException: Method public abstract java.util.List com.example.demo.repository.DataRepository.findDistinctById() expects at least 1 arguments but only found 0. This leaves an operator of type SIMPLE_PROPERTY for property id unbound.
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.throwExceptionOnArgumentMismatch(PartTreeJpaQuery.java:168) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.validate(PartTreeJpaQuery.java:154) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:97) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
    ... 54 common frames omitted

Query i want to be generated using spring data:

select distinct id from data

More clarification on my question :

If there a column y in table x and my requirement is to get all the distinct values of y. How can i achieve that using jpa data i.e, what should be the name of my method in the interface extending JPARepository which will result in query:

select distinct y from x;

the method should ideally return List<datatype of y> or all the entity having unique y.

Is it even possible with JPA data?

Upvotes: 4

Views: 18583

Answers (5)

Mert Demirok
Mert Demirok

Reputation: 11

I had the same question and could not find a solution. Finally I decided to implement @Query annotation as follows;

String FIND_DISTINCT_IDS = "SELECT DISTINCT <column_name> FROM <table_name>";

@Query(value = FIND_DISTINCT_IDS, nativeQuery = true)
List<Integer> findDistinctById();

Upvotes: 0

Hafiz Hamza
Hafiz Hamza

Reputation: 309

When you write ByColumnName in your JPA method, it anticipates a WHERE clause e.g. findByColumnName. In your scenario, you are doing findDistinctById and JPA is looking for a parameter id. Since you are not providing id parameter JPA is throwing the exception.

What you are trying to achieve is to fetch unique values of a specific column without giving any parameter and this is not achievable in JPA method. You can achieve this through @Query

@Query("SELECT DISTINCT d.id FROM Data d")
public List<Integer> findDistinctIds();

Upvotes: 5

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

I'm surely late, but as you might already know, there is no way to achieve that using built in implementation that uses reflection to create your query from the method name.

Just for the record (I know you didn't want that but for the sake of completeness), this works:

@Query("SELECT DISTINCT d.id FROM Data d")
public List<Integer> findDistinctIds();

Upvotes: 1

Willem
Willem

Reputation: 1060

Change 

@Repository
public interface DataRepository extends JpaRepository<Data, Integer> 
{
    public List<Integer> findDistinctById();
}

to:

@Repository
public interface DataRepository extends JpaRepository<Data, Integer> 
{
    public List<Integer> findDistinctById(int id);
}

Upvotes: 0

user1333371
user1333371

Reputation: 618

Your entity class uses the @Id annotation on the field id. This marks the field as a primary key which guarantees uniqueness. There's no need to query for distinct ids.

That aside, findDistinctById would generate a query like

select all <data_records> where id = ?

This is why the error includes expects at least 1 arguments but only found 0. findDistinctById is expecting an integer parameter.

Perhaps the example here has just been accidentally overly simplified. If that's the case I would still suggest that almost all places where select distinct seems useful is a code smell. The from and where clauses can usually be rewritten to avoid pulling duplicate rows from the database

Upvotes: 1

Related Questions