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 lt People  Long gt  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 lt String gt  findNonReferencedNames List lt String gt  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

User · Answer

Can you not use like this    Query  SELECT DISTINCT name FROM people p  nolock  WHERE p name NOT IN   myparam    List lt String gt  findNonReferencedNames  Param  myparam  List lt String gt  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

User · Answer

Query  SELECT distinct new com model referential Asset firefCode firefDescription  FROM AssetClass    List lt AssetClass gt  findDistinctAsset

User · Answer

Have you tried rewording your query like this    Query  SELECT DISTINCT p name FROM People p WHERE p name NOT IN  1   List lt String gt  findNonReferencedNames List lt String gt  names     Note  I m assuming your entity class is named People  and not people

User · Answer

I finally was able to figure out a simple solution without the  Query annotation   List lt People gt  findDistinctByNameNotIn List lt String gt  names     Of course  I got the people object instead of only Strings  I can then do the change in java

User · Answer

Query  SELECT DISTINCT name FROM people WHERE name NOT IN   names    List lt String gt  findNonReferencedNames  Param  names   List lt String gt  names

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

Examples related to java

Examples related to spring

Examples related to hibernate

Examples related to jpa

Examples related to java-8