首先批量插入,操作mysql和Oracle完全不同,
mysql:
<insert id = "insertList" parameterType="java.util.ArrayList">
INSERT INTO test_table ( `name`,`age`)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.name},#{item.age}
)
</foreach>
</insert>
Oracle:
<insert id="insertAbisLayer3Message" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="item" index="index">
INTO "ABISMON"."ABISLAYER3MESSAGE" (
MSGTIMESTAMP,LAC,
CI,BCCH
) VALUES
(
to_timestamp(#{item.msgTimestamp},'yyyy-mm-dd hh24:mi:ss:ff6'),
#{item.lac},
#{item.ci},#{item.bcch}
)
</foreach>
select 1 from dual
</insert>
select 1 from dual这个必须要,是为了保证SQL结构的完整性,这个和有时候用的那个if标签时,使用的 where 1 = 1 意义相同
还是能明显看出区别的,
更新时基本差不多,没啥区别;
mysql更新时有多中写法,我这里只列一种,够用就可以了:
<update id = "updateList" parameterType="java.util.ArrayList">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update
test_table
set
`age` = #{item.age}
where `name` = #{item.name}
</foreach>
</update>
需要注意的是,mysql这样写时需要在连接数据库的url后边加上 &allowMultiQueries=true
参考文章:https://blog.csdn.net/weixin_42209881/article/details/100318313
Oracle中的批量更新:
<update id="updateRecord" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
UPDATE "MON"."RECORD"
<set>
<if test="discTimeStamp != null">
DISCTIME = to_timestamp(#{discTimeStamp},'yyyy-mm-dd hh24:mi:ss:ff6'),
</if>
<if test="discKmpost != null">
DISCKMPOST = #{discKmpost},
</if>
<if test="discLac != null">
DISCLAC = #{discLac},
</if>
<if test="discCi != null">
DISCCI = #{discCi},
</if>
<if test="discBcch != null">
DISCBCCH = #{discBcch},
</if>
<if test="discSpeed != null">
DISCSPEED = #{discSpeed},
</if>
<if test="discCause != null">
DISCCAUSE = #{discCause},
</if>
<if test="discParty != null">
DISCPARTY = #{discParty},
</if>
</set>
where ID = #{crId,jdbcType=VARCHAR}
where id = #{item.id,jdbcType=VARCHAR}
</foreach>
</update>
删除时也没多大区别,更简单:
<delete id="deleteData">
delete from test_table where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")" >
#{item.id}
</foreach>
</delete>
java里Dao层的写法:
void insertLayer3Message(@Param("list") List<Message> list);
void insertMeasReport(@Param("list") List<MeasReport> list);
注意那个@Param中的“list”要和xml中的collection="list"对应一致
查询的话,基本没啥区别,自己随便发挥就可以,以上的都是我自己测过的,绝对可信