MyBatis批处理分仳次插入数据

在有大量数据操作时，同时发几万至几百条数据同时，执行insert,update时，没有及时commit,释放资源，会干挂掉数据库服务器的，所以我们有必要，分批次处理数据的插入和更新

public class StudentTest {

	private static Logger logger = Logger.getLogger(StudentTest.class);
	private SqlSession sqlSession = null;
	private StudentMapper studentMapper = null;

	/**
	 * 测试方法前调用
	 * 
	 * @throws Exception
	 */
	@Before
	public void setUp() throws Exception {
		sqlSession = SqlSessionFactoryUtil.openSession();
		studentMapper = sqlSession.getMapper(StudentMapper.class);
	}

	/**
	 * 测试方法后调用
	 * 
	 * @throws Exception
	 */
	@After
	public void tearDown() throws Exception {
		sqlSession.close();
	}

	@Test
	public 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 block
			e.printStackTrace();
		}
	}

	// 分批次插入
	private void save(List<Student> uidCodeList) throws Exception {
		SqlSession batchSqlSession = null;
		try {
			batchSqlSession = SqlSessionFactoryUtil.openSession();// 获取批量方式的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("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;// clob

	public 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;
	}

	@Override
	public 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