CODEWITHSUNDEEP
javahibernatespring-bootjpa
Ruslan Skaldin
Ruslan Skaldin

Reputation: 1101

JPA Distinct with "where" clause

Is there any way to write JPA function in JPA Repository to make a query with both "distinct" and "where" clause, like this:

SELECT DISTINCT department_id, * FROM companies where company_id='2';

When I write the function with only "distinct" works perfectly

List<Companies> findDistinctByDepartmentId();

But when I add "where" clause it stop working

List<Companies> findDistinctByDepartmentIdAndCompanyIdEquals(Long companyId);

Error logs:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hierarchyRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List com.companystruct.repository.HierarchyRepository.findDistinctByDepartmentIdAndCompanyId(java.lang.Long)! No parameter available for part company SIMPLE_PROPERTY (1): [Is, Equals] NEVER.
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1745)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:576)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:273)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1239)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1166)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:593)
    ... 19 common frames omitted
Caused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List com.companystruct.repository.HierarchyRepository.findDistinctByDepartmentIdAndCompanyId(java.lang.Long)! No parameter available for part company SIMPLE_PROPERTY (1): [Is, Equals] NEVER.
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:82)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:103)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:208)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:566)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:559)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Iterator.forEachRemaining(Iterator.java:116)
    at java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1049)
    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:561)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:551)
    at java.util.Optional.map(Optional.java:215)

Upvotes: 1

Views: 1354

Answers (3)

corroborator
corroborator

Reputation: 321

    Try any:

    List<Companies> findDistinctDepartmentIdByCompanyId(Long companyId); 
OR
@Query("SELECT DISTINCT c FROM Companies c WHERE c.companyId=?1")
    List<Companies> findDistinctByDepartmentIdAndCompanyId(Long companyId);
    OR 

    @Query("SELECT DISTINCT c FROM Companies c WHERE c.companyId=:companyId")
    List<Companies> findDistinctByDepartmentIdAndCompanyId(@Param("companyId") Long companyId);

    OR 
    @Query("SELECT DISTINCT * FROM companies where company_id=?1",nativeQuery = true)
    List<Companies> findDistinctByDepartmentIdAndCompanyIdEquals(Long companyId);

Upvotes: 2

Vladimir
Vladimir

Reputation: 710

List<Companies> findDistinctDepartmentIdByCompanyId(Long companyId);

Upvotes: 1

Abhijeet
Abhijeet

Reputation: 4309

You can write the query instead of using JPA repository classes like this

@Query("SELECT DISTINCT c.department_id FROM companies c where c.company_id=:companyId")
List<Integer> findDistinctByDepartmentIdAndCompanyIdEquals(Long companyId);

Upvotes: 3

Related Questions