mybatis整合mySql的一些常用批量操作

最新推荐文章于 2024-07-19 10:34:19 发布

葫芦胡

最新推荐文章于 2024-07-19 10:34:19 发布

阅读量2.5k

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/HXNLYW/article/details/80266030

版权

基础数据准备：

<sql id="entityColumnList"> 
     `ID`, NAME,PHONE,IS_DELETED
</sql>

1.批量插入

<insert id="insertBatch">
INSERT INTO
test.`t_TEST`
(
<include refid="entityColumnList" />
)
VALUES
<foreach collection="list" item= "item" index ="index" separator=",">
(
#{ item.id },
#{ item.name},
#{ item.phone}
)
</foreach >
</insert>

2.批量更新

1）推荐使用

<update id="updateBatch">
UPDATE
test.`t_TEST`
<trim prefix="set" suffixOverrides=",">
<trim prefix="NAME= case" suffix="end,">
<foreach collection="list" item="item" index="index">
WHEN (
`IS_DELETED`=0
AND `ID` = #{ item.id }
)
THEN #{item.name}
</foreach>
</trim>
<trim prefix="PHONE= case" suffix="end,">
<foreach collection="list" item="item" index="index">
WHEN (
`IS_DELETED`=0
AND `ID` = #{ item.id }
)
THEN #{item.phone}
</foreach>
</trim>
WHERE
<foreach collection="list" item="item" index="index" separator="or">
(
`IS_DELETED`=0
AND `ID` = #{ item.id }
)
</foreach>
</update>

2）

<update id="updateBatch">
<foreach collection="list" item="item" index="index" separator=";">
UPDATE
test.`t_TEST`
SET
`NAME`=#{ item.name },
WHERE
`IS_DELETED`=0
AND `ID`=#{item.id}
</foreach>
</update>

3.根据某个列表批量查询

SELECT
< include refid = "entityColumnList" />
FROM
 test.`t_TEST`
WHERE
`IS_DELETED` = 0
<IF test = "request.nameList!= null and request.nameList.size()>0" >
AND NAME IN
< foreach collection = "request.nameList" item = "name" INDEX = "index" OPEN = "(" CLOSE = ")" SEPARATOR = "," >
#{name}
</ foreach >
</IF >

4.数据库直接插入查询出的数据：

INSERT INTO   test.`t_TEST_COPY`
(
`ID`,
`NAME`,
`PHONE`
)
SELECT
RIGHT (UUID_SHORT(), 10) ID,
` t`.NAME AS `NAME`,
` t`.`PHONE` AS `PHONE`
FROM
test.`t_TEST` t
WHERE
(`t`.`IS_DELETED` = 0)
UNION ALL
SELECT
RIGHT (UUID_SHORT(), 10) ID,
`mcr`.`NAME` AS `NAME`,
`mcr`.`PHONE` AS `PHONE`
FROM
test.`t_TEST_1` t1
WHERE
(`t1`.`IS_DELETED` = 0)