0

I'm using JPA with Hibernate 4 as provider and I have the following entities:

@Entity
public class Customer {

    @Id
    private String id;

    private String name;

    @OneToMany(cascade=CascadeType.ALL)
    @JoinTable(name="customers_phones",
            joinColumns={@JoinColumn(name="id_customer")},
            inverseJoinColumns={@JoinColumn(name="id_phone")}
            )
    private List<Phone> phones;

    .. Getters/Setters ..
}

@Entity
public class Phone {

    @Id
    private String id;

    private String number;

    .. Getters/Setters ..
}

And I have the following JPQL:

SELECT c FROM Customer c INNER JOIN c.phones p WHERE p.id = :phone

For which Hibernate is generating the following SQL:

select
    customer0_.id as id1_0_,
    customer0_.name as name2_0_ 
from
    Customer customer0_ 
inner join
    customers_phones phones1_ 
        on customer0_.id=phones1_.id_customer 
inner join
    Phone phone2_ 
        on phones1_.id_phone=phone2_.id 
where
    phone2_.id=?

I'd like Hibernate to generate the SQL like follows (i.e. without joinning table 'Phone'):

select
    customer0_.id as id1_0_,
    customer0_.name as name2_0_ 
from
    Customer customer0_ 
inner join
    customers_phones phones1_ 
        on customer0_.id=phones1_.id_customer 
where
    phones1_.id_phone=?

I know I could map a new entity to the join table 'customers_phones' associate it to Customer and use it in the query or I could use native SQL instead of JPQL, but I was wondering if there is a better way to achieve this (without changing entity model and still using JPQL), maybe some Hibernate-specific Query Hint?

Rafael Vanderlei
  • 351
  • 2
  • 10
  • Why are you using a JoinTable for a OneToMany relationship? It is simpler to use a standard OneToMany and ManyToOne relationship. – SpartanElite Aug 12 '14 at 19:48
  • What if you use `SELECT c FROM Customer c WHERE :phone IN elements(c.phones)`? If Hibernate expects an object instead of ID, you can still call `em.getReference()`. – Guillaume Poussel Aug 12 '14 at 20:04
  • @SpartanElite, I asked myself the same, but I can't change database schema. The application runs with a legacy database and this OneToMany relationship was designed that way. I've read about it and noticed there are some [arguable] reasons to model OneToMany using JoinTable, as you can see here: - [1] (http://stackoverflow.com/questions/1307203/hibernate-unidirectional-one-to-many-association-why-is-a-join-table-better) - [2] (http://stackoverflow.com/questions/2092611/why-is-it-recommended-to-avoid-unidirectional-one-to-many-association-on-a-forei) – Rafael Vanderlei Aug 13 '14 at 14:22
  • @GuillaumePoussel, the query you suggested removes the join to table 'Phones', but it's a less intuitive jpql and still the generated SQL (as follows) has higher cost than the single "customers join customers_phones" that I expected from Hibernate. `select customer0_.id as id1_0_, customer0_.name as name2_0_ from Customer customer0_ where ? in ( select phones1_.id_phone from customers_phones phones1_ where customer0_.id=phones1_.id_customer )` Sorry but I don't know how to format this comments. – Rafael Vanderlei Aug 13 '14 at 14:29

0 Answers0