mybatis之批量插入

比眉伴天荒 2022-01-07 09:43 195阅读 0赞

# 通过动态SQL方式实现 #

通过动态SQL方式，Executor的类型不需要特别设置，用默认的SIMPLE就可以。  
具体步骤如下：

## 第一步：定义Mapper映射文件和接口类 ##

映射文件中定义动态SQL语句

<insert id="insertBatch" parameterType="list" useGeneratedKeys="true" keyProperty="id">
            insert into student( sname, age, gender, nick_name ) values
            <foreach collection="list" item="stu" index="index" separator=",">
                (#{stu.sname}, #{stu.age}, #{stu.gender}, #{stu.nickName})
            </foreach>
        </insert>

Mapper接口

public interface StudentMapper {
        
        int insertBatch(List<Student> studentList);
    }

## 第二步：调用 ##

/**
     * 使用默认的Executor类型SIMPLE，通过在Mapper文件中的<forEach>标签，拼接动态SQL，完成批量操作
     */
    @Test
    public void testBatchInsertStudentByForEachSqlMapper() {
        
        SqlSession sqlSession = MybatisUtil.getSessionFactory().openSession(false);
        try {
        
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    
            System.out.println("影响行数： " + studentMapper.insertBatch(studentList));
    
            // 提交事务
            sqlSession.commit();
    
            // 从本批次插入数据中随机抽查10条数据的自增id
            Random random = new Random();
            for(int i = 0; i < 10; i++) {
        
                int idx = random.nextInt(studentList.size());
                log.info("第"+(idx+1)+"条数据自增id: " + studentList.get(idx).getId());
            }
    
        } catch (Exception e) {
        
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
        
            sqlSession.close();
        }
    }

## 示例 ##

jdbc.properties数据源

jdbc.url=jdbc:mysql://localhost:3306/yanfa5
    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.user=root
    jdbc.password=root
    jdbc.characterEncoding=utf8

CityMapper.xml

<?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.lanou3g.mybatis.dao.CityDao">
    	
    	<insert id="insertByMap" parameterType="hashmap">
            insert into city (cname,pid) values
            
            <foreach collection="map" index="cname" item="pid" separator="," close=";">
                (#{cname},#{pid})
            </foreach>
        </insert>
    
    	
    	<insert id="insertByList">
            insert into city (cname,pid) values 
            
            <foreach collection="list" item="city" separator="," close=";">
                (#{city.cname},#{city.pid})
            </foreach>
        </insert>
    </mapper>

mybatis.xml

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

对应数据库表的java类

@Getter
    @Setter
    public class City {
        
        private Integer id;
        private String cname;
        private Integer pid;
    
        @Override
        public String toString() {
        
            return "City{" +
                    "id=" + id +
                    ", cname='" + cname + '\'' +
                    ", pid=" + pid +
                    "}\n";
        }
    }

dao层接口（操作数据库）

public interface CityDao {
        
    	//迭代List类型参数
    	int insertByList(List<City> cityList);
    	//迭代Map类型参数
        int insertByMap(@Param("map") Map<String,Integer> map);
    }

程序运行入口

public class App {
        
        public static void main(String[] args) throws IOException {
        
            //1、读入配置文件
            String confPath = "mybatis.xml";
            InputStream is = Resources.getResourceAsStream(confPath);
    
            //2、构建SqlSessionFactory(用于获取sqlSession)
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"development");
    
            //3、获取sqlSession对象（用于具体的RUID)
            SqlSession sqlSession = sessionFactory.openSession(true);
    
            //4、具体的RUID
            CityDao cityDao = sqlSession.getMapper(CityDao.class);
            String[] arr = new String[]{
        "金","木","水","火","土"};
            Random random = new Random();
    
    		//迭代List类型参数
            List<City> cityList = new ArrayList<>();
            for(int i = 0;i < 10 ;i++) {
        
                City city = new City();
                city.setCname(arr[random.nextInt(arr.length)] + "_" + (i + 1));
                cityList.add(city);
            }
            System.out.println("插入行数:" + cityDao.insertByList(cityList));
    
    		//迭代Map类型参数
            Map<String,Integer> map = new HashMap<>();
            for(int i = 10;i < 20 ;i++) {
        
                map.put(arr[random.nextInt(arr.length)] + "_" + (i + 1),(i + 1));
            }
            System.out.println("插入行数:" + cityDao.insertByMap(map));
            
        }
    }

# 通过将Executor类型设置为BATCH实现 #

此种方式需要将Executor类型设置为BATCH，而在映射文件中不需要拼接批量SQL，用普通单条插入的SQL即可。具体步骤如下：  
第一步：定义映射文件和映射接口类  
映射文件：

<insert id="insert" parameterType="Student" useGeneratedKeys="true" keyProperty="id">
            insert into student(sname, age, gender, nick_name) values (#{sname}, #{age}, #{gender}, #{nickName})
    </insert>

映射接口类：

public interface StudentMapper {
        
        int insert(Student student);
    }

第二步：调用

/**
     * 用Executor为Batch方式完成批量插入
     */
    @Test
    public void testBatchInsertStudentByExectorTypeBatch() {
        
        SqlSession sqlSession = MybatisUtil.getSessionFactory().openSession(ExecutorType.BATCH,false);
        try {
        
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            for(Student student : studentList) {
        
                studentMapper.insert(student);
            }
            // 提交暂存在JDBC驱动中的批量语句
            sqlSession.flushStatements();
    
            // 提交事务
            sqlSession.commit();
    
            log.info("成功批量插入： " + studentList.size()+"条数据");
    
            // 从本批次插入数据中随机抽查10条数据的自增id
            Random random = new Random();
            for(int i = 0; i < 10; i++) {
        
                int idx = random.nextInt(studentList.size());
                log.info("第"+(idx+1)+"条数据自增id: " + studentList.get(idx).getId());
            }
        } catch (Exception e) {
        
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
        
            sqlSession.close();
        }
    }

## 示例 ##

jdbc.properties数据源

jdbc.url=jdbc:mysql://localhost:3306/yanfa5
    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.user=root
    jdbc.password=root
    jdbc.characterEncoding=utf8

CityMapper.xml

mybatis.xml

对应数据库表的java类

dao层接口（操作数据库）

public interface CityDao {
        
    	int insertCity(City city);
    }

程序运行入口

public class App {
        
        public static void main(String[] args) throws IOException {
        
            //1、读入配置文件
            String confPath = "mybatis_conf.xml";
            InputStream is = Resources.getResourceAsStream(confPath);
    
            //2、构建SqlSessionFactory(用于获取sqlSession)
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"citydao");
    
            //3、获取sqlSession对象（用于具体的RUID)
            SqlSession sqlSession = sessionFactory.openSession(ExecutorType.BATCH,false);
    
            //4、具体的RUID
            CityDao cityDao = sqlSession.getMapper(CityDao.class);
    
            String[] arr = new String[]{
        "金","木","水","火","土"};
            Random random = new Random();
            List<City> cityList = new ArrayList<>();
            for(int i = 0;i < 100 ;i++) {
        
                City city = new City();
                city.setCname(arr[random.nextInt(arr.length)] + "_" + (i + 1));
                cityList.add(city);
            }
            long start = System.currentTimeMillis();
            int rows = 0;
            int batchSize = 10;
            int count = 0;
            for (City city : cityList) {
        
                cityDao.insertCity(city);
                count ++;
                if ( count % batchSize == 0){
        
                    rows += flushStatement(sqlSession);
                }
            }
            sqlSession.flushStatements();
            sqlSession.commit();
            sqlSession.close();
            log.info("插入数据行数：" + rows+", 耗时：" + (System.currentTimeMillis() - start));
        }
    
    	/**
         * 用于计算插入到数据库的数据行数
         * @param sqlSession
         * @return
         */
    	public static int flushStatement(SqlSession sqlSession) {
        
            int effectRows = 0;
            List<BatchResult> batchResults = sqlSession.flushStatements();
            if(batchResults == null || batchResults.size() < 1) {
        
                return effectRows;
            }
            int[] effectArr = batchResults.get(0).getUpdateCounts();
            for(int effectRow : effectArr) {
        
                effectRows += effectRow;
            }
            return effectRows;
        }
    }