title: How to write distinct SQL clause in JPA Repository interface
body: <p>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.</p>
  
  <p>Sample Entity class with just one column for which i want distinct values:</p>
  
  <pre><code>@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
  
  </code></pre>
  
  <p>Repository interface:</p>
  
  <pre><code>@Repository
  public interface DataRepository extends JpaRepository&lt;Data, Integer&gt; 
  {
      public List&lt;Integer&gt; findDistinctById();
  }
  </code></pre>
  
  <p>Error Trace:</p>
  
  <pre><code>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.&lt;init&gt;(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.&lt;init&gt;(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.&lt;init&gt;(PartTreeJpaQuery.java:97) ~[spring-data-jpa-2.2.0.RELEASE.jar:2.2.0.RELEASE]
      ... 54 common frames omitted
  
  
  </code></pre>
  
  <p>Query i want to be generated using spring data:</p>
  
  <pre><code>select distinct id from data
  </code></pre>
  
  <p><strong>More clarification on my question :</strong> </p>
  
  <p>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:</p>
  
  <pre><code>select distinct y from x;
  </code></pre>
  
  <p>the method should ideally return <code>List&lt;datatype of y&gt;</code> or all the entity having unique y.</p>
  
  <p>Is it even possible with JPA data?</p>