论坛首页 → Java企业应用论坛 →

hibernate 多表（三表）一对多（联合主键）关联查询

浏览 8950 次
锁定老帖子 主题：hibernate 多表（三表）一对多（联合主键）关联查询
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
作者 正文
  • tolys
  • 等级: 初级会员
  • 性别:
  • 文章: 11
  • 积分: 30
  • 来自: 杭州
   发表时间：2008-03-05  
TblEnter  (1..N) TblEva  (1..N) TblEvaMember
  A                 B                C
三表中各有搜索条件 TBL_A.XXX.. TBL_B.XXX.. TBL_C.XXX.. (A和B通过一个主键关联 ，B和C通过两个联合主键关联)

要求页面显示 ：

TBL_A.id 1
    TBL_B.id1  TBL_B.XXX
    TBL_B.id2  TBL_B.XXX
    TBL_B.id3  TBL_B.XXX
TBL_A.id 2
    TBL_B.id1  TBL_B.XXX
    TBL_B.id2  TBL_B.XXX 
TBL_A.id 3
    TBL_B.id1  TBL_B.XXX
    TBL_B.id2  TBL_B.XXX
    ...

求HQL 或者 SQL实现！
有空的加我MSN:hundsunlin@hotmail.com

映射关系如下：实体Bean略 用Set表示一对多 

<hibernate-mapping>
	<class name="jp.go.mlit.eva.domain.TblEnter" table="TBL_ENTER"
		schema="dbo" catalog="evadb">
		<id name="enterId" type="java.lang.String">
			<column name="ENTER_ID" length="5" />
			<generator class="assigned" />
		</id>
		<many-to-one name="mstOrgByEvaOrg"
			class="jp.go.mlit.eva.domain.MstOrg" fetch="select">
			<column name="EVA_ORG" length="2" not-null="true" />
		</many-to-one>
		<many-to-one name="mstPref"
			class="jp.go.mlit.eva.domain.MstPref" fetch="select">
			<column name="PREFECTURE" length="2" not-null="true" />
		</many-to-one>
		<many-to-one name="mstMode"
			class="jp.go.mlit.eva.domain.MstMode" fetch="select">
			<column name="MODE" length="2" not-null="true" />
		</many-to-one>
		<many-to-one name="mstOrgByJuris"
			class="jp.go.mlit.eva.domain.MstOrg" fetch="select">
			<column name="JURIS" length="2" not-null="true" />
		</many-to-one>
		<property name="enterName" type="java.lang.String">
			<column name="ENTER_NAME" length="200" not-null="true" />
		</property>
		<property name="lastName" type="java.lang.String">
			<column name="LAST_NAME" length="20" />
		</property>
		<property name="firstName" type="java.lang.String">
			<column name="FIRST_NAME" length="20" />
		</property>
		<property name="address" type="java.lang.String">
			<column name="ADDRESS" length="400" />
		</property>
		<property name="url" type="java.lang.String">
			<column name="URL" length="100" />
		</property>
		<property name="deleteFlg" type="java.lang.String">
			<column name="DELETE_FLG" length="1" not-null="true" />
		</property>
		<property name="updatetime" type="java.util.Date">
			<column name="UPDATETIME" length="23" />
		</property>
		<set name="tblEvas" inverse="true">
			<key>
				<column name="ENTER_ID" length="5" not-null="true" />
			</key>
			<one-to-many class="jp.go.mlit.eva.domain.TblEva" />
			<filter name="filterEvas"
				condition="ENTER_ID=:tblEnterId and EVA_ID= :tblEvaId" />
		</set>

		<property name="countEvaInfo"
			formula="(SELECT COUNT(*) FROM TBL_EVA AS t   WHERE  
        	         t.ENTER_ID = ENTER_ID
        	  AND t.DELETE_FLG = 0    
        	  AND t.EVA_DATE &lt;= Getdate()
        	  AND t.EVA_RPT IS NOT NULL  	      	 
        	  )">
		</property>

	</class>

	<filter-def name="filterEvas">
		<filter-param name="tblEnterId" type="java.lang.String" />
		<filter-param name="tblEvaId" type="java.lang.String" />
	</filter-def>

</hibernate-mapping>




<hibernate-mapping>
	<class name="jp.go.mlit.eva.domain.TblEva" table="TBL_EVA"
		schema="dbo" catalog="evadb">
		<composite-id name="id"
			class="jp.go.mlit.eva.domain.TblEvaId">
			<key-many-to-one name="tblEnter"
				class="jp.go.mlit.eva.domain.TblEnter">
				<column name="ENTER_ID" length="5" />
			</key-many-to-one>
			<key-property name="evaId" type="java.lang.String">
				<column name="EVA_ID" length="3" />
			</key-property>
		</composite-id>
		<many-to-one name="mstOrgByEvaOrg"
			class="jp.go.mlit.eva.domain.MstOrg" fetch="select">
			<column name="EVA_ORG" length="2" />
		</many-to-one>
		<many-to-one name="mstPref"
			class="jp.go.mlit.eva.domain.MstPref" fetch="select">
			<column name="PREFECTURE" length="2" />
		</many-to-one>
		<many-to-one name="mstMode"
			class="jp.go.mlit.eva.domain.MstMode" fetch="select">
			<column name="MODE" length="2" />
		</many-to-one>
		<many-to-one name="mstOrgByJuris"
			class="jp.go.mlit.eva.domain.MstOrg" fetch="select">
			<column name="JURIS" length="2" />
		</many-to-one>
		<property name="enterName" type="java.lang.String">
			<column name="ENTER_NAME" length="200" not-null="true" />
		</property>
		<property name="lastName" type="java.lang.String">
			<column name="LAST_NAME" length="20" />
		</property>
		<property name="firstName" type="java.lang.String">
			<column name="FIRST_NAME" length="20" />
		</property>
		<property name="address" type="java.lang.String">
			<column name="ADDRESS" length="400" />
		</property>
		<property name="url" type="java.lang.String">
			<column name="URL" length="100" />
		</property>
		<property name="evaDateDiv" type="java.lang.String">
			<column name="EVA_DATE_DIV" length="1" />
		</property>
		<property name="evaDate" type="java.lang.String">
			<column name="EVA_DATE" length="23" />
		</property>
		<property name="evaRpt"
			type="org.springframework.orm.hibernate3.support.BlobByteArrayType">
			<column name="EVA_RPT" length="1073741823" />
		</property>
		<property name="evaRptUpdater" type="java.lang.String">
			<column name="EVA_RPT_UPDATER" length="92" />
		</property>
		<property name="evaRptUpdatetime" type="java.util.Date">
			<column name="EVA_RPT_UPDATETIME" length="23" />
		</property>
		<property name="deleteFlg" type="java.lang.String">
			<column name="DELETE_FLG" length="1" not-null="true" />
		</property>
		<property name="updatetime" type="java.util.Date">
			<column name="UPDATETIME" length="23" />
		</property>
		<set name="tblEvaMembers" inverse="true">
			<key>
				<column name="ENTER_ID" length="5" not-null="true" />
				<column name="EVA_ID" length="3" not-null="true" />
			</key>
			<one-to-many class="jp.go.mlit.eva.domain.TblEvaMember" />
			<filter name="filterMembers"
				condition="EVA_MEMBER_ID=:memberId" />
		</set>
		<set name="tblEvaInfos" inverse="true">
			<key>
				<column name="ENTER_ID" length="5" not-null="true" />
				<column name="EVA_ID" length="3" not-null="true" />
			</key>
			<one-to-many class="jp.go.mlit.eva.domain.TblEvaInfo" />
		</set>

        <property name="countEvaRelateFile" 
        	 formula = "(
        	   SELECT COUNT(ei.ENTER_ID) 
				  FROM TBL_EVA_INFO ei
			     WHERE ei.DIVISION = 01
			         AND ei.ENTER_ID = ENTER_ID
			         AND ei.EVA_ID = EVA_ID
  		           )">
		</property>
		<property name="countEvaResultFile"
			formula="(
        	   SELECT 
        	   CASE WHEN EVA_RPT IS NOT NULL 
        	   THEN COUNT(ei.ENTER_ID)+1
        	   ELSE COUNT(ei.ENTER_ID)
        	   END
				  FROM TBL_EVA_INFO ei
			    WHERE ei.DIVISION = 02
			        AND ei.ENTER_ID = ENTER_ID
			        AND ei.EVA_ID = EVA_ID
  		           )">
		</property>
	</class>

	<filter-def name="filterMembers">
		<filter-param name="memberId" type="java.lang.String" />
	</filter-def>
</hibernate-mapping>



<hibernate-mapping>
    <class name="jp.go.mlit.eva.domain.TblEvaMember" table="TBL_EVA_MEMBER">
        <composite-id name="id" class="jp.go.mlit.eva.domain.TblEvaMemberId">
            <key-many-to-one name="tblEva" class="jp.go.mlit.eva.domain.TblEva">
                <column name="ENTER_ID" length="5" />
                <column name="EVA_ID" length="3" />
            </key-many-to-one>
            <key-many-to-one name="tblUser" class="jp.go.mlit.eva.domain.TblUser">
                <column name="EVA_MEMBER_ID" />
            </key-many-to-one>
        </composite-id>
        <property name="dspBelong" type="java.lang.String">
            <column name="DSP_BELONG" length="50" not-null="true" />
        </property>
        <property name="dspAuthority" type="java.lang.String">
            <column name="DSP_AUTHORITY" length="10" not-null="true" />
        </property>
        <property name="dspMode" type="java.lang.String">
            <column name="DSP_MODE" length="12" />
        </property>
        <property name="dspUserLastname" type="java.lang.String">
            <column name="DSP_USER_LASTNAME" length="20" not-null="true" />
        </property>
        <property name="dspUserFirstname" type="java.lang.String">
            <column name="DSP_USER_FIRSTNAME" length="20" not-null="true" />
        </property>
        <property name="leaderFlg" type="java.lang.String">
            <column name="LEADER_FLG" length="1" not-null="true" />
        </property>
        <property name="updatetime" type="java.util.Date">
            <column name="UPDATETIME" length="23" />
        </property>
    </class>
</hibernate-mapping>


返回顶楼
         
  • zhongxy0000
  • 等级: 初级会员
  • 性别:
  • 文章: 47
  • 积分: 0
  • 来自: 福州
   发表时间：2008-10-31  
好复杂。。。
返回顶楼
          回帖地址
0 请登录后投票
  • 流浪者A
  • 等级: 初级会员
  • 性别:
  • 文章: 8
  • 积分: 30
  • 来自: 东莞
   发表时间：2008-11-05  
给个简单说明就差不多了·
返回顶楼
          回帖地址
0 请登录后投票
论坛首页 → Java企业应用版

跳转论坛:
Global site tag (gtag.js) - Google Analytics