java - 如何在 JPA Repository 接口(interface)中编写不同的 SQL 子句

Question

我正在尝试使用 JpaRepository 接口(interface)来构建一个不同的子句。我尝试了几乎所有可能的编写 findDistinctBy... 的组合，但失败了。在许多资源/链接中，问题是通过使用@Query注释解决的，但由于一些限制，我只想使用spring数据。

示例实体类只有一列，我想要不同的值:

@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

存储库接口(interface):

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

错误跟踪:

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

我想使用 spring 数据生成查询:

select distinct id from data

对我的问题进行更多说明:

如果表x中有y列，我的要求是获取y的所有不同值。我如何使用 jpa 数据实现这一点，即扩展 JPARepository 的接口(interface)中我的方法的名称应该是什么，这将导致查询:

select distinct y from x;

理想情况下，该方法应返回 List<datatype of y>或所有具有唯一 y 的实体。

JPA 数据是否可能？

Answer 1

当您在 JPA 方法中编写 ByColumnName 时，它会预期一个 WHERE 子句，例如findByColumnName。在您的场景中，您正在执行 findDistinctById 并且 JPA 正在查找参数 id。由于您没有提供 id 参数，JPA 会引发异常。

您想要实现的是在不提供任何参数的情况下获取特定列的唯一值，而这在 JPA 方法中无法实现。您可以通过 @Query

实现此目的

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