mybatis中SQL語句運用總結

union 連接查詢  連接兩個表後會過濾掉重覆的值

<resultMap id="BaseResultMap" type="com.sprucetec.pay.etl.model.BillDetail">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="pay_order_no" jdbcType="VARCHAR" property="payOrderNo"/>
        <result column="pay_channel_id" jdbcType="TINYINT" property="payChannelId"/>
        <result column="pay_amount" jdbcType="INTEGER" property="payAmount"/>
        <result column="trans_date" jdbcType="INTEGER" property="transDate"/>
        <result column="trans_type" jdbcType="VARCHAR" property="transType"/>
        <result column="error_type" jdbcType="VARCHAR" property="errorType"/>
        <result column="is_check" jdbcType="TINYINT" property="isCheck"/>
    </resultMap>

  <sql id="condition">
　　<if test="transType != null">
　　　　and trans_type = #{transType,jdbcType=TINYINT}
　　</if>
　　<if test="payChannelId != null">
　　　　and pay_channel_id = #{payChannelId,jdbcType=TINYINT}
　　</if>

　</sql>

<select id="queryList" parameterType="com.pay.BillCheckQuery" resultMap="BaseResultMap">
       select a.pay_order_no,a.trans_date,a.pay_amount,a.pay_channel_id,a.trans_type,a.error_type
       from (select pay_order_no,trans_date,pay_amount,pay_channel_id,trans_type,"無結果" as error_type
       from t_pay_core_order
       where 1 = 1 <include refid="condition"/>
       union 
       select pay_order_no,trans_date,pay_amount,pay_channel_id,trans_type,"缺失" as error_type
       from t_pay_bill_file_detail 
       where 1 = 1 <include refid="condition"/>
       ) as a
       order by a.trans_date desc
       limit #{pageSize} offset #{startRecord}
    </select>

union all 才可以將所有的值都查詢出來，自己將所有的值查詢完總是少，才發現是這個問題

<select id="queryCountAndSum" parameterType="com.BillCheckQuery" resultMap="BaseResultMap">
       select sum(a.pay_amount) as trans_amount,count(1) as trans_num from 
       (select pay_amount from t_pay_core_order
       where 
        pay_channel_id = #{payChannelId,jdbcType=SMALLINT}and trans_date &gt;= #{startTime,jdbcType=INTEGER}
        and trans_date &lt;= #{endTime,jdbcType=INTEGER}union all
       select pay_amount from t_pay_bill_file_detail 
       where 
        pay_channel_id = #{payChannelId,jdbcType=SMALLINT}and trans_date &gt;= #{startTime,jdbcType=INTEGER}
        and trans_date &lt;= #{endTime,jdbcType=INTEGER}
       ) as a
    </select>

傳入對象中有list需要使用時，需要進行遍歷，我在in語句中使用

<update id="updateByNum" parameterType="com.query.BillCheckQuery">
         update t_pay_core_order t1,t_pay_bill_file_detail t2
           set t1.is_check = 1,t2.is_check = 1
        WHERE 
        t1.pay_order_no = t2.pay_order_no
        and t2.pay_order_no in
        <foreach item="payOrderNo" index="index" collection="orderlist" open="(" separator="," close=")">
                #{payOrderNo,jdbcType=VARCHAR}
        </foreach>
        and t1.trans_date &gt;= #{startTime,jdbcType=INTEGER} 
        and t1.trans_date &lt;= #{endTime,jdbcType=INTEGER}</update>

或者直接在list中儲存對象也可以遍歷取出值

<insert id="batchInsert" parameterType="java.util.List" >
        insert into t_pay_bill_file_detail (file_id,pay_order_no,third_trade_no,trans_type,
        pay_channel_id,pay_amount,trans_date)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.payOrderNo},
            #{item.transType},
            #{item.transDate}
            )
        </foreach>
    </insert>