MyBatis批量插入

转载自：http://www.it165.net/database/html/201408/7951.html

环境: mybatis + oracle11g 

1.使用"直接路径插入"(下面sql语句中的"/*+append_values */"),并且使用关键字"union all":

<insert id="addUidCodeBatch"  parameterType="java.util.List">  
    insert into /*+append_values */
        T_UID_CODE(C_UID_CODE,
		         C_SERAIL_LEN,
		         C_BATCH_CODE,
		         C_TYPE,
		         C_CREATE_TIME,
		         C_SUPER_CODE,
		         c_security_code,
		         C_SERIAL_CODE
		         )
       <foreach collection="list" item="item" index="index" separator="union all" >  
		  select #{item.uidCode},
		         #{item.kCode},      
		         #{item.batchCode},
		         #{item.type},
		         sysdate,
		         #{item.superCode},
		         #{item.securityCode},
		         #{item.serialCode}
		  from dual
       </foreach>  
    </insert>

2.dao层实现: 之前是一次性commit,这样做会随着插入数目的增大,执行速度陡然变慢,所以应该分批次进行插入:

public void save(List<UidCodeBean> uidCodeList) throws Exception {
	SqlSession batchSqlSession = null;
	try {
		batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);//获取批量方式的sqlsession		
		int batchCount = 1000;//每批commit的个数
		int batchLastIndex = batchCount - 1;//每批最后一个的下标
		for(int index = 0; index < uidCodeList.size()-1;){
			if(batchLastIndex > uidCodeList.size()-1){
				batchLastIndex = uidCodeList.size() - 1;
				batchSqlSession.insert(NAMESPACE+".addUidCodeBatch", uidCodeList.subList(index, batchLastIndex));
				batchSqlSession.commit();
				System.out.println("index:"+index+"     batchLastIndex:"+batchLastIndex);
				break;//数据插入完毕,退出循环
				
			}else{
				batchSqlSession.insert(NAMESPACE+".addUidCodeBatch", uidCodeList.subList(index, batchLastIndex));					                batchSqlSession.commit();
				System.out.println("index:"+index+"     batchLastIndex:"+batchLastIndex);
				index = batchLastIndex + 1;//设置下一批下标
				batchLastIndex = index + (batchCount - 1);						
			}				
		}						
	}finally{
		batchSqlSession.close();
	}		
}

    关于批量模式的sqlSession也可以这样获取，因为平时我们都是使用的springMVC+MyBatis， 所以在操作数据库时，选择的是org.mybatis.spring.SqlSessionTemplate这个对象，在spring配置文件中配置好这个bean（加入数据源）

        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
 		<property name="autoCommitOnClose" value="${cpool.autoCommitOnClose}" />
		<property name="checkoutTimeout" value="${cpool.checkoutTimeout}" />
		<property name="initialPoolSize" value="${cpool.minPoolSize}" />  
		<property name="minPoolSize" value="${cpool.minPoolSize}" />
		<property name="maxPoolSize" value="${cpool.maxPoolSize}" />
		<property name="maxIdleTime" value="${cpool.maxIdleTime}" />
		<property name="acquireIncrement" value="${cpool.acquireIncrement}" />
		<property name="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}" />
	</bean>
	
	
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="mapperLocations" value="classpath*:META-INF/mybatis/**/*.xml"></property>
	</bean>
      
	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
       
        ......<!--事务-->

然后在程序中可以这样获取

//新获取一个模式为BATCH，自动提交为false的session
//如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交，可能导致内存溢出
SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);