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

目录

问题现象：

问题分析：

1. 插入慢

2. 数量限制

解决方法：

应用：

---

问题现象：

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

---

问题分析：

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) + " 条数据插入了数据库！";
	}
}