Hibernate Left Outer Join

Question

I have a Hibernate service method as such: "SELECT sc FROM SecurityContact sc WHERE sc.securityId=:securityId2". securityId2 is passed in by the user. Each SecurityContact has a many to one relationship with a Contact, so Hibernate automatically calls a join when this query runs. However, the join that Hibernate always runs is an inner join, which will not work properly for my purposes. Is there any way to force Hibernate to internally generate a left outer join instead? Here is the code for the 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;

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

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

    //bi-directional many-to-one association to AuditContact
    @ManyToOne
    @JoinColumn(name="audit_id", referencedColumnName="contact_id")
    private AgentContact 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 AgentContact getAgentContact() {
        return this.agentContact;
    }

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

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

} 

Answer 1

Relationship: Account many -- Employee one

the configuration in xml:

        <many-to-one name="employee" class="active.security.domain.Employee" lazy="false">
        <column name="EmpId"  />
    </many-to-one>

java code :

        Session session = HibernateUtil.getCurrentSession();

    Criteria criteria = session.createCriteria(Account.class);
    criteria.add(Restrictions.eq("application.id", applicationID));
    List<Account> list = criteria.list();

the key is to use criteria.list();

Answer 2

Try fetch=select on the many-to-one matching

Also see http://community.jboss.org/wiki/AShortPrimerOnFetchingStrategies and http://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html

Answer 3

According to the Hibernate documentation the hibernate query language should support this. (At least in version 3.3)

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-joins

Try to create your query like this:

Query query = entityManager.createQuery("Select sc from SecurityContact as sc " +
                                        "left outer join sc.agentContact as c " +
                                        "where sc.securityId=:securityId2";

EDIT: Changed the contact property of sc to the agentContact property you supplied in your question details.

Answer 4

In your hibernate configs set the use_outer_join property to true.

Answer 5

I had a similar problem. I was having a SiebelUser DAO. Each SiebelUser related to a team with many to one relationship. Some SiebelUser were having has userid = 0 as foreign key for which no primary key was present in Users table. So naturally the fetch from SeibelUsers table was ignoring users with userd id =0. This was earlier configuration

<many-to-one name="teamid" class="com.hewitt.wlm.pojo.Tblteams"> <column name="TEAMID" length="4" not-null="true" /> </many-to-one>

So, In order to solve my problem I changed the config to . But it didn't work for me.

<many-to-one name="teamid" class="com.hewitt.wlm.pojo.Tblteams" **fetch="join" not-null="false" lazy="proxy" not-found="ignore"**> <column name="TEAMID" length="4" not-null="**false**" /> </many-to-one>

Finally I changed my query to explicitly do a left outer join like this giving the specifc path from SiebelUsers to Users table.

select property1, property2, ...
from from **SiebelUser s left outer join    s.team t**
where property1='x'

This worked for me.

Note that my SiebelUser class was having Team object as its property(as defined in the path above). Hope it helps someone.

Answer 6

I had the following configuration:

<many-to-one column="user_id" lazy="proxy" fetch="join" insert="false" name="user" not-null="true" update="false" not-found="ignore" />

The user_id column was not-null, but I wanted a LEFT OUTER JOIN (instead of the INNER JOIN I was getting), because it was just a DB hack - sometimes user_id = 0 which wasn't mapped to any row in the user table (0 as a replacement to NULL). I didn't want the fetch=select mode

After a lot of debugging in the Hibernate intervals I set not-null="false" which solved the issue for me :) (I got the LEFT OUTER JOIN). Hopefully someone will find it useful (btw.: I'm using Hibernate 3.6.0.Final).