[FIXED] QueryDsl extra query, even though I'm using a join

Issue

I have Car and Color classes. Car class has both Entity and Id fields of Color. Here is the Car class code:

public class Car {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;

    @JoinColumn(name = "color_id", insertable = false, updatable = false)
    @ManyToOne(targetEntity = CarColor.class, fetch = FetchType.EAGER)
    private CarColor color;

    @Column(name = "color_id")
    private Long colorId;

}

Here is the Color class code:

public class CarColor {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   
   @Column(name = "name")
   private String name;
   
   @JsonIgnore
   @OneToMany(mappedBy = "color")
   List<Car> cars;
}

When I'm fetching a Car i have an extra query to get a color Entity which is causing n+1 problem. So I have tried to use joins.

  return query.select(qCar)
  .from(qCar)
  .innerJoin(qCar.color(), QCarColor.carColor)
  .where(predicate)
  .fetch();

I'm getting these queries:

select
        car0_.id as id1_3_,
        car0_.color_id as color_id3_3_,
    from
        car car0_  
    inner join
        color carcolor2_ 
            on car0_.color_id=carcolor2_.id 

Hibernate: 
    select
        carcolor0_.id as id1_6_0_,
        carcolor0_.name as name2_6_0_ 
    from
        color carcolor0_ 
    where
        carcolor0_.id=?

As you can see, at the end I have an extra query to get color even though it has already joined Color entity. That is the main problem. Maybe it somehow connected with my decision to store both Entity and Id.

Solution

Queries don't use the association eagerness to add joins arbitrarily to your query. After that the entity loader still notices that this is an eager association and will fetch it separately.

You can however simply add the join yourself. Important here is to add the join as fetch join, so that the join result will be initialised as the value of the association.

return query.select(qCar)
  .from(qCar)
  .innerJoin(qCar.color(), QCarColor.carColor).fetchJoin()
  .where(predicate)
  .fetch();

Answered By - Jan-Willem Gmelig Meyling
Answer Checked By - Mildred Charles (JavaFixing Admin)