Oracle

Mapper技巧:

在一个数据库连接中,批量插入,

   
        insert into EQUITY_COMPANY (HFM_CODE,COMPANY_NAME,LOCAL_CODE)
        <foreach collection="list" item="item" separator="UNION ALL">
            select
            #{item.HFMCode},
            #{item.companyName},
            #{item.localCode}
            from dual
        </foreach>

在一个数据库连接中,批量删除(list 为List<String>)

delete from EQUITY_RELATION where EQUITY_KEY IN(
<foreach collection="list" item="item" separator=",">
     #{item}
</foreach>

统一返回模板:查出数据指向BaseResultMap,导向实体EquityRelation

<resultMap id="BaseResultMap" type="com.kpmg.datalake.equity.model.EquityRelation">
       <result column="INVEST_SIDE" jdbcType="VARCHAR" property="investSide" />
       <result column="INVEST_SIDE_TYPE" jdbcType="VARCHAR" property="investSideType" />
       <result column="INVESTED_SIDE" jdbcType="VARCHAR" property="investedSide" />
       <result column="INVESTED_SIDE_TYPE" jdbcType="VARCHAR" property="investedSideType"/>
       <result column="EQUITY_YEAR" jdbcType="VARCHAR" property="equityYear" />
       <result column="EQUITY_MONTH" jdbcType="VARCHAR" property="equityMonth" />
       <result column="EQUITY_RATIO" jdbcType="VARCHAR" property="equityRatio" />
    </resultMap>

<select id="queryTotalRelation"  resultMap="BaseResultMap">
        SELECT
          INVEST_SIDE,
          INVEST_SIDE_TYPE,
          INVESTED_SIDE,
          INVESTED_SIDE_TYPE,
          EQUITY_YEAR,
          EQUITY_MONTH,
          EQUITY_RATIO
        FROM
        Equity_Relation WHERE EQUITY_YEAR = #{year} AND EQUITY_MONTH = #{month}
    </select