批量与分段插入insert数据到数据库中

傷城~ 2022-11-15 11:17 251阅读 0赞

# 问题现象： #

项目中的一个程序，其中包括了插入数据库的逻辑，但由于是单条数据插入，所以入库就很慢，利用**批量插入**提高速度。

--------------------

# 问题分析： #

## **1. 插入慢** ##

**单数据插入**数据库很慢，因为需要**多次访问数据库**，而**批量插入**则只需要**访问一次数据库**即可，所以会快很多。

## 2. 数量限制 ##

**所有的数据库**（Oracle，Mysql，SQLServer，Postgresql，SQLite等等）都有自己的**入库数量限制，这个就需要测试和验证得知了！**

--------------------

# 解决方法： #

## 应用： ##

**1. 实体类和批量入库方法：**

**First ：**

import java.io.Serializable;
    
    public class First implements Serializable {
    
    	private static final long serialVersionUID = 1L;
    
    	/**
    	 * id
    	 */
    	private Integer id;
    	/**
    	 * name
    	 */
    	private String name;
    	/**
    	 * class_id
    	 */
    	private Integer classId;
    	/**
    	 * class_name
    	 */
    	private String className;
    
    	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 Integer getClassId() {
    		return classId;
    	}
    
    	public void setClassId(Integer classId) {
    		this.classId = classId;
    	}
    
    	public String getClassName() {
    		return className;
    	}
    
    	public void setClassName(String className) {
    		this.className = className;
    	}
    }

**Second：**

import java.io.Serializable;
    
    public class Second implements Serializable {
    
    	private static final long serialVersionUID = 1L;
    
    	/**
    	 * id
    	 */
    	private Integer id;
    	/**
    	 * name
    	 */
    	private String name;
    	/**
    	 * class_id
    	 */
    	private Integer classId;
    	/**
    	 * class_name
    	 */
    	private String className;
    
    	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 Integer getClassId() {
    		return classId;
    	}
    
    	public void setClassId(Integer classId) {
    		this.classId = classId;
    	}
    
    	public String getClassName() {
    		return className;
    	}
    
    	public void setClassName(String className) {
    		this.className = className;
    	}
    }

**FirstMapper:**

@Mapper
    @Component
    public interface FirstMapper {
    
    	/**
    	 * 批量插入
    	 *
    	 * @return
    	 */
    	int saveBatch(List<First> list);
    }

**FirstMapper.xml:**

<insert id="saveBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into fist_table( id, name, class_id, class_name )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.id},
                #{item.name},
                #{item.classId},
                #{item.className}
            )
        </foreach>
    </insert>

**SecondMapper:**

@Mapper
    @Component
    public interface SecondMapper {
    
    	/**
    	 * 批量插入
    	 *
    	 * @return
    	 */
    	int saveBatch(List<Second> list);
    }

**SecondMapper.xml:**

<insert id="saveBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into second_table( id, name, class_id, class_name )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.id},
                #{item.name},
                #{item.classId},
                #{item.className}
            )
        </foreach>
    </insert>

**2. 分段方法与调用：**

**demoController ：**

@RestController
    @RequestMapping
    public class demoController {
    	//分段大小,经测试发现SQLite数据库，一次性批量插入数据不能>=50条记录
    	private int splitSize = 49;
    
    	@Autowired
    	FirstMapper firstMapper;
    
    	@Autowired
    	SecondMapper secondMapper;
    
    	/**
    	 * 将list集合平均分成几等份
    	 *
    	 * @param num  分的份数
    	 * @param list 需要分的集合
    	 */
    	private List splitList(List list, Integer num) {
    		int listSize = list.size(); //list 长度
    		List<List> splitLists = new ArrayList<>();
    		List stringlist = new ArrayList<>();
    		for ( int i = 0; i < listSize; i++ ) {
    			stringlist.add(list.get(i));
    			if ( ((i + 1) % num == 0) || (i + 1 == listSize) ) {
    				splitLists.add(stringlist);
    				stringlist = new ArrayList<>();
    			}
    		}
    		return splitLists;
    	}
    
    	/**
    	 * 批量插入数据
    	 *
    	 * @param type * 1: firstList
    	 *             * 2: secondList
    	 * @param list
    	 * @return
    	 */
    	private int saveBatchList(int type, List list) {
    		int count = 0;
    		if ( type == 1 ) {
    			count = firstMapper.saveBatch(list);
    		}
    		if ( type == 2 ) {
    			count = secondMapper.saveBatch(list);
    		}
    		return count;
    	}
    
    	//集合分段再批量插入数据库
    	public int splitListSaveBatch(int type, List list) {
    		if ( list.size() <= 0 ) {
    			return 0;
    		}
    		int sumCount = 0;
    		List splitLists = null;
    		if ( list.size() > splitSize ) {
    			splitLists = splitList(list, splitSize);
    		}
    		if ( splitLists == null ) {
    			sumCount = saveBatchList(type, list);
    		} else {
    			for ( int i = 0; i < splitLists.size(); i++ ) {
    				ArrayList splitList = (ArrayList)splitLists.get(i);
    				sumCount += saveBatchList(type, splitList);
    			}
    		}
    		return sumCount;
    	}
    
    	@GetMapping(value = "/testSaveBatch")
    	public String testSaveBatch() {
    		//创建数据集合
    		List<First> firstList = new ArrayList<>();
    		List<Second> secondList = new ArrayList<>();
    		for ( int i = 1; i <= 100; i++ ) {
    			First first = new First();
    			first.setId(i);
    			first.setName("FirstName" + i);
    			first.setClassId(1);
    			first.setClassName("FirstClass");
    
    			Second second = new Second();
    			second.setId(i);
    			second.setName("SecondName" + i);
    			second.setClassId(2);
    			second.setClassName("SecondClass");
    		}
    
    		//批量插入数据库
    		int firstCount = splitListSaveBatch(1, firstList);
    		int secondCount = splitListSaveBatch(2, secondList);
    		return "共： " + (firstCount + secondCount) + " 条数据插入了数据库！";
    	}
    }