MyBatis——常用sql写法

1.批量插入

mapper层

void insertBatch(List<LaneSilo> list);

 <insert id="insertBatch">
            insert into lane_silo
            ( lane_id,silo_id,create_by,create_time)
            values
            <foreach collection="list" item="item" index="index" separator=",">
                (
                #{item.laneId},
                #{item.siloId},
                #{item.createBy},
                #{item.createTime}
                )
            </foreach>
    </insert>

2.批量删除

mapper层

void deleteLaneInfoByLaneIds(List<Long> laneIds);

<delete id="deleteLaneInfoByLaneIds" parameterType="String">
        delete from lane_info
        where lane_id in
        <foreach item="laneId" collection="list" open="(" separator="," close=")">
            #{laneId}
        </foreach>
    </delete>

3.批量更新

mapper层

void batchUpdate(List<CowInfo> list);

    <update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" separator=";">
            UPDATE t_cow_info SET cow_no = #{item.cowNo} WHERE id = #{item.id}
        </foreach>
    </update>

4.查询条件包含时间以及case when 用法

 select ci.short_name as custName,
        pi.prod_name prodName,
        pi.prod_id as prodId,
        a.cust_id as custId,
        count(1) as total,
        sum(CASE WHEN a.state = 8 THEN 1 ELSE 0 END) as finish,
        sum(CASE WHEN   <![CDATA[a.state < 8]]> THEN 1 ELSE 0 END) as unfinished
        from cust_trucking a
        left join cust_info ci on a.cust_id = ci.cust_id
        left join prod_info pi on a.prod_id = pi.prod_id
        <where>
            and a.del_flag = '0'
            and a.begin_time  <![CDATA[ <= ]]> #{currentTime}
            and a.end_time    <![CDATA[ >= ]]> #{currentTime}
            <if test="custId != null  ">and a.cust_id = #{custId}</if>
            <if test="prodId != null  ">and pi.prod_id = #{prodId}</if>
        </where>
        group by a.cust_id, a.prod_id

5.动态标签case when用法

  select service_type_name as businessName,
        cj_account as cjCurrentCount,
        fz_account as currentDevelopCount,
        zw_account as onlineCount
        from TF_A_NATURE_CUST_COUNT_USER_B
        where NATURE_CUST_ID = #{id} and BUILDING_STAFF_ID = #{staffId}
        <choose>
            <when test='buildingInfo != null and buildingInfo != "" and 
            buildingInfo == "0"'>
                and building_id = '非楼内全部'
            </when>
            <when test='buildingInfo != null and buildingInfo != "" and 
            buildingInfo == "1"'>
                and building_id = '楼内全部'
            </when>
            <when test='buildingInfo != null and buildingInfo != "" and 
            buildingInfo == "2"'>
                and building_id = '落地业务'
            </when>
            <when test='buildingInfo != null and buildingInfo != "" and 
            buildingInfo != "0" and buildingInfo != "1" and buildingInfo
            != "2"'>
                and building_id = #{buildingInfo}
            </when>
            <otherwise>
                and building_id = '全部'
            </otherwise>
        </choose>
        order by case when service_type_name ='固话' then 1
        when service_type_name ='移网' then 2
        when service_type_name ='宽带' then 3
        when service_type_name ='创新' then 4
        when service_type_name ='双线' then 5
        when service_type_name ='其他' then 6 end