需求:向Mysql插入10000条数据
环境:Mysql和Java代码都运行在我本地Windows电脑(i7处理器,4核,16G运行内存,64位操作系统)
1 JPA执行
代码省略,大概需要39S左右
2 通过Mybatis的insert语句插入
大概需要0.2S左右
Controller代码如下:
@RestController
@RequestMapping("/system/student")
public class StudentController extends BaseController {
@Autowired
StudentMapper studentMapper;
@GetMapping
public int save() {
List<Student> studentList = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
Student student = new Student();
student.setName("张" + i);
student.setAge(i);
studentList.add(student);
}
// mybatis insert语句返回的是插入的条数
int quantity = studentMapper.insertBatch(studentList);
return quantity;
}
}
Xml和Mapper代码如下:
<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
insert into student(age,name) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.age},#{item.name})
</foreach>
</insert>
public interface StudentMapper extends BaseMapper<Student> {
int insertBatch(List<Student> students);
}
3 注意点
当mybatis的批量insert语句过长时会报错,不建议修改数据库配置文件,分批插入即可,比如十万条数据,每5000条执行一次