批量插入数据(基于Mybatis的实现-Oracle)

项目DB框架：Mybatis。DataBase:Oracle。

----------------------------------------------------------------------------

批量插入数据方式：

一、Mybatis 全局设置批处理；

二、Mybatis 局部设置批处理；

三、Mybatis foreach批量插入：

①SELECT UNION ALL；

②BEGIN INSERT INTO ...;INSERT INTO...;...;END;

四、java自带的批处理插入；

五、其他方式

-----------------------------------------------------------------------------

先说结论：Mybatis(全局/局部)批处理和java自带的批处理 性能上差不多，属于最优处理办法，我这边各种测试后，最后采用Mybatis局部批处理方式。

一、Mybatis 全局设置批处理

先上Spring-Mybatis.xml 配置信息

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" 5 xsi:schemaLocation="http://www.springframework.org/schema/beans 6 http://www.springframework.org/schema/beans/spring-beans.xsd 7 http://www.springframework.org/schema/context 8 http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> 9 10 <!-- 自动扫描(自动注入) -->11 <context:annotation-config/>12 <context:component-scan base-package="com.company.dao"/>13 14 <!-- 动态数据源 -->15 <bean id="dataSource" class="com.company.dao.datasource.DataSource">16 <property name="myConfigFile" value="mySource.xml"/>17 </bean>18 19 <!-- mybatis配置 -->20 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">21 <property name="dataSource" ref="dataSource"/>22 <property name="mapperLocations" value="classpath*:mapper/*/*/*.xml"/>23 <property name="configLocation" value="classpath:/mybatisConfig.xml"/>24 </bean>25 26 <!-- 自动创建映射器，不用单独为每个 mapper映射-->27 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">28 <property name="basePackage" value="com.company.dao.mapper"/>29 <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>30 </bean>31 32 <!-- 事务管理器配置,单数据源事务 -->33 <bean id="transactionManager"34 class="org.springframework.jdbc.datasource.DataSourceTransactionManager">35 <property name="dataSource" ref="dataSource"/>36 </bean>37 38 <tx:annotation-driven transaction-manager="transactionManager"/>39 40 </beans>

再上mybatisConfig.xml（在本项目中，我没有设置setting。最终采用的局部批处理，因此未设置全局批处理，具体原因后面再说。）

 1 <?mapper.xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 5 <settings> 6 <!-- 配置默认的执行器。SIMPLE 就是普通的执行器；REUSE 执行器会重用预处理语句（prepared statements）； BATCH 执行器将重用语句并执行批量更新。--> 7 <setting name="defaultExecutorType" value="BATCH"/> 8 <!--详见：http://www.mybatis.org/mybatis-3/zh/configuration.html--> 9 </settings>10 11 <!-- 别名列表 -->12 <typeAliases>13 <!-- typeAliases 中的配置都是配置别名，在此就不贴出来了 -->14 </typeAliases>15 16 </configuration>

这样子设置好后，在BaseService开放saveBatch(List<T> list)方法

 1 @Override 2 public void save(List<T> list) { 3 for (int i = 0;i < list.size();i++){ 4 mapper.insert(list.get(i)); 5 } 6 } 7 8 @Override 9 public void saveBatch(List<T> list) {10 int size = list.size();11 int unitNum = 500;12 int startIndex = 0;13 int endIndex = 0;14 while (size > 0){15 if(size > unitNum){16 endIndex = startIndex+unitNum;17 }else {18 endIndex = startIndex+size;19 }20 List<T> insertData = list.subList(startIndex,endIndex);21 save(insertData);22 size = size - unitNum;23 startIndex = endIndex;24 }25 }

虽然看上去是500条记录，一次次INSERT INTO，但由于在全局已经设置Mybatis是批处理执行器，所以这500条INSERT INTO只会与Oracle数据库通信一次。

全局设置批处理的局限性在哪里呢？

先附上mybatis官方的讨论列表中最很关键的一句：“If the BATCH executor is in use, the update counts are being lost. ”

设置全局批处理后，DB里的insert、Update和delete方法，都无法返回进行DML影响DB_TABLE的行数。

1.insert 无法返回影响的行数，这个好解决，一个批处理放在一个事务里，记录批处理失败次数，总数-批处理失败次数*单位批处理数据量，就能得到insert 影响DB_TABLE的行数；

2.但是update和delete就无法很简单的去统计影响行数了，如果做反复查询，反而降低了效率，得不偿失。

虽现在的项目尚未有需要反馈影响DB_TABLE行数的需求，但是为了更灵活，我们放弃了全局批处理的方式。

!这里提个疑问：为什么Mybatis官方，不将批处理的选择方式下沉到方法级别？方便开发者根据实际情况，灵活选择。我觉得这是个可以改进的地方，如有机会，可看源码去进行改进。

---------------------------------------------------------------------------------------------------------

二、Mybatis局部批处理方式

由于领导说全局批处理方式，不够灵活，不适宜项目所需，要另想办法支持。但是java自带的批处理，因为项目代码管理的要求，也不能采用。因此，在仔细阅读官方文档后，设想自己能否获取SQLSession后openSession，将这个会话设置为批处理呢？

先看MyBatis官方网站(须FanQiang)：http://www.mybatis.org/mybatis-3/zh/getting-started.html

1 SqlSession session = sqlSessionFactory.openSession();2 try {3 BlogMapper mapper = session.getMapper(BlogMapper.class);4 // do work5 } finally {6 session.close();7 }

后查阅Mybatis java API(须FanQiang):  http://www.mybatis.org/mybatis-3/zh/java-api.html

现在你有一个 SqlSessionFactory,可以用来创建 SqlSession 实例。

SqlSessionFactory 有六个方法可以用来创建 SqlSession 实例。通常来说,如何决定是你 选择下面这些方法时:

重载的 openSession()方法签名设置允许你选择这些可选中的任何一个组合。

1 SqlSession openSession()2 SqlSession openSession(boolean autoCommit)3 SqlSession openSession(Connection connection)4 SqlSession openSession(TransactionIsolationLevel level)5 SqlSession openSession(ExecutorType execType,TransactionIsolationLevel level)6 SqlSession openSession(ExecutorType execType)7 SqlSession openSession(ExecutorType execType, boolean autoCommit)8 SqlSession openSession(ExecutorType execType, Connection connection)9 Configuration getConfiguration();

因此出来了局部批处理第一套代码实现方式：

 1 public static void sqlSession(List<Student> data) throws IOException { 2 String resource = "mybatis-dataSource.xml"; 3 InputStream inputStream = null; 4 SqlSession batchSqlSession = null; 5 try{ 6 inputStream = Resources.getResourceAsStream(resource); 7 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 8 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 9 int batchCount = 500;//每批commit的个数10 for(int index = 0; index < data.size();index++){11 Student stu = data.get(index);12 batchSqlSession.getMapper(Student.class).insert(stu);13 if(index !=0 && index%batchCount == 0){14 batchSqlSession.commit();15 }16 }17 batchSqlSession.commit();18 }catch (Exception e){19 e.printStackTrace();20 }finally {21 if(batchSqlSession != null){22 batchSqlSession.close();23 }24 if(inputStream != null){25 inputStream.close();26 }27 }28 }

 1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6 <environments default="development"> 7 <environment id="development"> 8 <transactionManager type="JDBC"/> 9 <dataSource type="POOLED">10 <property name="driver" value="${driver}"/>11 <property name="url" value="${url}"/>12 <property name="username" value="${username}"/>13 <property name="password" value="${password}"/>14 </dataSource>15 </environment>16 </environments>17 <mappers>18 <mapper resource="org/mybatis/example/Student.xml"/>19 </mappers>20 </configuration>

已经在Spring-Mybatis.xml 中配置了SQLSessionFactory，那我为何还要自己去创建SQLSessionFactory呢？因此继续改良代码

 1 public static void mybatisSqlSession(List<Student> data){ 2 DefaultSqlSessionFactory sqlSessionFactory = (DefaultSqlSessionFactory) ServiceBeanConstant.CTX.getBean("sqlSessionFactory"); 3 SqlSession batchSqlSession = null; 4 try{ 5 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 6 int batchCount = 500;//每批commit的个数 7 for(int index = 0; index < data.size();index++){ 8 Student stu = data.get(index); 9 batchSqlSession.getMapper(StudentMapper.class).insert(stu);10 if(index !=0 && index%batchCount == 0){11 batchSqlSession.commit();12 }13 }14 batchSqlSession.commit();15 }catch (Exception e){16 e.printStackTrace();17 }finally {18 if(batchSqlSession != null){19 batchSqlSession.close();20 }21 }22 }

这个版本的局部批处理插入是比较满意的，最终采用的方式也是这个版本。

下面放出在IService接口定义和Service的具体实现代码：

IService接口定义

1 /**2 * 批处理插入数据(方法内部定义500条为一个批次进行提交)3 * 使用注意事项：必须在XxxMappper.xml中实现<insert id="insert" ...>....<insert/>的sql4 * @param data 批量插入的数据5 * @param mClass 调用的XxxMaperr.class6 * @auth robin7 * Created on 2016/3/148 */9 void saveBatch(List<T> data,Class mClass);

Service实现

 1 @Override 2 public void saveBatch(List<T> data,Class mClass) { 3 DefaultSqlSessionFactory sqlSessionFactory = (DefaultSqlSessionFactory) ServiceBeanConstant.CTX.getBean("sqlSessionFactory"); 4 SqlSession batchSqlSession = null; 5 try{ 6 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 7 int batchCount = 500;//每批commit的个数 8 for(int index = 0; index < data.size();index++){ 9 T t = data.get(index);10 ((BaseMapper)batchSqlSession.getMapper(mClass)).insert(t);11 if(index !=0 && index%batchCount == 0){12 batchSqlSession.commit();13 }14 }15 batchSqlSession.commit();16 }catch (Exception e){17 e.printStackTrace();18 }finally {19 if(batchSqlSession != null){20 batchSqlSession.close();21 }22 }23 }

局部和全局批处理插入对比：局部批处理，可以对特定一类的方法，进行数据批处理，不会影响其他DML语句，其他DML语句，可以正常返回影响DB_TABLE的行数。

!这样既能针对特殊需求(批处理)支持，也能支持未来需要返回影响数据行的要求。

注意：使用批处理方式进行DML操作，是无法反馈影响DB_TABLE行数的数据。无论是局部批处理还是java自带的批处理方式，皆无法反馈DB_TABLE count。

-------------------------------------------------------------------------------------

 三、Mybatis foreach批量插入

Mybatis foreach 批量插入，如果批量插入的数据量大，不得不说这真是一个非常糟糕的做法。

无论是SELECT ** UNION ALL 还是BEGIN ...;END; ，相对而言后者比前者稍微好点。

放出DB和我测试的结果：

①foreach union all的批量插入，现已有大量的博客资源可供参考，我就不贴出自己的实现方式了。

如果有兴趣可以参阅：http://blog.csdn.net/sanyuesan0000/article/details/19998727 (打开浏览器，复制url)

这篇博客。BEGIN END的方式，也是从这篇博客中得到启发。只不过他是把BEGIN END用在update中。

②foreach begin end 语句块

我的实现：

1 <insert id="insertBatch" parameterType="java.util.List">2 BEGIN3 <foreach collection="list" item="item" index="index" separator=";" >4 INSERT INTO TABLE.STUDENT (ID,AGE,NAME,STU_ID) VALUES5 ( DEMO.SEQ_EID.NEXTVAL,#{item.age},#{item.name},#{item.stuId} )6 </foreach>7 ;END ;8 </insert>

 调用方式：

 1 @Override 2 public void saveBatch(List<T> list) { 3 int size = list.size(); 4 int unitNum = 500; 5 int startIndex = 0; 6 int endIndex = 0; 7 while (size > 0){ 8 if(size > unitNum){ 9 endIndex = startIndex+unitNum;10 }else {11 endIndex = startIndex+size;12 }13 List<T> insertData = list.subList(startIndex,endIndex);14 mapper.insertBatch(insertData);15 size = size - unitNum;16 startIndex = endIndex;17 }

---------------------------------------------------------------------

四、java自带的批处理方式

废话不多说，直接上代码

 1 package DB; 2 3 import base.Student; 4 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.PreparedStatement; 8 import java.sql.SQLException; 9 import java.util.ArrayList;10 import java.util.List;11 12 /**13 * Created by robin on 2016/5/23.14 *15 * @author robin16 */17 public class InsertTableDemo {18 19 public static void main(String args[]) throws SQLException {20 Connection connection = null;21 List<Student> dataList = getDataList(100000);22 long startTime = 0;23 try{24 connection = getConn();25 startTime=System.currentTimeMillis();26 connection.setAutoCommit(false);27 PreparedStatement statement = connection.prepareStatement("INSERT INTO STUDENT (ID,AGE,NAME,STU_ID) VALUES ( DEMO.SEQ_EID.NEXTVAL, ?,?,? ) ");28 int num = 0;29 for (int i = 0;i< dataList.size();i++){30 Student s = dataList.get(i);31 statement.setInt(1, s.getAge());32 statement.setString(2, s.getName());33 statement.setString(3, s.getStuId());34 statement.addBatch();35 num++;36 if(num !=0 && num%500 == 0){37 statement.executeBatch();38 connection.commit();39 num = 0;40 }41 }42 statement.executeBatch();43 connection.commit();44 }catch (Exception e){45 e.printStackTrace();46 connection.rollback();47 }finally {48 if(connection != null){49 connection.close();50 }51 long endTime=System.currentTimeMillis();52 System.out.println("方法执行时间："+(endTime-startTime)+"ms");53 }54 55 }56 57 public static Connection getConn(){58 String driver = "oracle.jdbc.driver.OracleDriver";59 String url = "jdbc:oracle:thin:@//ip:port/DMEO"; //DMEO为数据库名60 String user = "user";61 String password = "pwd";62 try{63 Class.forName(driver);64 Connection conn = DriverManager.getConnection(url, user, password);65 return conn;66 } catch (ClassNotFoundException e) {67 e.printStackTrace();68 } catch (SQLException e) {69 e.printStackTrace();70 }71 return null;72 }73 public static List<Student> getDataList(int f){74 List<Student> data = new ArrayList<>();75 for (int i =0;i<f;i++){76 Student s = new Student(""+i,"小明" + i,i);77 data.add(s);78 }79 return data;80 }81 82 83 }

这种批量插入大量数据的方式，性能上最好。但是因为我们小组代码管理所限制，因此这种方式不使用。

------------------------------------------------------------------------

五、其他方式

现在已经忘了，其他方式到底使用过哪些，但总归是比以上四种效果都更差，所以没什么印象了。

如果各位，还有什么其他更好的批量插入数据的方式，欢迎加入讨论，集思广益。 

小编还为您整理了以下内容，可能对您也有帮助：

Oracle 批量插入数据怎么做

Oracle批量插入数据可用PL/SQL的命令窗口执行。

使用工具：PL/SQL

步骤：

1、登录PL/SQL到指定数据库。

2、登录后，点击左上方“纸片”状图标，然后选择“Command Window”选项，进入命令窗口。

3、然后在本地电脑编写insert（即插入语句），每句以逗号分隔。如图：

4、然后为文件起名字，以.sql为文件后缀，如“执行.sql”，保存到指定路径，如c盘data目录下。

5、在打开的命令窗口下执行如下语句：

其中“@”为必写内容，“c:data”为sql文件保存路径，“执行.sql”为要执行的脚本文件名。

6、敲击回车键执行，执行后会有成功提示，如图：

7、最后去目标表查询数据，检验结果成功。其中如图的四条为新插入的数据。

Oracle 批量插入数据怎么做

Oracle批量插入数据可用PL/SQL的命令窗口执行。

使用工具：PL/SQL

步骤：

1、登录PL/SQL到指定数据库。

2、登录后，点击左上方“纸片”状图标，然后选择“Command Window”选项，进入命令窗口。

3、然后在本地电脑编写insert（即插入语句），每句以逗号分隔。如图：

4、然后为文件起名字，以.sql为文件后缀，如“执行.sql”，保存到指定路径，如c盘data目录下。

5、在打开的命令窗口下执行如下语句：

其中“@”为必写内容，“c:data”为sql文件保存路径，“执行.sql”为要执行的脚本文件名。

6、敲击回车键执行，执行后会有成功提示，如图：

7、最后去目标表查询数据，检验结果成功。其中如图的四条为新插入的数据。

oracle中怎么插入多条数据

1、采用insert into values 语句插入一条，写很多条语句即可多条数据，这种主要针对于离散值以及一些基础信息的录入，如：insert into test（xh,mc) values('123','测试');

如果插入的数据有规律，可利用for、loop循环插入，主要用于批量生成测试数据

begin

for i in 1 .. 100 loop

insert into test(xh,mc) values(i||'','测试');

end loop;

end ;。

2、采用insert into selct from 语句来一次性插入一个集合,这种主要依据于要插入的数据源已经存储于数据库对象中,或者利用al虚表来构造数据，经过加工后写入一个集合。

insert into test (xh,mx) select '123','测试' from al;

3、采用plsql等工具、或者oracle的imp、impdp命令来导入,这种主要用数据库与数据库之间的大批量数据导入，导入的数据格式为plsql的pde、oracle的dmp等。dmp文件可使用

table_exists_action参数控制导入动作:replace替换原表,truncate清除原表数据再导入，append增量导入数据，当然impdp数据泵的导入要依赖于directory路径。

impdp 用户名/密码 mpfile=123.dmp logfile=123.log directory=imp_dir tables=test table_exists_action=append

4、使用excel文件直接拷贝。这种主要用于要写入的数据已是excel文件或者行列分明的其它格式文件，每一列的值和表结构相对应，可直接打开表的行级锁，把数据拷贝进入。

打开行级锁方法：

select t.*,rowid from 表名 t where 1=2;

select * from 表名 where 1=2 for update;

直接把excel数据拷贝到表里

mybatis向oracle批量插入CLOB数据（如果数据超过4000就会报错：仅能绑定要插入 LONG 列的 LONG 值）

参考下面的步骤

--clob测试表

create table t_clob(seq int,str clob);

insert into t_clob select rownum ,rpad('a',4000,'*') str from al connect by rownum<=10;

update t_clob set str=str||rpad('b',4000,'*')||rpad('c',4000,'*');

commit;

select * from t_clob;

--源数据库

--1.将clob字段的数据按4000转为多行保存到临时表中

create table t_clob_bak as 

SELECT seq,cast(substr(str,(level-1)*4000,4000) as varchar2(4000)) str

  FROM t_clob

CONNECT BY LEVEL <= trunc(length(str) / 4000)

       AND PRIOR seq = seq

       AND prior dbms_random.value IS NOT NULL;

select * from t_clob_bak;

--2.将t_clob_bak 导出t_clob_bak.sql文件（create+insert脚本）

--目标数据库

--1.执行t_clob_bak.sql,创建和导入临时表数据

--2.执行下面sql，将临时表数据转换到正式表t_clob

insert into t_clob 

SELECT seq, str

  FROM (SELECT seq,

               wm_concat(str) over(PARTITION BY seq ORDER BY rownum) str,

               row_number() over(PARTITION BY seq ORDER BY rownum DESC) rn

          FROM t_clob_bak)

WHERE rn = 1;