MySQL数据库批量操作语句

1、模糊查询：

<if test="goodName != null and goodName != ''">                AND bcp. company_name LIKE                     <if test="dbName == 'oracle'">'%'||#{commName}||'%'</if>                    <if test="dbName == 'mssql'">'%'+#{commName}+'%'</if>                    <if test="dbName == 'mysql'">concat('%',#{commName},'%')</if></if>

concat可以将两个字符串连接起来，形成一个单一的字符串。concat 等同于字符串连接符 ||, concat有 to_char的作用，就是把其他类型转成varchar类型的。

2、Mybatis中遍历集合：
一个字段同时要对应多个值进行查询，实体类中传过来一个list集合，要遍历出集合中的数据来进行查询，显示查询的结果。

<if test="selectedGood != null">                AND a.good_type_id in                 <foreach item="item" index="index" collection="selectedGood" open="(" separator="," close=")">                      #{item}                </foreach>             </if>

mybatis批量操作

3、批量插入：

    <insert id="insertOCCGoods" parameterType="java.util.List">             <![CDATA[    INSERT INTO order_c_city_goods (         id,        create_by,        create_date,        update_by,        update_date,        remarks,        del_flag,        order_c_good_id,        good_base_id,        good_lin_shou_price,        good_lin_shou_sums,        good_lin_shou_prices,        good_comm_prices,        is_miao,        good_appraisal        ) VALUES ]]>    <foreach collection="list" item="item" index="index" separator=",">        (        #{item.id},        #{item.createBy},        #{item.createDate},        #{item.updateBy},        #{item.updateDate},        #{item.remarks},        #{item.delFlag},        #{item.orderCGoodId},        #{item.goodBaseId},        #{item.goodLinShouPrice},        #{item.goodLinShouSums},        #{item.goodLinShouPrices},        #{item.goodCommPrices},        #{item.isMiao},        #{item.goodAppraisal}        )       </foreach></insert>

4、批量更新：

<update id="upateBusShopCars" parameterType="java.util.List">        update bus_shop_car        <trim prefix="set" suffixOverrides=",">            <trim prefix="update_date =case" suffix="end,">                <foreach collection="list" item="i" index="index">                    <if test="i.updateDate!=null">                        when id=#{i.id} then #{i.updateDate}                    </if>                </foreach>            </trim>            <trim prefix=" del_flag =case" suffix="end,">                <foreach collection="list" item="i" index="index">                    <if test="i.delFlag!=null">                        when id=#{i.id} then #{i.delFlag}                    </if>                </foreach>            </trim>                 </trim>        where        <foreach collection="list" separator="or" item="i" index="index">            id=#{i.id}        </foreach>    </update>