Spring Data JPA中用于@ManyToMany关联的查询方法

Question

我有两个实体，User 和 Conversation，它们之间有一个@ManyToMany的关系:

@Entity
@Table(name="USER")
public class User {
    ...
    ...
    @ManyToMany
    @JoinTable(name="USER_CONVERSATION",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "conversation_id"))
    Set<Conversation> conversations = new HashSet<>();
    ...
    ...
}

@Entity
@Table(name="CONVERSATION")
public class Conversation {
    ...
    ...
    @ManyToMany(mappedBy = "conversations", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    Set<User> users = new HashSet<>();
    ...
    ...
}

我希望能够接收包含指定用户列表的所有对话。我尝试了下面这样的代码：

public interface ConversationRepository extends JpaRepository<Conversation, Long> {

    List<Conversation> findAllConversationsByUsers(List<User> users);

但是我收到了一个带有错误信息的错误提示：

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'conversationRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List pl.teamvi.project.repositories.ConversationRepository.findAllConversationsByUsers(java.util.List)! Operator SIMPLE_PROPERTY on users requires a scalar argument, found interface java.util.List in method public abstract java.util.List pl.teamvi.project.repositories.ConversationRepository.findAllConversationsByUsers(java.util.List).
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:207) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1114) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.data.repository.support.Repositories.cacheRepositoryFactory(Repositories.java:99) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.support.Repositories.populateRepositoryFactoryInformation(Repositories.java:92) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.support.Repositories.<init>(Repositories.java:85) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.rest.webmvc.config.RepositoryRestMvcConfiguration.repositories(RepositoryRestMvcConfiguration.java:212) ~[spring-data-rest-webmvc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.data.rest.webmvc.config.RepositoryRestMvcConfiguration$$EnhancerBySpringCGLIB$$f3b0aabb.CGLIB$repositories$51(<generated>) ~[spring-data-rest-webmvc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.data.rest.webmvc.config.RepositoryRestMvcConfiguration$$EnhancerBySpringCGLIB$$f3b0aabb$$FastClassBySpringCGLIB$$4b18e8f9.invoke(<generated>) ~[spring-data-rest-webmvc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244) ~[spring-core-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:363) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.data.rest.webmvc.config.RepositoryRestMvcConfiguration$$EnhancerBySpringCGLIB$$f3b0aabb.repositories(<generated>) ~[spring-data-rest-webmvc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_201]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_201]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_201]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_201]
    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    ... 142 common frames omitted
Caused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List pl.teamvi.project.repositories.ConversationRepository.findAllConversationsByUsers(java.util.List)! Operator SIMPLE_PROPERTY on users requires a scalar argument, found interface java.util.List in method public abstract java.util.List pl.teamvi.project.repositories.ConversationRepository.findAllConversationsByUsers(java.util.List).
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:102) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:106) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:211) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:574) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:567) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[na:1.8.0_201]
    at java.util.Iterator.forEachRemaining(Iterator.java:116) ~[na:1.8.0_201]
    at java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1049) ~[na:1.8.0_201]
    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:1.8.0_201]
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481) ~[na:1.8.0_201]
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471) ~[na:1.8.0_201]
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708) ~[na:1.8.0_201]
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:1.8.0_201]
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499) ~[na:1.8.0_201]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:569) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:559) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at java.util.Optional.map(Optional.java:215) ~[na:1.8.0_201]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:559) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:332) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:297) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:212) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.util.Lazy.get(Lazy.java:94) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:300) ~[spring-data-commons-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:121) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    ... 163 common frames omitted
Caused by: java.lang.IllegalStateException: Operator SIMPLE_PROPERTY on users requires a scalar argument, found interface java.util.List in method public abstract java.util.List pl.teamvi.project.repositories.ConversationRepository.findAllConversationsByUsers(java.util.List).
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.throwExceptionOnArgumentMismatch(PartTreeJpaQuery.java:177) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.validate(PartTreeJpaQuery.java:153) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:96) ~[spring-data-jpa-2.2.3.RELEASE.jar:2.2.3.RELEASE]
    ... 189 common frames omitted 

有没有不使用 @Query 注解来编写此查询的方法？

如果我将方法更改为以下内容，为什么会出现问题：

List<Conversation> findAllConversationsByUsers(User users);

我没有任何错误？Users是一个Set，不是单个元素，那么这个查询在做什么？

- M. Dudek

---

这取决于查询的目的是什么：获取所有至少有一个给定用户的对话，还是获取具有所有给定用户的对话，或者获取恰好具有给定用户（不多不少）的对话。但无论如何，您不应该害怕使用查询。在大多数情况下，这是正确的做法。 - JB Nizet

我需要包含给定用户的对话。 - M. Dudek

然后，您需要编写一个带有子查询的查询。它应该找到所有具有与属于给定列表的对话的用户数相等的总用户数的对话。 - JB Nizet

你能给一些关于创建查询的建议吗？我还遇到了问题。 - M. Dudek

这个应该能行：select c from Conversation c where size(c.users) = (select count(u2.id) from Conversation c2 join c.users u where c2.id = c.id and u2 in :users) - JB Nizet

Answer 1

我遇到了同样的问题，并通过在方法结尾处添加"In"后缀来解决它。例如：

List<Conversation> findAllConversationsByUsersIn(List<User> users);

- Felipe Desiderati

---

好的，如果我想要一个精确匹配的情况怎么办？我是必须编写查询还是也有一些存储库关键字可用于此目的？ - monsIgnore

@monslgnore 精确匹配列表中的所有值？还是单个值？ - Felipe Desiderati

仅当实体列表属性中包含列表参数中包含的所有对象时，才返回精确匹配的列表。 - monsIgnore

@monsIgnore 我不知道可以使用的任何关键字。如果列表具有固定大小且很短，我会使用“item1 = ?1 and ...”，因为它很简单。如果列表具有可变大小，则我会使用Specification Api。https://dimitr.im/writing-dynamic-queries-with-spring-data-jpa - Felipe Desiderati

Answer 2

你可以在方法中添加OrderByUsers并删除参数，而不是做得那么复杂。因此，你应该有类似于List<Conversation> findAllByOrderByUsers();的东西，以获得你想要的结果。 另外请注意，By是用来替换Conversations的

- Big Zed