CODEWITHSUNDEEP
hibernatejpa
Richard
Richard

Reputation: 1739

What SQL does Hibernate's @JoinColumns generate?

I'm trying to analyse a legacy app and I've got this annotation in a class called Role.java. I'm trying to work out what the generated SQL for this particular join would look like. 

@ManyToOne
@JoinColumns({
@JoinColumn(name="COMPANY_NUMBER", referencedColumnName="COMPANY_NUMBER", insertable=false, updatable=false),
@JoinColumn(name="EMPLOYEE_ID", referencedColumnName="EMPLOYEE_ID", insertable=false, updatable=false)
})
private CompanyPerson person;

I'm guessing it would be using aliases, something like this

LEFT JOIN person_role ROLEA 
    ON ROLEA.employee_id = PERSON.employee_id
LEFT JOIN person_role ROLEB
    ON ROLEB.company_number = PERSON.company_number;

But I'm not sure, and I've only got the source code to go on - no way to build the application and I can't get near the server to turn the logging up enough to get Hibernate to log the SQL. Also had a quick look at the JPA spec but couldn't figure it out.

Could someone give me an example of how the generated SQL would look?

Thanks

Upvotes: 1

Views: 3994

Answers (1)

eternay
eternay

Reputation: 3814

The @JoinColumns annotation is defining the many-to-one relationship between the Role and the CompanyPerson tables, indicating the columns of the composite foreign key.

In your Role table you'll find 2 columns, COMPANY_NUMBER and EMPLOYEE_ID, with foreign keys against the primary key of the CompanyPerson table (expected to be composed by a company number and an employee id).

When hibernate has to select the rows in the Role table, and if the fetch type is EAGER, it will generate the following SELECT:

select
    role0_.id as id17_,
    role0_.COMPANY_NUMBER as COMPANY2_17_,
    role0_.EMPLOYEE_ID as EMPLOYEE3_17_
from Role role0_

and then one SELECT for each related row in the CompanyPerson table, passing the values the first query returned for the COMPANY_NUMBER and EMPLOYEE_ID fields :

select
    companyper0_.COMPANY_NUMBER as COMPANY1_18_0_,
    companyper0_.EMPLOYEE_ID as EMPLOYEE2_18_0_,
    companyper0_.information as informat3_18_0_
from CompanyPerson companyper0_
where companyper0_.COMPANY_NUMBER=? and companyper0_.EMPLOYEE_ID=?

If you force the join to avoid the N+1 queries, with a fetch join, you obtain the following select:

select
    role0_.id as id17_0_,
    companyper1_.COMPANY_NUMBER as COMPANY1_18_1_,
    companyper1_.EMPLOYEE_ID as EMPLOYEE2_18_1_,
    role0_.COMPANY_NUMBER as COMPANY2_17_0_,
    role0_.EMPLOYEE_ID as EMPLOYEE3_17_0_,
    companyper1_.information as informat3_18_1_
from Role role0_
    inner join CompanyPerson companyper1_
        on role0_.COMPANY_NUMBER=companyper1_.COMPANY_NUMBER and role0_.EMPLOYEE_ID=companyper1_.EMPLOYEE_ID

You should execute something like the following code to generate this query:

List<Role> roles = (List<Role>)session.createQuery(
        "from Role role join fetch role.person person").list();

I hope this can help you understand the @JoinColumns annotation.

Upvotes: 1

Related Questions