MyBatis批處理分仳次插入資料__MyBatis/Ibatis

在有大量資料操作時，同時發幾萬至幾百條資料同時，執行insert,update時，沒有及時commit,釋放資源，會幹掛掉資料庫伺服器的，所以我們有必要，分批次處理資料的插入和更新

public class StudentTest {private static Logger logger = Logger.getLogger(StudentTest.class);private SqlSession sqlSession = null;private StudentMapper studentMapper = null;/** * 測試方法前調用 *  * @throws Exception */@Beforepublic void setUp() throws Exception {sqlSession = SqlSessionFactoryUtil.openSession();studentMapper = sqlSession.getMapper(StudentMapper.class);}/** * 測試方法後調用 *  * @throws Exception */@Afterpublic void tearDown() throws Exception {sqlSession.close();}@Testpublic void batchInsertStudentPage() {List<Student> list = new ArrayList<Student>();for (int i = 0; i < 10; i++) {Student student = new Student();student.setId(i);student.setName("test" + i);list.add(student);}try {save(list);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}// 分批次插入private void save(List<Student> uidCodeList) throws Exception {SqlSession batchSqlSession = null;try {batchSqlSession = SqlSessionFactoryUtil.openSession();// 擷取批量方式的sqlsessionint 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("com.mybatis.mappers.StudentMapper.batchInsertStudent", uidCodeList.subList(index, batchLastIndex + 1));batchSqlSession.commit();System.out.println("index:" + index + "     batchLastIndex:" + batchLastIndex);break;// 資料插入完畢,退出迴圈} else {batchSqlSession.insert("com.mybatis.mappers.StudentMapper.batchInsertStudent", uidCodeList.subList(index, batchLastIndex + 1));batchSqlSession.commit();System.out.println("index:" + index + "     batchLastIndex:" + batchLastIndex);index = batchLastIndex + 1;// 設定下一批下標batchLastIndex = index + (batchCount - 1);}}} finally {batchSqlSession.close();}}}}

介面

public interface StudentMapper {public int batchInsertStudent(List<Student> list);}

Mapper

<mapper namespace="com.mybatis.mappers.StudentMapper"><!-- 1，size:表示緩衝cache中能容納的最大元素數。預設是1024； 2，flushInterval:定義緩衝重新整理周期，以毫秒計； 3，eviction:定義緩衝的移除機制；預設是LRU(least recently userd，最近最少使用),還有FIFO(first in first out，先進先出) 4，readOnly:預設值是false，假如是true的話，緩衝只能讀。 --><cache size="1024" flushInterval="60000" eviction="LRU" readOnly="false" /><resultMap type="Student" id="StudentResult"><id property="id" column="id" /><result property="name" column="name" /></resultMap><!-- 批次更新 --><insert id="batchInsertStudent" parameterType="List">insert into /*+append_values */ t_student(id,name)<foreach collection="list" item="item" index="index"separator="union all">select #{item.id}, #{item.name} from dual</foreach></insert></mapper>

實體類

public class Student implements Serializable {private Integer id;private String name;private String remark;// clobpublic Student() {super();// TODO Auto-generated constructor stub}public Student(Integer id, String name) {super();this.id = id;this.name = name;}public Student(String namee) {super();this.name = name;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", remark=" + remark + "]";}}

 SqlSessionFactionUtil

public class SqlSessionFactoryUtil {private static SqlSessionFactory sqlSessionFactory;public static SqlSessionFactory getSqlSessionFactory() {if (sqlSessionFactory == null) {InputStream inputStream = null;try {inputStream = Resources.getResourceAsStream("mybatis-config.xml");sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);} catch (Exception e) {e.printStackTrace();}}return sqlSessionFactory;}public static SqlSession openSession() {return getSqlSessionFactory().openSession();}}

說明:本人於ITEYE建立於2014年,現轉移到CSDN