MyBatis批量操作_MySql

1.配置文件

   jdbc.properties,需要注意，需要加allowMultiQueries=true配置

#MySqljdbc.driverClassName=com.mysql.jdbc.Driverjdbc.url=jdbc\:mysql\://127.0.0.1\:3306/mybatis?useUnicode\=true&characterEncoding\=UTF-8&allowMultiQueries\=truejdbc.username=rootjdbc.password=root

 mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><properties resource="jdbc.properties"/><typeAliases><package name="com.mybatis.model"/></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="${jdbc.driverClassName}" /><property name="url" value="${jdbc.url}" /><property name="username" value="${jdbc.username}" /><property name="password" value="${jdbc.password}" /></dataSource></environment><environment id="test"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="${jdbc.driverClassName}" /><property name="url" value="${jdbc.url}" /><property name="username" value="${jdbc.username}" /><property name="password" value="${jdbc.password}" /></dataSource></environment></environments><mappers><package name="com.mybatis.mappers"/></mappers></configuration>

2.接口Mapper:StudentMapper

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

3.Mapper配置StudentMapper

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//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_studentwhere 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> 

 4.SqlSessionFactoryUtil

package com.mybatis.util;import java.io.InputStream;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;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();}}

5.Model:Student

package com.mybatis.model;import java.io.Serializable;public class Student implements Serializable {private Integer id;private String name;private byte[] pic;// blobprivate 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 byte[] getPic() {return pic;}public void setPic(byte[] pic) {this.pic = pic;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", remark=" + remark + "]";}}

6.测试类

package com.mybatis.service;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 */@Beforepublic void setUp() throws Exception {sqlSession=SqlSessionFactoryUtil.openSession();studentMapper=sqlSession.getMapper(StudentMapper.class);}/** * 测试方法后调用 * @throws Exception */@Afterpublic void tearDown() throws Exception {sqlSession.close();}//通过list进行批量插入@Testpublic 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();}//分页批量插入@Testpublic 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();//获取批量方式的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.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批量更新@Testpublic 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进行批量更新@Testpublic 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批量更新@Testpublic 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进行批量删除@Testpublic void batchDeleteStudentWithArray() {logger.info("删除学生，通过Array");int array[] = new int[] { 3, 4 };studentMapper.batchDeleteStudentWithArray(array);sqlSession.commit();}@Testpublic 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();}@Testpublic 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();}@Testpublic 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();}}

说明:本人于ITEYE创建于2014年,现转移到CSDN