国产成人精品久久免费动漫-国产成人精品天堂-国产成人精品区在线观看-国产成人精品日本-a级毛片无码免费真人-a级毛片毛片免费观看久潮喷

您的位置:首頁(yè)技術(shù)文章
文章詳情頁(yè)

spring中使用mybatis實(shí)現(xiàn)批量插入的示例代碼

瀏覽:3日期:2023-09-04 08:26:00

有3種實(shí)現(xiàn)方式:foreach,spring事務(wù),以及ExecutorType.BATCH.

1. foreach方式

這種方式實(shí)際是對(duì)SQL語(yǔ)句進(jìn)行拼接,生成一個(gè)長(zhǎng)長(zhǎng)的SQL,對(duì)很多變量進(jìn)行綁定。如果數(shù)據(jù)量不大(1000個(gè)以內(nèi)),可以用這種方式。如果數(shù)據(jù)量太大,可能數(shù)據(jù)庫(kù)會(huì)報(bào)錯(cuò)。

定義接口

public interface StudentMapper05 { public void insertStudent(List<Student> studentList);}

定義mapper

適用于Oracle數(shù)據(jù)庫(kù)

<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>

這個(gè)mapper的含義,就是把上送的studentList拼接成一個(gè)長(zhǎng)SQL,拼成的SQL類似:

BEGININSERT 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有幾個(gè),就會(huì)生成多少個(gè)insert語(yǔ)句拼接到一起,每個(gè)?都會(huì)進(jìn)行變量綁定,所以當(dāng)studentList中數(shù)據(jù)量較多時(shí),生成的SQL會(huì)很長(zhǎng),導(dǎo)致數(shù)據(jù)庫(kù)執(zhí)行報(bào)錯(cuò)。

dao

public class StudentDao05 { private StudentMapper05 studentMapper; // 省略getter和setter public void insertStudentList(List<Student> studentList) { studentMapper.insertStudent(studentList); }}

beans

mybatis-spring-05.xml:

<bean class='org.mybatis.spring.SqlSessionFactoryBean'> <property name='dataSource' ref='oracleDataSource' /> <property name='configLocation' value='classpath:mybatis/config/mybatis-config-05.xml'/></bean><bean class='org.mybatis.spring.mapper.MapperFactoryBean'> <property name='mapperInterface' value='com.ws.experiment.spring.mybatis.mapper.StudentMapper05' /> <property name='sqlSessionFactory' ref='sqlSessionFactory' /></bean><bean 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ù)耗時(shí): ' + (endTime - startTime) +' ms'); }}

測(cè)試結(jié)果

插入100筆數(shù)據(jù)耗時(shí): 197 ms插入200筆數(shù)據(jù)耗時(shí): 232 ms插入500筆數(shù)據(jù)耗時(shí): 421 ms插入1000筆數(shù)據(jù)耗時(shí): 650 ms插入2000筆數(shù)據(jù)耗時(shí): 1140 ms插入3000筆數(shù)據(jù)耗時(shí): 27113 ms插入5000筆數(shù)據(jù)耗時(shí): 98213 ms插入8000筆數(shù)據(jù)耗時(shí): 301101 ms

2. 借助spring事務(wù)

借助spring事務(wù),插入一組數(shù)據(jù)

開(kāi)啟spring事務(wù)

<bean 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 class='org.mybatis.spring.SqlSessionFactoryBean'> <property name='dataSource' ref='oracleDataSource' /> <property name='configLocation' value='classpath:mybatis/config/mybatis-config-06.xml'/></bean><bean class='org.mybatis.spring.mapper.MapperFactoryBean'> <property name='mapperInterface' value='com.ws.experiment.spring.mybatis.mapper.StudentMapper06' /> <property name='sqlSessionFactory' ref='sqlSessionFactory' /></bean><bean class='com.ws.experiment.spring.mybatis.dao.StudentDao06'> <property name='studentMapper' ref='studentMapper06' /></bean>

main

測(cè)試結(jié)果

batchInsert001插入10筆數(shù)據(jù)耗時(shí): 602 msbatchInsert001插入50筆數(shù)據(jù)耗時(shí): 196 msbatchInsert001插入100筆數(shù)據(jù)耗時(shí): 284 msbatchInsert001插入200筆數(shù)據(jù)耗時(shí): 438 msbatchInsert001插入500筆數(shù)據(jù)耗時(shí): 944 msbatchInsert001插入1000筆數(shù)據(jù)耗時(shí): 1689 msbatchInsert001插入2000筆數(shù)據(jù)耗時(shí): 3138 msbatchInsert001插入3000筆數(shù)據(jù)耗時(shí): 4427 msbatchInsert001插入5000筆數(shù)據(jù)耗時(shí): 7368 msbatchInsert001插入8000筆數(shù)據(jù)耗時(shí): 11832 ms

3. 使用ExecutorType.BATCH

基本原理是SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);,設(shè)置BATCH方式的sqlSession

有三種設(shè)置方式:

3.1 在mybatis的config文件中設(shè)置

SqlSessionFactoryBean中可以配置配置文件:

<bean class='org.mybatis.spring.SqlSessionFactoryBean'> <property name='dataSource' ref='oracleDataSource' /> <property name='configLocation' value='classpath:mybatis/config/mybatis-config-06.xml'/></bean>

這個(gè)mybatis配置文件中,設(shè)置BATCH方式:

<configuration> <settings> <!-- 默認(rèn)打開(kāi)BATCH的Executor --> <setting name='defaultExecutorType' value='BATCH' /> </settings> <mappers> <mapper /> </mappers></configuration>

這樣,默認(rèn)打開(kāi)的sqlSession就都是BATCH方式的。再與spring的事務(wù)結(jié)合(參看上一節(jié)中的spring事務(wù)設(shè)置),就可以實(shí)現(xiàn)批量插入。

測(cè)試結(jié)果:

batchInsert001插入10筆數(shù)據(jù)耗時(shí): 565 msbatchInsert001插入50筆數(shù)據(jù)耗時(shí): 117 msbatchInsert001插入100筆數(shù)據(jù)耗時(shí): 98 msbatchInsert001插入200筆數(shù)據(jù)耗時(shí): 106 msbatchInsert001插入500筆數(shù)據(jù)耗時(shí): 145 msbatchInsert001插入1000筆數(shù)據(jù)耗時(shí): 132 msbatchInsert001插入2000筆數(shù)據(jù)耗時(shí): 154 msbatchInsert001插入3000筆數(shù)據(jù)耗時(shí): 163 msbatchInsert001插入5000筆數(shù)據(jù)耗時(shí): 200 msbatchInsert001插入8000筆數(shù)據(jù)耗時(shí): 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();

測(cè)試結(jié)果:

batchInsert002插入10筆數(shù)據(jù)耗時(shí): 568 msbatchInsert002插入50筆數(shù)據(jù)耗時(shí): 157 msbatchInsert002插入100筆數(shù)據(jù)耗時(shí): 132 msbatchInsert002插入200筆數(shù)據(jù)耗時(shí): 135 msbatchInsert002插入500筆數(shù)據(jù)耗時(shí): 148 msbatchInsert002插入1000筆數(shù)據(jù)耗時(shí): 139 msbatchInsert002插入2000筆數(shù)據(jù)耗時(shí): 151 msbatchInsert002插入3000筆數(shù)據(jù)耗時(shí): 139 msbatchInsert002插入5000筆數(shù)據(jù)耗時(shí): 207 msbatchInsert002插入8000筆數(shù)據(jù)耗時(shí): 299 ms

3.3 使用sqlSessionTemplate在XML文件中創(chuàng)建bean

創(chuàng)建一個(gè)SqlSessionTemplate,然后注入到MapperFactoryBean中,生成對(duì)應(yīng)的mapper:

<!-- 以ExecutorType.BATCH方式插入數(shù)據(jù)庫(kù) --><bean class='org.mybatis.spring.SqlSessionTemplate'> <constructor-arg name='sqlSessionFactory' ref='sqlSessionFactory' /> <constructor-arg name='executorType' value='BATCH' /></bean><bean class='org.mybatis.spring.mapper.MapperFactoryBean'> <property name='mapperInterface' value='com.ws.experiment.spring.mybatis.mapper.StudentMapper06' /> <property name='sqlSessionTemplate' ref='batchSqlSessionTemplate' /></bean><bean class='com.ws.experiment.spring.mybatis.dao.StudentDao06'> <property name='studentMapper' ref='studentMapper06_batch' /></bean>

與spring的事務(wù)結(jié)合后(參看上一節(jié)中的spring事務(wù)設(shè)置),就可以實(shí)現(xiàn)批量插入

測(cè)試結(jié)果

batchInsert003插入10筆數(shù)據(jù)耗時(shí): 651 msbatchInsert003插入50筆數(shù)據(jù)耗時(shí): 133 msbatchInsert003插入100筆數(shù)據(jù)耗時(shí): 124 msbatchInsert003插入200筆數(shù)據(jù)耗時(shí): 129 msbatchInsert003插入500筆數(shù)據(jù)耗時(shí): 144 msbatchInsert003插入1000筆數(shù)據(jù)耗時(shí): 179 msbatchInsert003插入2000筆數(shù)據(jù)耗時(shí): 229 msbatchInsert003插入3000筆數(shù)據(jù)耗時(shí): 241 msbatchInsert003插入5000筆數(shù)據(jù)耗時(shí): 216 msbatchInsert003插入8000筆數(shù)據(jù)耗時(shí): 259 ms

到此這篇關(guān)于spring中使用mybatis實(shí)現(xiàn)批量插入的示例代碼的文章就介紹到這了,更多相關(guān)spring mybatis批量插入內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)!

標(biāo)簽: Spring
相關(guān)文章:
主站蜘蛛池模板: 久久久91精品国产一区二区 | 美女又爽又黄视频 | 亚洲一区免费观看 | 色www永久免费网站国产 | 成人免费高清视频 | 91精品国产乱码久久久久久 | 久久亚洲精品成人综合 | 3d动漫精品成人一区二区三 | 免费播放欧美毛片欧美a | 欧美一级艳片视频免费观看 | 在线观看欧洲成人免费视频 | 国产欧美精品午夜在线播放 | 毛片在线免费观看网站 | 国产乱码一区二区三区四川人 | 午夜刺激爽爽视频免费观看 | 韩国在线精品福利视频在线观看 | 日韩精品网址 | 免费看a网站 | 情侣自拍啪啪 | 欧美一区三区 | 天天精品在线 | 亚洲看看 | 亚洲高清在线观看视频 | 国产一区二区三区四区五区tv | 成人欧美精品久久久久影院 | 自拍国内| 日韩欧美中文字幕一区二区三区 | 国产成年人在线观看 | a在线v | 国产精选经典三级小泽玛利亚 | 日本一区二区不卡久久入口 | 亚洲超大尺度激情啪啪人体 | 三级全黄a | 免费黄色成人 | 免费网站看v片在线香蕉 | 综合久久99久久99播放 | 国产微拍精品福利视频 | 欧美第一网站 | 国产男女乱淫真视频全程播放 | 色国产精品| 国产精品久久久久久一区二区三区 |