All geek questions in one place

Hibernate left outer join

I have a Hibernate service method as such: "SELECT sc FROM SecurityContact sc WHERE sc.securityId=:securityId2" . securityId2 is passed by the user. Each SecurityContact has many relationships with a contact, so Hibernate automatically calls the connection when this request is run. However, the connection that Hibernate always starts is an internal connection that will not work properly for my purposes. Is there a way to get Hibernate to internally generate a left outer join? 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; } }
+6
source share
6 answers

According to the Hibernate documentation, this hibernate support 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 creating your query as follows:

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

EDIT: Changed the sc contact property for the agentContact property that you specified in your question details.

+1
source

Relations: Accounting for many - one employee

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 must use the criteria .list ();

+1
source

In your hibernate configurations, set the use_outer_join property to true.

0
source

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 is not-null, but I need a LEFT OUTER JOIN (instead of the INNER JOIN I got), because it was just a DB hack - sometimes user_id = 0 , which was not matched to any row in the user table (0 in as a replacement for NULL). I don't need fetch=select mode

After much debugging at Hibernate intervals, I set not-null="false" , which solved the problem for me :) (I got a LEFT OUTER JOIN ). Hope someone finds this useful (by the way, I'm using Hibernate 3.6.0.Final).

0
source

I had a similar problem. I had a SiebelUser DAO. Each SiebelUser is associated with a team in which there are many relationships. Some SiebelUser had userid = 0 as a foreign key, for which the primary key was not present in the Users table. Therefore, naturally, the selection from the SeibelUsers table ignored users with the identifier userd = 0. This was an 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, To solve my problem, I changed the configuration to. But that did not 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 modified my query to explicitly make the left outer join in such a way as to specify the path from the SiebelUsers table to Users.

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

It worked for me.

Notice that in my SiebelUser class there was a Team object as its property (as defined in the path above). Hope this helps someone.

0
source

Source: https://habr.com/ru/post/894063/

More articles:


All Articles