JAVA批量新增、批量修改

若数据量非常大，可以把List拆成多份，每份1000条数据。

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.StrUtil;
import cn.路劲.NetPointDTO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@Service
public class NetPointServiceImpl implements NetPointService {
	/**
     * 批量新增
     */
    @Override
    public boolean add(List<NetPointDTO> dataList) {
        //数据里的 编码 查重
        String netPointCode = dataList.stream()
                .collect(Collectors.groupingBy(NetPointDTO::getNetPointCode, Collectors.counting()))
                .entrySet()
                .stream()
                .filter(e -> e.getValue() > 1L)
                .map(Map.Entry::getKey)
                .collect(Collectors.joining("、"));
        if (StrUtil.isNotBlank(netPointCode)){
            throw new RuntimeException("网点编码 "+ netPointCode + " 重复");
        }

        //切分List， 此方法返回的是原List的视图，也就是说原List有变更，切分后的结果也会变更
        List<List<NetPointDTO>> partition = ListUtil.partition(dataList, 1000);

        Integer n = 0;
        for (List<NetPointDTO> list : partition){
        	//Mapper 很简单，就不赘述了
            n = 执行批量新增的Mapper.addBatch(list);
        }
        return n > 0;
    }
}

NetPointDTO

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Data
public class NetPointDTO {
    @ApiModelProperty(value = "网点信息编码", example = "企业网点编码", required = true)
    @NotBlank(message = "网点信息编码, 值不能为空")
    @Size(max = 50, message = "网点信息编码，最大长度 50位")
    private String netPointCode;;

    @ApiModelProperty(value = "网点名称", example = "网点名称", required = true)
    @NotBlank(message = "网点名称,值不能为空")
    @Size(max = 50, message = "网点名称，值长度 不能超过 50位")
    private String netPointName;
    
	@ApiModelProperty(value = "点坐标", example = "39.625934 118.232805", required = true)
    @NotBlank(message = "点坐标, 不能为空")
    private String point;

    @ApiModelProperty(value = "办公地点", example = "办公地址", required = true)
    @NotBlank(message = "办公地址, 不能为空")
    @Size(max = 100, message = "办公地点，值长度 不能超过 100位")
    private String officeAddress;
}

批量新增SQL.xml

useGeneratedKeys=“true” keyProperty=“id”
useGeneratedKeys设置为 true 时，
表示如果插入的表id以自增列为主键，则允许 JDBC 支持自动生成主键，并可将自动生成的主键id返回。
下边这种写法，实际上是通过循环，数据也是一条一条的插入的，好处是每条数据都有判断是否为null。

<insert id="addBatch" parameterType="java.util.List" keyProperty="id" useGeneratedKeys="true">
    <foreach collection="list" item="e" index="index" separator=";">
        insert into NET_POINT
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="e.netPointCode != null"> net_point_code, </if>
            <if test="e.netPointName != null"> NET_POINT_NAME, </if>
            <if test="e.point != null"> POINT, </if>
            <if test="e.officeAddress != null"> OFFICE_ADDRESS, </if>
            CREATE_TIME
        </trim>

        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="e.netPointCode != null"> #{e.netPointCode}, </if>
            <if test="e.netPointName != null"> #{e.netPointName}, </if>
            <if test="e.point != null"> dmgeo.ST_PointFromText(CONCAT('point(', #{e.point}, ')'), 0), </if>
            <if test="e.officeAddress != null"> #{e.officeAddress}, </if>
            now()
        </trim>
    </foreach>
</insert>

sql中point点坐标，前边的函数 是达梦数据库的，
MySQL数据库的函数为 ST_GEOMFROMTEXT(CONCAT(‘POINT(’, #{point}, ‘)’))
若直接批量新增，可以这样写

<insert id="saveBatch">
    insert into NET_POINT(net_point_code, NET_POINT_NAME, 
		      POINT, 
		      CREATE_TIME)
    values
    <foreach collection="list" item="e" separator=",">
        ( 	#{e.netPointCode}, #{e.netPointName},
        	dmgeo.ST_PointFromText(CONCAT('point(', #{e.point}, ')'), 0),
         	NOW())
    </foreach>
</insert>

批量修改SQL.xml

<update id="updateBatch" parameterType="java.util.List">
    <foreach collection="list" item="e" index="index" open="" close="" separator=";">
        UPDATE NET_POINT
        <set>
            update_time = NOW(),

            <if test="e.netPointName != null and e.netPointName != ''">
                NET_POINT_NAME = #{e.netPointName},
            </if>
           
            <if test="e.point != null and e.point != ''">
                point = dmgeo.ST_PointFromText(CONCAT('point(', #{e.point}, ')'), 0),
            </if>
            
            <if test="e.officeAddress != null and e.officeAddress != ''">
                OFFICE_ADDRESS = #{e.officeAddress},
            </if>
        </set>
        WHERE NET_POINT_CODE = #{e.netPointCode}
    </foreach>
</update>

pom.xml 文件 需要一个 hutool 依赖

	<!-- hutool支持 -->
	<dependency>
	    <groupId>cn.hutool</groupId>
	    <artifactId>hutool-all</artifactId>
	    <version>5.7.20</version>
	</dependency>