MyBatis：批量添加记录

最新推荐文章于 2024-03-29 14:24:22 发布

是席木木啊

最新推荐文章于 2024-03-29 14:24:22 发布

阅读量1.9k

点赞数 1

分类专栏： Java 文章标签： mybatis java postgresql 批量添加

本文链接：https://blog.csdn.net/weixin_43524214/article/details/128794601

版权

Java 专栏收录该内容

67 篇文章 3 订阅

订阅专栏

MyBatis，一款优秀的ORM映射框架，可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。同时，MyBatis提供了动态SQL特性，这为我们编写代码按条件拼接SQL提供了极大地方便。但是，这也并不意味着我们可以随意使用这种特性来应对不同的业务逻辑。

以批量添加记录为例，我们通常会按照如下的思路去做：

①创建数据表，并编写Pojo实体类；

②编写Mapper接口，提供XML配置文件，通过<insert>标签+动态SQL实现批量添加数据；

③编写Service层接口及其实现子类；

④编写Controller层接口，提供接口访问路径，进行接口测试。

在数据量较小的场景下，上述思路是没有问题的，但是当批量添加的数据量达到十几万、几十万、上百万时，那就要开始考虑是否会导致JVM堆内存溢出（java.lang.OutOfMemoryError: Java heap space）的问题了。——毕竟JVM可利用的堆区内存有限，然后瞬间的高额内存输出必然会导致内存溢出。

如何更为优雅的实现批量添加数据呢？以下，以批量添加People人员信息记录为例，提供个人应对方案，也欢迎大家补充指正（PS：数据库为PostGreSQL；后端使用MyBatis持久层框架）。

数据表设计tb_people

数据表的创建SQL语句如下（geometry对应几何类型），

-- Table: public.tb_people

-- DROP TABLE IF EXISTS public.tb_people;

CREATE TABLE IF NOT EXISTS public.tb_people
(
    id integer NOT NULL DEFAULT nextval('tb_people_gid_seq'::regclass),
    name character varying COLLATE pg_catalog."default",
    id_card character varying(18) COLLATE pg_catalog."default",
    age integer,
    gender character(1) COLLATE pg_catalog."default",
    occupation character varying COLLATE pg_catalog."default",
    tel character varying(11) COLLATE pg_catalog."default",
    house_id integer,
    floor integer,
    house_card character varying COLLATE pg_catalog."default",
    geom geometry,
    is_holder integer,
    CONSTRAINT tb_people_primary_key PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.tb_people
    OWNER to postgres;

COMMENT ON TABLE public.tb_people
    IS '人员信息表';

Pojo实体类编写

实体类编写如下，

package com.xwd.pojo.business;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.Point;

import javax.validation.constraints.Max;
import javax.validation.constraints.Min;
import java.io.Serializable;

/**
 * @className Family
 * @description: com.xwd.pojo.business
 * @auther: xiwd
 * @date: 2023-01-29 - 01 - 29 - 15:36
 * @version: 1.0
 * @jdk: 1.8
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value = "人员信息实体")
public class People implements Serializable {
    private static final long serialVersionUID = 946928072194669586L;
    //properties
    @ApiModelProperty(value = "主键ID",required = true)
    private Long id;

    @ApiModelProperty(value = "姓名",required = false)
    private String name;

    @ApiModelProperty(value = "身份证号",required = false)
    private String idCard;

    @ApiModelProperty(value = "年龄",required = false)
    @Min(0)
    @Max(120)
    private Integer age;

    @ApiModelProperty(value = "房屋编码",required = false)
    private String houseCard;


    @ApiModelProperty(value = "性别",required = false)
    private String gender;

    @ApiModelProperty(value = "职业",required = false)
    private String occupation;

    @ApiModelProperty(value = "联系方式",required = false)
    private String tel;

    @ApiModelProperty(value = "房屋id",required = false)
    private Long houseId;

    @ApiModelProperty(value = "所在房屋层数",required = false)
    private Integer floor;

    @ApiModelProperty(value = "是否为户主",required = false)
    private Integer isHolder;
    //methods

    @ApiModelProperty(value = "位置",required = false)
    private Geometry location;
}

Mapper层接口比编写&XML配置

Mapper接口实现

package com.xwd.mapper;

import com.xwd.pojo.business.People;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @className PeopleMapper
 * @description: com.xwd.mapper
 * @auther: xiwd
 * @date: 2023-01-29 - 01 - 29 - 17:41
 * @version: 1.0
 * @jdk: 1.8
 */
@Mapper
public interface PeopleMapper {
    //properties

    //methods

    int insertBatch(@Param(value = "list")List<People> peopleList);
}

XML配置与SQL编写

XML配置文件中，基于MyBatis提供的动态SQL特性，实现批量添加数据。

<?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.xwd.mapper.PeopleMapper">


    <insert id="insertBatch"  keyProperty="id" useGeneratedKeys="true" keyColumn="id">
        INSERT INTO public.tb_people(name, id_card, age, house_card, gender, occupation, tel, house_id, floor, is_holder, geom)
            VALUES
            <foreach collection="list" item="people" separator=",">
                (#{people.name},
                #{people.idCard},
                #{people.age},
                #{people.houseCard},
                #{people.gender},
                #{people.occupation},
                #{people.tel},
                #{people.houseId},
                #{people.floor},
                #{people.isHolder},
                #{people.location,typeHandler=com.xwd.typehandler.AbstractGeometryTypeHandler})
            </foreach>
    </insert>
</mapper>

PS：AbstractGeometryTypeHandler是自定义的typeHandler，用于为MyBatis的类型映射提供拓展，使其支持Geometry字段到JTS-Geometry类型之间的自动转换。

Service接口&子类实现

Service接口实现

package com.xwd.service;

import com.xwd.pojo.business.People;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @className PeopleService
 * @description: com.xwd.service
 * @auther: xiwd
 * @date: 2023-01-29 - 01 - 29 - 20:19
 * @version: 1.0
 * @jdk: 1.8
 */
public interface PeopleService {
    //methods

    long insertBatch(List<People> peopleList);

}

子类定义&分段批量添加

为避免大量数据进行批量添加时会导致JVM内存溢出，此处将原始数据先分段，然后再执行批量添加。本地测试可支持100万条数据的批量添加操作。

package com.xwd.service.impl;

import com.xwd.mapper.PeopleMapper;
import com.xwd.pojo.business.People;
import com.xwd.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @className PeopleServiceImpl
 * @description: com.xwd.service.impl
 * @auther: xiwd
 * @date: 2023-01-29 - 01 - 29 - 20:19
 * @version: 1.0
 * @jdk: 1.8
 */
@Service
public class PeopleServiceImpl implements PeopleService {
    //properties
    @Autowired
    private PeopleMapper mapper;


    //methods
    @Override
    public long insertBatch(List<People> peopleList) {
        int total = peopleList.size();
        //分段更新
        int piece = 1000;
        //判断要批量更新的数据量
        if (total < piece)
            return mapper.insertBatch(peopleList);
        else{
            int executeCount = total % piece + 1;
            long affectedRows  = 0L;
            for (int i = 0; i < executeCount; i++) {
                List<People> pieceList = peopleList.subList(i * piece, (i + 1) * piece);
                affectedRows += mapper.insertBatch(pieceList);
            }
            return affectedRows;
        }
    }
}

PS：Controller层接口略。

Service接口测试

如下为Service层批量添加数据的方法测试，基于建筑数据模拟并随机生成约100万条人员数据，然后将其添加到PostGreSQL数据库中。

import com.xwd.PuDongApplication;
import com.xwd.common.GeneratorTools;
import com.xwd.pojo.business.People;
import com.xwd.pojo.geo.Geo_Building;
import com.xwd.service.Geo_BuildingService;
import com.xwd.service.PeopleService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.Point;
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.Arrays;
import java.util.List;
import java.util.Random;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @className Geo_BuildingServiceTest
 * @description: PACKAGE_NAME
 * @auther: xiwd
 * @date: 2023-01-25 - 01 - 25 - 18:16
 * @version: 1.0
 * @jdk: 1.8
 */
@SpringBootTest(classes = PuDongApplication.class)
@RunWith(SpringRunner.class)
public class Geo_BuildingServiceTest {
    //properties
    @Autowired
    private Geo_BuildingService service;
    @Autowired
    private PeopleService peopleService;

    //methods
    @Test
    public void test_service() {
        List<Geo_Building> geo_buildings = service.selectList();
        //构造人口数据
        Long count = 1L;
        Random random = new Random();
        List<List<People>> resultList = geo_buildings.stream().map(geo_building -> {
            Long building_id = geo_building.getId();
            String areaCode = geo_building.getAreaCode();
            Geometry geometry = geo_building.getGeometry();
            Point centroid = geometry.getCentroid();
            List<People> peopleList = new ArrayList<>();
            Integer floor = geo_building.getFloor();
            for (int i = 0; i < floor; i++) {
                int familyNum = 3 + random.nextInt(4);
                boolean hasHolder = false;
                for (int k = 0; k < familyNum; k++) {
                    Integer age = GeneratorTools.randomAge();
                    String gender = GeneratorTools.randomGender();
                    boolean isHolder = GeneratorTools.randdomBoolean(age, hasHolder);
                    if (isHolder) {
                        hasHolder = true;
                    }
                    peopleList.add(
                            new People(
                                    count,
                                    GeneratorTools.randomName(),
                                    GeneratorTools.randomIDCard(gender),
                                    age,
                                    areaCode + building_id + floor,
                                    gender,
                                    age<18?(age<5)?"无":"学生":(age>65?"退休劳动者":GeneratorTools.randomOccupation()),
                                    age<18?null:GeneratorTools.randomTel(),
                                    building_id,
                                    floor,
                                    isHolder?1:0,
                                    centroid));
                }
            }
            return peopleList;
        }).collect(Collectors.toList());
        List<People> resultSet = new ArrayList<>();
        for (int i = 0; i < resultList.size(); i++) {
            List<People> people = resultList.get(i);
            for (int i1 = 0; i1 < people.size(); i1++) {
                resultSet.add(people.get(i1));
            }
        }
        System.out.println(resultSet.get(0).toString());
        System.out.println();
        //分段批量添加
        int piece = 1000;
        int total = resultSet.size();
        System.out.println(total);
        long batch = peopleService.insertBatch(resultSet);
        System.out.println("影响的总行数："+batch);
}

如下为约91万条数据下的测试结果，