CODEWITHSUNDEEP
javaspringspring-boothibernatejpa
Vaibhav Kumar
Vaibhav Kumar

Reputation: 41

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

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

Upvotes: 0

Views: 177

Answers (1)

Davide D&#39;Alto
Davide D&#39;Alto

Reputation: 8246

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

Upvotes: 1

Related Questions