• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem in getting data from joined table:More than one row with the given identifire

 
k. krish
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All ....
I am new to hibernate.
in action having simple queries to access database records i have no problem,its with join queries.
i am using....
Hibernate 3.0.5
Struts 1.2.7
MSSqlServer 2000
tomcat 5.5.12
jdk 1.5.0

In My Action(servlet) I have to fetch record from two tables;
1.AppMst
2.PaymentTranDraft
based on common AppMstId.

I did in following way...
------------------------------------------------------------------------------
Session session=common.HibernateUtil.currentSession();
Transaction transaction = session.beginTransaction();

Query query=session.createQuery("from admin.PaidMemberListingForm appmst left join appmst.payout as payTD where appmst.numPaid=:AppMstPaid and appmst.numActivate=:AppMstActivate and payTD.txtPaymentFromDate>=:fromDate and payTD.txtPaymentToDate<=:toDate order by appmst.appID");
query.setInteger("AppMstPaid",1);
query.setInteger("AppMstActivate",1);
query.setString("fromDate",fromDate);
query.setString("toDate",toDate);

Iterator iterator=query.iterate();

while (iterator.hasNext())
PayoutForm payoutForm= new PayoutForm();

Object[] recordsPair = (Object[]) iterator.next();
PaidMemberListingForm paidMemberListingForm=PaidMemberListingForm)recordsPair[0];
PaymentTranDraftBean payoutRecords = (PaymentTranDraftBean) recordsPair[1];

String fName=paidMemberListingForm.getTxtFName();
String lName=paidMemberListingForm.getTxtLName();
payoutForm.setTxtName(fName + " " + lName);
payoutForm.setTxtCity(paidMemberListingForm.getTxtCity());

dispatchedAmount=payoutRecords.getFloatDispachAmount(); //Line causing Exception!!

totalAmount=totalAmount+dispatchedAmount;
payoutForm.setFloatDispachAmount(dispatchedAmount);
list.add(payoutForm);

}
----------------------------------------------------------------------------

It is compiled successfully.But at run time throwing following Exception:
{Error is caused by Italced line in above code..}

org.hibernate.HibernateException: More than one row with the given identifier was found: 62, for class: admin.PaymentTranDraftBean
at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:162)
at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:126)
at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersister.java:2496)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:387)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:368)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:166)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:140)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:119)
at org.hibernate.impl.SessionImpl.immediateLoad(SessionImpl.java:577)
at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:59)
at org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:80)
at org.hibernate.proxy.CGLIBLazyInitializer.intercept(CGLIBLazyInitializer.java:134)
at admin.PaymentTranDraftBean$$EnhancerByCGLIB$$6b023c9c.getFloatDispachAmount(<generated>
at admin.PayoutAction.execute(PayoutAction.java:88)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)...........
.....more

---------------------------------------------------------------------------
I am using following mapping files:

AppMst.hbm.xml

<?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="admin.PaidMemberListingForm" table="APPMST">

<id name="appID" type="integer" column="AppMstID" >
<generator class="increment"/>
</id>


<property name="txtIdNo"
column="AppMstRegNo"/>

<property name="txtFName">
<column name="AppMstFName"/>
</property>

<property name="txtLName">
<column name="AppMstLName"/>
</property>

<property name="txtCity">
<column name="AppMstCity"/>
</property>

<property name="txtState">
<column name="AppMstState"/>
</property>
<property name="txtPaidDate">
<column name="AppPaidDateTime"/>
</property>
<property name="numPaid">
<column name="AppMstPaid"/>
</property>
<property name="numActivate">
<column name="AppMstActivate"/>
</property>

<set name="payout" cascade="all">
<key column="AppMstID"/>
<one-to-many class="admin.PaymentTranDraftBean"/>
</set>

</class>
</hibenate-mapping>

And PaymentTranDraft.hbm.xml

<hibernate-mapping>
<class name="admin.PaymentTranDraftBean" table="PaymentTranDraft">

<id name="appID" type="integer" column="AppMstId" >
<generator class="increment"/>
</id>


<property name="txtPaymentFromDate"
column="PaymentFromDate"/>

<property name="txtPaymentToDate">
<column name="PaymentToDate"/>
</property>

<property name="floatDispachAmount" not-null="false">
<column name="DispachedAmt"/>
</property>
</class>
</hibernate-mapping>

------------------------------------------------------------
Setters and Getters of properties in above mapping files are in,
admin.PaidMemberListingForm,admin.PaymentTranDraftBean respectively.


Please help me to come over this problem.
Thanks in advance.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try setting "show_sql" in your hibernate.cfg.xml so that the query that is executed is printed out to the console, then post that.

Do you need a join column in the "left join" definition of the query?

Maybe the query is returning some sort of cartesian product and that is why you get dups.

Mark
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"k"

Please refrain from duplicate posting. I had to delete two duplicate threads already for this same question.

Thanks

Mark
 
k. krish
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Mark..!!
thanks a lot for replying.
i set "show_sql" to true in my hibernate.cfg.xml
and then i get following generated sql on server when i run the code:

Hibernate: select paidmember0_.AppMstID as col_0_0_, payout1_.AppMstId as col_1_0_ from APPMST paidmember0_ left outer join PaymentTranDraft payout1_ on paidmember0_.AppMstID=payout1_.AppMstID where (paidmember0_.AppMstPaid=? and paidmember0_.AppMstActivate=? and payout1_.PaymentFromDate>=? and payout1_.PaymentToDate<=?) order by paidmember0_.AppMstID

Hibernate: select paymenttra0_.AppMstId as AppMstId0_, paymenttra0_.PaymentFromDate as PaymentF2_3_0_, paymenttra0_.PaymentToDate as PaymentT3_3_0_, paymenttra0_.DispachedAmt as Dispache4_3_0_ from PaymentTranDraft paymenttra0_ where paymenttra0_.AppMstId=?

Hibernate: select paidmember0_.AppMstID as AppMstID0_, paidmember0_.AppMstRegNo as AppMstRe2_1_0_, paidmember0_.AppMstFName as AppMstFN3_1_0_, paidmember0_.AppMstLName as AppMstLN4_1_0_, paidmember0_.AppMstCity as AppMstCity1_0_, paidmember0_.AppMstState as AppMstSt6_1_0_, paidmember0_.AppPaidDateTime as AppPaidD7_1_0_, paidmember0_.AppMstPaid as AppMstPaid1_0_, paidmember0_.AppMstActivate as AppMstAc9_1_0_ from APPMST paidmember0_ where paidmember0_.AppMstID=?

Hibernate: select paymenttra0_.AppMstId as AppMstId0_, paymenttra0_.PaymentFromDate as PaymentF2_3_0_, paymenttra0_.PaymentToDate as PaymentT3_3_0_, paymenttra0_.DispachedAmt as Dispache4_3_0_ from PaymentTranDraft paymenttra0_ where paymenttra0_.AppMstId=?

its realy messy.but its what i get on server.
as you asked:
Do you need a join column in the "left join" definition of the query?

i dint understand exactally what you are asking about.
but if you are asking about common column then it is AppMstId.
and i need Dispatch amount for each id which is in joined table PaymentTranDraft.


and sorry for duplicate posting,it was by mistake.
please reply to solve my problem.
Thanks.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic