How to find distinct rows with field in list using JPA and Spring?

Question

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 NOT that's fine.

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)

---

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)]

Answer 1

I finally was able to figure out a simple solution without the @Query annotation.

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.

Answer 2

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.

Answer 3

@Query("SELECT DISTINCT name FROM people WHERE name NOT IN (:names)")
List<String> findNonReferencedNames(@Param("names") List<String> names);

Answer 4

@Query("SELECT distinct new com.model.referential.Asset(firefCode,firefDescription) FROM AssetClass ")
List<AssetClass> findDistinctAsset();