Spring Data JPA - Get All Unique Values in Column
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
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.