Ibatis 多表查询

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="XfMaterialInfo">
 <typeAlias alias="xfmaterialinfo" type="com.weboa.xfadminister.model.XfMaterialInfo"/>
 <typeAlias alias="queryXfMaterialInfo" type="com.weboa.xfadminister.model.QueryXfMaterialInfo"/>
 <resultMap id="xfmaterialinfoResult" class="xfmaterialinfo">
   <result property="id"  column="ID"  jdbcType="NUMBER"/>
   <result property="materialinfoId" column="MATERIALINFO_ID"  jdbcType="NUMBER"/>
   <result property="materialInfo" column="MATERIALINFO_ID"  select="getMaterialInfo"/>
   <result property="taskInstId" column="TASK_INST_ID"  jdbcType="NUMBER"/>
   <result property="xfbusinessLettersId" column="XFBUSINESS_LETTERS_ID"  jdbcType="NUMBER"/>
   <result property="xfchildRecordsId" column="XFCHILD_RECORDS_ID"  jdbcType="NUMBER"/>
 </resultMap>
 
 <!-- set the factor for search -->
 <sql id="queryXfMaterialInfoSql">
  <![CDATA[
   1=1
  ]]>
       <dynamic>
      
  <isNotEmpty property="materialinfoId" prepend="and">
   <![CDATA[
   t.MATERIALINFO_ID=#materialinfoId#
   ]]>
  </isNotEmpty>
  <isNotEmpty property="taskInstId" prepend="and">
   <![CDATA[
   t.TASK_INST_ID=#taskInstId#
   ]]>
  </isNotEmpty>
  <isNotEmpty property="xfbusinessLettersId" prepend="and">
   <![CDATA[
   t.XFBUSINESS_LETTERS_ID=#xfbusinessLettersId#
   ]]>
  </isNotEmpty>
  
  <isNotEmpty property="xfchildRecordsId" prepend="and">
   <![CDATA[
   t.XFCHILD_RECORDS_ID=#xfchildRecordsId#
   ]]>
  </isNotEmpty>
 </dynamic>
   </sql>
  
   <!-- search a xfmaterialinfo information through the id of xfmaterialinfo -->
 <select id="getXfMaterialInfo" resultMap="xfmaterialinfoResult" parameterClass="int">
  <![CDATA[
  SELECT
  *
  FROM T_XFMATERIALINFO WHERE ID=#id#
  ]]>
 </select>
 
   <!-- 业务表ID -->
 <select id="getXfMaterialInfoBYBusines" resultMap="xfmaterialinfoResult" parameterClass="int">
  <![CDATA[
  SELECT
  *
  FROM T_XFMATERIALINFO  WHERE XFBUSINESS_LETTERS_ID=#xfBusinessLettersId# ORDER BY MATERIALINFO_ID
  ]]>
 </select>
 
 <!-- search xfmaterialinfo-informations -->
 <select id="browseXfMaterialInfo" resultMap="xfmaterialinfoResult" parameterClass="queryXfMaterialInfo">
  <![CDATA[                   
   SELECT * FROM(SELECT TV.*, ROWNUM rn FROM
   (SELECT t.* FROM T_XFMATERIALINFO t   WHERE
     ]]>
     <include refid="queryXfMaterialInfoSql"/>
     <![CDATA[
      ORDER BY t.ID)TV WHERE ROWNUM<=$endSize$)WHERE rn>$startSize$
        ]]>
 </select>
 
 
 <!-- 根据环节ID  查找信访材料信息集合 -->
 <select id="getXfMaterialInfoByTaskInstId" resultMap="xfmaterialinfoResult" parameterClass="java.util.Map">
  <![CDATA[
   SELECT
   t.*
   FROM T_XFMATERIALINFO t,T_MATERIALINFO m WHERE t.MATERIALINFO_ID=m.id and t.XFBUSINESS_LETTERS_ID=#xfBusinessLettersId# and (m.FORM_TYPE=1 or t.TASK_INST_ID=#taskInstId#)
    ORDER BY t.MATERIALINFO_ID DESC
     ]]>
 </select>
 
 <!-- 根据环节ID  查找信访材料信息集合 -->
 <select id="getXfBasicAndDealMateBYBusines" resultMap="xfmaterialinfoResult" parameterClass="int">
  <![CDATA[
   SELECT
   t.*
   FROM T_XFMATERIALINFO t,T_MATERIALINFO m WHERE t.MATERIALINFO_ID=m.id and t.XFBUSINESS_LETTERS_ID=#xfBusinessLettersId# and m.FORM_TYPE=1
    ORDER BY t.MATERIALINFO_ID asc
     ]]>
 </select>
 
  <select id="getXfMaterialInfoByxfChildRecordId" resultMap="xfmaterialinfoResult" parameterClass="int">
  <![CDATA[
   SELECT
   t.*
   FROM T_XFMATERIALINFO t,T_MATERIALINFO m WHERE t.MATERIALINFO_ID=m.id and t.XFCHILD_RECORDS_ID=#xfChildRecordsId# 
    ORDER BY t.MATERIALINFO_ID asc
     ]]>
 </select>
 
 <!-- get the item number for search -->
 <!-- SELECT COUNT(t.$dbKeyColName) FROM T_XFMATERIALINFO t WHERE -->
 <select id="getXfMaterialInfoCount" resultClass="int" parameterClass="queryXfMaterialInfo">
  <![CDATA[
         SELECT COUNT(1) FROM T_XFMATERIALINFO t WHERE
     ]]>
     <include refid="queryXfMaterialInfoSql"/>
 </select>
 
 <!-- add a xfmaterialinfo information -->
 <insert id="addXfMaterialInfo" parameterClass="xfmaterialinfo">
  <selectKey resultClass="int" keyProperty="id">
         SELECT XFMATERIALINFO_SEQ.NEXTVAL AS ID FROM DUAL
     </selectKey>
 <![CDATA[
 INSERT INTO T_XFMATERIALINFO(
  ID,
  MATERIALINFO_ID,
  TASK_INST_ID,
  XFBUSINESS_LETTERS_ID,
  XFCHILD_RECORDS_ID
  )VALUES(
   #id#,
    #materialinfoId#,
    #taskInstId#,
    #xfbusinessLettersId#,
    #xfchildRecordsId#
    
   )
 ]]>
 </insert>
 
 <!-- update a xfmaterialinfo information -->
 <update id="updateXfMaterialInfo" parameterClass="xfmaterialinfo">
 <![CDATA[
  UPDATE T_XFMATERIALINFO SET
  MATERIALINFO_ID=#materialinfoId#,
  TASK_INST_ID=#taskInstId#,
  XFBUSINESS_LETTERS_ID=#xfbusinessLettersId#,
  t.XFCHILD_RECORDS_ID=#xfchildRecordsId#
  WHERE ID=#id#
 ]]>
 </update>
 
 <!-- 业务表ID -->
 <delete id="deleteXfMaterialInfoBYBusines" parameterClass="int">
  <![CDATA[
   DELETE FROM T_XFMATERIALINFO WHERE XFBUSINESS_LETTERS_ID=#xfbusinessLettersId#
  ]]>
 </delete>
 
 <!-- delete a xfmaterialinfo information -->
 <delete id="deleteXfMaterialInfo" parameterClass="int">
  <![CDATA[
   DELETE FROM T_XFMATERIALINFO WHERE ID=#id#
  ]]>
 </delete>
</sqlMap>