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>