mibatis标准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.BasicDemandMapper" >
  <resultMap id="BaseResultMap" type="com.yuanchuangyun.libra.entity.BasicDemand" >
    <id column="BBD_ID" property="id" />
    <result column="BBD_KIND" property="kind" />
    <result column="BBD_BUSINESS_ID" property="businessId" />
    <result column="BBD_IS_CLAIM_NUMBER" property="isClaimNumber" />
    <result column="BBD_CLAIM_NUMBER" property="claimNumber" />
    <result column="BBD_IS_INSTRUCTION_PAGES" property="isInstructionPages" />
    <result column="BBD_INSTRUCTION_PAGES" property="instructionPages" />
    <result column="BBD_IS_PAY_HOMEWORK" property="isPayHomework" />
    <result column="BBD_IS_TECHNOLOGY_MINING" property="isTechnologyMining" />
    <result column="BBD_IS_WE_WRITE_TECHNICAL_DISCLOSURE_BOOKS" property="isWeWriteTechnicalDisclosureBooks" />
    <result column="BBD_IS_MAIL_ATTACHMENT_ENCRYPTION" property="isMailAttachmentEncryption" />
    <result column="BBD_EMAIL_PASSWORD" property="emailPassword" />
    <result column="BBD_IS_NOVELTY_RETRIEVAL" property="isNoveltyRetrieval" />
    <result column="BBD_RETRIEVAL_RANGE" property="retrievalRange" />
    <result column="BBD_IS_RETRIEVAL_REPORT" property="isRetrievalReport" />
    <result column="BBD_IS_RISK_AGENT" property="isRiskAgent" />
    <result column="BBD_IS_COST_REDUCTION" property="isCostReduction" />
    <result column="BBD_COST_REDUCTION_PERCENT" property="costReductionPercent" />
    <result column="BBD_COST_REDUCTION_NUMBER" property="costReductionNumber" />
    <result column="BBD_PERIOD" property="period" />
    <result column="BBD_CREATE_USER_ID" property="createUserId" />
    <result column="BBD_LAST_UPDATE_TIME" property="lastUpdateTime" />
    <result column="BBD_LAST_UPDATE_USER_ID" property="lastUpdateUserId" />
    <result column="BBD_INSTRUCTION" property="instruction" />
    <result column="BBD_OTHER_REMARK" property="otherRemark" />
  </resultMap>
 
  <sql id="Base_Column_List" >
    BBD.ID AS BBD_ID,
    BBD.KIND AS BBD_KIND,
    BBD.BUSINESS_ID AS BBD_BUSINESS_ID,
    BBD.IS_CLAIM_NUMBER AS BBD_IS_CLAIM_NUMBER,
    BBD.CLAIM_NUMBER AS BBD_CLAIM_NUMBER,
    BBD.IS_INSTRUCTION_PAGES AS BBD_IS_INSTRUCTION_PAGES,
    BBD.INSTRUCTION_PAGES AS BBD_INSTRUCTION_PAGES,
    BBD.IS_PAY_HOMEWORK AS BBD_IS_PAY_HOMEWORK,
    BBD.IS_TECHNOLOGY_MINING AS BBD_IS_TECHNOLOGY_MINING,
    BBD.IS_WE_WRITE_TECHNICAL_DISCLOSURE_BOOKS AS BBD_IS_WE_WRITE_TECHNICAL_DISCLOSURE_BOOKS,
    BBD.IS_MAIL_ATTACHMENT_ENCRYPTION AS BBD_IS_MAIL_ATTACHMENT_ENCRYPTION,
    BBD.EMAIL_PASSWORD AS BBD_EMAIL_PASSWORD,
    BBD.IS_NOVELTY_RETRIEVAL AS BBD_IS_NOVELTY_RETRIEVAL,
    BBD.RETRIEVAL_RANGE AS BBD_RETRIEVAL_RANGE,
    BBD.IS_RETRIEVAL_REPORT AS BBD_IS_RETRIEVAL_REPORT,
    BBD.IS_RISK_AGENT AS BBD_IS_RISK_AGENT,
    BBD.IS_COST_REDUCTION AS BBD_IS_COST_REDUCTION,
    BBD.COST_REDUCTION_PERCENT AS BBD_COST_REDUCTION_PERCENT,
    BBD.COST_REDUCTION_NUMBER AS BBD_COST_REDUCTION_NUMBER,
    BBD.PERIOD AS BBD_PERIOD,
    BBD.CREATE_USER_ID AS BBD_CREATE_USER_ID,
    BBD.LAST_UPDATE_TIME AS BBD_LAST_UPDATE_TIME,
    BBD.LAST_UPDATE_USER_ID AS BBD_LAST_UPDATE_USER_ID,
    BBD.INSTRUCTION AS BBD_INSTRUCTION,
    BBD.OTHER_REMARK AS BBD_OTHER_REMARK
  </sql>
 
  <select id="getAllData" resultMap="BaseResultMap" >
    select   <include refid="Base_Column_List" />
    from BIZ_BASIC_DEMAND BBD
 
  </select>
 
  <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select   <include refid="Base_Column_List" />
    from BIZ_BASIC_DEMAND BBD
    where BBD.ID= #{id}
  </select>
 
  <select id="getByEntity" resultMap="BaseResultMap" parameterType="com.yuanchuangyun.libra.entity.BasicDemand" >
    select   <include refid="Base_Column_List" />
    from BIZ_BASIC_DEMAND BBD
    <where>
      <trim prefixOverrides="AND |OR ">
        <if test="id != null" > 
          AND BBD.ID = #{id}
        </if> 
        <if test="kind != null and kind !='' " > 
          AND BBD.KIND = #{kind}
        </if> 
        <if test="businessId != null and businessId !='' " > 
          AND BBD.BUSINESS_ID = #{businessId}
        </if> 
        <if test="isClaimNumber != null and isClaimNumber !='' " > 
          AND BBD.IS_CLAIM_NUMBER = #{isClaimNumber}
        </if> 
        <if test="claimNumber != null and claimNumber !='' " > 
          AND BBD.CLAIM_NUMBER = #{claimNumber}
        </if> 
        <if test="isInstructionPages != null and isInstructionPages !='' " > 
          AND BBD.IS_INSTRUCTION_PAGES = #{isInstructionPages}
        </if> 
        <if test="instructionPages != null and instructionPages !='' " > 
          AND BBD.INSTRUCTION_PAGES = #{instructionPages}
        </if> 
        <if test="isPayHomework != null and isPayHomework !='' " > 
          AND BBD.IS_PAY_HOMEWORK = #{isPayHomework}
        </if> 
        <if test="isTechnologyMining != null and isTechnologyMining !='' " > 
          AND BBD.IS_TECHNOLOGY_MINING = #{isTechnologyMining}
        </if> 
        <if test="isWeWriteTechnicalDisclosureBooks != null and isWeWriteTechnicalDisclosureBooks !='' " > 
          AND BBD.IS_WE_WRITE_TECHNICAL_DISCLOSURE_BOOKS = #{isWeWriteTechnicalDisclosureBooks}
        </if> 
        <if test="isMailAttachmentEncryption != null and isMailAttachmentEncryption !='' " > 
          AND BBD.IS_MAIL_ATTACHMENT_ENCRYPTION = #{isMailAttachmentEncryption}
        </if> 
        <if test="emailPassword != null and emailPassword !='' " > 
          AND BBD.EMAIL_PASSWORD = #{emailPassword}
        </if> 
        <if test="isNoveltyRetrieval != null and isNoveltyRetrieval !='' " > 
          AND BBD.IS_NOVELTY_RETRIEVAL = #{isNoveltyRetrieval}
        </if> 
        <if test="retrievalRange != null and retrievalRange !='' " > 
          AND BBD.RETRIEVAL_RANGE = #{retrievalRange}
        </if> 
        <if test="isRetrievalReport != null and isRetrievalReport !='' " > 
          AND BBD.IS_RETRIEVAL_REPORT = #{isRetrievalReport}
        </if> 
        <if test="isRiskAgent != null and isRiskAgent !='' " > 
          AND BBD.IS_RISK_AGENT = #{isRiskAgent}
        </if> 
        <if test="isCostReduction != null and isCostReduction !='' " > 
          AND BBD.IS_COST_REDUCTION = #{isCostReduction}
        </if> 
        <if test="costReductionPercent != null and costReductionPercent !='' " > 
          AND BBD.COST_REDUCTION_PERCENT = #{costReductionPercent}
        </if> 
        <if test="costReductionNumber != null and costReductionNumber !='' " > 
          AND BBD.COST_REDUCTION_NUMBER = #{costReductionNumber}
        </if> 
        <if test="period != null" > 
          AND BBD.PERIOD = #{period}
        </if> 
        <if test="createUserId != null and createUserId !='' " > 
          AND BBD.CREATE_USER_ID = #{createUserId}
        </if> 
        <if test="lastUpdateTime != null" > 
          AND BBD.LAST_UPDATE_TIME = #{lastUpdateTime}
        </if> 
        <if test="lastUpdateUserId != null and lastUpdateUserId !='' " > 
          AND BBD.LAST_UPDATE_USER_ID = #{lastUpdateUserId}
        </if> 
        <if test="instruction != null and instruction !='' " > 
          AND BBD.INSTRUCTION = #{instruction}
        </if> 
        <if test="otherRemark != null and otherRemark !='' " > 
          AND BBD.OTHER_REMARK = #{otherRemark}
        </if> 
      </trim>
    </where>
 
  </select>
 
  <delete id="deleteById" parameterType="java.lang.String" >
    delete from BIZ_BASIC_DEMAND
    where ID = #{id}
  </delete>
 
  <delete id="deleteByIds" parameterType="java.util.List" >
    delete from BIZ_BASIC_DEMAND
    where ID in 
    <foreach item="item" index="index" collection="list" 
    open="(" separator="," close=")"> 
      #{item} 
    </foreach>
  </delete>
 
  <insert id="save" parameterType="com.yuanchuangyun.libra.entity.BasicDemand" >
    <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
        SELECT UUID() AS ID FROM DUAL
    </selectKey>
    insert into BIZ_BASIC_DEMAND(
                    ID,
                    KIND,
                    BUSINESS_ID,
                    IS_CLAIM_NUMBER,
                    CLAIM_NUMBER,
                    IS_INSTRUCTION_PAGES,
                    INSTRUCTION_PAGES,
                    IS_PAY_HOMEWORK,
                    IS_TECHNOLOGY_MINING,
                    IS_WE_WRITE_TECHNICAL_DISCLOSURE_BOOKS,
                    IS_MAIL_ATTACHMENT_ENCRYPTION,
                    EMAIL_PASSWORD,
                    IS_NOVELTY_RETRIEVAL,
                    RETRIEVAL_RANGE,
                    IS_RETRIEVAL_REPORT,
                    IS_RISK_AGENT,
                    IS_COST_REDUCTION,
                    COST_REDUCTION_PERCENT,
                    COST_REDUCTION_NUMBER,
                    PERIOD,
                    CREATE_USER_ID,
                    LAST_UPDATE_TIME,
                    LAST_UPDATE_USER_ID,
                    INSTRUCTION,
                    OTHER_REMARK)
    values (
                    #{id},
                    #{kind},
                    #{businessId},
                    #{isClaimNumber},
                    #{claimNumber},
                    #{isInstructionPages},
                    #{instructionPages},
                    #{isPayHomework},
                    #{isTechnologyMining},
                    #{isWeWriteTechnicalDisclosureBooks},
                    #{isMailAttachmentEncryption},
                    #{emailPassword},
                    #{isNoveltyRetrieval},
                    #{retrievalRange},
                    #{isRetrievalReport},
                    #{isRiskAgent},
                    #{isCostReduction},
                    #{costReductionPercent},
                    #{costReductionNumber},
                    #{period},
                    #{createUserId},
                    #{lastUpdateTime},
                    #{lastUpdateUserId},
                    #{instruction},
                    #{otherRemark})
  </insert>
 
  <update id="update" parameterType="com.yuanchuangyun.libra.entity.BasicDemand" >
    update BIZ_BASIC_DEMAND
    <set>
      <if test="kind != null" > 
        KIND = #{kind},
      </if> 
      <if test="businessId != null" > 
        BUSINESS_ID = #{businessId},
      </if> 
      <if test="isClaimNumber != null" > 
        IS_CLAIM_NUMBER = #{isClaimNumber},
      </if> 
      <if test="claimNumber != null" > 
        CLAIM_NUMBER = #{claimNumber},
      </if> 
      <if test="isInstructionPages != null" > 
        IS_INSTRUCTION_PAGES = #{isInstructionPages},
      </if> 
      <if test="instructionPages != null" > 
        INSTRUCTION_PAGES = #{instructionPages},
      </if> 
      <if test="isPayHomework != null" > 
        IS_PAY_HOMEWORK = #{isPayHomework},
      </if> 
      <if test="isTechnologyMining != null" > 
        IS_TECHNOLOGY_MINING = #{isTechnologyMining},
      </if> 
      <if test="isWeWriteTechnicalDisclosureBooks != null" > 
        IS_WE_WRITE_TECHNICAL_DISCLOSURE_BOOKS = #{isWeWriteTechnicalDisclosureBooks},
      </if> 
      <if test="isMailAttachmentEncryption != null" > 
        IS_MAIL_ATTACHMENT_ENCRYPTION = #{isMailAttachmentEncryption},
      </if> 
      <if test="emailPassword != null" > 
        EMAIL_PASSWORD = #{emailPassword},
      </if> 
      <if test="isNoveltyRetrieval != null" > 
        IS_NOVELTY_RETRIEVAL = #{isNoveltyRetrieval},
      </if> 
      <if test="retrievalRange != null" > 
        RETRIEVAL_RANGE = #{retrievalRange},
      </if> 
      <if test="isRetrievalReport != null" > 
        IS_RETRIEVAL_REPORT = #{isRetrievalReport},
      </if> 
      <if test="isRiskAgent != null" > 
        IS_RISK_AGENT = #{isRiskAgent},
      </if> 
      <if test="isCostReduction != null" > 
        IS_COST_REDUCTION = #{isCostReduction},
      </if> 
      <if test="costReductionPercent != null" > 
        COST_REDUCTION_PERCENT = #{costReductionPercent},
      </if> 
      <if test="costReductionNumber != null" > 
        COST_REDUCTION_NUMBER = #{costReductionNumber},
      </if> 
      <if test="period != null" > 
        PERIOD = #{period},
      </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="instruction != null" > 
        INSTRUCTION = #{instruction},
      </if> 
      <if test="otherRemark != null" > 
        OTHER_REMARK = #{otherRemark},
      </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.ProcdefRelationMapper" >
  <resultMap id="BaseResultMap" type="com.yuanchuangyun.libra.entity.ProcdefRelation" >
    <id column="BPR_ID" property="id" />
    <result column="BPR_TRACK_CODE" property="trackCode" />
    <result column="BPR_FROM_COUNTRY_CODE" property="fromCountryCode" />
    <result column="BPR_TO_COUNTRY_CODE" property="toCountryCode" />
    <result column="BPR_STYLE_CODE" property="styleCode" />
    <result column="BPR_STAGES_ID" property="stagesId" />
    <result column="BPR_INSTRUCTION" property="instruction" />
    <result column="BPR_CREATE_USER_ID" property="createUserId" />
    <result column="BPR_CREATE_TIME" property="createTime" />
    <result column="BPR_LAST_UPDATE_USER_ID" property="lastUpdateUserId" />
    <result column="BPR_LAST_UPDATE_TIME" property="lastUpdateTime" />
  <association property="style" resultMap="com.yuanchuangyun.libra.mapper.StyleMapper.BaseResultMap"/>
    <association property="fromCountry" resultMap="com.yuanchuangyun.libra.mapper.CountryMapper.BaseResultMap"/>
    <association property="toCountry" resultMap="com.yuanchuangyun.libra.mapper.CountryMapper.BaseResultMap1"/>
    <association property="track" resultMap="com.yuanchuangyun.libra.mapper.TrackMapper.BaseResultMap"/>  
    <association  property="stages" resultMap="com.yuanchuangyun.libra.mapper.StagesMapper.BaseResultMap"/>
  </resultMap>
 
  <sql id="Base_Column_List" >
    BPR.ID AS BPR_ID,
    BPR.TRACK_CODE AS BPR_TRACK_CODE,
    BPR.FROM_COUNTRY_CODE AS BPR_FROM_COUNTRY_CODE,
    BPR.TO_COUNTRY_CODE AS BPR_TO_COUNTRY_CODE,
    BPR.STYLE_CODE AS BPR_STYLE_CODE,
    BPR.STAGES_ID AS BPR_STAGES_ID,
    BPR.INSTRUCTION AS BPR_INSTRUCTION,
    BPR.CREATE_USER_ID AS BPR_CREATE_USER_ID,
    BPR.CREATE_TIME AS BPR_CREATE_TIME,
    BPR.LAST_UPDATE_USER_ID AS BPR_LAST_UPDATE_USER_ID,
    BPR.LAST_UPDATE_TIME AS BPR_LAST_UPDATE_TIME
  </sql>
 
  <select id="getAllData" resultMap="BaseResultMap" >
    select   <include refid="Base_Column_List" />
    from BIZ_PROCDEF_RELATION BPR
    order by BPR.CREATE_TIME desc
  </select>
 
  <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select   <include refid="Base_Column_List" />
    from BIZ_PROCDEF_RELATION BPR
    where BPR.ID= #{id}
  </select>
 
  <select id="getByEntity" resultMap="BaseResultMap" parameterType="com.yuanchuangyun.libra.entity.ProcdefRelation" >
    select   <include refid="Base_Column_List" />,
         <include refid="com.yuanchuangyun.libra.mapper.StyleMapper.Base_Column_List"/>,
             <include refid="com.yuanchuangyun.libra.mapper.CountryMapper.Base_Column_List"/>,
             <include refid="com.yuanchuangyun.libra.mapper.CountryMapper.Base_Column_List1"/>,
             <include refid="com.yuanchuangyun.libra.mapper.TrackMapper.Base_Column_List"/>,
             <include refid="com.yuanchuangyun.libra.mapper.StagesMapper.Base_Column_List"/>
    from BIZ_PROCDEF_RELATION BPR
    left join BIZ_STYLE STY on BPR.STYLE_CODE=STY.CODE
    left join BIZ_COUNTRY COU on BPR.FROM_COUNTRY_CODE=COU.CODE
    left join BIZ_COUNTRY COU1 on BPR.TO_COUNTRY_CODE=COU1.CODE
    left join BIZ_TRACK TRA on BPR.TRACK_CODE=TRA.CODE
    left join biz_stages AS SAE ON BPR.`STAGES_ID` = SAE.`ID`
    INNER JOIN biz_procdef_relation_rel AS BPRR ON BPR.ID = BPRR.RELATION_ID
    <where>
      <trim prefixOverrides="AND |OR ">
        <if test="id != null" > 
          AND BPR.ID = #{id}
        </if> 
        <if test="trackCode != null and trackCode !='' " > 
          AND BPR.TRACK_CODE = #{trackCode}
        </if> 
        <if test="fromCountryCode != null and fromCountryCode !='' " > 
          AND BPR.FROM_COUNTRY_CODE = #{fromCountryCode}
        </if> 
        <if test="toCountryCode != null and toCountryCode !='' " > 
          AND BPR.TO_COUNTRY_CODE = #{toCountryCode}
        </if> 
        <if test="styleCode != null and styleCode !='' " > 
          AND BPR.STYLE_CODE = #{styleCode}
        </if> 
        <if test="stagesId != null and stagesId !='' " > 
          AND BPR.STAGES_ID = #{stagesId}
        </if> 
        <if test="instruction != null and instruction !='' " > 
          AND BPR.INSTRUCTION = #{instruction}
        </if> 
        <if test="createUserId != null and createUserId !='' " > 
          AND BPR.CREATE_USER_ID = #{createUserId}
        </if> 
        <if test="createTime != null" > 
          AND BPR.CREATE_TIME = #{createTime}
        </if> 
        <if test="lastUpdateUserId != null and lastUpdateUserId !='' " > 
          AND BPR.LAST_UPDATE_USER_ID = #{lastUpdateUserId}
        </if> 
        <if test="lastUpdateTime != null" > 
          AND BPR.LAST_UPDATE_TIME = #{lastUpdateTime}
        </if>
        <if test="procdefKey != null and procdefKey != '' ">
          AND BPRR.PROCDEF_KEY = #{procdefKey}  
        </if> 
      </trim>
    </where>
    order by BPR.CREATE_TIME desc
  </select>
 
  <delete id="deleteById" parameterType="java.lang.String" >
    delete from BIZ_PROCDEF_RELATION
    where ID = #{id}
  </delete>
 
  <delete id="deleteByIds" parameterType="java.util.List" >
    delete from BIZ_PROCDEF_RELATION
    where ID in 
    <foreach item="item" index="index" collection="list" 
    open="(" separator="," close=")"> 
      #{item} 
    </foreach>
  </delete>
 
  <insert id="save" parameterType="com.yuanchuangyun.libra.entity.ProcdefRelation" >
    <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
        SELECT UUID() AS ID FROM DUAL
    </selectKey>
    insert into BIZ_PROCDEF_RELATION(
                    ID,
                    TRACK_CODE,
                    FROM_COUNTRY_CODE,
                    TO_COUNTRY_CODE,
                    STYLE_CODE,
                    STAGES_ID,
                    INSTRUCTION,
                    CREATE_USER_ID,
                    CREATE_TIME,
                    LAST_UPDATE_USER_ID,
                    LAST_UPDATE_TIME)
    values (
                    #{id},
                    #{trackCode},
                    #{fromCountryCode},
                    #{toCountryCode},
                    #{styleCode},
                    #{stagesId},
                    #{instruction},
                    #{createUserId},
                    #{createTime},
                    #{lastUpdateUserId},
                    #{lastUpdateTime})
  </insert>
 
  <update id="update" parameterType="com.yuanchuangyun.libra.entity.ProcdefRelation" >
    update BIZ_PROCDEF_RELATION
    <set>
      <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="instruction != null" > 
        INSTRUCTION = #{instruction},
      </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>