我在几年前写过一个用hibernate进行批量新增的功能。由于,那个功能只是根据前台页面输入的数据进行新增,理论上一次输入也不会太多,因此,只是用for循环遍历全部数据,每10条数据刷出保存到数据库中而已。
这几年用mybatis比较多,很想知道如何用mybatis来批量新增。实际上,最好是用insert into...select这种sql来批量新增比较好,但是,这种方式依赖于select的那张表,其业务场景类似这样:需要将订单明细表中的数据写入到拣货出库明细表。而那种从无到有的新增,就不能用这种方式,比如:Excel批量导入。
从无到有的新增有三种方式:
1、mybatis普通模式下用for循环进行新增。
2、mybatis批量模式下用for循环进行新增。
3、依赖于数据库,使用<foreach>拼接批量插入的SQL:
--①、mysql中可以是这样:insert into table(field1,field2,...) values
('test1','test1',...),('test2','test2',...),('test3','test3',...)...
--②、oracle 9i之后的版本中可以这样:insert all
into table(field1,field2,...) values ('test1','test1',...)
into table(field1,field2,...) values ('test2','test2',...)
into table(field1,field2,...) values ('test3','test3',...)
......
select 1 from dual (这种方式只是猜想,没有实践过)
接下来贴出测试使用的代码和插入10W、50W、100W条数据的完成时间:
SqlSessionFactoryUtil工具类
public class SqlSessionFactoryUtil {
// SqlSessionFactory
private static SqlSessionFactory sqlSessionFactory = null;
// 锁
private static final Class<?> CLASS_LOCK = SqlSessionFactoryUtil.class;
/**
* 私有构造方法
*/
private SqlSessionFactoryUtil() {
}
/**
* 获取SqlSessionFactory的实例
*
* @return
*/
public static SqlSessionFactory getSqlSessionFactory() {
try {
// mybatis配置文件
String resource = "mybatis.xml";
InputStream cfgStream = Resources.getResourceAsStream(resource);
InputStream proStream = Resources.getResourceAsStream("jdbc.properties");
// 对加密的数据库账户/密码进行解密
Properties properties = new Properties();
properties.load(proStream);
properties.setProperty("jdbc.username",
new String(DESUtil.decryptDES(properties.getProperty("jdbc.username"))));
properties.setProperty("jdbc.password",
new String(DESUtil.decryptDES(properties.getProperty("jdbc.password"))));
// 实例化sqlSessionFactory
synchronized (CLASS_LOCK) {
if (sqlSessionFactory == null) {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(cfgStream, properties);
}
}
} catch (Exception e) {
Logger.getLogger(SqlSessionFactoryUtil.class.getName()).error("获取SqlSessionFactory失败", e);
}
return sqlSessionFactory;
}
/**
* 获取SqlSession
*
* @return
*/
public static SqlSession openSqlSession(ExecutorType mode) {
if (sqlSessionFactory == null) {
getSqlSessionFactory();
}
return sqlSessionFactory.openSession(mode, false);
}
}
实体类:
public class Test {
private String id;
private String name;
private String delFlag;
// 省略getter/setter方法
}
Mapper类:
public interface TestMapper {
public int insertTest(Map<String, String> param);
public int insertTestList(List<Map<String, String>> params);
}
Mapper的XML配置文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://www.mybatis.org/dtd/mybatis-3-config.dtd">
<mapper namespace="com.wang.learn.map.TestMapper">
<insert id="insertTest" parameterType="java.util.Map">
INSERT INTO t_test (
id,
name,
del_flag
)
VALUES
(
#{id},
#{name},
#{delFlag}
)
</insert>
<insert id="insertTestList" parameterType="java.util.List">
INSERT INTO t_test (
id,
name,
del_flag
)
VALUES
<foreach collection ="list" item="test" separator =",">
(#{test.id}, #{test.name}, #{test.delFlag})
</foreach >
</insert>
</mapper>
使用的Junit进行测试,代码如下:
public class TestBatch {
/**
* 测试for循环插入
*/
@Test
public void testForInsert() {
long start = System.currentTimeMillis();
SqlSession sqlSession = SqlSessionFactoryUtil.openSqlSession(ExecutorType.SIMPLE);
TestMapper mapper = sqlSession.getMapper(TestMapper.class);
Map<String, String> params = new HashMap<String, String>();
for (int i = 0; i < 100000; i++) {
params = new HashMap<String, String>();
params.put("id", "testForInsert:" + i);
params.put("name", "test:" + i);
params.put("delFlag", "N");
mapper.insertTest(params);
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
/**
* 测试mybatis的批量模式插入
*/
@Test
public void testBatchMode() {
long start = System.currentTimeMillis();
SqlSession sqlSession = SqlSessionFactoryUtil.openSqlSession(ExecutorType.BATCH);
TestMapper mapper = sqlSession.getMapper(TestMapper.class);
Map<String, String> params = new HashMap<String, String>();
for (int i = 0; i < 100000; i++) {
params = new HashMap<String, String>();
params.put("id", "testForInsert:" + i);
params.put("name", "test:" + i);
params.put("delFlag", "N");
mapper.insertTest(params);
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
/**
* 测试xml中foreach循环插入
* 由于mysql会限制server接受的数据包大小,因此需配置my.ini中[mysqld]中max_allowed_packet=50M
*/
@Test
public void testXmlForeach() {
long start = System.currentTimeMillis();
SqlSession sqlSession = SqlSessionFactoryUtil.openSqlSession(ExecutorType.SIMPLE);
TestMapper mapper = sqlSession.getMapper(TestMapper.class);
List<Map<String, String>> params = new ArrayList<Map<String, String>>();
Map<String, String> param;
for (int i = 0; i < 100000; i++) {
param = new HashMap<String, String>();
param.put("id", "testForInsert:" + i);
param.put("name", "test:" + i);
param.put("delFlag", "N");
params.add(param);
}
mapper.insertTestList(params);
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
}
使用三种批量新增的方式插入10W、50W、100W条数据,完成时间如下:
综上,可以看出mybatis的批量模式是最快的(mybatis测试版本为3.3.0)。