Hibernate ignoring LEFT JOIN

I have a table called SecurityContact that has a many to one relationship with another table Contacts. It has two join columns to C开发者_如何学JAVAontacts, one of them called agentContact and the other auditContact. I am trying to run the following HQL query:

SELECT sc FROM SecurityContact sc LEFT JOIN sc.agentContact ac LEFT JOIN sc.auditContact ac2 WHERE sc.securityId=:securityId2

However, Hibernate completely ignores the LEFT JOIN statements and proceeds to generate SQL that utilizes inner joins. This does not suit my purposes at all. I have tried setting fetch annotations with no luck. This problem has been driving me crazy for over two days, so any help at all would be much appreciated.

Here is the code for my SecurityContact class:

/**
 * The persistent class for the SecurityContact database table.
 * 
 */
@Entity
@FXClass(kind=FXClassKind.REMOTE)
public class SecurityContact implements Serializable {
    private static final long serialVersionUID = 1L;
    @Transient private String uid;
    @FXIgnore
    public String getUid() {
        if (uid == null) {
            uid = "" + securityContactId;
        }
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="securityContact_id")
    private Long securityContactId;

    @Column(name="security_id")
    private String securityId;

    @Column(name="create_date")
    private String createDate;

    @Column(name="create_user")
    private String createUser;

    @Column(name="modify_date")
    private String modifyDate;

    @Column(name="modify_user")
    private String modifyUser;

    //uni-directional many-to-one association to AgentContact

    @ManyToOne
    @JoinColumn(name="agent_id", referencedColumnName="contact_id")
    private Contact agentContact;

    //uni-directional many-to-one association to AuditContact
    @ManyToOne
    @JoinColumn(name="audit_id", referencedColumnName="contact_id")
    private Contact auditContact;

    public SecurityContact() {
    }
    @FXKeyColumn
    public Long getSecurityContactId() {
        return this.securityContactId;
    }

    public void setSecurityContactId(Long securityContactId) {
        this.securityContactId = securityContactId;
    }

    public String getSecurityId() {
        return this.securityId;
    }

    public void setSecurityId(String securityId) {
        this.securityId = securityId;
    }

    public String getCreateDate() {
        return this.createDate;
    }

    public void setCreateDate(String createDate) {
        this.createDate = createDate;
    }

    public String getCreateUser() {
        return this.createUser;
    }

    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }

    public String getModifyDate() {
        return this.modifyDate;
    }

    public void setModifyDate(String modifyDate) {
        this.modifyDate = modifyDate;
    }

    public String getModifyUser() {
        return this.modifyUser;
    }

    public void setModifyUser(String modifyUser) {
        this.modifyUser = modifyUser;
    }
    @FXManyToOne(parent="parent", property="contactId")
    public Contact getAgentContact() {
        return this.agentContact;
    }

    public void setAgentContact(Contact agentContact) {
        this.agentContact = agentContact;
    }
    @FXManyToOne(parent="parent", property="contactId")
    public Contact getAuditContact() {
        return this.auditContact;
    }

    public void setAuditContact(Contact auditContact) {
        this.auditContact = auditContact;
    }

} 

Here is the generated SQL from the above HQL:

select securityco0_.agent_id as col_0_0_, securityco0_.audit_id as col_1_0_, securityco0_.create_date as col_2_0_, securityco0_.create_user as col_3_0_, securityco0_.modify_date as col_4_0_, securityco0_.modify_user as col_5_0_, securityco0_.securityContact_id as col_6_0_, securityco0_.security_id as col_7_0_, agentconta3_.contact_id as contact1_0_0_, agentconta4_.contact_id as contact1_0_1_, agentconta3_.bank_id as bank10_0_0_, agentconta3_.create_date as create2_0_0_, agentconta3_.create_user as create3_0_0_, agentconta3_.email as email0_0_, agentconta3_.fax as fax0_0_, agentconta3_.modify_date as modify6_0_0_, agentconta3_.modify_user as modify7_0_0_, agentconta3_.name as name0_0_, agentconta3_.phone as phone0_0_, agentconta4_.bank_id as bank10_0_1_, agentconta4_.create_date as create2_0_1_, agentconta4_.create_user as create3_0_1_, agentconta4_.email as email0_1_, agentconta4_.fax as fax0_1_, agentconta4_.modify_date as modify6_0_1_, agentconta4_.modify_user as modify7_0_1_, agentconta4_.name as name0_1_, agentconta4_.phone as phone0_1_ from SecurityContact securityco0_ left outer join AgentContact agentconta1_ on securityco0_.agent_id=agentconta1_.contact_id left outer join AgentContact agentconta2_ on securityco0_.audit_id=agentconta2_.contact_id inner join AgentContact agentconta3_ on securityco0_.agent_id=agentconta3_.contact_id inner join AgentContact agentconta4_ on securityco0_.audit_id=agentconta4_.contact_id where securityco0_.security_id=?

I've done some testing in regards to your problem, and it seems to me that you must have either a problem in your mapping or a bug in the (outdated) version of Hibernate you are using.

For an optional @ManyToOne association, Hibernate should already be using a left join to query the entity and it's association (as it is the only way to query for the root entity whether or not the associated entity is null).

I threw together a sample project which illustrates this at https://github.com/mattnworb/hibernate-sample.

In my sample project, the Employee class has a unidirectional many-to-one mapping to the Team class:

/**
 * Unidirectional relationship between Employee and Team.
 */
@ManyToOne
@JoinColumn(name = "team_id")
private Team team;

In the unit tests, there is both a test that the Employee.team reference is not-null when it is set in the DB, and another test that asserts that Employee.team is null when not set in the DB table.

In addition, there is a similar bi-directional relationship between the Company and Employee entities, with similar tests.

Finally, in the logs I can see that when Hibernate queries for the Employee entity (with a simple session.get(Employee.class, id)), it uses a left join to pull in the teams table (I added line breaks myself):

DEBUG org.hibernate.SQL - select employee0_.id as id1_2_, employee0_.company_id as company6_1_2_, employee0_.dateOfBirth as dateOfBi2_1_2_, employee0_.employmentStartDate as employme3_1_2_, employee0_.firstName as firstName1_2_, employee0_.lastName as lastName1_2_, employee0_.team_id as team7_1_2_, company1_.id as id0_0_, company1_.name as name0_0_, team2_.id as id2_1_, team2_.name as name2_1_
from employees employee0_
left outer join companies company1_ on employee0_.company_id=company1_.id
left outer join teams team2_ on employee0_.team_id=team2_.id where employee0_.id=?

So in summation, in order for Hibernate to use a left join between your associated entities, as long as the relationship is set as optional, there should be no need for a special HQL queries at all - a simple session.get() for the root entity should query the associated entity's table with a left join. If you are seeing different results, this suggests that either there is a bug in your version of Hibernate (I am using 3.6.6.Final here), or that there is something incorrect in your mapping.

Your HQL join syntax looks wonky. Try this:

SELECT sc
FROM SecurityContact sc
LEFT JOIN FETCH sc.agentContact
LEFT JOIN FETCH sc.auditContact
WHERE sc.securityId=:securityId2