欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間：2020年06月01日 11:21:40   作者：槲櫟  
這篇文章主要介紹了spring中使用mybatis實現(xiàn)批量插入的示例代碼，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

有3種實現(xiàn)方式：foreach，spring事務，以及ExecutorType.BATCH.

1. foreach方式

這種方式實際是對SQL語句進行拼接，生成一個長長的SQL，對很多變量進行綁定。如果數(shù)據(jù)量不大（1000個以內），可以用這種方式。如果數(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語句拼接到一起，每個?都會進行變量綁定，所以當studentList中數(shù)據(jù)量較多時，生成的SQL會很長，導致數(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相關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");
  }
}

測試結果

插入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事務

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

開啟spring事務

<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事務控制
  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

略

測試結果

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);，設置BATCH方式的sqlSession

有三種設置方式：

3.1 在mybatis的config文件中設置

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配置文件中，設置BATCH方式:

<configuration>
  <settings>
    <!-- 默認打開BATCH的Executor -->
    <setting name="defaultExecutorType" value="BATCH" />
  </settings>
  <mappers>
    <mapper class="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" />
  </mappers>
</configuration>

這樣，默認打開的sqlSession就都是BATCH方式的。再與spring的事務結合（參看上一節(jié)中的spring事務設置），就可以實現(xiàn)批量插入。

測試結果:

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();

測試結果:

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中，生成對應的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的事務結合后（參看上一節(jié)中的spring事務設置），就可以實現(xiàn)批量插入

測試結果

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

到此這篇關于spring中使用mybatis實現(xiàn)批量插入的示例代碼的文章就介紹到這了,更多相關spring mybatis批量插入內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

最新評論