Derived Query for distinct object using JPA is not returning proper result

Question

I have a entity class

public class Hex {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "native")
    @Column(name = "hex_id")
    private Long hexId;

    @Column(name = "class")
    String vehicleClass;

    @Column(name = "manufacturer")
    String manufacturer;

}

I need distinct manufacturer on basis of Vehicleclass, I am using below function

    List<Hex> findManufacturerDistinctByVehicleClass(String classId);

But this return Distinct Hex objects on the basis of vehicle ID ,I observed the query in console and it was fetching results based on distinct primary key and applying a where clause on class

   select
        distinct hex0_.hex_id as hex_id1_1_,
        hex0_.Manufacturer as manufact2_1_,
        hex0_.class as class3_1_ 
    from
        hex hex0_ 
    where
        hex0_.class=?

I can do it by using @Query but I am looking for a derived query solution and if cannot be done then why

https://stackoverflow.com/a/40934100/14624844 Does this answer help? — Bob, Oct 27 '22 at 03:16

score 1 · Answer 1 · answered Oct 27 '22 at 17:59

It seems this is how derived queries work:

Anything between find and By is skipped, except for the Distinct keyword
Distinct will apply the distinct keyword to the select clause of the SQL query
Everything after By is used in the where clause as a filter (it won't affect the select clause)

I think you already know this but I guess you have two options:

The @Query annotation:

@Query("select distinct manufacturer from Hex where ...")
List<String> findManufacturerDistinctByVehicleClass(String classId);

Projections:

interface ManufacturerProjection {
    String getManufacturer();
}

List<ManufacturerProjection> findManufacturerDistinctByVehicleClass(String classId);

Derived Query for distinct object using JPA is not returning proper result

1 Answers1