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

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

Answer 2

I have a similar problem with Spring Data Rest.

public interface ProjectReportRepository extends JpaRepository<ProjectReport, Long>{

ProjectReport findByProjectAndPeriod(Project project, ReportingPeriod period);
/**
 * 
 * @param project
 * @return
 */
@Query("SELECT "
        + " new api.model.reports.ProjectTimeAggregation("
        + " count(*) "
        + ") "
        + "FROM ProjectReport r "
        + "WHERE r.project = :project "
        + "")
ProjectTimeAggregation aggregateTimeInfo(Project project);

@Query("SELECT "
        + " new api.model.reports.ProjectTimeAggregation("
        + " count(*) "
        + ") "
        + "FROM ProjectReport r "
        + "WHERE r.project = :project "
        + " group by r.timeRag")
List<ProjectTimeAggregation> aggregateTimeInfoGroup(Project project);

Both methods works with spring-data and I can use it in Spring Services, but via REST endpoint only the first method works:

http://localhost:8080/api/projectreports/search/aggregateTimeInfo?project=/api/projects/6

The 2nd method returns an mapping error:

"Couldn't find PersistentEntity for type class ProjectTimeAggregation!"

It works with an POJO object but not if the object is in a collection. Is this a bug in spring-data-rest, that it works with objects but not with collections.

Spring-Boot 2.0.4.Release Spring-Data: 2.1.0.RC1 Spring-Data-Rest: 3.1.0.RC1

Answer 3

This can be achieved using a @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.