mybatis的一对多，多对一，以及多对对的配置和使用

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.yuanchuangyun.libra.mapper.AgencyMapper" >
  <resultMap id="BaseResultMap" type="com.yuanchuangyun.libra.entity.Agency" >
    <id column="AEY_ID" property="id" />
    <result column="AEY_AREA" property="area" />
    <result column="AEY_ORGANIZATION_NAME" property="organizationName" />
    <result column="AEY_ORGANIZATION_NAME_ABBR" property="organizationNameAbbr" />
    <result column="AEY_ADDRESS" property="address" />
    <result column="AEY_COUNTRY_ID" property="countryId" />
     <result column="APT_CREATE_TIME" property="createTime" />
    <result column="APT_CREATE_USER_ID" property="createUserId" />
    <result column="APT_LAST_UPDATE_TIME" property="lastUpdateTime" />
    <result column="APT_LAST_UPDATE_USER_ID" property="lastUpdateUserId" />
    <result column="APT_DELETE_TIME" property="deleteTime" />
    <result column="APT_DELETE_USER_ID" property="deleteUserId" />
    <result column="APT_MARK_DELETE" property="markDelete" />
    <association property="country" resultMap="com.yuanchuangyun.libra.mapper.CountryMapper.BaseResultMap" />
  </resultMap>
 
  <sql id="Base_Column_List" >
    AEY.ID AS AEY_ID,
    AEY.AREA AS AEY_AREA,
    AEY.ORGANIZATION_NAME AS AEY_ORGANIZATION_NAME,
    AEY.ORGANIZATION_NAME_ABBR AS AEY_ORGANIZATION_NAME_ABBR,
    AEY.ADDRESS AS AEY_ADDRESS,
    AEY.COUNTRY_ID AS AEY_COUNTRY_ID,
    AEY.CREATE_TIME AS AEY_CREATE_TIME,
    AEY.CREATE_USER_ID AS AEY_CREATE_USER_ID,
    AEY.LAST_UPDATE_TIME AS AEY_LAST_UPDATE_TIME,
    AEY.LAST_UPDATE_USER_ID AS AEY_LAST_UPDATE_USER_ID,
    AEY.DELETE_TIME AS AEY_DELETE_TIME,
    AEY.DELETE_USER_ID AS AEY_DELETE_USER_ID,
    AEY.MARK_DELETE AS AEY_MARK_DELETE
  </sql>
 
  <select id="getAllData" resultMap="BaseResultMap" >
    select   <include refid="Base_Column_List" />
    from BIZ_AGENCY AEY
  ORDER BY AEY.CREATE_TIME desc
  </select>
 
  <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select   <include refid="Base_Column_List" />,
     <include refid="com.yuanchuangyun.libra.mapper.CountryMapper.Base_Column_List"/>
    from BIZ_AGENCY AEY
    left join BIZ_COUNTRY COU on AEY.COUNTRY_ID = COU.ID
    where AEY.ID= #{id}
    and  AEY.MARK_DELETE = 0
    ORDER BY AEY.CREATE_TIME desc
  </select>
 
  <select id="getByEntity" resultMap="BaseResultMap" parameterType="com.yuanchuangyun.libra.entity.Agency" >
    select   <include refid="Base_Column_List" />,
    <include refid="com.yuanchuangyun.libra.mapper.CountryMapper.Base_Column_List"/>
    from BIZ_AGENCY AEY
    left join BIZ_COUNTRY COU on AEY.COUNTRY_ID = COU.ID
    <where>
      <trim prefixOverrides="AND |OR ">
        <if test="id != null" > 
          AND AEY.ID = #{id}
        </if> 
        <if test="area != null" > 
          AND AEY.AREA = #{area}
        </if> 
        <if test="organizationName != null" > 
          AND AEY.ORGANIZATION_NAME = #{organizationName}
        </if> 
        <if test="organizationNameAbbr != null" > 
          AND AEY.ORGANIZATION_NAME_ABBR = #{organizationNameAbbr}
        </if> 
        <if test="address != null" > 
          AND AEY.ADDRESS = #{address}
        </if>
        <if test="countryId != null" > 
          AND AEY.COUNTRY_ID = #{countryId}
        </if>
        <if test="createTime != null" > 
          AND AEY.CREATE_TIME = #{createTime}
        </if> 
        <if test="createUserId != null and createUserId !='' " > 
          AND AEY.CREATE_USER_ID = #{createUserId}
        </if> 
        <if test="lastUpdateTime != null" > 
          AND AEY.LAST_UPDATE_TIME = #{lastUpdateTime}
        </if> 
        <if test="lastUpdateUserId != null and lastUpdateUserId !='' " > 
          AND AEY.LAST_UPDATE_USER_ID = #{lastUpdateUserId}
        </if> 
        <if test="deleteTime != null" > 
          AND AEY.DELETE_TIME = #{deleteTime}
        </if> 
        <if test="deleteUserId != null and deleteUserId !='' " > 
          AND AEY.DELETE_USER_ID = #{deleteUserId}
        </if> 
        AND AEY.MARK_DELETE = 0
        ORDER BY AEY.CREATE_TIME desc
      </trim>
    </where>
 
  </select>
 
  <delete id="deleteById" parameterType="java.lang.String" >
     update BIZ_AGENCY set DELETE_TIME=now(),MARK_DELETE=1
    where ID = #{id}
  </delete>
 
  <delete id="deleteByIds" parameterType="java.util.List" >
    update BIZ_AGENCY set DELETE_TIME=now(),MARK_DELETE=1
    where ID in 
    <foreach item="item" index="index" collection="list" 
    open="(" separator="," close=")"> 
      #{item} 
    </foreach>
  </delete>
 
  <insert id="save" parameterType="com.yuanchuangyun.libra.entity.Agency" >
    <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
        SELECT UUID() AS ID FROM DUAL
    </selectKey>
    insert into BIZ_AGENCY(
                    ID,
                    AREA,
                    ORGANIZATION_NAME,
                    ORGANIZATION_NAME_ABBR,
                    ADDRESS,
                    COUNTRY_ID,
                    CREATE_TIME,
                    CREATE_USER_ID,
                    LAST_UPDATE_TIME,
                    LAST_UPDATE_USER_ID,
                    DELETE_TIME,
                    DELETE_USER_ID,
                    MARK_DELETE)
    values (
                    #{id},
                    #{area},
                    #{organizationName},
                    #{organizationNameAbbr},
                    #{address},
                    #{countryId},
                    #{createTime},
                    #{createUserId},
                    #{lastUpdateTime},
                    #{lastUpdateUserId},
                    #{deleteTime},
                    #{deleteUserId},
                    #{markDelete})
  </insert>
 
  <update id="update" parameterType="com.yuanchuangyun.libra.entity.Agency" >
    update BIZ_AGENCY
    <set>
      <if test="area != null" > 
        AREA = #{area},
      </if> 
      <if test="organizationName != null" > 
        ORGANIZATION_NAME = #{organizationName},
      </if> 
      <if test="organizationNameAbbr != null" > 
        ORGANIZATION_NAME_ABBR = #{organizationNameAbbr},
      </if> 
      <if test="address != null" > 
        ADDRESS = #{address},
      </if>
      <if test="countryId != null" > 
        COUNTRY_ID = #{countryId},
      </if> 
      <if test="createTime != null" > 
        CREATE_TIME = #{createTime},
      </if> 
      <if test="createUserId != null" > 
        CREATE_USER_ID = #{createUserId},
      </if> 
      <if test="lastUpdateTime != null" > 
        LAST_UPDATE_TIME = #{lastUpdateTime},
      </if> 
      <if test="lastUpdateUserId != null" > 
        LAST_UPDATE_USER_ID = #{lastUpdateUserId},
      </if> 
      <if test="deleteTime != null" > 
        DELETE_TIME = #{deleteTime},
      </if> 
      <if test="deleteUserId != null" > 
        DELETE_USER_ID = #{deleteUserId},
      </if> 
      <if test="markDelete != null" > 
        MARK_DELETE = #{markDelete}
      </if> 
    </set>
    where ID = #{id}
  </update>
 
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace="com.yuanchuangyun.libra.mapper.TimeTemplateMapper" >

 <resultMap id="BaseResultMap" type="com.yuanchuangyun.libra.entity.TimeTemplate" >

   <id column="TFC_ID" property="id" />

   <result column="TFC_NAME" property="name" />

   <result column="TFC_ENTITY_CODE" property="entityCode" />

   <result column="TFC_TRACK_CODE" property="trackCode" />

   <result column="TFC_FROM_COUNTRY_CODE" property="fromCountryCode" />

   <result column="TFC_TO_COUNTRY_CODE" property="toCountryCode" />

   <result column="TFC_STYLE_CODE" property="styleCode" />

   <result column="TFC_STAGES_ID" property="stagesId" />

   <result column="TFC_TIME_ONE" property="timeOne" />

   <result column="TFC_TIME_ONE_TYPE" property="timeOneType" />

   <result column="TFC_TIME_TWO" property="timeTwo" />

   <result column="TFC_TIME_TWO_TYPE" property="timeTwoType" />

   <result column="TFC_FIRST_REMINDER_DAYS" property="firstReminderDays" />

   <result column="TFC_SECOND_REMINDER_DAYS" property="secondReminderDays" />

   <result column="TFC_THIRD_REMINDER_DAYS" property="thirdReminderDays" />

   <result column="TFC_SUPERVISORY_PERSONNEL_ID" property="supervisoryPersonnelId" />

   <result column="TFC_PRINCIPAL_ID" property="principalId" />

   <result column="TFC_OPERATOR_ID" property="operatorId" />

   <result column="TFC_VERIFICATION_ID" property="verificationId" />

   <result column="TFC_CREATE_USER_ID" property="createUserId" />

   <result column="TFC_CREATE_TIME" property="createTime" />

   <result column="TFC_LAST_UPDATE_USER_ID" property="lastUpdateUserId" />

   <result column="TFC_LAST_UPDATE_TIME" property="lastUpdateTime" />

   <association property="stages" resultMap="com.yuanchuangyun.libra.mapper.StagesMapper.BaseResultMap"></association>

   <collection property="users" resultMap="com.yuanchuangyun.framework.mapper.system.UserMapper.BaseResultMap" ></collection>

 </resultMap>

 <sql id="Base_Column_List" >

   TFC.ID AS TFC_ID,TFC.NAME AS TFC_NAME,TFC.ENTITY_CODE AS TFC_ENTITY_CODE,TFC.TRACK_CODE AS TFC_TRACK_CODE,TFC.FROM_COUNTRY_CODE AS TFC_FROM_COUNTRY_CODE,TFC.TO_COUNTRY_CODE AS TFC_TO_COUNTRY_CODE,TFC.STYLE_CODE AS TFC_STYLE_CODE,TFC.STAGES_ID AS TFC_STAGES_ID,TFC.TIME_ONE AS TFC_TIME_ONE,TFC.TIME_ONE_TYPE AS TFC_TIME_ONE_TYPE,TFC.TIME_TWO AS TFC_TIME_TWO,TFC.TIME_TWO_TYPE AS TFC_TIME_TWO_TYPE,TFC.FIRST_REMINDER_DAYS AS TFC_FIRST_REMINDER_DAYS,TFC.SECOND_REMINDER_DAYS AS TFC_SECOND_REMINDER_DAYS,TFC.THIRD_REMINDER_DAYS AS TFC_THIRD_REMINDER_DAYS,

   TFC.SUPERVISORY_PERSONNEL_ID AS TFC_SUPERVISORY_PERSONNEL_ID,

   TFC.PRINCIPAL_ID AS TFC_PRINCIPAL_ID,

   TFC.OPERATOR_ID AS TFC_OPERATOR_ID,

   TFC.VERIFICATION_ID AS TFC_VERIFICATION_ID,

   TFC.CREATE_USER_ID AS TFC_CREATE_USER_ID,TFC.CREATE_TIME AS TFC_CREATE_TIME,TFC.LAST_UPDATE_USER_ID AS TFC_LAST_UPDATE_USER_ID,TFC.LAST_UPDATE_TIME AS TFC_LAST_UPDATE_TIME

 </sql>

 <select id="getAllData" resultMap="BaseResultMap" >

   select   <include refid="Base_Column_List" />

   from BIZ_TIME_TEMPLATE TFC

   order by TFC.CREATE_TIME desc

 </select>

 <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.String" >

   select   <include refid="Base_Column_List" />,

   <include

           refid="com.yuanchuangyun.framework.mapper.system.UserMapper.Base_Column_List" />

   from BIZ_TIME_TEMPLATE TFC left join BIZ_LEGAL_TIME_LIMIT_USER_REF CSM

   ON TFC.SUPERVISORY_PERSONNEL_ID =CSM.SOURCE_ID left join PF_USER SU on CSM.USER_ID=SU.ID

   where TFC.ID= #{id}

 </select>

 <select id="getByEntity" resultMap="BaseResultMap" parameterType="com.yuanchuangyun.libra.entity.TimeTemplate" >

   select   <include refid="Base_Column_List" />,

   <include

           refid="com.yuanchuangyun.framework.mapper.system.UserMapper.Base_Column_List" />,

               <include refid="com.yuanchuangyun.libra.mapper.StagesMapper.Base_Column_List"></include>

   from BIZ_TIME_TEMPLATE TFC left join BIZ_LEGAL_TIME_LIMIT_USER_REF CSM

   ON TFC.SUPERVISORY_PERSONNEL_ID =CSM.SOURCE_ID left join PF_USER SU on CSM.USER_ID=SU.ID

   left join BIZ_STAGES SAE on TFC.STAGES_ID = SAE.ID

   <where>

     <trim prefixOverrides="AND |OR ">

       <if test="id != null" >

         AND TFC.ID = #{id}

       </if>

       <if test="name != null and name !='' " >

         AND TFC.NAME = #{name}

       </if>

       <if test="entityCode != null and entityCode !='' " >

         AND TFC.ENTITY_CODE = #{entityCode}

       </if>

       <if test="trackCode != null and trackCode !='' " >

         AND TFC.TRACK_CODE = #{trackCode}

       </if>

       <if test="fromCountryCode != null and fromCountryCode !='' " >

         AND TFC.FROM_COUNTRY_CODE = #{fromCountryCode}

       </if>

       <if test="toCountryCode != null and toCountryCode !='' " >

         AND TFC.TO_COUNTRY_CODE = #{toCountryCode}

       </if>

       <if test="styleCode != null and styleCode !='' " >

         AND TFC.STYLE_CODE like  CONCAT('%','${styleCode}','%' )

       </if>

       <if test="stagesId != null" >

         AND TFC.STAGES_ID = #{stagesId}

       </if>

       <if test="timeOne != null" >

         AND TFC.TIME_ONE = #{timeOne}

       </if>

       <if test="timeOneType != null" >

         AND TFC.TIME_ONE_TYPE = #{timeOneType}

       </if>

       <if test="timeTwo != null" >

         AND TFC.TIME_TWO = #{timeTwo}

       </if>

       <if test="timeTwoType != null" >

         AND TFC.TIME_TWO_TYPE = #{timeTwoType}

       </if>

       <if test="firstReminderDays != null" >

         AND TFC.FIRST_REMINDER_DAYS = #{firstReminderDays}

       </if>

       <if test="secondReminderDays != null" >

         AND TFC.SECOND_REMINDER_DAYS = #{secondReminderDays}

       </if>

       <if test="thirdReminderDays != null" >

         AND TFC.THIRD_REMINDER_DAYS = #{thirdReminderDays}

       </if>

       <if test="supervisoryPersonnelId != null and supervisoryPersonnelId !='' " >

         AND TFC.SUPERVISORY_PERSONNEL_ID = #{supervisoryPersonnelId}

       </if>

       <if test="createUserId != null and createUserId !='' " >

         AND TFC.CREATE_USER_ID = #{createUserId}

       </if>

       <if test="createTime != null" >

         AND TFC.CREATE_TIME = #{createTime}

       </if>

       <if test="lastUpdateUserId != null and lastUpdateUserId !='' " >

         AND TFC.LAST_UPDATE_USER_ID = #{lastUpdateUserId}

       </if>

       <if test="lastUpdateTime != null" >

         AND TFC.LAST_UPDATE_TIME = #{lastUpdateTime}

       </if>

     </trim>

   </where>

   order by TFC.CREATE_TIME desc

 </select>

 <delete id="deleteById" parameterType="java.lang.String" >

   delete from BIZ_TIME_TEMPLATE

   where ID = #{id}

 </delete>

 <delete id="deleteByIds" parameterType="java.util.List" >

   delete from BIZ_TIME_TEMPLATE

   where ID in

   <foreach item="item" index="index" collection="list"

   open="(" separator="," close=")">

     #{item}

   </foreach>

 </delete>

 <insert id="save" parameterType="com.yuanchuangyun.libra.entity.TimeTemplate" >

   <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">

       SELECT UUID() AS ID FROM DUAL

   </selectKey>

   insert into BIZ_TIME_TEMPLATE(

                   ID,

                   NAME,

                   ENTITY_CODE,

                   TRACK_CODE,

                   FROM_COUNTRY_CODE,

                   TO_COUNTRY_CODE,

                   STYLE_CODE,

                   STAGES_ID,

                   TIME_ONE,

                   TIME_ONE_TYPE,

                   TIME_TWO,

                   TIME_TWO_TYPE,

                   FIRST_REMINDER_DAYS,

                   SECOND_REMINDER_DAYS,

                   THIRD_REMINDER_DAYS,

                   SUPERVISORY_PERSONNEL_ID,

                   PRINCIPAL_ID,

                   OPERATOR_ID,

                   VERIFICATION_ID,

                   CREATE_USER_ID,

                   CREATE_TIME,

                   LAST_UPDATE_USER_ID,

                   LAST_UPDATE_TIME)

   values (

                   #{id},

                   #{name},

                   #{entityCode},

                   #{trackCode},

                   #{fromCountryCode},

                   #{toCountryCode},

                   #{styleCode},

                   #{stagesId},

                   #{timeOne},

                   #{timeOneType},

                   #{timeTwo},

                   #{timeTwoType},

                   #{firstReminderDays},

                   #{secondReminderDays},

                   #{thirdReminderDays},

                   #{supervisoryPersonnelId},

                   #{principalId},

                   #{operatorId},

                   #{verificationId},

                   #{createUserId},

                   #{createTime},

                   #{lastUpdateUserId},

                   #{lastUpdateTime})

 </insert>

 <update id="update" parameterType="com.yuanchuangyun.libra.entity.TimeTemplate" >

   update BIZ_TIME_TEMPLATE

   <set>

     <if test="name != null" >

       NAME = #{name},

     </if>

     <if test="entityCode != null" >

       ENTITY_CODE = #{entityCode},

     </if>

     <if test="trackCode != null" >

       TRACK_CODE = #{trackCode},

     </if>

     <if test="fromCountryCode != null" >

       FROM_COUNTRY_CODE = #{fromCountryCode},

     </if>

     <if test="toCountryCode != null" >

       TO_COUNTRY_CODE = #{toCountryCode},

     </if>

     <if test="styleCode != null" >

       STYLE_CODE = #{styleCode},

     </if>

     <if test="stagesId != null" >

       STAGES_ID = #{stagesId},

     </if>

     <if test="timeOne != null" >

       TIME_ONE = #{timeOne},

     </if>

     <if test="timeOneType != null" >

       TIME_ONE_TYPE = #{timeOneType},

     </if>

     <if test="timeTwo != null" >

       TIME_TWO = #{timeTwo},

     </if>

     <if test="timeTwoType != null" >

       TIME_TWO_TYPE = #{timeTwoType},

     </if>

     <if test="firstReminderDays != null" >

       FIRST_REMINDER_DAYS = #{firstReminderDays},

     </if>

     <if test="secondReminderDays != null" >

       SECOND_REMINDER_DAYS = #{secondReminderDays},

     </if>

     <if test="thirdReminderDays != null" >

       THIRD_REMINDER_DAYS = #{thirdReminderDays},

     </if>

     <if test="supervisoryPersonnelId != null" >

       SUPERVISORY_PERSONNEL_ID = #{supervisoryPersonnelId},

     </if>

     <if test="operatorId != null" >

       OPERATOR_ID = #{operatorId},

     </if>

     <if test="verificationId != null" >

       VERIFICATION_ID = #{verificationId},

     </if>

     <if test="principalId != null" >

       PRINCIPAL_ID = #{principalId},

     </if>

     <if test="createUserId != null" >

       CREATE_USER_ID = #{createUserId},

     </if>

     <if test="createTime != null" >

       CREATE_TIME = #{createTime},

     </if>

     <if test="lastUpdateUserId != null" >

       LAST_UPDATE_USER_ID = #{lastUpdateUserId},

     </if>

     <if test="lastUpdateTime != null" >

       LAST_UPDATE_TIME = #{lastUpdateTime},

     </if>

   </set>

   where ID = #{id}

 </update>

</mapper>