I m trying to fetch result set using join query in hibernate xml file.But problem is wen primary key on which join is done repeats it doesn't give new set of values..it takes original value only.I mean if there are 5 results of primary key value 1 then all 5 values will be same instead thy r different 5 values..



My xml file code

<class name="com.netechinc.forms.TenderForm.EMDRefundSearchForm" table="works_info">
<id name="pk_work_id">
</id>
<property name="work_name" column="work_name" type="java.lang.String"/>

<join table="tender">
<subselect>
select a.tender_id,
a.work_id as work_id,
a.work_no as work_no,
a.emd as emd_amount,
a.status as status,
TO_CHAR(b.createdate, 'DD/MM/YYYY') as tender_date,
b.tender_no as tender_no
from
tender_details a
,tender b
where a.tender_id = b.pk_tender_id and a.work_id not in(select work_id from emd_refund)
</subselect>
<key column="work_id" />
<property name="tender_no" column="tender_no" type="java.lang.String"/>
<property name="tender_date" column="tender_date" type="java.lang.String"/>
<property name="status" column="status" type="java.lang.String"/>
<property name="work_no" column="work_no" type="java.lang.String"/>
<property name="tender_id" column="tender_id" type="java.lang.String"/>
<property name="emd_amount" column="emd_amount" type="java.lang.String"/>
<property name="work_id" column="work_id" insert="false" update="false" type="java.lang.String"/>
</join>

</class>

problem here is when work_id on which join is done repeats,results for all that work_id gets replaced with 1st value..so if 5 results are there with 1 work_id all 5 results have same value instead of different values.


Can any1 help why this is happening??

Thanks in advance

database identity is defined via the primary key, if you're loading multiple times the same type and having the same PK you will be returned the same object instance (in the scope of a single Session). Hope this helps?

So i need to create new instance of object every time in single session??

Criteria crit = session.createCriteria(form.getClass());
searchresult = crit.list();

this is how i create my criteria and convert it into list..

please, no. you should fix your model: if they are actually different objects, why do they have the same primary? that's nonsense, also on the database if they have the same PK they are the same row, so why would you need separate objects?

I am sorry i forgot to tell one table's primary key is foreign key for other table which is joined..so other table have many values of primary key of 1st table. for eg. table A's primary key is foreign key of table b.so if table A as primary key 1 then that 1 value is many times in table b.

I am still facing this problem

Hi,
could you provide a testcase? a simple test having a failure to highlight what you would expect would really help understanding the problem.

table A
parent_name (PK)
xyz

table B
child_name(PK) parent_name(FK)
abc xyz
def xyz
ghi xyz

so when i m trying to join table A & B on primary key parent_name (value=xyz) i should get 3 results as
abc xyz
def xyz
ghi xyz

but i m getting my result set as
abc xyz
abc xyz
abc xyz

because i think data is getting overwritten.But why is this happening??


HBM file Code:

<class name="PARENT" table="parent">
<id name="parent_name">
</id>
<join table="child">
<key column="parent_name" />
<property name="child_name" column="child_name" type="java.lang.String"/>
</join>
</class>

Business Logic Code:

Criteria crit = session.createCriteria(form.getClass());
searchresult = crit.list();


Thanks,
Chirag

Please post the model too, if I have to write the code it will work, so if you want me to understand what you've wrong you should provide a fully running testcase and specify the hibernate version you use.

Hibernate Version i m using is 3.0 (hibernate-configuration-3.0.dtd)

Model :

HBM file Code:

<class name="PARENT" table="parent">
<id name="parent_name">
</id>
<join table="child">
<key column="parent_name" />
<property name="child_name" column="child_name" type="java.lang.String"/>
</join>
</class>


Business code:

List searchresult = null;

session = sessionFactory.openSession();
AlterSession(session); //This code just alters the date and timestamp format.
Criteria crit = session.createCriteria(form.getClass());
searchresult = crit.list();
session.flush();
session.close();

return searchresult;

//End of business code

And after that i am storing this searchresult in session as
session.setAttribute("searchresult",searchresult); //Session here used is HttpSession

Then on GUI i am iterating this searchresult using code as below

ResultDisplay.jsp code :

<%List searchresult = (List)session.getAttribute("searchresult"); %>

<c:set var="searchresult" value="${searchresult}"/>
<c:if test='${not empty searchresult}'>
<bean:define id="start" value="<%=startIndex%>"/>
<bean:define id="endIndex" value="<%=end%>"/>
<c:forEach var='result' items='${searchresult}' varStatus='j' begin='${start}' end='${endIndex}'>
<bean:define id="searchMap" name="result"/>
<bean:write name="searchMap" property="parent_name"/>
<bean:write name="searchMap" property="child_name"/>
</c:forEach>
</c:if>


Hibernate.cfg.xml code:


<hibernate-configuration>
<session-factory name="java:comp/env/hibernate/SessionFactory">
<property name="connection.datasource">jdbc/ZpDS</property>
<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>
<property name="hibernate.connection.pool_size">10</property>
<property name="hibernate.c3p0.acquire_increment">3</property>
<property name="hibernate.c3p0.idle_test_period">14400</property>
<property name="hibernate.c3p0.timeout">25200</property>
<property name="hibernate.c3p0.max_size">15</property>
<property name="hibernate.c3p0.min_size">3</property>
<property name="c3p0.max_statements">50</property>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
</session-factory>
</hibernate-configuration>



Hope this is what you were asking for.

Thanks,
Chirag

hibernate-configuration-3.0.dtd doesn't imply hibernate 3.0: all versions of hibernate 3.X.X are compatible with that

And I don't need you jsp files, I need you java classes.
Are you sure you are iterating correctly with those tags? looks utterly complex to me, why do you copy the list in a set? I don't need an answer on that, please try printing out the result without using a jsp.

But when i m firing same query generated by hibernate using prepareStatement it is working fine with those tags as well in JSP.

Ok i will try to iterate search result in java class itself(without JSP).


Thanks,
Chirag