Hi,
I trying to join two tables.
The table and its details are as follows,
TANNUAL_STMT_LINE --- A
TRESERVE_FACTOR -- B
Trying to add table B (TRESERVE_FACTOR) as collection in table A (TANNUAL_STMT_LINE)
The mapping file is given below,
Code:
<hibernate-mapping>
<class name="icp.entity.TannualStmtLine" table="REPORTER.TANNUAL_STMT_LINE">
<id column="ANNUAL_STMT_LN_ID" name="annualStmtLnId" type="string">
<generator class="assigned"/>
</id>
<property name="annualStmtLnNm" type="string">
<column length="30" name="ANNUAL_STMT_LN_NM" not-null="true"/>
</property>
<property name="updateTs" type="timestamp">
<column length="45" name="UPDATE_TS" not-null="true"/>
</property>
<property name="updUserId" type="string">
<column length="8" name="UPD_USER_ID" not-null="true"/>
</property>
<property name="exceptionIn" type="char">
<column length="1" name="EXCEPTION_IN" not-null="true"/>
</property>
<property name="ovAnnlStmtLnId" type="string">
<column length="3" name="OV_ANNL_STMT_LN_ID" not-null="true"/>
</property>
<set name="treserveFactors" table="REPORTER.TRESERVE_FACTOR">
<key column="ANNUAL_STMT_LN_ID" />
<composite-element class="icp.entity.TreserveFactor">
<nested-composite-element name="treserveFactorPK" class="icp.entity.TreserveFactorPK">
<property name="measureId" column ="MEASURE_ID" not-null="true"/>
<property name="yearNo" column ="YEAR_NO" not-null="true"/>
<property name="quarterNo" column ="QUARTER_NO" not-null="true"/>
<property name="poolCd" column ="POOL_CD" not-null="true"/>
<property name="companyNo" column ="COMPANY_NO" not-null="true"/>
</nested-composite-element>
<property name ="crntResrvFctrPc" column="CRNT_RESRV_FCTR_PC"/>
<property name ="min1ResrvFctrPc" column="MIN1_RESRV_FCTR_PC"/>
<property name ="min2ResrvFctrPc" column="MIN2_RESRV_FCTR_PC"/>
<property name ="min3ResrvFctrPc" column="MIN3_RESRV_FCTR_PC"/>
<property name ="min4ResrvFctrPc" column="MIN4_RESRV_FCTR_PC"/>
<property name ="updateTs" column="UPDATE_TS"/>
<property name ="updUserId" column="UPD_USER_ID"/>
<property name ="min5ResrvFctrPc" column="MIN5_RESRV_FCTR_PC"/>
</composite-element>
</set>
</class>
<sql-query name="findReserveFactors"><![CDATA[
SELECT TannualStmtLine.*,TreserveFactor.*
FROM REPORTER.TANNUAL_STMT_LINE TannualStmtLine
right outer join REPORTER.TRESERVE_FACTOR TreserveFactor
on TannualStmtLine.ANNUAL_STMT_LN_ID = TreserveFactor.ANNUAL_STMT_LN_ID
AND TreserveFactor.YEAR_NO=:year
AND TreserveFactor.QUARTER_NO=:quarter
AND TreserveFactor.MEASURE_ID=:measureid
AND TreserveFactor.COMPANY_NO=:comp
where
TannualStmtLine.ANNUAL_STMT_LN_ID <> '310'
order by TannualStmtLine.ANNUAL_STMT_LN_ID]]>
<return alias ="TannualStmtLine" class="icp.entity.TannualStmtLine"/>
</sql-query>
</hibernate-mapping>
Below is the code snippet used to execute the above query.
Code:
try{
Session session = ICPHibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
org.hibernate.Query hibQry = session.getNamedQuery("findReserveFactors")
.setInteger("year",2001)
.setInteger("quarter",4 )
.setInteger("measureid",25)
.setString("comp","0229");
java.util.List result = hibQry.list();
session.getTransaction().commit();
//java.util.List result =session.getNamedQuery("findreserve").list();
Iterator tann=result.iterator();
while(tann.hasNext()){
TannualStmtLine tanns = (TannualStmtLine)tann.next();
System.out.println(tanns.getAnnualStmtLnId());
System.out.println(tanns.getAnnualStmtLnNm());
TreserveFactor tres= (TreserveFactor)tanns.getTreserveFactors().iterator().next();
System.out.println(tres.getTreserveFactorPK().getAnnualStmtLnId());
System.out.println(tres.getTreserveFactorPK().getCompanyNo());
System.out.println(tres.getTreserveFactorPK().getMeasureId());
}
}catch(HibernateException he) {
he.printStackTrace();
}
The sysout is as follows,
Code:
021
ALLIED LINES
Hibernate: select treservefa0_.ANNUAL_STMT_LN_ID as ANNUAL1_0_, treservefa0_.MEASURE_ID as MEASURE2_0_, treservefa0_.YEAR_NO as YEAR3_0_, treservefa0_.QUARTER_NO as QUARTER4_0_, treservefa0_.POOL_CD as POOL5_0_, treservefa0_.COMPANY_NO as COMPANY6_0_, treservefa0_.CRNT_RESRV_FCTR_PC as CRNT7_0_, treservefa0_.MIN1_RESRV_FCTR_PC as MIN8_0_, treservefa0_.MIN2_RESRV_FCTR_PC as MIN9_0_, treservefa0_.MIN3_RESRV_FCTR_PC as MIN10_0_, treservefa0_.MIN4_RESRV_FCTR_PC as MIN11_0_, treservefa0_.UPDATE_TS as UPDATE12_0_, treservefa0_.UPD_USER_ID as UPD13_0_, treservefa0_.MIN5_RESRV_FCTR_PC as MIN14_0_ from REPORTER.TRESERVE_FACTOR treservefa0_ where treservefa0_.ANNUAL_STMT_LN_ID=?
null
0742
I could not get the ANNUAL_STMT_LN_ID column in the set, which is key column. The mapping may look very complex. But, the mappings are mandatory for the project requirement.
I have two pojo classes for the table TRESERVE_FACTOR, class TreserveFactor and TreserveFactorPK. The class TreserveFactorPK contains properties for primary keys of TRESERVE_FACTOR table. Hence, I have to use the nested-composite-element to do the mapping.
Now, the quetion is I could not map the key of the set. The key ANNUAL_STMT_LN_ID is a primary key. Hence, it needs to be reference inside the nested-composite-element. But, when I tried it, hibernate throws error saying that duplicate mapping. I can't get rid of key from the set.
I need to refer the key ANNUAL_STMT_LN_ID to TreserveFactorPK inside the nested-composite-element. Is it possible? is there any other option ? Help is appericated.
Login
FAQ
Search