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

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();

CodePudding user response：

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]));
    }

CodePudding user response：

The problem is in Query DSL Distinct doesn't work the way you are trying to use it here. It is expecting a parameter (for car number) and you aren't giving it any.

Here is the cleanest workaround I can think of if my previous suggestion of using @Query doesn't work:

Create a custom interface:

public interface CustomJpaRepository {

    List<String[]> giveMeAName();
}

Add it to your existing JpaRepository:

public interface ExistingJpaRepository extends JpaRepository<Car, Long>, CustomJpaRepository { }

Implement the new CustomJpaRepository:

public class CustomJpaRepositoryImpl implements CustomJpaRepository {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<String[]> giveMeAName() {
        return entityManager.createQuery("SELECT DISTINCT c.carNumber, c.carName FROM carEntity c WHERE c.carNumber IS NOT NULL ORDER BY c.carNumber").getResultList();
    }
}

This way you have a place where more complex queries can live that Query DSL can't handle.