spring中使用mybatis實現(xiàn)批量插入的示例代碼
有3種實現(xiàn)方式:foreach,spring事務(wù),以及ExecutorType.BATCH.
1. foreach方式
這種方式實際是對SQL語句進(jìn)行拼接,生成一個長長的SQL,對很多變量進(jìn)行綁定。如果數(shù)據(jù)量不大(1000個以內(nèi)),可以用這種方式。如果數(shù)據(jù)量太大,可能數(shù)據(jù)庫會報錯。
定義接口
public interface StudentMapper05 { public void insertStudent(List<Student> studentList); }
定義mapper
適用于Oracle數(shù)據(jù)庫
<insert id="insertStudent"> BEGIN <foreach collection="list" item="student" index="index" separator=""> INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, #{student.name}, #{student.branch}, #{student.percentage}, #{student.phone}, #{student.email}); </foreach> END; </insert>
這個mapper的含義,就是把上送的studentList拼接成一個長SQL,拼成的SQL類似:
BEGIN INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, ?, ?, ?, ?, ?); INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, ?, ?, ?, ?, ?); INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, ?, ?, ?, ?, ?); ... END;
studentList有幾個,就會生成多少個insert語句拼接到一起,每個?都會進(jìn)行變量綁定,所以當(dāng)studentList中數(shù)據(jù)量較多時,生成的SQL會很長,導(dǎo)致數(shù)據(jù)庫執(zhí)行報錯。
dao
public class StudentDao05 { private StudentMapper05 studentMapper; // 省略getter和setter public void insertStudentList(List<Student> studentList) { studentMapper.insertStudent(studentList); } }
beans
mybatis-spring-05.xml:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="oracleDataSource" /> <property name="configLocation" value="classpath:mybatis/config/mybatis-config-05.xml"/> </bean> <bean id="studentMapper05" class="org.mybatis.spring.mapper.MapperFactoryBean"> <property name="mapperInterface" value="com.ws.experiment.spring.mybatis.mapper.StudentMapper05" /> <property name="sqlSessionFactory" ref="sqlSessionFactory" /> </bean> <bean id="studentDao05" class="com.ws.experiment.spring.mybatis.dao.StudentDao05"> <property name="studentMapper" ref="studentMapper05" /> </bean>
main函數(shù)
public static void main(String[] args) { String[] configFiles = new String[]{"spring-beans-config.xml", "mybatis/mybatis-spring-05.xml"}; // 分別配置datasource和mybatis相關(guān)bean ApplicationContext context = new ClassPathXmlApplicationContext(configFiles); StudentDao05 studentDao = (StudentDao05)context.getBean("studentDao05"); int counts[] = new int[]{10, 50, 100, 200, 500, 1000, 2000, 3000, 5000, 8000}; for (int count : counts) { List<Student> studentList = new ArrayList<>(); for (int i = 0; i < count; i++) { Student st = new Student(); st.setName("name"); st.setBranch(""); st.setEmail(""); st.setPercentage(0); st.setPhone(0); studentList.add(st); } long startTime = System.currentTimeMillis(); studentDao.insertStudentList(studentList); long endTime = System.currentTimeMillis(); System.out.println("插入" + count + "筆數(shù)據(jù)耗時: " + (endTime - startTime) +" ms"); } }
測試結(jié)果
插入100筆數(shù)據(jù)耗時: 197 ms
插入200筆數(shù)據(jù)耗時: 232 ms
插入500筆數(shù)據(jù)耗時: 421 ms
插入1000筆數(shù)據(jù)耗時: 650 ms
插入2000筆數(shù)據(jù)耗時: 1140 ms
插入3000筆數(shù)據(jù)耗時: 27113 ms
插入5000筆數(shù)據(jù)耗時: 98213 ms
插入8000筆數(shù)據(jù)耗時: 301101 ms
2. 借助spring事務(wù)
借助spring事務(wù),插入一組數(shù)據(jù)
開啟spring事務(wù)
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="oracleDataSource" /> </bean> <tx:annotation-driven transaction-manager="transactionManager" />
定義接口
public interface StudentMapper06 { public void insertStudent(@Param("student") Student student); }
mapper
<insert id="insertStudent"> INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, #{student.name}, #{student.branch}, #{student.percentage}, #{student.phone}, #{student.email}) </insert>
dao
public class StudentDao06 { private StudentMapper06 studentMapper; // 省略getter和setter @Transactional // spring事務(wù)控制 public void insertStudentList(List<Student> students) { for (Student student : students) { studentMapper.insertStudent(student); } } }
beans
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="oracleDataSource" /> <property name="configLocation" value="classpath:mybatis/config/mybatis-config-06.xml"/> </bean> <bean id="studentMapper06" class="org.mybatis.spring.mapper.MapperFactoryBean"> <property name="mapperInterface" value="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" /> <property name="sqlSessionFactory" ref="sqlSessionFactory" /> </bean> <bean id="studentDao06" class="com.ws.experiment.spring.mybatis.dao.StudentDao06"> <property name="studentMapper" ref="studentMapper06" /> </bean>
main
略
測試結(jié)果
batchInsert001插入10筆數(shù)據(jù)耗時: 602 ms
batchInsert001插入50筆數(shù)據(jù)耗時: 196 ms
batchInsert001插入100筆數(shù)據(jù)耗時: 284 ms
batchInsert001插入200筆數(shù)據(jù)耗時: 438 ms
batchInsert001插入500筆數(shù)據(jù)耗時: 944 ms
batchInsert001插入1000筆數(shù)據(jù)耗時: 1689 ms
batchInsert001插入2000筆數(shù)據(jù)耗時: 3138 ms
batchInsert001插入3000筆數(shù)據(jù)耗時: 4427 ms
batchInsert001插入5000筆數(shù)據(jù)耗時: 7368 ms
batchInsert001插入8000筆數(shù)據(jù)耗時: 11832 ms
3. 使用ExecutorType.BATCH
基本原理是SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);,設(shè)置BATCH方式的sqlSession
有三種設(shè)置方式:
3.1 在mybatis的config文件中設(shè)置
SqlSessionFactoryBean中可以配置配置文件:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="oracleDataSource" /> <property name="configLocation" value="classpath:mybatis/config/mybatis-config-06.xml"/> </bean>
這個mybatis配置文件中,設(shè)置BATCH方式:
<configuration> <settings> <!-- 默認(rèn)打開BATCH的Executor --> <setting name="defaultExecutorType" value="BATCH" /> </settings> <mappers> <mapper class="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" /> </mappers> </configuration>
這樣,默認(rèn)打開的sqlSession就都是BATCH方式的。再與spring的事務(wù)結(jié)合(參看上一節(jié)中的spring事務(wù)設(shè)置),就可以實現(xiàn)批量插入。
測試結(jié)果:
batchInsert001插入10筆數(shù)據(jù)耗時: 565 ms
batchInsert001插入50筆數(shù)據(jù)耗時: 117 ms
batchInsert001插入100筆數(shù)據(jù)耗時: 98 ms
batchInsert001插入200筆數(shù)據(jù)耗時: 106 ms
batchInsert001插入500筆數(shù)據(jù)耗時: 145 ms
batchInsert001插入1000筆數(shù)據(jù)耗時: 132 ms
batchInsert001插入2000筆數(shù)據(jù)耗時: 154 ms
batchInsert001插入3000筆數(shù)據(jù)耗時: 163 ms
batchInsert001插入5000筆數(shù)據(jù)耗時: 200 ms
batchInsert001插入8000筆數(shù)據(jù)耗時: 250 ms
3.2 自己創(chuàng)建sqlSession,手工commit
SqlSessionFactory sqlSessionFactory = (SqlSessionFactory)context.getBean("sqlSessionFactory"); SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); StudentMapper06 studentMapper = sqlSession.getMapper(StudentMapper06.class); for (int i = 0; i < count; i++) { Student st = new Student(); st.setName("name"); ... studentMapper.insertStudent(st); } sqlSession.commit(); sqlSession.clearCache(); sqlSession.close();
測試結(jié)果:
batchInsert002插入10筆數(shù)據(jù)耗時: 568 ms
batchInsert002插入50筆數(shù)據(jù)耗時: 157 ms
batchInsert002插入100筆數(shù)據(jù)耗時: 132 ms
batchInsert002插入200筆數(shù)據(jù)耗時: 135 ms
batchInsert002插入500筆數(shù)據(jù)耗時: 148 ms
batchInsert002插入1000筆數(shù)據(jù)耗時: 139 ms
batchInsert002插入2000筆數(shù)據(jù)耗時: 151 ms
batchInsert002插入3000筆數(shù)據(jù)耗時: 139 ms
batchInsert002插入5000筆數(shù)據(jù)耗時: 207 ms
batchInsert002插入8000筆數(shù)據(jù)耗時: 299 ms
3.3 使用sqlSessionTemplate在XML文件中創(chuàng)建bean
創(chuàng)建一個SqlSessionTemplate,然后注入到MapperFactoryBean中,生成對應(yīng)的mapper:
<!-- 以ExecutorType.BATCH方式插入數(shù)據(jù)庫 --> <bean id="batchSqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" /> <constructor-arg name="executorType" value="BATCH" /> </bean> <bean id="studentMapper06_batch" class="org.mybatis.spring.mapper.MapperFactoryBean"> <property name="mapperInterface" value="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" /> <property name="sqlSessionTemplate" ref="batchSqlSessionTemplate" /> </bean> <bean id="studentDao06_batch" class="com.ws.experiment.spring.mybatis.dao.StudentDao06"> <property name="studentMapper" ref="studentMapper06_batch" /> </bean>
與spring的事務(wù)結(jié)合后(參看上一節(jié)中的spring事務(wù)設(shè)置),就可以實現(xiàn)批量插入
測試結(jié)果
batchInsert003插入10筆數(shù)據(jù)耗時: 651 ms
batchInsert003插入50筆數(shù)據(jù)耗時: 133 ms
batchInsert003插入100筆數(shù)據(jù)耗時: 124 ms
batchInsert003插入200筆數(shù)據(jù)耗時: 129 ms
batchInsert003插入500筆數(shù)據(jù)耗時: 144 ms
batchInsert003插入1000筆數(shù)據(jù)耗時: 179 ms
batchInsert003插入2000筆數(shù)據(jù)耗時: 229 ms
batchInsert003插入3000筆數(shù)據(jù)耗時: 241 ms
batchInsert003插入5000筆數(shù)據(jù)耗時: 216 ms
batchInsert003插入8000筆數(shù)據(jù)耗時: 259 ms
到此這篇關(guān)于spring中使用mybatis實現(xiàn)批量插入的示例代碼的文章就介紹到這了,更多相關(guān)spring mybatis批量插入內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- mybatis中批量插入的兩種方式(高效插入)
- MyBatis批量插入(insert)數(shù)據(jù)操作
- Mybatis中使用updateBatch進(jìn)行批量更新
- Mybatis批量修改的操作代碼
- 詳解mybatis 批量更新數(shù)據(jù)兩種方法效率對比
- mybatis執(zhí)行批量更新batch update 的方法(oracle,mysql兩種)
- MyBatis批量添加、修改和刪除
- Mybatis批量刪除多表
- Mybatis 插入一條或批量插入 返回帶有自增長主鍵記錄的實例
- MyBatis批量插入數(shù)據(jù)到Oracle數(shù)據(jù)庫中的兩種方式(實例代碼)
- Mybatis批量更新報錯問題
- Mybatis批量更新三種方式的實現(xiàn)
相關(guān)文章
Java Socket實現(xiàn)猜數(shù)字小游戲
這篇文章主要為大家詳細(xì)介紹了Java Socket實現(xiàn)猜數(shù)字小游戲,文中示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2020-09-09如何使用@Slf4j和logback-spring.xml搭建日志框架
這篇文章主要介紹了如何使用@Slf4j和logback-spring.xml搭建日志框架問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06Springboot Thymeleaf實現(xiàn)HTML屬性設(shè)置
這篇文章主要介紹了Springboot Thymeleaf實現(xiàn)HTML屬性設(shè)置,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2007-11-11SpringBoot集成redis與session實現(xiàn)分布式單點登錄
這篇文章主要介紹了SpringBoot集成redis與session實現(xiàn)分布式單點登錄,文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價值,需要的小伙伴可以參考一下2022-09-09Java concurrency集合之ConcurrentHashMap_動力節(jié)點Java學(xué)院整理
這篇文章主要介紹了Java concurrency集合之ConcurrentHashMap的相關(guān)資料,需要的朋友可以參考下2017-06-06JDK1.7 Paths,Files類實現(xiàn)文件夾的復(fù)制與刪除的實例
下面小編就為大家分享一篇JDK1.7 Paths,Files類實現(xiàn)文件夾的復(fù)制與刪除的實例,具有很好的參考價值,希望對大家有所幫助。以前跟隨小編過來看看吧2017-11-11解決idea報錯 Connot resolve column 的問題
這篇文章主要介紹了解決idea報錯 Connot resolve column 的問題,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02