How to Find Distinct Rows With Field in List Using JPA and Spring

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

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.

Get distinct column values with a Spring Data JpaRepository

The unwanted order by gets created since you are requesting a paged result. The notion of the n-th page only makes sense when your results are ordered and the ordering is coming from your Pageable method argument.

Remove the order by from your query and set the sort attribute of your Pageable to sort by category.

Distinct on specific column using JPA Specification

You can't use Specifications because you want to return a List of Strings.

So you could use JPQL

@Query("select distinct h.name from Hcp h where area = 'Dhaka'")
List<String> findDistinctName();

Spring Data JPA - Get All Unique Values in Column

This can be achieved using the @Query annotation as:

public interface AddressRepository extends CrudRepository<Address, Long> {
    @Query("SELECT DISTINCT a.city FROM Address a")
    List<String> findDistinctCity();
}

Then, a call to addressRepository.findDistinctCity() would return the distinct city names.

A sample application is available on Github for review. Run integration test as mvn clean test to verify the approach.

Spring Data JPA Distinct - Return results from a single column

If that state concept is closed - you know its possible set of values - it should be an enum.

After that you can create queries that you invoke like:

repository.findByState(State.APPROVED)

If you can't create an enum, you need a separate method to get the distinct values, which can't be provided by JPA, because you need a list of strings and not a list of CalloutRequests.
Then you need to specify a query manually like:

@Query("SELECT DISTINCT State FROM CALLOUT_REQUEST")
List<String> findDistinctStates();

---

Related Topics