Mybatis批量插入数据

系列文章目录

---

前言

批量插入数据是项目中必不可少的，记录下(ps:以前有点懒)

版本介绍:

• jdk1.8
• spring-boot 2.0.0.RELEASE
• druid 1.1.10
• mybatis 3.4.6
• mysql 8.0
• mybatis文档

---

二、代码部分

1.实体对象

StudentInfo.java

package com.student.model;

import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import java.lang.Character;
import java.util.Date;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.lang.Integer;

/**
 * 学生基本信息 StudentInfo 
 * @date 2022-01-27 22:12:56
 */
 @ApiModel(value="StudentInfo", description="学生基本信息")
public class StudentInfo implements Serializable {

	private static final long serialVersionUID = 1L;
	
		
	/** 学号 **/
	@ApiModelProperty(value = "学号")
	private Integer id;
		
	/** 姓名 **/
	@ApiModelProperty(value = "姓名")
	private String name;
		
	/** 年龄 **/
	@ApiModelProperty(value = "年龄")
	private Integer age;
		
	/** 出生日期 **/
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
	@ApiModelProperty(value = "出生日期")
	private Date birthday;
		
	/** 民族 **/
	@ApiModelProperty(value = "民族")
	private String nation;
		
	/** 证件类型 **/
	@ApiModelProperty(value = "证件类型")
	private String idType;
		
	/** 证件号码 **/
	@ApiModelProperty(value = "证件号码")
	private String idNumber;
		
	/** 手机号 **/
	@ApiModelProperty(value = "手机号")
	private Integer tel;
		
	/** 入学时间 **/
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
	@ApiModelProperty(value = "入学时间")
	private Date admissionTime;
		
	/** 家庭住址 **/
	@ApiModelProperty(value = "家庭住址")
	private String address;
		
	/** 院系 **/
	@ApiModelProperty(value = "院系")
	private String faculty;
		
	/** 专业 **/
	@ApiModelProperty(value = "专业")
	private String major;
		
	/** 班级 **/
	@ApiModelProperty(value = "班级")
	private Integer classID;
		
	/** 辅导员 **/
	@ApiModelProperty(value = "辅导员")
	private String instructor;
		
	/** 是否在籍(0:否;1:是) **/
	@ApiModelProperty(value = "是否在籍(0:否;1:是)")
	private Character registered;
		
		
	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 getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
	 
			
	public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
	 
			
	public String getNation() {
        return nation;
    }

    public void setNation(String nation) {
        this.nation = nation;
    }
	 
			
	public String getIdType() {
        return idType;
    }

    public void setIdType(String idType) {
        this.idType = idType;
    }
	 
			
	public String getIdNumber() {
        return idNumber;
    }

    public void setIdNumber(String idNumber) {
        this.idNumber = idNumber;
    }
	 
			
	public Integer getTel() {
        return tel;
    }

    public void setTel(Integer tel) {
        this.tel = tel;
    }
	 
			
	public Date getAdmissionTime() {
        return admissionTime;
    }

    public void setAdmissionTime(Date admissionTime) {
        this.admissionTime = admissionTime;
    }
	 
			
	public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
	 
			
	public String getFaculty() {
        return faculty;
    }

    public void setFaculty(String faculty) {
        this.faculty = faculty;
    }
	 
			
	public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }
	 
			
	public Integer getClassID() {
        return classID;
    }

    public void setClassID(Integer classID) {
        this.classID = classID;
    }
	 
			
	public String getInstructor() {
        return instructor;
    }

    public void setInstructor(String instructor) {
        this.instructor = instructor;
    }
	 
			
	public Character getRegistered() {
        return registered;
    }

    public void setRegistered(Character registered) {
        this.registered = registered;
    }
	 
			
	public StudentInfo() {
        super();
    }
    
																																																																													
	public StudentInfo(Integer id,String name,Integer age,Date birthday,String nation,String idType,String idNumber,Integer tel,Date admissionTime,String address,String faculty,String major,Integer classID,String instructor,Character registered) {
	
		this.id = id;
		this.name = name;
		this.age = age;
		this.birthday = birthday;
		this.nation = nation;
		this.idType = idType;
		this.idNumber = idNumber;
		this.tel = tel;
		this.admissionTime = admissionTime;
		this.address = address;
		this.faculty = faculty;
		this.major = major;
		this.classID = classID;
		this.instructor = instructor;
		this.registered = registered;
		
	}

	@Override
	public String toString() {
		return "StudentInfo{" +
				"id=" + id +
				", name='" + name + '\'' +
				", age=" + age +
				", birthday=" + birthday +
				", nation='" + nation + '\'' +
				", idType='" + idType + '\'' +
				", idNumber='" + idNumber + '\'' +
				", tel=" + tel +
				", admissionTime=" + admissionTime +
				", address='" + address + '\'' +
				", faculty='" + faculty + '\'' +
				", major='" + major + '\'' +
				", classID=" + classID +
				", instructor='" + instructor + '\'' +
				", registered=" + registered +
				'}';
	}
}

StudentInfoMapper.java

package com.student.mapper;

import java.util.List;
import com.student.model.StudentInfo;
import org.apache.ibatis.annotations.Param;

/**
 * 学生基本信息 StudentInfoMapper
 * @author zjg_自动生成
 * @date 2022-01-27 22:12:56
 */
public interface StudentInfoMapper {
    int batchStudentInfo(@Param("record") List<StudentInfo> record);
}

StudentInfoMapper.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.student.mapper.StudentInfoMapper">
  <resultMap id="BaseResultMap" type="com.student.model.StudentInfo">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="birthday" jdbcType="TIMESTAMP" property="birthday" />
    <result column="nation" jdbcType="VARCHAR" property="nation" />
    <result column="id_type" jdbcType="VARCHAR" property="idType" />
    <result column="id_number" jdbcType="VARCHAR" property="idNumber" />
    <result column="tel" jdbcType="INTEGER" property="tel" />
    <result column="admission_time" jdbcType="TIMESTAMP" property="admissionTime" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="faculty" jdbcType="VARCHAR" property="faculty" />
    <result column="major" jdbcType="VARCHAR" property="major" />
    <result column="class" jdbcType="INTEGER" property="classID" />
    <result column="instructor" jdbcType="VARCHAR" property="instructor" />
    <result column="registered" jdbcType="CHAR" property="registered" />
  </resultMap>
  <insert id="batchStudentInfo">
    insert into t_student_info (id,name,age,birthday,nation,id_type,id_number,tel,admission_time,address,faculty,major,class,instructor,registered)values
    <foreach collection="record" item="item"  separator=",">
      (#{item.id},#{item.name},#{item.age},#{item.birthday,jdbcType=TIMESTAMP},#{item.nation},#{item.idType},#{item.idNumber},#{item.tel},#{item.admissionTime,jdbcType=TIMESTAMP},#{item.address},#{item.faculty},#{item.major},#{item.classID},#{item.instructor},#{item.registered})
    </foreach>
  </insert>                                                                         
</mapper>

2.测试类

SpringbootStartTest.java

package com.student;

import com.student.mapper.StudentInfoMapper;
import com.student.model.StudentInfo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Create by zjg on 2022/9/25
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringbootStart.class)
public class SpringbootStartTest {
    @Autowired
    private StudentInfoMapper studentInfoMapper;
    @Test
    public void batchStudentInfo(){
        List<StudentInfo> studentInfoList=new ArrayList<>();
        int count=1000;
        for(int i=0;i<count;i++){
            StudentInfo studentInfo=new StudentInfo(i,"马斯克"+i+"号",i,new Date(),"汉族","1","1",i,new Date(),"圣弗朗西斯科"+i,"","",i,"",'1');
            studentInfoList.add(studentInfo);
        }
        long start = System.currentTimeMillis();
        studentInfoMapper.batchStudentInfo(studentInfoList);
        long end = System.currentTimeMillis();
        System.out.println("数据共"+count+"条,耗时"+(end-start)+"毫秒");
    }

}

---

总结

千条以下消耗的时间还在可接受范围内
大批量插入测试数据可以使用存储过程

拓展

jdbcTemplate.batchUpdate(不建议使用)

@RunWith(SpringRunner.class) //作用：让当前类在容器环境下进行测试
@SpringBootTest(classes = SpringbootStart.class)//作用：声明当前类是springboot的测试类并且获取入口类上的相关信息 SpringBootApplication是入口类类名
public class SpringbootStartTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void batchUpdate(){
        String sql="insert into t_student_info (id,name,age,birthday,nation,id_type,id_number,tel,admission_time,address,faculty,major,class,instructor,registered)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        List<StudentInfo> studentInfoList=new ArrayList<>();
        int count=1000;
        for(int i=0;i<count;i++){
            StudentInfo studentInfo=new StudentInfo(String.valueOf(i),"马斯克"+i+"号",i,new Date(),"汉族","1","1",i,new Date(),"圣弗朗西斯科"+i,"","",i,"",'1');
            studentInfoList.add(studentInfo);
        }
        long start = System.currentTimeMillis();
        jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setString(1, studentInfoList.get(i).getId());
                preparedStatement.setString(2, studentInfoList.get(i).getName());
                preparedStatement.setInt(3, studentInfoList.get(i).getAge());
                preparedStatement.setString(4, DateUtil.format(studentInfoList.get(i).getBirthday(),"yyyy-MM-dd"));
                preparedStatement.setString(5, studentInfoList.get(i).getNation());
                preparedStatement.setString(6, studentInfoList.get(i).getIdType());
                preparedStatement.setString(7, studentInfoList.get(i).getIdNumber());
                preparedStatement.setInt(8,studentInfoList.get(i).getTel());
                preparedStatement.setString(9, DateUtil.format(studentInfoList.get(i).getAdmissionTime(),"yyyy-MM-dd"));
                preparedStatement.setString(10, studentInfoList.get(i).getAddress());
                preparedStatement.setString(11, studentInfoList.get(i).getFaculty());
                preparedStatement.setString(12, studentInfoList.get(i).getMajor());
                preparedStatement.setInt(13, studentInfoList.get(i).getClassID());
                preparedStatement.setString(14, studentInfoList.get(i).getInstructor());
                preparedStatement.setString(15, studentInfoList.get(i).getRegistered().toString());
            }

            @Override
            public int getBatchSize() {
                return studentInfoList.size();
            }
        });
        long end = System.currentTimeMillis();
        System.out.println("数据共"+count+"条,耗时"+(end-start)+"毫秒");
    }
}