sad
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <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> <delete id="batchDeleteStudentWithArray" parameterType="java.lang.String"> DELETE FROM t_student where id in <foreach item="idItem" collection="array" open="(" separator="," close=")"> #{idItem} </foreach> </delete> <delete id="batchDeleteStudentWithIdList" parameterType="java.util.List"> DELETE FROM t_student where id in <foreach collection="list" item="idItem" index="index" open="(" separator="," close=")"> #{idItem} </foreach> </delete> <delete id="batchDeleteStudentWithListOnlyId" parameterType="java.util.List"> DELETE FROM t_student where id in <foreach collection="list" index="index" item="item" open="(" separator="," close=")"> #{item.id} </foreach> </delete> <!– 效率低，不推荐 –> <delete id="batchDeleteStudentWithList" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> DELETE FROM t_student where id=#{item.id} </foreach> </delete> <update id="batchUpdateByIdList" parameterType="java.util.List"> UPDATE t_student set name=‘test’ where id in <foreach collection="list" item="idItem" index="index" open="(" separator="," close=")"> #{idItem} </foreach> </update> <update id="batchUpdateStudentWithList" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> UPDATE t_student <set> name=#{item.name} </set> where id=#{item.id} </foreach> </update> <update id="batchUpdateStudentWithMap" parameterType="java.util.Map"> UPDATE t_student SET name = #{name} WHERE id IN <foreach collection="idList" index="index" item="idItem" open="(" separator="," close=")"> #{idItem} </foreach> </update> <insert id="batchInsertStudentWithList" parameterType="java.util.List"> INSERT INTO /*+append_values */ t_student (name) VALUES <foreach collection="list" item="item" index="index" separator=","> (#{item.name}) </foreach> </insert> </mapper>
gfsh
import java.util.List; import java.util.Map; import com.mybatis.model.Student; public interface StudentMapper { /** * 通过List集合批量插入 * @param list * @return */ public int batchInsertStudentWithList(List<Student> list); /** * 通过IdList进行Update特定字段为特定值 * @param list * @return */ public int batchUpdateByIdList(List<Integer> list); /** * 通过Map批量更新 * @param map * @return */ public int batchUpdateStudentWithMap(Map<String, Object> map); /** * 通过List集合批量更新 * @param list * @return */ public int batchUpdateStudentWithList(List<Student> list); /** * 通过数组进行批量删除 * @param array * @return */ public int batchDeleteStudentWithArray(int array[]); /** * 能过IdList进行批量删除 * @param list * @return */ public int batchDeleteStudentWithIdList(List<Integer> list); /** * 通过list删除 * @param list * @return */ public int batchDeleteStudentWithList(List<Student> list); /** * 通过list中对象进行删除 * @param list * @return */ public int batchDeleteStudentWithListOnlyId(List<Student> list); }
hg
import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import org.junit.After; import org.junit.Before; import org.junit.Test; import com.mybatis.mappers.StudentMapper; import com.mybatis.model.Student; import com.mybatis.util.SqlSessionFactoryUtil; public class StudentBatchTest { private static Logger logger=Logger.getLogger(Student.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(); } //通过list进行批量插入 @Test public void batchInsertStudentWithList(){ List<Student> list= new ArrayList<Student>(); for(int i = 2;i < 10;i++){ Student student = new Student(); student.setName("test" + i); list.add(student); } int n=studentMapper.batchInsertStudentWithList(list); System.out.println("成功插入"+n+"条记录"); sqlSession.commit(); } //分页批量插入 @Test public void batchInsertStudentPage(){ List<Student> list= new ArrayList<Student>(); for(int i = 0;i < 2000;i++){ Student student = new Student(); student.setName("test" + i); list.add(student); } try { save(list); } catch (Exception e) { 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.batchInsertStudentWithList", uidCodeList.subList(index, batchLastIndex+1)); batchSqlSession.commit(); System.out.println("index:"+index+" batchLastIndex:"+batchLastIndex); break;//数据插入完毕,退出循环 }else{ batchSqlSession.insert("com.mybatis.mappers.StudentMapper.batchInsertStudentWithList", uidCodeList.subList(index, batchLastIndex+1)); batchSqlSession.commit(); System.out.println("index:"+index+" batchLastIndex:"+batchLastIndex); index = batchLastIndex + 1;//设置下一批下标 batchLastIndex = index + (batchCount – 1); } } }finally{ batchSqlSession.close(); } } //通过IdList批量更新 @Test public void batchUpdateByIdList() { logger.info("通过IdList批量更新"); List<Integer> list = new ArrayList<Integer>(); list.add(5); list.add(6); int n = studentMapper.batchUpdateByIdList(list); System.out.println("成功更新" + n + "条记录"); sqlSession.commit(); } //通过map进行批量更新 @Test public void batchUpdateStudentWithMap() { List<Integer> ls = new ArrayList<Integer>(); for (int i = 5; i < 7; i++) { ls.add(i); } Map<String, Object> map = new HashMap<String, Object>(); map.put("idList", ls); map.put("name", "小群11"); int n = studentMapper.batchUpdateStudentWithMap(map); System.out.println("成功更新" + n + "条记录"); sqlSession.commit(); } //通过list批量更新 @Test public void batchUpdateStudentWithList() { logger.info("更新学生(带条件)"); List<Student> list = new ArrayList<Student>(); list.add(new Student(6, "张三aa")); list.add(new Student(6, "李四aa")); int n = studentMapper.batchUpdateStudentWithList(list); System.out.println("成功更新" + n + "条记录"); sqlSession.commit(); } //通过Array进行批量删除 @Test public void batchDeleteStudentWithArray() { logger.info("删除学生，通过Array"); int array[] = new int[] { 3, 4 }; studentMapper.batchDeleteStudentWithArray(array); sqlSession.commit(); } @Test public void batchDeleteStudentWithIdList() { logger.info("通过IdList批量更新"); List<Integer> list = new ArrayList<Integer>(); list.add(9); list.add(10); int n = studentMapper.batchDeleteStudentWithIdList(list); System.out.println("成功删除" + n + "条记录"); sqlSession.commit(); } @Test public void batchDeleteStudentWithList() { logger.info("通过IdList批量更新"); List<Student> list = new ArrayList<Student>(); list.add(new Student(12, null)); list.add(new Student(13, null)); int n = studentMapper.batchDeleteStudentWithList(list); System.out.println("成功删除" + n + "条记录"); sqlSession.commit(); } @Test public void batchDeleteStudentWithListOnlyId() { logger.info("通过IdList批量更新"); List<Student> list = new ArrayList<Student>(); list.add(new Student(14, null)); list.add(new Student(15, null)); int n = studentMapper.batchDeleteStudentWithListOnlyId(list); System.out.println("成功删除" + n + "条记录"); sqlSession.commit(); } }