【技术应用】mybatis数据库操作insert、update、delete返回值为0的场景

    • 一、前言
    • 二、数据库异常处理
    • 三、insert操作返回值为: 0
    • 四、update操作返回值为: 0
    • 五、delete操作返回值为: 0
    • 六、总结

一、前言

最近在review项目组成员代码时，发现代码中有很多mybatis执行数据库操作（insert、update、delete）的返回值没有做任何处理，在一些业务场景下可能是存在的潜在bug，产生垃圾数据或者导致数据不一致的问题，所在今天特意总结一下；

mybatis对数据库操作返回值得理解：
insert： **插入n条记录，返回影响行数n。（n>=1，n为0时实际为插入失败）
update：更新n条记录，返回影响行数n。（n>=0）
delete： 删除n条记录，返回影响行数n。（n>=0）

我们比较容易理解返回值 n>0的情况，但是在什么场景下mybatis操作(insert、update、delete)数据库时，返回值 n =0，这是我们今天总结的主要目的；

二、数据库异常处理

在操作数据库时经常遇到异常情况，操作数据库连接conn抛出的异常我们可以直接try{}catch(){}捕获异常，这种情况时没有返回值的，直接对捕获的异常进行事务回滚或者其它处理，这里是没有太大异议的；

我们在写存储过程的时候，可能会出现下列一些情况：

  • 插入的数据违反唯一约束，导致插入失败
  • 插入或者更新数据超过字段最大长度，导致操作失败
  • update 影响行数和期望结果不一致

遇到上面各种异常情况的时，可能需要我们能够捕获，然后可能需要回滚当前事务。

三、insert操作返回值为: 0

在mybatis中insert操作返回值为0的场景不多见，大多数的操作失败都是以异常的形式抛出，我们可以通过try{}catch(){}捕获；

针对insert主要介绍几种可能返回0或者其它返回值的操作命令，也是比较容易出错的地方

1、insert ignore into

mysq 支持 insert ignore into。
insert into 表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

1) sql示例：

INSERT IGNORE INTO tbl_user(id,NAME,age,pass_time)VALUES(9,'哈哈哈',18,11111111);

执行结果：

1 queries executed, 1 success, 0 errors, 1 warnings查询：INSERT IGNORE INTO tbl_user(id,NAME,age,pass_time)VALUES(9,'哈哈哈',18,11111111)共 0 行受到影响， 1 个警告执行耗时 : 0.001 sec传送时间 : 0.001 sec总耗时: 0.002 sec

2) mybatis示例：

mapper方法：

@Insert(value = "INSERT IGNORE INTO tbl_user (id,name,age,pass_time) VALUES (${user.id},'${user.name}',${user.age},${user.pass_time})")Integer add(@Param(value = "user") User user);

Service类：

@Overridepublic Integer add(User user) {Integer insert = userMapper.add(user);System.out.println("insert 执行结果: "+insert);return insert;}

请求：
id=9数据已在数据库表tbl_user中已存在

执行结果：

2022-12-15 19:45:17.972INFO 14868 --- [nio-8079-exec-2] o.s.web.servlet.DispatcherServlet: Completed initialization in 0 msinsert 执行结果: 0

2、replace into

replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换（先删除旧数据，再插入新数据），如果没有数据效果则和insert into一样；

replace语句会返回一个数，来指示受影响的行的数目；

  • 如果数据已存在，返回数为：变更数据行数+执行数据行数；
  • 如果数据不存在，返回数为：执行数据行数；

mysql数据库执行的返回值，即为mybatis操作返回值，为了方便我们接下来会在数据库中操作；

1) replace into 示例：

REPLACE INTO tbl_user(id,NAME,age,pass_time)VALUES(10,'小四',10,10000000000);REPLACE INTO tbl_user(id,NAME,age,pass_time)VALUES(10,'小四',10,100000001000);

执行结果：

2 queries executed, 2 success, 0 errors, 0 warnings查询：replace into tbl_user(id,name,age,pass_time)values(10,'小四',10,10000000000)共 1 行受到影响执行耗时 : 0.026 sec传送时间 : 0 sec总耗时: 0.026 sec-----------------------------------------------------------查询：REPLACE INTO tbl_user(id,NAME,age,pass_time)VALUES(10,'小四',10,100000001000)共 2 行受到影响执行耗时 : 0.042 sec传送时间 : 0 sec总耗时: 0.042 sec

2) mybatis示例：

@Overridepublic Integer test() {User user = new User();user.setId(10);user.setName("小四");user.setAge(10);user.setPass_time(10000000002L);Integer insert = userMapper.add2(user);System.out.println("insert 执行结果: "+insert);return insert;}

执行结果：

2022-12-15 20:54:08.698INFO 17236 --- [restartedMain] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} initedinsert 执行结果: 2

3、on duplicate key update

概念：INSERT ... ON DUPLICATE KEY UPDATE 用于解决重复性问题。首先判断记录是否存在，存在时更新，不存在时插入。因此重点在于判断记录是否存在。
判断标准是如果插入的记录导致一个唯一索引或主键重复，就认为该记录已存在。

根据受影响行数可以区分该语句实际上执行的是插入还是更新：

  • 受影响行数为1，表明行作为新纪录插入；
  • 受影响行数为2，表明行重复，原有记录被更新；
  • 受影响行数为0，表明更新的数据与原数据一样，实际上未更新。

1) sql示例：

INSERT INTO tbl_user(id,NAME,age,pass_time)VALUES(12,'小六',10,10000000000) ON DUPLICATE KEY UPDATE NAME=VALUES(NAME),age=VALUES(age),pass_time=VALUES(pass_time);INSERT INTO tbl_user(id,NAME,age,pass_time)VALUES(12,'小六',11,10000000000) ON DUPLICATE KEY UPDATE NAME=VALUES(NAME),age=VALUES(age),pass_time=VALUES(pass_time);INSERT INTO tbl_user(id,NAME,age,pass_time)VALUES(12,'小六',11,10000000000) ON DUPLICATE KEY UPDATE NAME=VALUES(NAME),age=VALUES(age),pass_time=VALUES(pass_time);

执行结果：

3 queries executed, 3 success, 0 errors, 3 warnings查询：INSERt INTO tbl_user(id,NAME,age,pass_time)VALUES(12,'小六',10,10000000000) on duplicate key update NAME=VALUES(NAME),age=VALU...共 1 行受到影响， 3 个警告执行耗时 : 0.057 sec传送时间 : 0.001 sec总耗时: 0.058 sec注意：要查看所有警告的完整列表，请启用 工具 -> 首选项 -> 常规 -> 在信息选项卡下显示警告-----------------------------------------------------------查询：INSERT INTO tbl_user(id,NAME,age,pass_time)VALUES(12,'小六',11,10000000000) ON DUPLICATE KEY UPDATE NAME=VALUES(NAME),age=VALU...共 2 行受到影响， 3 个警告执行耗时 : 0.063 sec传送时间 : 0 sec总耗时: 0.063 sec注意：要查看所有警告的完整列表，请启用 工具 -> 首选项 -> 常规 -> 在信息选项卡下显示警告-----------------------------------------------------------查询：INSERT INTO tbl_user(id,NAME,age,pass_time)VALUES(12,'小六',11,10000000000) ON DUPLICATE KEY UPDATE NAME=VALUES(NAME),age=VALU...共 0 行受到影响， 3 个警告执行耗时 : 0.001 sec传送时间 : 0 sec总耗时: 0.001 sec注意：要查看所有警告的完整列表，请启用 工具 -> 首选项 -> 常规 -> 在信息选项卡下显示警告

2) mybatis示例：

@Overridepublic Integer test() {//12,'小六',11,10000000000User user = new User();user.setId(15);user.setName("小六");user.setAge(11);user.setPass_time(10000000010L);Integer insert = userMapper.add3(user);System.out.println("insert 执行结果: "+insert);return insert;}

执行结果：

2022-12-15 21:01:41.720INFO 12340 --- [restartedMain] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} initedinsert 执行结果: 1

注：在insert数据没有变更时，在mysql客户端执行返回的影响行数为0，但是在mybatis执行返回值为1，这里的原因还有待分析；

四、update操作返回值为: 0

update操作返回值为0比较常见的一种情况为：根据主键更新数据库表中的数据时，如果数据库表tbl_user中没有id=13的数据，返回影响行数：0

1) sql示例：

UPDATE tbl_user SET NAME='小七' , age=18, pass_time=10000000000 WHERE ID=13

执行结果：

<n>查询：UPDATE tbl_user SET name='小七' , age=18, pass_time=10000000000 WHERE ID=13共 0 行受到影响执行耗时 : 0.001 sec传送时间 : 0 sec总耗时: 0.001 sec

2) mybatis示例：

@Overridepublic Integer test() {//12,'小六',11,10000000000User user = new User();user.setId(13);user.setName("小七");user.setAge(18);user.setPass_time(10000000010L);Integer insert = userMapper.update(user);System.out.println("insert 执行结果: "+insert);return insert;}

执行结果：

2022-12-15 21:04:41.245INFO 7896 --- [restartedMain] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} initedinsert 执行结果: 0

五、delete操作返回值为: 0

delete操作返回值为0比较常见的一种情况为：根据某个属性（示例为id=13）删除数据库表中的数据时，如果数据库表tbl_user中没有id=13的数据，返回影响行数：0

1) sql示例：

DELETE FROM tbl_user WHERE ID = 13;

执行结果：

1 queries executed, 1 success, 0 errors, 0 warnings查询：DELETE FROM tbl_user WHERE ID = 13共 0 行受到影响执行耗时 : 0.001 sec传送时间 : 0.001 sec总耗时: 0.002 sec

2) mybatis示例：

@Overridepublic Integer test() {//12,'小六',11,10000000000/*User user = new User();user.setId(13);user.setName("小七");user.setAge(18);user.setPass_time(10000000010L);*/Integer delete = userMapper.delete(13);System.out.println("insert 执行结果: "+delete);return delete;}

执行结果：

2022-12-15 21:06:17.742INFO 12644 --- [restartedMain] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} initedinsert 执行结果: 0

六、总结

在使用mybatis操作数据库时，返回值一定要结合不同的使用场景进行业务处理，避免潜在的bug，也是代码规范性的体现。