spring boot + sql server大数据量批量新增

帆仔哟

已于 2023-10-23 15:04:03 修改

阅读量490

点赞数

分类专栏：数据库文章标签： spring boot 后端 sqlserver

于 2023-09-20 11:17:01 首次发布

本文链接：https://blog.csdn.net/FanZaiYo/article/details/133071167

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.SQLServerBulkCopy方式（推荐这种没什么诡异的bug）

代码

    /**
     * 获取类中所有属性注解@TableId和@TableField
     *
     * @param instance
     * @return
     * @throws NoSuchFieldException
     */
    public static Map<String, String[]> getDeclaredFieldsInfo(Object instance) throws NoSuchFieldException {
        Map<String, String[]> map = new HashMap();
        Class<?> clazz = instance.getClass();
        Field[] fields = clazz.getDeclaredFields();
        StringBuilder stringBuilder = new StringBuilder();

        for (int i = 0; i < fields.length; i++) {
            String annotationName = "";
            boolean annotationId = fields[i].isAnnotationPresent(TableId.class);
            if (annotationId) {
                // 获取注解值
                annotationName = fields[i].getAnnotation(TableId.class).value();
            }
            boolean annotationPresent = fields[i].isAnnotationPresent(TableField.class);
            if (annotationPresent) {
                // 获取注解值
                annotationName = fields[i].getAnnotation(TableField.class).value();
            }
            stringBuilder.append(annotationName);
            stringBuilder.append(",");
            // 字段名称
            String attributesName = fields[i].getName();
            map.put(attributesName, new String[]{annotationName, fields[i].getType().getName()});
        }
        return map;
    }


    /**
     * 采用saveBulkCopy的方式批量保存数据
     *
     * @param objects
     * @param <T>
     */
    public <T> void saveBulkCopy(List<T> objects, String tableName) throws SQLException {
        if (ObjectUtil.isEmpty(objects)) {
            return;
        }
        T objClass = objects.get(0);
        Connection connection = ConnectionFactory.getConnection();
        try {
            // 数据库表名
//            String tableName = objClass.getClass().getAnnotation(TableName.class).value();
            // 表字段属性名和类型@TableId和@TableField
            Map<String, String[]> declaredFieldsInfo = getDeclaredFieldsInfo(objClass);

            ResultSet rs = executeSQL(connection, "select * from " + tableName + " where 1=0");
            CachedRowSetImpl crs = new CachedRowSetImpl();
            crs.populate(rs);
            //循环插入数据
            long startTime = System.currentTimeMillis();
            //既然是批量插入肯定是需要循环
            for (int i = 0, leg = objects.size(); i < leg; i++) {
                //移动指针到“插入行”，插入行是一个虚拟行
                crs.moveToInsertRow();
                //更新虚拟行的数据（实体类要新增的字段，大家根据自己的实体类的字段来修改）
                //数据库字段     ，填写的值
                JSONObject jsonObject = JSONUtil.parseObj(objects.get(i), false, true);
                for (Map.Entry<String, Object> map : jsonObject.entrySet()) {
                    // 属性和值
                    String key = map.getKey();
                    Object value = map.getValue();
                    // 过滤不是数据库的字段
                    if (!declaredFieldsInfo.containsKey(key)) {
                        continue;
                    }
                    // 字段名称，字段类型
                    String[] strings = declaredFieldsInfo.get(key);
                    String type = strings[1];

                    if (StrUtil.equals(type, String.class.getName())) {
                        if (ObjectUtil.isEmpty(value) || StrUtil.equals("null", value.toString())) {
                            crs.updateString(key, null);
                        } else {
                            crs.updateString(key, value.toString());
                        }
                    } else if (StrUtil.equals(type, Integer.class.getName())) {
                        if (ObjectUtil.isEmpty(value) || StrUtil.equals("null", value.toString())) {
                            crs.updateInt(key, 0);
                        } else {
                            crs.updateInt(key, Integer.parseInt(value.toString()));
                        }
                    } else if (StrUtil.equals(type, LocalDateTime.class.getName())) {
                        LocalDateTime localDateTime = (LocalDateTime) value;
                        crs.updateDate(key, localTimeToDate(localDateTime));
                    } else if (StrUtil.equals(type, LocalDate.class.getName())) {
                        LocalDate localDate = (LocalDate) value;
                        crs.updateDate(key, java.sql.Date.valueOf(localDate));
                    } else if (StrUtil.equals(type, Long.class.getName())) {
                        crs.updateLong(key, 0);
                    } else if (StrUtil.equals(type, Double.class.getName())) {
                        crs.updateDouble(key, (Double) value);
                    } else if (StrUtil.equals(type, int.class.getName())) {
                        crs.updateInt(key, (Integer) value);
                    } else if (StrUtil.equals(type, Float.class.getName())) {
                        crs.updateFloat(key, (Float) value);
                    } else if (StrUtil.equals(type, java.util.Date.class.getName())) {
                        java.util.Date date = (java.util.Date) value;
                        crs.updateDate(key, new java.sql.Date(date.getTime()));
                    } else if (StrUtil.equals(type, java.math.BigDecimal.class.getName())) {
                        if (ObjectUtil.isEmpty(value) || StrUtil.equals("null", value.toString())) {
                            crs.updateBigDecimal(key, BigDecimal.ZERO);
                        } else {
                            BigDecimal bigDecimal = null;
                            if (value instanceof BigDecimal) {
                                bigDecimal = (BigDecimal) value;
                            } else if (value instanceof String) {
                                bigDecimal = new BigDecimal((String) value);
                            } else if (value instanceof BigInteger) {
                                bigDecimal = new BigDecimal((BigInteger) value);
                            } else if (value instanceof Number) {
                                bigDecimal = new BigDecimal(((Number) value).doubleValue());
                            }
                            crs.updateBigDecimal(key, bigDecimal);
                        }
                    } else {
                        log.info("未知的数据类型：{}", type);
                    }
                }
                //插入虚拟行
                crs.insertRow();
                //移动指针到当前行
                crs.moveToCurrentRow();
            }
            //进行批量插入
            SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
            copyOptions.setKeepIdentity(false);
            copyOptions.setBatchSize(1000);
            copyOptions.setUseInternalTransaction(false);

            SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connection);
            bulkCopy.setBulkCopyOptions(copyOptions);
            bulkCopy.setDestinationTableName(tableName);
            bulkCopy.writeToServer(crs);
            crs.close();
            bulkCopy.close();
            log.info("耗时：{},数量:{}", System.currentTimeMillis() - startTime, objects.size());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 数据查找，返回查找的内容，向上抛异常
    public ResultSet executeSQL(Connection con, String sql, Object... object) throws SQLException {
        PreparedStatement ps = con.prepareStatement(sql);
        for (int i = 0; i < object.length; i++) {
            //ps传入参数的下标是从1开始
            ps.setObject(i + 1, object[i]);
        }
        //返回结果集
        return ps.executeQuery();
    }

    public static java.sql.Date localTimeToDate(LocalDateTime lt) {
        return new java.sql.Date(lt.atZone(ZoneId.systemDefault()).toInstant()
                .toEpochMilli());
    }

因为我有很多张表（表名称不同，字段相同）都要走这个方法，所以我的表名是直接写死的，如果你不是这样的话可以在实体类上直接使用@Table()指定数据库表即可，无需传入表名。

直接复制粘贴代码就可以使用，调用的是saveBulkCopy方法，这个方法是sqlserver独有的新增方法，mybaits plus的批量新增对于sqlserver来说其实还是慢新增，而且也无法手写foreach新增，因为sqlserver对与参数是有限制的限制在2100个参数。

用以上方法记得要在实体类标记对应注解，我把我的sqlserver数据库变成了自增主键，也是需要打上注解的，所以无需担心，直接使用即可。

实体类

package cn.iocoder.yudao.module.biz.dal.dataobject;

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class SdyDataVisualDailySortResult {
    @TableField("id")
    private Integer id;
    @TableField("targetTime")
    private Date targetTime;
    @TableField("manager")
    private String manager;


}

ConnectionFactory 建立连接方法

public class ConnectionFactory {
    private static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static String URL = "你的数据库连接";
    private static String USER = "账号";
    private static String PASSWORD = "密码";

    /**
     * 提供getConnection()方法
     * @return Connection
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

实测第一种方式新增40w+数据仅需30左右

2.原始jdbc方式（有诡异bug，技术不够无法查明原因）

代码

public void insertBach(List<DashboardsDO> dashboardsDOS) throws SQLException {
        Connection conn = null;
        try {
            // SQLSERVER驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // 情况2：使用批处理 ==> rewriteBatchedStatements=true  ==> 每次50000条  耗时: 6秒
            conn = DriverManager.getConnection("数据库连接", "账号", "密码");
            //批量插入50000
            int batchSize = 50000;
            // 总条数1000000
            int count = dashboardsDOS.size();
            //设置自动提交为false
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("insert into " + "数据库表名" + " ("字段1","字段2","字段3") values ("值1","值2","值3")");
            Long t1 = System.currentTimeMillis();
            System.out.println("========开始运行=========");
            for (int i = 0; i < dashboardsDOS.size(); ++i) {
                //设置第一个参数的值为i
                ps.setString(1, dashboardsDOS.get(i).getStoreCode());
                ps.setString(2, dashboardsDOS.get(i).getStoreName());
                ps.setTimestamp(3, new Timestamp(format.parse(format.format(calendar.getTime())).getTime()));
                //将该条记录添加到批处理中
                ps.addBatch();
                if (i % batchSize == 0) {
                    //执行批处理
                    ps.executeBatch();
                    //提交
                    conn.commit();
                    System.out.println(i + ":添加" + batchSize + "条");
                }
            }
            if (count % batchSize != 0) {
                ps.executeBatch();
                conn.commit();
            }
            ps.close();
            Long t2 = System.currentTimeMillis();
            System.out.println("总条数:" + count + "条  每次插入" + batchSize + "条   " + "  每次耗时:" + (t2 - t1) / 1000 + "秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
    }