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.ftvalue.customer.repository.mapper.ismp.CmCustomerCheckMapper">
  <resultMap id="BaseResultMap" type="com.ftvalue.customer.repository.model.ismp.CmCustomerCheck">
    <result column="ID" jdbcType="DECIMAL" property="id" />
    <result column="ACCOUNT_NO" jdbcType="VARCHAR" property="accountNo" />
    <result column="API_KEY" jdbcType="VARCHAR" property="apiKey" />
    <result column="CUSTOMER_NO" jdbcType="VARCHAR" property="customerNo" />
    <result column="DATE_CREATED" jdbcType="TIMESTAMP" property="dateCreated" />
    <result column="LAST_UPDATED" jdbcType="TIMESTAMP" property="lastUpdated" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="STATUS" jdbcType="VARCHAR" property="status" />
    <result column="TYPE" jdbcType="VARCHAR" property="type" />
    <result column="VERSION" jdbcType="DECIMAL" property="version" />
    <result column="NEED_INVOICE" jdbcType="DECIMAL" property="needInvoice" />
    <result column="POST_FEE" jdbcType="FLOAT" property="postFee" />
    <result column="CUSTOMER_CATEGORY" jdbcType="VARCHAR" property="customerCategory" />
    <result column="CM_CUST_INSTITUTION_ID" jdbcType="DECIMAL" property="cmCustInstitutionId" />
    <result column="CUST_INSTITUTION_ID" jdbcType="DECIMAL" property="custInstitutionId" />
    <result column="IS_VIEW_DATE" jdbcType="DECIMAL" property="isViewDate" />
    <result column="AUDIT_STATUS" jdbcType="VARCHAR" property="auditStatus" />
    <result column="REMARK" jdbcType="VARCHAR" property="remark"/>
    <association property="cmCorporationInfoCheck" javaType="com.ftvalue.customer.repository.model.ismp.CmCorporationInfoCheck" column="ID" select="com.ftvalue.customer.repository.mapper.ismp.CmCorporationInfoCheckMapper.selectByPrimaryKey"/>
    <association property="cmInstitutionAccessoryCheck" javaType="com.ftvalue.customer.repository.model.ismp.CmInstitutionAccessoryCheck" column="ID" select="com.ftvalue.customer.repository.mapper.ismp.CmInstitutionAccessoryCheckMapper.selectInstitutionByCustomerId"/>
    <association property="cmCustomerBankAccountCheck" javaType="com.ftvalue.customer.repository.model.ismp.CmCustomerBankAccountCheck" column="ID" select="com.ftvalue.customer.repository.mapper.ismp.CmCustomerBankAccountCheckMapper.selectCmBankAccountCheckByCustomerId"/>
    <collection property="boCustomerProductCheck" javaType="ArrayList" column="CUSTOMER_NO" ofType="com.ftvalue.customer.repository.model.boss.BoCustomerProductCheck" select="com.ftvalue.customer.repository.mapper.boss.BoCustomerProductCheckMapper.selectBoCustomerProductCheckByCustomerNo"/>
  </resultMap>
  <insert id="insertCustomerCheck" parameterType="com.ftvalue.customer.repository.model.ismp.CmCustomerCheck">
    <selectKey keyProperty="id" order="BEFORE" resultType="java.math.BigDecimal">
      SELECT SEQ_CM_CUSTOMER_CHECK.nextval AS id FROM dual
    </selectKey>
    insert into CM_CUSTOMER_CHECK (id, ACCOUNT_NO, API_KEY,
      CUSTOMER_NO, DATE_CREATED, LAST_UPDATED, 
      NAME, STATUS, TYPE, 
      VERSION, NEED_INVOICE, POST_FEE, 
      CUSTOMER_CATEGORY, CM_CUST_INSTITUTION_ID, 
      CUST_INSTITUTION_ID, IS_VIEW_DATE, AUDIT_STATUS,
      REMARK)
    values (#{id,jdbcType=DECIMAL}, #{accountNo,jdbcType=VARCHAR}, #{apiKey,jdbcType=VARCHAR}, 
      #{customerNo,jdbcType=VARCHAR}, CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ,
      #{name,jdbcType=VARCHAR}, #{status,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR},
      0, #{needInvoice,jdbcType=DECIMAL}, #{postFee,jdbcType=FLOAT},
      #{customerCategory,jdbcType=VARCHAR}, #{cmCustInstitutionId,jdbcType=DECIMAL}, 
      #{custInstitutionId,jdbcType=DECIMAL}, #{isViewDate,jdbcType=DECIMAL}, #{auditStatus,jdbcType=VARCHAR},
      #{remark,jdbcType=VARCHAR})
  </insert>
  <select id="countCustomerCheck"  resultType="java.lang.Long">
    select count(*) from CM_CUSTOMER_CHECK
  </select>
  <update id="updateCustomerCheckById" parameterType="com.ftvalue.customer.repository.model.ismp.CmCustomerCheck">
    update CM_CUSTOMER_CHECK
    <set>
      <if test="id != null">
        ID = #{id,jdbcType=DECIMAL},
      </if>
      <if test="accountNo != null">
        ACCOUNT_NO = #{accountNo,jdbcType=VARCHAR},
      </if>
        API_KEY = #{apiKey,jdbcType=VARCHAR},
      <if test="customerNo != null">
        CUSTOMER_NO = #{customerNo,jdbcType=VARCHAR},
      </if>
      <if test="dateCreated != null">
        DATE_CREATED = #{dateCreated,jdbcType=TIMESTAMP},
      </if>
        LAST_UPDATED = CURRENT_TIMESTAMP ,
      <if test="name != null">
        NAME = #{name,jdbcType=VARCHAR},
      </if>
      <if test="status != null">
        STATUS = #{status,jdbcType=VARCHAR},
      </if>
      <if test="type != null">
        TYPE = #{type,jdbcType=VARCHAR},
      </if>
      <if test="version != null">
        VERSION = #{version,jdbcType=DECIMAL},
      </if>
      <if test="needInvoice != null">
        NEED_INVOICE = #{needInvoice,jdbcType=DECIMAL},
      </if>
      <if test="postFee != null">
        POST_FEE = #{postFee,jdbcType=FLOAT},
      </if>
      <if test="customerCategory != null">
        CUSTOMER_CATEGORY = #{customerCategory,jdbcType=VARCHAR},
      </if>
      <if test="cmCustInstitutionId != null">
        CM_CUST_INSTITUTION_ID = #{cmCustInstitutionId,jdbcType=DECIMAL},
      </if>
      <if test="custInstitutionId != null">
        CUST_INSTITUTION_ID = #{custInstitutionId,jdbcType=DECIMAL},
      </if>
      <if test="isViewDate != null">
        IS_VIEW_DATE = #{isViewDate,jdbcType=DECIMAL},
      </if>
      <if test="auditStatus != null">
        AUDIT_STATUS = #{auditStatus,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        REMARK = #{remark,jdbcType=VARCHAR},
      </if>
    </set>
    WHERE  ID = #{id,jdbcType=DECIMAL}
  </update>
  
  <select id="selectCmCustomerCheckByCustomerNo" parameterType="java.lang.String" resultMap="BaseResultMap">
   SELECT  * FROM CM_CUSTOMER_CHECK
   WHERE CUSTOMER_NO = #{customerNo,jdbcType=VARCHAR}
   AND AUDIT_STATUS = 'nosubmit'
  </select>

  <select id="selectCmCustomerCheckPassByCustomerNo" parameterType="java.lang.String" resultMap="BaseResultMap">
    SELECT  * FROM CM_CUSTOMER_CHECK
    WHERE CUSTOMER_NO = #{customerNo,jdbcType=VARCHAR}
    AND AUDIT_STATUS = 'pass'
  </select>
  
  <select id="selectCmCustomerCheckById" parameterType="java.math.BigDecimal" resultMap="BaseResultMap">
    SELECT  * FROM  CM_CUSTOMER_CHECK
    WHERE  ID = #{id,jdbcType=DECIMAL}
  </select>

  <select id="selectCmCustomerById" parameterType="java.math.BigDecimal" resultMap="BaseResultMap">
    SELECT  *  FROM  CM_CUSTOMER_CHECK
    WHERE  ID = #{id,jdbcType=DECIMAL}
    AND AUDIT_STATUS = 'nosubmit'
  </select>

  <select id="selectAgentList" parameterType="map" resultMap="BaseResultMap">
    SELECT * FROM
    (SELECT A.*,ROWNUM RN FROM
    (SELECT c.ID,c.ACCOUNT_NO,c.CUSTOMER_NO,c.DATE_CREATED,c.NAME,c.STATUS,c.TYPE,c.AUDIT_STATUS FROM CM_CUSTOMER_CHECK c,CM_CORPORATION_INFO_CHECK i
    <trim prefix="WHERE" prefixOverrides="AND">
      AND i.ID = c.ID
      AND i.SOURCE_CLIENT_NO = #{sourceClientNo,jdbcType=VARCHAR}
      AND i.SOURCE_TYPE = #{sourceType,jdbcType=VARCHAR}
      <if test="name != null">
        AND c.NAME = #{name,jdbcType=VARCHAR}
      </if>
      <if test="status != null">
        AND c.AUDIT_STATUS = #{status,jdbcType=VARCHAR}
      </if>
      <if test="status == null">
        AND c.AUDIT_STATUS in ('pendingaudit','refuse','nosubmit')
      </if>
    </trim>
    ORDER BY c.DATE_CREATED DESC) A
    WHERE ROWNUM <= #{limit}
    )
    WHERE RN > #{offset}
  </select>

  <select id="selectCount" parameterType="map" resultType="java.lang.String">
    SELECT COUNT(1) FROM CM_CUSTOMER_CHECK c,CM_CORPORATION_INFO_CHECK i
    <trim prefix="WHERE" prefixOverrides="AND">
      AND i.ID = c.ID
      AND i.SOURCE_CLIENT_NO = #{sourceClientNo,jdbcType=VARCHAR}
      AND i.SOURCE_TYPE = #{sourceType,jdbcType=VARCHAR}
      <if test="name != null">
        AND c.NAME = #{name,jdbcType=VARCHAR}
      </if>
      <if test="status != null">
        AND c.STATUS = #{status,jdbcType=VARCHAR}
      </if>
      <if test="status == null">
        AND c.AUDIT_STATUS in ('pendingaudit','refuse','nosubmit')
      </if>
    </trim>
    ORDER BY c.DATE_CREATED DESC
  </select>

  <select id="selectCustomerByCustomerNo" parameterType="java.lang.String" resultMap="BaseResultMap">
      SELECT * FROM CM_CUSTOMER_CHECK
      WHERE  CUSTOMER_NO = #{customerNo,jdbcType=VARCHAR}
  </select>
  
  <select id="selectCannotUpdateCmCustomerCheckByCustomerNo" parameterType="java.lang.String" resultType="java.lang.Integer">
    SELECT COUNT(*) FROM CM_CUSTOMER_CHECK
    WHERE CUSTOMER_NO = #{customerNo,jdbcType=VARCHAR}
    AND AUDIT_STATUS IN ('pendingaudit','nosubmit')
    AND  REMARK IS  NULL
  </select>
</mapper>