Hibernate doing Multiple Queries when JOIN FETCH is used with reference column other than primary ID

Viewed 860

I have the following problem.

Whenever my Repository is called with a query using a JOIN FETCH clause, Hibernate produces an additional query which is not needed at all. The main problem here is that by the corresponding database structure the two Entities (corresponding to two separate tables) should be joined by a column different than the IDs of the two Entities (namely "customerid" denoted in the Entity Order by @JoinColumn(name = "customerid", referencedColumnName = "customerid")").

The repository is the following:

public interface CustomerRepository extends CrudRepository<Customer, Long>{

    Customer findByMsisdn(String msisdn);

    @Query("SELECT c FROM Customer c JOIN FETCH c.orders WHERE c.msisdn =:msisdn")
    Customer getCustomersAndOrdersByMsisdn(@Param("msisdn") String msisdn);
}

Whenever I invoke the method getCustomersAndOrdersByMsisdn the following two queries are produced (naturally we would wish only to have Query 1 produced):

--Query1
select
   customer0_.id as id1_0_0_,
   orders1_.id as id1_1_1_,
   customer0_.customerid as customer2_0_0_,
   customer0_.msisdn as msisdn3_0_0_,
   customer0_.status as status4_0_0_,
   orders1_.customerid as customer4_1_1_,
   orders1_.orderid as orderid2_1_1_,
   orders1_.orderstatus as ordersta3_1_1_,
   orders1_.customerid as customer4_1_0__,
   orders1_.id as id1_1_0__ 
from
   customers customer0_ 
   inner join
      orders orders1_ 
      on customer0_.customerid = orders1_.customerid 
where
   customer0_.msisdn =?

and

--Query2
select
      customer0_.id as id1_0_0_,
      customer0_.customerid as customer2_0_0_,
      customer0_.msisdn as msisdn3_0_0_,
      customer0_.status as status4_0_0_ 
   from
      customers customer0_ 
   where
      customer0_.customerid =?

And my two entities (I ommited getters and setters here for clarity, these of course are present in my code) are:

@Entity
@Table(name = "customers")
public class Customer implements Serializable {
    static final long serialVersionUID = 1L;

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


    @Column(name = "msisdn")
    private String msisdn;

    @Column(name = "status")
    private String status;

    @Column(name = "customerid", unique = true)
    @NaturalId
    private String customerid;

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL,fetch = FetchType.LAZY)
    private Set<Order> orders;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Customer customer = (Customer) o;
        return id == customer.id &&
                msisdn.equals(customer.msisdn) &&
                customerid.equals(customer.customerid);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, msisdn, customerid);
    }
}

and

@Entity
@Table(name = "orders")
public class Order implements Serializable {

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

    @Column(name = "orderid")
    private int orderid;

    @Column(name = "orderstatus")
    private String orderstatus;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customerid", referencedColumnName = "customerid")
    private Customer customer;


    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Order orders = (Order) o;
        return id == orders.id &&
                orderid == orders.orderid;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, orderid);
    }
}
1 Answers

This is absolutely normal and it is occurring because the join is being made using a natural id rather than the id of the entity. Hibernate works with the id of the entity to perform operations on the entity instead of the natural id defined.

So when making the join, hibernate need to fetch the entity of the Customer having the natural id.

For more info, read the article @NaturalId – A good way to persist natural IDs with Hibernate?

Related