mybatis 中 使用记录

1、mybatis 中  使用模糊查询，防止sql注入写法：

<select id="selectAllList" resultMap="BaseResultMap" parameterType="com.jidou.entity.OilCardBatch">
    select
    <include refid="Base_Column_List"/>
    from oil_card_batch
    where 1=1
    <if test="batchNumber!=null and batchNumber != '' ">
        and batch_number like concat('%',#{batchNumber,jdbcType=VARCHAR},'%')
    </if>
    <if test="batchName!=null and batchName != '' ">
        and batch_name like concat('%', #{batchName,jdbcType=VARCHAR},'%')
    </if>
    <if test="applianceChannel!=null and applianceChannel != '' ">
        and appliance_channel like concat('%',#{applianceChannel,jdbcType=VARCHAR},'%')
    </if>
    order by create_time desc
</select>

2、mybatis 中， 开始时间，结束时间的写法如下：

<select id="selectByBrandChannel"  resultMap="BaseResultMap">
    SELECT
    ct.*
    cd.*
    FROM
    coupon_detail cd
    LEFT JOIN coupon_template ct ON cd.template_id = ct.template_id
    <if test="brandChannel!=null and brandChannel!=''">
        where cd.brand_channel=#{brandChannel}
    </if>
    <if test="startDate!=null and startDate.trim() neq ''">
        and date_format(cd.create_time,'%Y-%m-%d %H:%i:%s') &gt;= str_to_date(#{startDate},'%Y-%m-%d %H:%i:%s')
    </if>
    <if test="endDate!=null and endDate.trim() neq ''">
        and date_format(cd.create_time,'%Y-%m-%d %H:%i:%s') &lt;= str_to_date(#{endDate},'%Y-%m-%d %H:%i:%s')
    </if>
</select>

3、参数自动根据“，”拆分拼接

<select id="couponDetailByTemplateIdList" resultType="com.jidou.coupon.entity.NewCouponDetail">
    select * from  coupon_detail
    where
        user_id = #{userId}
    and
        template_id in
    <if test="templateId != null and templateId != ''">
        <foreach item="item" index="index" collection="templateId.split(',')" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
</select>

4、批量插入

<insert id="batchInsert" parameterType="java.util.List">
   insert into coupon_nissan_receive
   (
   template_id,
   user_id,
   create_time
   )
    values
    <foreach collection ="list" item="item" separator =",">
    (
    #{item.templateId},
    #{item.userId},
    #{item.createTime}
    )
    </foreach>
</insert>