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

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

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.

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.

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

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

I think this should work without using a native query:

List<Name> findDistinctByNameNotIn(List<String> names);

interface Name{
    String getName();
}

and to get the Strings use:

peopleRepository.findDistinctByNameNotIn().stream().filter(Objects::nonNu
ll).map(PeopleRepository.Name::getName).collect(Collectors.toList())

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 nolock in server is a good practice, you can ignore nolock if a local db is used.

Seems like your db name is not being mapped correctly (after you've updated your question)

you can create another bean for getting the data by creating variables for each column like below.

@Getter
@Setter
@Builder
@ToString
public static class NameOnlyBean {
    //you have to follow camelCase here when creating variables.
    private String name;
}

and get a list of data by using created bean like this by using your JPA query.

List<NameOnlyBean> yourJPAQuery(List<String> names);