一、批量插入或者更新:减少与数据库交互
<insert id="betchInsertMallItemPrice" parameterType="java.util.List">
<![CDATA[
insert into mall_item_price (mall_item_id, price,discout_price,limit_num,level,create_user_id,type,status,gmt_create,gmt_modify,attribute,comm_only_key) values
]]>
<foreach collection="list" item="mallItemPrice" index="index" separator=",">
<![CDATA[
(#{mallItemPrice.mallItemId}, #{mallItemPrice.price}, #{mallItemPrice.discoutPrice},
#{mallItemPrice.limitNum}, #{mallItemPrice.level}, #{mallItemPrice.createUserId},
#{mallItemPrice.type}, #{mallItemPrice.status}, #{mallItemPrice.gmtCreate},
#{mallItemPrice.gmtModify}, #{mallItemPrice.attribute}, #{mallItemPrice.commOnlyKey})
]]>
</foreach>
<selectKey resultType="long" keyProperty="id">
SELECT @@IDENTITY AS id
</selectKey>
</insert>
二、批量更新以及插入:
<insert id="replaceMallItemPrice" parameterType="java.util.List">
<![CDATA[
replace into mall_item_price (id,mall_item_id, price,discout_price,limit_num,level,create_user_id,type,status,gmt_create,gmt_modify,attribute,comm_only_key) values
]]>
<foreach collection="list" item="mallItemPrice" index="index" separator=",">
<![CDATA[
(#{mallItemPrice.id},#{mallItemPrice.mallItemId}, #{mallItemPrice.price}, #{mallItemPrice.discoutPrice},
#{mallItemPrice.limitNum}, #{mallItemPrice.level}, #{mallItemPrice.createUserId},
#{mallItemPrice.type}, #{mallItemPrice.status}, #{mallItemPrice.gmtCreate},
#{mallItemPrice.gmtModify}, #{mallItemPrice.attribute}, #{mallItemPrice.commOnlyKey})
]]>
</foreach>
<selectKey resultType="int" keyProperty="id">
SELECT @@IDENTITY AS id
</selectKey>
</insert>
三、数据库存 1,2,3,13 判断1是否在其中
<select id="getMallActivityListByLevel" resultType="com.mall.pojo.MallActivity" parameterType="java.lang.Integer">
select *
from mall_activity where status = 1 and comm_only_key = #{1} and find_in_set(#{0},activity_rull) order by id DESC
</select>
此处用的是find_in_set()函数