MyBatis批量插入、批量更新、批量删除、批量查询

批量插入

WaterEleMapper部分代码

1
int insertList(List<WaterEle> list);

WaterEleMapper.xml部分代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<!--批量增加测试-->
<insert id="insertList" parameterType="java.util.List">
insert into t_enterprise_water_ele
(
/*方法一*/
-- WATER_ELE_ID,
-- ENTERPRISE_ID,
-- ENTERPRISE_USCC,
-- ENTERPRISE_NAME,
-- YEARMONTH,
-- WATER_SIZE,
-- WATER_AMOUNT,
-- ELE_SIZE,
-- ELE_AMOUNT,
-- STATUS,
-- OPERATOR,
-- OPERATE_TIME
/*方法二*/
<include refid="Base_Column_List"/>
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.waterEleId,jdbcType=VARCHAR},
#{item.enterpriseId,jdbcType=VARCHAR},
#{item.enterpriseUscc,jdbcType=VARCHAR},
#{item.enterpriseName,jdbcType=VARCHAR},
#{item.yearmonth,jdbcType=VARCHAR},
#{item.waterSize,jdbcType=DECIMAL},
#{item.waterAmount,jdbcType=VARCHAR},
#{item.eleSize,jdbcType=DOUBLE},
#{item.eleAmount,jdbcType=VARCHAR},
#{item.status,jdbcType=INTEGER},
#{item.operator,jdbcType=VARCHAR},
#{item.operateTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>

对于foreach标签的解释具体如下:

属性 描述 备注
item 循环体中的具体对象
支持属性的点路径访问,如item.age,item.info.details
具体说明:在list和数组中是其中的对象,在map中是value
该参数为必选
collection 要做foreach的对象,作为入参时,List<?>对象默认用list代替作为键
数组对象用array代替作为键,Map对象没有默认的键
当然在作为入参时可以使用@Param("keyName")来设置键,设置keyName后,list,array将会失效
除了入参这种情况外,还有一种作为参数对象的某个字段的时候。举个例子:
如果User有属性List ids,入参是User对象,那么这个collection = "ids"
如果User有属性Ids ids,其中Ids是个对象,Ids有个属性List id,入参是User对象,那么collection = "ids.id"
该参数为必选
separator 元素之间的分隔符
例如在in()的时候,separator=","会自动在元素中间用,隔开,避免手动输入逗号导致sql错误,如in(1,2,)这样
该参数可选
open foreach代码的开始符号,一般是(close=")"合用。常用在in(),values() 该参数可选
close foreach代码的关闭符号,一般是)open="("合用。常用在in(),values() 该参数可选
index 在list和数组中,index是元素的序号,在map中,index是元素的key 该参数可选

使用批量插入执行的SQL语句应该等价于:

1
2
insert into redeem_code (batch_id, code, type, facevalue,create_user,create_time)
values (?,?,?,?,?,? ),(?,?,?,?,?,? ),(?,?,?,?,?,? ),(?,?,?,?,?,? )

批量更新

传list集合

(1)单个字段方法一

1
2
3
4
5
6
7
8
9
10
11
12
13
<update id="updateByBatch" parameterType="java.util.List">
update t_goods
set NODE_ID=
<foreach collection="list" item="item" index="index"
separator=" " open="case" close="end">
WHEN GOODS_ID=#{item.goodsId} then #{item.nodeId}
</foreach>
WHERE GOODS_ID in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.goodsId,jdbcType=BIGINT}
</foreach>
</update>

以上单字段更新实际执行:

1
UPDATE t_goods SET NODE_ID = WHEN GOODS_ID = ? THEN ? WHERE GOODS_ID IN ( ? )

(2)单个字段方法二

1
2
3
4
5
6
7
8
9
10
11
12
13
<update id="updateByBatch" parameterType="java.util.List">
UPDATE
t_goods
SET NODE_ID = CASE
<foreach collection="list" item="item" index="index">
WHEN GOODS_ID = #{item.goodsId} THEN #{item.nodeId}
</foreach>
END
WHERE GOODS_ID IN
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item.goodsId}
</foreach>
</update>

以上单字段更新实际执行:

1
UPDATE t_goods SET NODE_ID = CASE WHEN GOODS_ID = ? THEN ? END WHERE GOODS_ID IN ( ? )

(3)多个字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<update id="updateBatch" parameterType="java.util.List">
update t_user
<trim prefix="set" suffixOverrides=",">
<trim prefix="STATUS =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.status!=null">
when USER_ID=#{i.userId} then #{i.status}
</if>
</foreach>
</trim>
<trim prefix=" OPERATE_TIME =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.operateTime!=null">
when USER_ID=#{i.userId} then #{i.operateTime}
</if>
</foreach>
</trim>

<trim prefix="OPERATOR =case" suffix="end," >
<foreach collection="list" item="i" index="index">
<if test="i.operator!=null">
when USER_ID=#{i.userId} then #{i.operator}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index" >
USER_ID=#{i.userId}
</foreach>
</update>
1
int updateBatch(List<WaterEle> list);

(4)更新多条记录的同一个字段为同一个值

1
2
3
4
5
<update id="updateByBatchPrimaryKey" parameterType="java.util.Map">
UPDATE t_goods
SET NODE_ID = #{nodeId}
WHERE GOODS_ID IN (${goodsIdList})
</update>
1
UPDATE t_goods SET NODE_ID = ? WHERE GOODS_ID IN (1,2,5);

传map/传String(同批量删除的”传map/传String”)

1
2
3
4
5
<update id="deleteByPrimaryKey" parameterType="java.util.Map">
UPDATE t_order_checkout
SET NODE_ID = #{nodeId, jdbcType=VARCHAR}, OPERATOR = #{operator, jdbcType=VARCHAR}
WHERE CHECKOUT_ID IN (${checkoutIdList})
</update>

批量删除(数组)

传数组

1
int deleteByBatch(String[] array);
1
2
3
4
5
6
7
<delete id="deleteByBatch" parameterType="java.lang.String">
delete from t_enterprise_output_value
where OUTPUT_ID IN
<foreach collection="array" item="outputId" open="(" separator="," close=")">
#{outputId}
</foreach>
</delete>

传map/传String

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<delete id="deleteByRole" parameterType="java.util.Map">
DELETE
FROM
t_user_role
<where>
<if test="userIdList != null">
USER_ID IN (#{userIdList,jdbcType=VARCHAR})
</if>
<if test="roleId != null">
AND ROLE_ID=#{roleId,jdbcType=VARCHAR}
</if>
<if test="sysCode != null">
AND SYSCODE=#{sysCode}
</if>
</where>
</delete>

因为表中没有fileIds字段,所以如果传map进入的话,需要在map中定义该字段

1
map.put("fileIds", "1,2,3");
1
2
3
4
//美元符$直接注入
<delete id="deleteByPrimaryKey" parameterType="java.util.Map">
DELETE FROM t_attachment WHERE FILE_ID IN (${fileIds})
</delete>

完整的sql语句是:

1
DELETE FROM t_attachment WHERE FILE_ID IN (1,2,3)

适用于表中该字段是int或者bigint类型,不适用于varchar。如果该字段是varchar类型,则正确的sql语句应该是:

1
DELETE FROM t_attachment WHERE FILE_ID IN ("1","2","3");

map中就应该这样定义:

1
2
3
4
5
6
7
8
9
数组/集合 -->(1,2,3,4)
String userIdList = formData.get("userIdList");
String[] users = userIdList.split(",");
String str = "";
for (String user : users) {
str += "\"" + user + "\"" + ",";
}
String substring = str.substring(0, str.lastIndexOf(","));
System.out.println(substring);

如果要传substring进sql语句中,但是substring不是数据库表中的字段,三种方法:
map.put("substring",substring),sql接收参数parameterType传java.util.Map
② 将substring放入对象中,传对象进去
③ 使用@Param("substring")注解

1
int deleteByCheckoutId(@Param("checkoutIdList") String checkoutIdList);

int deleteByCheckoutId(long cDetailId);不需要注解是因为cDetailId对应表中的C_DETAIL_ID,因为是表中现存的所以可以不用加,我个人理解。

1
2
3
4
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from t_checkout_detail
where C_DETAIL_ID = #{cDetailId,jdbcType=BIGINT}
</delete>

多参数批量删除示例

如果删除不是以主键为条件,而是多个条件同时成立才可以删除

1
2
3
4
5
6
7
<delete id="deleteByUserIdSysRoleBatch">
delete from t_user_role
where SYSCODE = #{sysCode,jdbcType=VARCHAR} AND ROLE_ID = #{roleId,jdbcType=VARCHAR} AND USER_ID IN
<foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</delete>

Controller层接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
@RequestMapping(value = "", method = RequestMethod.POST)
public ResponseObj<Boolean> setMoreUserToRole(@RequestBody Map<String, String> formData) {

String userIdList = formData.get("userIdList");
String[] users = userIdList.split(",");
String str = "";
for (String user : users) {
str += "\"" + user + "\"" + ",";
}
String substring = str.substring(0, str.lastIndexOf(","));
System.out.println(substring);

String sysCode = formData.get("sysCode");
String roleId = formData.get("roleId");
userRoleMapper.deleteByUserIdSysRoleBatch(sysCode, roleId, users);

List<UserRole> list = new ArrayList<>();
for (int i = 0; i < users.length; i++) {
UserRole userRole = new UserRole();
String roleId = formData.get("roleId");
if (roleId != null && !"".equals(roleId)) {
userRole.setRoleId(roleId);
}
userRole.setStatus(SysData.STATUS_NORMAL);
userRole.setOperateTime(DateUtil.getDateTime());
userRole.setOperator(formData.get("operator"));
userRole.setSysCode(formData.get("sysCode"));
userRole.setId(CommonUtil.getSysRef());
userRole.setUserId(users[i]);

list.add(userRole);
}

int i = userRoleMapper.addByBatch(list);
if (i == users.length)
return new ResponseObj<Boolean>(true, RetCode.SUCCESS);
return new ResponseObj<Boolean>(false, RetCode.FAIL);
}
1
2
3
int deleteByUserIdSysRoleBatch(@Param("sysCode") String sysCode, 
@Param("roleId") String roleId,
@Param("userIds") String[] userId);

批量查询

in()语句实现

注:以下orderList可以使用map/String传入

1
2
3
<select id="selectOrder" parameterType="java.util.Map" resultMap="OrderMap">
SELECT * FROM t_order WHERE ORDER_ID in (${orderList})
</select>

(1)对于表中的主键是bigint / int

1
2
3
4
5
==>  Preparing: SELECT * FROM t_order WHERE ORDER_ID in (?) 
==> Parameters: 1,2,5(String)
<== Columns: ORDER_ID, GOODS_ID, UNIT_PRICE, SOLD_NUM, ORDER_STATUS, OPERATOR, OPERATE_TIME
<== Row: 1, 356346093, 43.00, 3, 109002, admin, 2019-12-16 21:01:20.0
<== Total: 1

注意"1,2,5"传过来的是String类型,实际sql语句如下:

1
2
SELECT * FROM t_order WHERE ORDER_ID in (#{orderList})
SELECT * FROM t_order WHERE ORDER_ID in ('1,2,5') #只能查到一条

将mapper.xml中的sql语句改为

1
2
SELECT * FROM t_order WHERE ORDER_ID in (${orderList})
SELECT * FROM t_order WHERE ORDER_ID in (1,2,5) #就可以全部查到

(2)对于表中的主键是varchar

1
2
SELECT * FROM t_order WHERE ORDER_ID in (#{orderList})
SELECT * FROM t_order WHERE ORDER_ID in ('1,2,5') #查询不到

将mapper.xml中的sql语句改为

1
2
SELECT * FROM t_order WHERE ORDER_ID in (${orderList})
SELECT * FROM t_order WHERE ORDER_ID in ('1','2','5') #就可以全部

'1,2,5'转为'1','2','5'步骤如下:

1
2
3
4
5
6
7
8
9
10
String orderList = (String) formData.get("orderList");
if (!StringUtils.isEmpty(orderList)) {
String[] split = orderList.split(",");
String sqlParamter = "";
for (String s : split) {
sqlParamter += "'" + s + "',";
}
String substring = sqlParamter.substring(0, sqlParamter.length() - 1);
formData.put("orderList", substring);
}

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2021 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :