Spring Data JPA - Get All Unique Values in Column

Question

I have a project using Spring Data JPA that consumes data from a table full of addresses. One of the columns of this table is the city. I would like to get a distinct list of cities that are in the table i.e. SELECT DISTINCT city FROM address.

Is there a way to do this using Spring Data JPA?

Answer 1

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.

Answer 2

Manish's comment should probably be bumped up to an answer (which i'll try to capture here since it ultimately solved my problem...although projections didn't seem to work with select distinct). The selected answer works in spring-data-jpa, but fails in spring-data-rest. One possible workaround for the spring-data-rest scenario is to create a separate @RestController for the select distinct results

@RestController
public class AddressRepoAdditionals {
     @Autowired
     private AddressRepository repo;

     @RequestMapping("/additional/address/distictCities")
     public List<String> findDistinctCity() {
          return repo.findDistinctCity();
     }
 }

perhaps there's a similar but more elegant variation based on @RepositoryRestController