Hi I am new to hibernate and am not very confident about the joining feature in hibernate I want to be able to implement the follow join between three tables. What is the best way to implement it in hibernate.


select trans.date, con.type, ai.item
from transactions trans, condition con, assigned_items ai
where trans.trans_id = ai.trans_id
and ai.con_id = con.id
and trans.department=10

There's almost no differences. You can use following variants (implied that you have correct corresponding mappings and many-to-one relationship between AssignedItem class and Transaction, Condition classes):

1. Using theta-style joins:

select trans.date, con.type, ai.item from Transaction tran, Condition con, AssignedItem ai where tran.id = ai.transaction.id and con.id = ai.condition.id and tran.department=:deptNum

2. Using regular join:

select trans.date, con.type, ai.item from
AssignedItem ai
join ai.transaction tran,
join ai.condition con
where tran.department=:deptNum

Hi to @ll. Lester, is there any option to use theta join without any explicit association between classes in join?

Thanks in advance

so will I have three hibernate mappings for each table and then in each class mapping what will the join look like... I am a bit confused about the mapping in the config and where would my sql be put int

Suzie, if I understood to Lester You must declare 2 many-to-one associations in AssignedItem mapping resource to Transaction and Condition classes, and stay another 2 mappings (Transaction, Condition) without changes.

I hope this helps

so in the assigned_items_cfg.xml mapping I will have the below and in the (condition and transaction) classes I wont have any many-to-one relationships:


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="AssignedItem" table="AssignedItem">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="item" column="item"/>

<property name="ai_trans_id" column="trans_id"/>
<property name="ai_con_id" column="con_id"/>

<property name="title"/>

<set name="transactions" table="transactions" inverse="true">
<key column="ai_con_id"/>
<many-to-one column="id" class="transactions"/>
</set>

<set name="conditions" table="condition" inverse="true">
<key column="ai_con_id"/>
<many-to-one column="id" class="condition"/>
</set>


</class>


</hibernate-mapping>

Suzie, my two cents:
1. for convention the mapping resources are named: ClassName.hbm.xml
2. if you're using any many-to-one association you don't need any set declaration, because the set is in "one" side (if the association was bi-directional).

I hope this helps.

sor are the mapping below correct

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="AssignedItem" table="AssignedItem">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="item" column="item"/>

<property name="ai_trans_id" column="trans_id"/>
<property name="ai_con_id" column="con_id"/>

<property name="title"/>

<many-to-one column="ai_con_id" class="transactions"/>

<many-to-one column="ai_con_id" class="condition"/>

</class>


</hibernate-mapping>

Hi again suzie, sorry for my delayed reply. I believe your mapping is right except by you need an transactions class and an condition class variable. To say, the AssignedItem class will have an transactions class variable to know which transactions depends of. And you'd add it to your mapping indicated with name behind your many-to-one declaration.

I hope this helps

so the correct way is below right and this way i dont have to hard code my sql where clause right:

<hibernate-mapping>

<class name="AssignedItem" table="AssignedItem">
<id name="id" column="id">
<generator class="native"/>
</id>

<property name="item" column="item"/>
<property name="title"/>

<many-to-one name="ai_con_id"
column="ai_con_id"
class="com.dao.hibernate.Condition"
not-null="true" />

<many-to-one name="ai_trans_id"
column="ai_trans_id"
class="com.dao.hibernate.Transactions"
not-null="true" />

</class>


</hibernate-mapping>

I am getting the following error:


java.lang.ClassCastException: com.dao.hibernate.Transactions
org.apache.jsp.pages.AssignmentResultPage_jsp._jspService(AssignmentResultPage_jsp.java:99)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1085)
org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestProcessor.java:263)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:398)
org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(TilesRequestProcessor.java:318)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:241)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)



for my java bean mapping for the foreign keys for the assignment_item bean I have :


private Condition ai_con_id;

private Transactions ai_trans_id;

public Transactions getTran_id() {
return ai_trans_id;
}

public void setTrans_id(Transactions ai_trans_id) {
this.ai_trans_id = ai_trans_id;
}


public Rule getCon_id() {
return ai_con_id;
}

public void setCon_id(Condition con_id) {
this.ai_con_id = ai_con_id;
}

Hi again suzie, I think You're ok (completing "many-to-one" in your mapping resources), and you could to use inner join to reach your goal retrieving data with hql query.

Regards

ignore the above it is incorrect:

I am getting the following error:


java.lang.ClassCastException: com.dao.hibernate.Transactions
org.apache.jsp.pages.AssignmentResultPage_jsp._jspService(AssignmentResultPage_jsp.java:99)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1085)
org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestProcessor.java:263)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:398)
org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(TilesRequestProcessor.java:318)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:241)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)



for my java bean mapping for the foreign keys for the assignment_item bean I have :


private Condition ai_con_id;

private Transactions ai_trans_id;

public Transactions getTran_id() {
return ai_trans_id;
}

public void setTrans_id(Transactions ai_trans_id) {
this.ai_trans_id = ai_trans_id;
}


public Condition getCon_id() {
return ai_con_id;
}

public void setCon_id(Condition con_id) {
this.ai_con_id = ai_con_id;
}

Hi again suzie, can you explain what query statement in are you getting this trace? It seems like u're retrieving any distinct type resultset query.list is returning...

I found out because I had
Criteria crit = session.createCriteria(Transction.class);
crit.add(Restrictions.like("department", 10));

in my action I got the error. but when I change it to the below I cant pass the Restriction Parameter:

Criteria crit = session.createCriteria(AssignmentItems.class);
//crit.add(Restrictions.like("department", 601));


it doesnt work since the assignment class does not have a department
column and it is in the transaction class. so how do i provide this
criteria.