Get distinct column values with a Spring Data JpaRepository

Question

I have this entity (annotations omitted for brevity):

@Entity
class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Lob
    private String category;

    @Lob
    private String name;

    @Lob
    private String customer_number;

    // more attributes omitted
}

I have to get a list of distinct category value for a particular uid

In my JpaRepository I have this:

@Query("SELECT DISTINCT product.category FROM Product as product WHERE product.customerNumber = :cn ORDER BY product.category")
Page<String> findDistinctCategoryByCustomerNumber(String cn,
                                                  Pageable pageable);

Without the @Query annotation, the ids are returned, instead of the category values. The generated SQL looks like this:

select distinct product0_.id as id1_0_, product0_.customer_number as customer2_0_, product0_.category as directory3_0_, product0_.name as name4_0_ from product product0_ 
where product0_.customer_number=? 
order by product0_.id desc limit ?

But I need the distinct categories not the product entities. Short of another idea I added the @Query annotation above. But now I get this error:

Order by expression "PRODUCT0_.ID" must be in the result list in this case; SQL statement:
select distinct product0_.directory as col_0_0_ from product product0_ where product0_.customer_number=? order by product0_.directory, product0_.id desc limit ? [90068-197]

But I cannot add id to the result list because that would make the DISTINCT useless, as id is the primary key.

So I either need a suitable method name for automatic query generation or a way to stop JpaRepository from adding its order by clause to the end of my @Query.

Answer 1

The unwanted order by gets created since you are requesting a paged result. The notion of the n-th page only makes sense when your results are ordered and the ordering is coming from your Pageable method argument.

Remove the order by from your query and set the sort attribute of your Pageable to sort by category.