CODEWITHSUNDEEP
spring-bootspring-data-jpa
pixel
pixel

Reputation: 10587

Spring JPA repository method to get sorted distinct and non-null values

To get distinct data based on multiple columns and exclude NULL values on a column and sort the result in SQL, I would write query like:

SELECT DISTINCT CAR_NUMBER, CAR_NAME
FROM CAR
WHERE CAR_NUMBER IS NOT NULL
ORDER BY CAR_NUMBER

This would return me rows with distinct values for CAR_NUMBER and CAR_NAME and it would exclude any rows having CAR_NUMBER = NULL and finally, it would sort the result by CAR_NUMBER.

However, In Spring JPA, I gather you can use either methods named based on your entity fields or using @Query annotation.

I am trying to do this:

List<Car> findDistinctByCarNumberAndCarNameAndCarNumberIsNotNull(Sort sort);

, and to call this method like:

myRepo.findDistinctByCarNumberAndCarNameAndCarNumberIsNotNull(Sort.by("carNumber"));

but this is failing on Maven > Install with error like "findDistinctByCarNumberAndCarNameAndCarNumberIsNotNull(Sort sort) expects at least 1 arguments but only found 0".

Similarly, I tried using @Query like below but with same effect:

@Query(SELECT DISTINCT c.carNumber, c.carName FROM carEntity c WHERE c.carNumber IS NOT NULL ORDER BY c.carNumber)
List<Car> findAllCars();

Upvotes: 1

Views: 1895

Answers (2)

H3AR7B3A7
H3AR7B3A7

Reputation: 5261

I just remembered there is a better way to solve this than that helper function that you described in your answer and thought I would share it.

Projection in JPQL would be a cleaner way to create your DTO:

@Query("SELECT DISTINCT new com.yourdomain.example.models.MyDto(c.carNumber, c.carName)
FROM CarEntity c WHERE c.carNumber is not null")
List<CarDto> findAllDistinctRegions(Sort sort);

Upvotes: 0

pixel
pixel

Reputation: 10587

I figured out the problem. Following is how I solved it:

In my repository:

@Query("select distinct c.carNumber, c.carName from CarEntity c where c.carNumber is not null")
List<Object> findAllDistinctRegions(Sort sort);

Important here to realize is that @Query returns List<Object>, not List<Car>.

Next, in my service, call this method:

    List<Object> carData = carRepository.findAllDistinctCars(Sort.by("carNumber"));

That worked finally fine; however, I run into another problem where I had to do necessary conversion from List to List.

    // This is bit tricky as the returned List<Object> is actually
    // List<Object[]>.  Basically, each field returned by the @Query 
    // is placed into an array element.  
    //To solve it, I had to do following:
    List<Car> cars = new ArrayList<Car>();
    for(Object data: carsData) {
        Object[] obj = (Object[]) data;
        cars.add(new CarDto((Short) obj[0], ((String) obj[1]));
    }

Upvotes: 2

Related Questions