Mybatis关于mysql批量插入学习
1.概述
对mybatis常用的操作命令做一下总结,数据库基于mysql。后面章节会涉及数据的批量插入、批量更新和更新后返回主键ID等内容。
1.数据库信息
1.1.表结构定义
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
age int(11) DEFAULT NULL,
remark varchar(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=682 DEFAULT CHARSET=utf8;
1.2.数据库配置db.properties
mysql.driver=com.mysql.jdbc.Driver
需要开启多行执行功能
jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false"
jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true"
mysql.url=jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
mysql.username=root
mysql.password=123456
1.3.实体类
package com.tech.ability.mybatis.Entity;
/**
-
Created by kikop on 2019/1/17.
*/
public class MyUser {//实体类的属性和表的字段名称一一对应
private int id;
private String name;
private int age;public String getRemark() {
return remark;
}public void setRemark(String remark) {
this.remark = remark;
}private String remark;
public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public String getName() {
return name;
}public void setName(String name) {
this.name = name;
}public int getAge() {
return age;
}public void setAge(int age) {
this.age = age;
}@Override
public String toString() {
return "MyUser{" +
"age=" + age +
", id=" + id +
", name='" + name + ''' +
", remark='" + remark + ''' +
'}';
}
}
2.mybatis mapper接口文件
package com.tech.ability.mybatis.Mapper;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.SelectKey;
import org.springframework.data.repository.query.Param;
/**
-
Created by kikop on 2019/5/19.
*/
public interface MyUserMapper {}
3.mybatis sqlmap映射文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.tech.ability.mybatis.Mapper.MyUserMapper">
<insert id="batchInsertUserInfoListByUnionAll" parameterType="com.alibaba.fastjson.JSONObject">
insert into users(name,age,remark)
<foreach collection="idList" index="index" item="currentRow" separator="union all">
( select '{currentRow.remark}')
</foreach>
</insert>
<!--7.批量插入-->
<!--union all的方式-->
<!--oracle:useGeneratedKeys="false"-->
<!--主键在myaql自增涨-->
<insert id="batchInsertUserInfoListByExecutorBach" parameterType="com.alibaba.fastjson.JSONObject">
insert into users(name,age,remark)
values('${name}',#{age},'${remark}')
</insert>
</mapper>
4.mybatis工具类
package com.tech.ability.mybatis;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
- Created by kikop on 2019/5/19.
*/
public class mybatisUtils {
//mybatis核心配置文件
private static final String mybatis_configPath = "mybatisconfig.xml";
/**
* 读取SqlSession
*
* @return
*/
public static SqlSession getSqlSession(ExecutorType execType, boolean isAutoCommit) {
SqlSession sqlSession = null;
try {
//1.读取mybatis的配置文件
//String resource = "mybatisconfig.xml";
//2.构建sqlSession的工厂
//使用类加载器加载 mybatis 的 classspath中配置文件
InputStream inputStream = null;
// InputStream inputStream = mybatisTest.class.getClassLoader().getResourceAsStream(resource);
inputStream = Resources.getResourceAsStream(mybatis_configPath);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.构建sqlSession的工厂
//使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
//Reader reader = Resources.getResourceAsReader(resource);
//SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//3.创建能执行映射文件中sql的sqlSession
// sqlSession = sessionFactory.openSession(isAutoCommit);
sqlSession = sessionFactory.openSession(execType, isAutoCommit);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
/**
* 关闭session(回收连接池必须)
*
* @param session
*/
public static void closeSqlSession(SqlSession session) {
session.close();
}
/**
* 关闭inputStream
*
* @param inputStream
*/
public static void closeInputSream(InputStream inputStream) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
5.测试代码
5.1.测试1
package com.tech.ability.mybatis;
import com.alibaba.fastjson.JSONObject;
import com.tech.ability.mybatis.Entity.MyUser;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import java.util.ArrayList;
import java.util.List;
/**
-
Created by kikop on 2019/1/17.
*/
public class mybatisTest {private static final Logger logger = LogManager.getLogger(mybatisTest.class);
/**
* 批量插入实现1
*/
public static void batchInsertUserInfoListByUnionAllTest() {
//1.构建sqlSession
SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);
//2.映射sql的标识字符串
StringBuffer statement = new StringBuffer();
statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值
statement.append(".batchInsertUserInfoListByUnionAll"); //select标签的id属性值
//3.定义查询条件
List<JSONObject> idList = new ArrayList<>();
for (int i = 0; i < 100; i++) {
JSONObject jsonObject = new JSONObject();
jsonObject.put("name", String.format("name_%d", i));
jsonObject.put("age", i);
jsonObject.put("remark", String.format("remark_%d", i));
idList.add(jsonObject);
}
JSONObject requestJsonObject = new JSONObject();
requestJsonObject.put("idList", idList);
//4.执行查询返回一个唯一user对象的sql
int updateResult = session.insert(statement.toString(), requestJsonObject);
System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + requestJsonObject.get("respID"));
if (updateResult > 0) {
session.commit();
} else {
session.rollback();
}
//5.资源释放(连接池回收)
mybatisUtils.closeSqlSession(session);
}
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/E:/workfolder/JavaWebSoft/maven/LocalMavenRep/org/apache/logging/log4j/log4j-slf4j-impl/2.1/log4j-slf4j-impl-2.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/E:/workfolder/JavaWebSoft/maven/LocalMavenRep/org/slf4j/slf4j-log4j12/1.6.1/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2019-05-19 17:06:11.931 DEBUG [main][LogFactory.java:124] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
2019-05-19 17:06:11.945 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 17:06:11.946 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 17:06:11.946 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 17:06:11.947 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 17:06:12.132 DEBUG [main][JdbcTransaction.java:129] - Opening JDBC Connection
2019-05-19 17:06:12.591 DEBUG [main][PooledDataSource.java:378] - Created connection 898694235.
2019-05-19 17:06:12.591 DEBUG [main][JdbcTransaction.java:95] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
2019-05-19 17:06:12.595 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
2019-05-19 17:06:12.600 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) ( select 'name_0',?,'remark_0') union all ( select 'name_1',?,'remark_1') union all ( select 'name_2',?,'remark_2') union all ( select 'name_3',?,'remark_3') union all ( select 'name_4',?,'remark_4') union all ( select 'name_5',?,'remark_5') union all ( select 'name_6',?,'remark_6') union all ( select 'name_7',?,'remark_7') union all ( select 'name_8',?,'remark_8') union all ( select 'name_9',?,'remark_9') union all ( select 'name_10',?,'remark_10') union all ( select 'name_11',?,'remark_11') union all ( select 'name_12',?,'remark_12') union all ( select 'name_13',?,'remark_13') union all ( select 'name_14',?,'remark_14') union all ( select 'name_15',?,'remark_15') union all ( select 'name_16',?,'remark_16') union all ( select 'name_17',?,'remark_17') union all ( select 'name_18',?,'remark_18') union all ( select 'name_19',?,'remark_19') union all ( select 'name_20',?,'remark_20') union all ( select 'name_21',?,'remark_21') union all ( select 'name_22',?,'remark_22') union all ( select 'name_23',?,'remark_23') union all ( select 'name_24',?,'remark_24') union all ( select 'name_25',?,'remark_25') union all ( select 'name_26',?,'remark_26') union all ( select 'name_27',?,'remark_27') union all ( select 'name_28',?,'remark_28') union all ( select 'name_29',?,'remark_29') union all ( select 'name_30',?,'remark_30') union all ( select 'name_31',?,'remark_31') union all ( select 'name_32',?,'remark_32') union all ( select 'name_33',?,'remark_33') union all ( select 'name_34',?,'remark_34') union all ( select 'name_35',?,'remark_35') union all ( select 'name_36',?,'remark_36') union all ( select 'name_37',?,'remark_37') union all ( select 'name_38',?,'remark_38') union all ( select 'name_39',?,'remark_39') union all ( select 'name_40',?,'remark_40') union all ( select 'name_41',?,'remark_41') union all ( select 'name_42',?,'remark_42') union all ( select 'name_43',?,'remark_43') union all ( select 'name_44',?,'remark_44') union all ( select 'name_45',?,'remark_45') union all ( select 'name_46',?,'remark_46') union all ( select 'name_47',?,'remark_47') union all ( select 'name_48',?,'remark_48') union all ( select 'name_49',?,'remark_49') union all ( select 'name_50',?,'remark_50') union all ( select 'name_51',?,'remark_51') union all ( select 'name_52',?,'remark_52') union all ( select 'name_53',?,'remark_53') union all ( select 'name_54',?,'remark_54') union all ( select 'name_55',?,'remark_55') union all ( select 'name_56',?,'remark_56') union all ( select 'name_57',?,'remark_57') union all ( select 'name_58',?,'remark_58') union all ( select 'name_59',?,'remark_59') union all ( select 'name_60',?,'remark_60') union all ( select 'name_61',?,'remark_61') union all ( select 'name_62',?,'remark_62') union all ( select 'name_63',?,'remark_63') union all ( select 'name_64',?,'remark_64') union all ( select 'name_65',?,'remark_65') union all ( select 'name_66',?,'remark_66') union all ( select 'name_67',?,'remark_67') union all ( select 'name_68',?,'remark_68') union all ( select 'name_69',?,'remark_69') union all ( select 'name_70',?,'remark_70') union all ( select 'name_71',?,'remark_71') union all ( select 'name_72',?,'remark_72') union all ( select 'name_73',?,'remark_73') union all ( select 'name_74',?,'remark_74') union all ( select 'name_75',?,'remark_75') union all ( select 'name_76',?,'remark_76') union all ( select 'name_77',?,'remark_77') union all ( select 'name_78',?,'remark_78') union all ( select 'name_79',?,'remark_79') union all ( select 'name_80',?,'remark_80') union all ( select 'name_81',?,'remark_81') union all ( select 'name_82',?,'remark_82') union all ( select 'name_83',?,'remark_83') union all ( select 'name_84',?,'remark_84') union all ( select 'name_85',?,'remark_85') union all ( select 'name_86',?,'remark_86') union all ( select 'name_87',?,'remark_87') union all ( select 'name_88',?,'remark_88') union all ( select 'name_89',?,'remark_89') union all ( select 'name_90',?,'remark_90') union all ( select 'name_91',?,'remark_91') union all ( select 'name_92',?,'remark_92') union all ( select 'name_93',?,'remark_93') union all ( select 'name_94',?,'remark_94') union all ( select 'name_95',?,'remark_95') union all ( select 'name_96',?,'remark_96') union all ( select 'name_97',?,'remark_97') union all ( select 'name_98',?,'remark_98') union all ( select 'name_99',?,'remark_99')
2019-05-19 17:06:12.661 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 0(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer), 8(Integer), 9(Integer), 10(Integer), 11(Integer), 12(Integer), 13(Integer), 14(Integer), 15(Integer), 16(Integer), 17(Integer), 18(Integer), 19(Integer), 20(Integer), 21(Integer), 22(Integer), 23(Integer), 24(Integer), 25(Integer), 26(Integer), 27(Integer), 28(Integer), 29(Integer), 30(Integer), 31(Integer), 32(Integer), 33(Integer), 34(Integer), 35(Integer), 36(Integer), 37(Integer), 38(Integer), 39(Integer), 40(Integer), 41(Integer), 42(Integer), 43(Integer), 44(Integer), 45(Integer), 46(Integer), 47(Integer), 48(Integer), 49(Integer), 50(Integer), 51(Integer), 52(Integer), 53(Integer), 54(Integer), 55(Integer), 56(Integer), 57(Integer), 58(Integer), 59(Integer), 60(Integer), 61(Integer), 62(Integer), 63(Integer), 64(Integer), 65(Integer), 66(Integer), 67(Integer), 68(Integer), 69(Integer), 70(Integer), 71(Integer), 72(Integer), 73(Integer), 74(Integer), 75(Integer), 76(Integer), 77(Integer), 78(Integer), 79(Integer), 80(Integer), 81(Integer), 82(Integer), 83(Integer), 84(Integer), 85(Integer), 86(Integer), 87(Integer), 88(Integer), 89(Integer), 90(Integer), 91(Integer), 92(Integer), 93(Integer), 94(Integer), 95(Integer), 96(Integer), 97(Integer), 98(Integer), 99(Integer)
2019-05-19 17:06:12.665 DEBUG [main][BaseJdbcLogger.java:132] - <== Updates: 100
2019-05-19 17:06:12.666 DEBUG [main][JdbcTransaction.java:66] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
2019-05-19 17:06:12.668 DEBUG [main][JdbcTransaction.java:117] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
2019-05-19 17:06:12.669 DEBUG [main][JdbcTransaction.java:85] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
2019-05-19 17:06:12.670 DEBUG [main][PooledDataSource.java:332] - Returned connection 898694235 to pool.
5.2.测试2
/**
* 批量插入实现2
* Batch返回值:固定
*/
public static void batchInsertUserInfoListByExecutorBachTest2() {
//1.构建sqlSession
SqlSession session = mybatisUtils.getSqlSession(ExecutorType.BATCH, false);
//2.映射sql的标识字符串
StringBuffer statement = new StringBuffer();
statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值
statement.append(".batchInsertUserInfoListByExecutorBach"); //select标签的id属性值
try {
//3.定义查询条件
for (int i = 0; i < 100; i++) {
JSONObject jsonObject = new JSONObject();
jsonObject.put("name", String.format("name_%d", i));
jsonObject.put("age", i);
jsonObject.put("remark", String.format("remark_%d", i));
// handler.parameterize(stmt);
// handler.batch(stmt);
// return -2147482646;
//4.执行查询返回一个唯一user对象的sql
int updateResult = session.insert(statement.toString(), jsonObject);
System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + jsonObject.get("respID"));
}
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
session.rollback();
} finally {
//5.资源释放(连接池回收)
mybatisUtils.closeSqlSession(session);
}
}
6.调用
public static void main(String[] args) throws Exception {
//getUserInfoByIDTest();
//getUserInfoByInTest();
//getUserInfoByInnerForeachTest();
//batchUpdateUserInfoListManualTest();
//batchUpdateUserInfoListAutoTest();
//batchInsertUserInfoListByUnionAllTest();
//batchInsertUserInfoListByExecutorBachTest2();
//insertUserXmlTest();
batchInsertUserInfoListByUnionAllTest();
}
}
2019-05-19 22:31:24.436 DEBUG [main][LogFactory.java:124] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
2019-05-19 22:31:24.449 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 22:31:24.450 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 22:31:24.450 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 22:31:24.451 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
2019-05-19 22:31:24.576 DEBUG [main][JdbcTransaction.java:129] - Opening JDBC Connection
2019-05-19 22:31:24.915 DEBUG [main][PooledDataSource.java:378] - Created connection 1786294176.
2019-05-19 22:31:24.918 DEBUG [main][JdbcTransaction.java:95] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:24.921 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:24.923 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_0',?,'remark_0')
2019-05-19 22:31:24.985 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 0(Integer)
[batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
2019-05-19 22:31:24.986 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:24.987 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_1',?,'remark_1')
2019-05-19 22:31:24.988 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 1(Integer)
[batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
2019-05-19 22:31:24.988 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:24.989 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_2',?,'remark_2')
2019-05-19 22:31:24.991 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 2(Integer)
[batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
2019-05-19 22:31:24.991 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:24.992 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_3',?,'remark_3')
2019-05-19 22:31:24.992 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 3(Integer)
[batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
2019-05-19 22:31:24.993 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:24.993 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_4',?,'remark_4')
2019-05-19 22:31:24.995 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 4(Integer)
[batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
2019-05-19 22:31:24.997 DEBUG [main][JdbcTransaction.java:66] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:25.002 DEBUG [main][JdbcTransaction.java:117] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:25.003 DEBUG [main][JdbcTransaction.java:85] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
2019-05-19 22:31:25.003 DEBUG [main][PooledDataSource.java:332] - Returned connection 1786294176 to pool.
总结
1.数据库连接时,设置运行多行查询。
2.多条语句,用“；”隔开即可。
3.batchInsertUserInfoListByUnionAll 一次性事务提交,数据库执行的sql长度有限制。
4.batchInsertUserInfoListByExecutorBach均是一次性提交。
