建网站 主流软件,学校网站建设申请报告,公司网络营销策划书,wordpress解析优化温故而知新#xff0c;这里记录一下
案例1 批量插入时#xff0c;xxxMapper.java 中方法的参数都必须是 List #xff0c;泛型可以是 bean #xff0c;也可以是 Map 。配合使用 mybatis 的 foreach 即可。示例如下#xff1a;
public Integer batchInsertDemo(ListD…温故而知新这里记录一下
案例1 批量插入时xxxMapper.java 中方法的参数都必须是 List 泛型可以是 bean 也可以是 Map 。配合使用 mybatis 的 foreach 即可。示例如下
public Integer batchInsertDemo(ListDemo list);
首先对于批量数据的插入有两种解决方案
1、for 循环调用 Dao 中的单条插入方法
2、传一个 ListObject 参数使用 MyBatis 的 foreach 标签进行批量插入
insert idaddUser parameterTypejava.util.List insert into user(name,age) valuesforeach collectionlist itemitem indexindex separator,(#{item.name},#{item.age})/foreach
/insert性能上对比批量插入性能好更加省时间。
原因如下
循环插入需要每次都获取 session, 获取连接然后将 SQL 语句发给 MySQL 去执行JDBC 一般情况下是通过 TCP/IP 进行连接和数据库进行通信的。可以看这里 mysql 四种通信协议批量插入 批量插入通过 foreach 标签将多条数据拼接在 SQL 语句后一次执行只获取一次 session, 提交一条 SQL 语句。减少了程序和数据库交互的准备时间。 1、只批量插入数值 这种写法适合插入数据的项不变即 sql 中 VALUES 前括号中的列不变。若插入的项有所变化则适用下一种方法。
insert idbatchInsertDemo parameterTypejava.util.List INSERT INTO demo(id,name,code,age,address) VALUES foreach collectionlist itemitem indexindex separator, (#{item.id},#{item.name},#{item.code},#{item.age},#{item.address}) /foreach
/insert
2、根据数值变动插入选项 此时需适用 foreach 循环包含整个sql语句VALUES 前后括号中的插入项和插入数据使用 trim 标签再配合使用 if 标签即可。示例如下
insert idbatchInsertDemo parameterTypelist foreach collectionlist itemitem indexindex separator;INSERT INTO demotrim prefix( suffix) suffixOverrides, if testitem.id! nullid,/ifif testitem.name! nullname,/ifif testitem.code ! nullcode,/ifif testitem.age! nullage,/ifif testitem.address! nulladdress,/if/trimtrim prefixvalues ( suffix) suffixOverrides, if testitem.id! null#{item.id,jdbcTypeINTEGER},/ifif testitem.name! null#{item.name,jdbcTypeVARCHAR},/ifif testitem.code ! null#{item.code ,jdbcTypeVARCHAR},/ifif testitem.age! null#{item.age,jdbcTypeINTEGER},/ifif testitem.address! null#{item.address,jdbcTypeVARCHAR},/if/trim/foreach/insert 本案2
例的建表语句是
-- auto-generated definition create table contact_type ( sid varchar(50) not null primary key, name varchar(50) default null, status int default 1 null comment 状态,默认1表示有效0为冻结, seq float default 0 null, create_time datetime default CURRENT_TIMESTAMP null ) comment 往来单位类型; 所以主键是字符串类型而不是自增类型。写在 Mybatis 的 xml 文件中的SQL语句如下
insert idsaveOne parameterTypecom.ccsoft.femis.model.ContactType!--selectKey resultTypejava.lang.Integer keyPropertyid orderAFTER--!--SELECT LAST_INSERT_ID()--!--/selectKey--insert into contact_typetrim prefix( suffix) suffixOverrides,if testcreate_time ! null create_time, /ifif testname ! null name, /ifif testseq ! null seq, /ifif testsid ! null sid, /ifif teststatus ! null status, /if/trimvaluestrim prefix( suffix) suffixOverrides,if testcreate_time ! null #{create_time},/ifif testname ! null #{name},/ifif testseq ! null #{seq},/ifif testsid ! null #{sid},/ifif teststatus ! null #{status},/if/trimON DUPLICATE KEY UPDATEtrim suffixOverrides,if testcreate_time ! null create_time #{create_time}, /ifif testname ! null name #{name}, /ifif testseq ! null seq #{seq}, /ifif testsid ! null sid #{sid}, /ifif teststatus ! null status #{status}, /if/trim/insert 可以看到由于使用了 if test... 如果 Java 端传递来的对象有部分属性没有设置导致对象中该属性是空那么最终执行的 SQL 语句中就不会有该字段。 测试表创建的主键字段非自增所以将上面 xml 中的头部 SQL 语句返回新增行的主键字段值给注释掉了如果你的表示有自增字段的去掉注释即可得到新增的行的自增字段数值。 通过在 java 中测试发现上面的语句新增一行成功后会返回1修改成功后会返回2这里有疑惑如果清楚原因的麻烦跟帖科普下。 如果有未设置的属性恰好在数据库端对应的字段被设置为非空并且没有默认值导致新增或者修改失败那么会报异常 java.sql.SQLException 。 批量写入数据有则修改无则新增同时判断空选择性写入字段 数据表还是上面的直接贴出写在 Mybatis 的 XML 文件中的 SQL 是
insert idsaveBatch parameterTypejava.util.List!--selectKey resultTypejava.lang.String keyPropertysid orderAFTER--!--SELECT LAST_INSERT_ID()--!--/selectKey--foreach collection list itemele index index separator ;insert into contact_typetrim prefix( suffix) suffixOverrides,if testele.create_time ! null create_time, /ifif testele.name ! null name, /ifif testele.seq ! null seq, /ifif testele.sid ! null sid, /ifif testele.status ! null status, /if/trimvaluestrim prefix( suffix) suffixOverrides,if testele.create_time ! null #{ele.create_time},/ifif testele.name ! null #{ele.name},/ifif testele.seq ! null #{ele.seq},/ifif testele.sid ! null #{ele.sid},/ifif testele.status ! null #{ele.status},/if/trimON DUPLICATE KEY UPDATEtrim suffixOverrides,if testele.create_time ! null create_time #{ele.create_time}, /ifif testele.name ! null name #{ele.name}, /ifif testele.seq ! null seq #{ele.seq}, /ifif testele.sid ! null sid #{ele.sid}, /ifif testele.status ! null status #{ele.status}, /if/trim/foreach/insert 上面代码中在 SQL 语句的最外层使用了 for 循环好处是将 ListContactType 类型的集合传递来写入数据时可以有的是新增有的是修改例如3行数据第一三行由于主键字段对应属性 sid 被设置为 NULL 会向数据库中新增行第二行数据设置了 sid 并且该值在数据库中有对应行那么会修改数据库中的该行上的数据。不过这种做法也有问题就是返回给 Java 的数据永远都是1因为每个对象构成的 SQL 语句间使用的间隔符号是 ; 那么最终返回的影响的行数是最后一条 SQL 语句影响的行数。对此有其他见解的话麻烦跟帖科普下。 例子3
insert idinsertSelective parameterTypecom.yimayhd.snscenter.client.domain.ComentDO useGeneratedKeystrue keyPropertyid insert into com_comenttrim prefix( suffix) suffixOverrides, domain,status,if testgmtCreated ! null gmt_created,/ifif testgmtModified ! null gmt_modified,/if/trimtrim prefixvalues ( suffix) suffixOverrides, #{domain,jdbcTypeINTEGER},#{status,jdbcTypeINTEGER},if testgmtCreated ! null #{gmtCreated,jdbcTypeTIMESTAMP},/ifif testgmtModified ! null #{gmtModified,jdbcTypeTIMESTAMP},/if/trim/insert注意事项
注意1 MySQL默认接受sql的大小是1048576(1M)即第三种方式若数据量超过1M会报如下异常可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的max_allowed_packet 1M
nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 1048576).
You can change this value on the server by setting the max_allowed_packet variable.
注意2 由于上面的 SQL 在数据库端是多条语句需要在 Java 连接数据库的字串中设置 allowMultiQueriestrue 返回值
对于普通的单条插入数据库的返回值就是 0/1 。 对于返回值代表的意思可以认为是“语句执行返回的数据库受影响的行数。”或者是“此次执行是否成功0 - 失败1 - 成功。” 对应的也就是在 Dao 层中对于插入方法的返回值类型的设定有int/boolean两种。
对于批量插入的返回值返回的还是0/1, 而不是统计插入成功几条即使你的 Dao 层方法的返回值类型为 int. 这里的0/1 也就代表着这次批量插入是否成功0 - 失败1 - 成功。 当然你 Dao 层的返回值还是可以是int/boolean
批量插入中间有一个失败会怎么样
猜想有下面三种情况
a、继续插入后面的把失败的跳过b、停止插入但前面的插入成功保持。c、全部回滚
这里就直接放结果了。 批量语句只要有一个失败就会全部失败。数据库会回滚全部数据。原子性 关于测试过程可以看这篇博客mysql 批量插入语句执行失败的话是部分失败还是全部失败
其实也很好理解。 首先我们知道了 MyBatis foreach 批量插入是在程序内拼接 SQL 语句拼接成多条同时插入的 SQL 语句拼接后发给数据库。 就相当于咱们自己在 MySQL 的命令行中执行一条多插入的语句。默认情况下 MySQL 单条语句是一个事务这在一个事务范围内当中间的 SQL 语句有问题或者有一个插入失败就会触发事务回滚。同时你也能看到错误提示。命令行执行单条 SQL 的情况 所以有一个插入不成功肯定全部回滚。 批量插入数据量的限制 我这里就直接放结论又兴趣的可以看这篇博客有探究过程 Mybatis 批量插入引发的血案
1、MyBatis 本身对插入的数据量没有限制
2、MySQL 对语句的长度有限制默认是 4M 其他数据库的情况这里不介绍可以自行百度。通过上面 “MySQL 对语句的长度有限制默认是 4M” 我们可以知道批量插入数据是有限制的。不能一下把几万条数据就是太大数据量意思一次性插入。
所以一般情况下我们推荐即使使用批量插入也要分批次。 每次批次设置多少需要根据你的插入一条数据的参数量来做度量。因为受限条件是 SQL 语句的长度。 而且分批插入更加合理对于插入失败回滚范围会缩小很多。
对空集合参数进行校验 MyBatis 并没有做集合容量的验证如果集合参数为空或者 size 为 0 则生成的 SQL 可能只有”insert into user (name,age) values” 这样一段或者没有所以说写批量 SQL 的时候注意在调用批量方法的地方加入对容量的验证。
另外一种 foreach 插入不推荐
insert idaddBatchUser parameterTypejava.util.List foreach collectionlist itemitem indexindex separator;insert into user(name,age) values(#{item.name},#{item.age})/foreach
/insert 这种写法也能实现批量插入。但是有很多问题。
a、首先这种方式的批量插入也是 SQL 拼接。但是明显字符长度增加。这就导致每批次可插入的数量减少b、这种方式执行返回值还是0、1是已经尝试插入的最后一条数据是否成功。由于这种 foreach 拼接成的 SQL 语句是以分号 “” 分隔的多条 insert 语句。这就导致前面的数据项都插入成功了。默认数据库的事务处理是单条提交的出错前的执行都是一个个单条语句所以并并没有回滚数据。 所以如果你想中间插入失败回滚的话需要使用 Spring 事务但是还需要注意 spring 事务是抛出运行时异常时才会回滚。这种批量插入中间有没插入成功的是不会抛出异常的。所以你需要根据返回值判断手动编码抛出异常。 而最上面的那种写法就不用是用事务因为他是一条 SQL 语句。
foreach 标签 foreach 的主要用在构建 in 条件中它可以在 SQL 语句中进行迭代一个集合。 foreach 元素的属性主要有 itemindexcollectionopenseparatorclose。 item 表示集合中每一个元素进行迭代时的别名 index 指定一个名字用于表示在迭代过程中每次迭代到的位置 open 表示该语句以什么开始 separator 表示在每次进行迭代之间以什么符号作为分隔符 close 表示以什么结束 在使用 foreach 的时候最关键的也是最容易出错的就是 collection 属性该属性是必须指定的但是在不同情况 下该属性的值是不一样的主要有一下 3 种情况
1、如果传入的是单参数且参数类型是一个 List 的时候collection 属性值为 list2、如果传入的是单参数且参数类型是一个 array 数组的时候collection 的属性值为 array3、如果传入的参数是多个的时候我们就需要把它们封装成一个 Map 了当然单参数也可以封装成 map 参考
Mybatis 批量插入数据 SQL-腾讯云开发者社区-腾讯云
Mybatis 批量写入有则修改无则新增同时判断空选择性写入字段_mybatis批量新增并且判断空-CSDN博客
https://www.cnblogs.com/fantastic-clouds/p/13090557.html
MyBatis 批量插入 - Yaxings Blog
