Java 如何使用 JPA 和 Spring 在列表中查找具有字段的不同行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32079084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find distinct rows with field in list using JPA and Spring?
提问by Paul Fournel
I am using Spring to connect to the db. I have an interface extending CrudRepository<People, Long>Here is the query I want execute on the db: SELECT DISTINCT name FROM people WHERE name NOT IN UserInputSet. I would prefer to do it without any sql annotation, so if it is possible without the NOTthat's fine.
我正在使用 Spring 连接到数据库。我有一个接口扩展CrudRepository<People, Long>这是我想在数据库上执行的查询：SELECT DISTINCT name FROM people WHERE name NOT IN UserInputSet。我更愿意在没有任何 sql 注释的情况下执行此操作，因此如果可能没有 sql 注释也可以NOT。
Is there a way to do it? I looked at the spring doc, but I cannot find anything (http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation)
有没有办法做到这一点？我查看了 spring 文档，但找不到任何内容（http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation）
This is what I tired but it is not working.
这就是我累但它不起作用。
@Query("SELECT DISTINCT name FROM people WHERE name NOT IN (?1)")
List<String> findNonReferencedNames(List<String> names);
this is the exception I get:
这是我得到的例外：
Error creating bean with name 'peopleRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List de.test.tasks.persistence.PeopleRepository.findNonReferencedNames(java.util.List)!
and
和
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: people is not mapped [SELECT name FROM people WHERE name NOT IN (?1)]
采纳答案by Paul Fournel
I finally was able to figure out a simple solution without the @Queryannotation.
我终于能够找到一个没有@Query注释的简单解决方案。
List<People> findDistinctByNameNotIn(List<String> names);
Of course, I got the people object instead of only Strings. I can then do the change in java.
当然，我得到了人对象，而不仅仅是字符串。然后我可以在java中进行更改。
回答by ha9u63ar
Can you not use like this?
不能这样用吗？
@Query("SELECT DISTINCT name FROM people p (nolock) WHERE p.name NOT IN (:myparam)")
List<String> findNonReferencedNames(@Param("myparam")List<String> names);
P.S. I write queries in SQL Server 2012 a lot and using nolockin server is a good practice, you can ignore nolockif a local db is used.
PS 我在 SQL Server 2012 中写了很多查询，nolock在服务器中使用是一个很好的做法，nolock如果使用本地数据库，您可以忽略。
Seems like your db name is not being mapped correctly (after you've updated your question)
似乎您的数据库名称未正确映射（在您更新问题后）
回答by pioto
Have you tried rewording your query like this?
你有没有试过像这样改写你的查询？
@Query("SELECT DISTINCT p.name FROM People p WHERE p.name NOT IN ?1")
List<String> findNonReferencedNames(List<String> names);
Note, I'm assuming your entity class is named People, and not people.
请注意，我假设您的实体类名为People，而不是people。
回答by aviv zvi
@Query("SELECT DISTINCT name FROM people WHERE name NOT IN (:names)")
List<String> findNonReferencedNames(@Param("names") List<String> names);
回答by user11949964
@Query("SELECT distinct new com.model.referential.Asset(firefCode,firefDescription) FROM AssetClass ")
List<AssetClass> findDistinctAsset();
