SQL Server大批量数据插入

数据库连接及相关操作

public class DataBase {
    /**
     * 驱动
     */
    private static final String DRIVER = PropertiesUtil.getString("spring.datasource.driver-class-name");

    /**
     * 数据库地址
     */
    private static final String URL = PropertiesUtil.getString("spring.datasource.url");

    /**
     * 数据库用户名
     */
    private static final String NAME = PropertiesUtil.getString("spring.datasource.username");

    /**
     * 数据库密码
     */
    private static final String PASSWORD = PropertiesUtil.getString("spring.datasource.password");

    /**
     * 数据库连接，定义为全局变量，方便调用
     */
    private Connection con;

    //导入驱动，静态代码块的作用为只运行一次,异常无法向上抛出，只能及时处理
    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            //打印异常相关信息
            e.printStackTrace();
        }
    }

    /**
     * 无参构造方法，连接数据库
     */
    public DataBase() {
        try {
            con = DriverManager.getConnection(URL, NAME, PASSWORD);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

    /**
     * 数据查找，返回查找的内容，向上抛异常
     *
     * @param sql
     * @param object
     * @return
     * @throws SQLException
     */
    public ResultSet executeSQL(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();
    }

    /**
     * 关闭数据库连接
     *
     * @throws SQLException
     */
    public void close() throws SQLException {
        con.close();
    }

    /**
     * 设置 AutoCommit 模式为 false
     *
     * @throws SQLException
     */
    public void setAutoCommit(boolean flag) throws SQLException {
        // 设置 AutoCommit 模式
        con.setAutoCommit(flag);
    }

    /**
     * 事务提交
     *
     * @throws SQLException
     */
    public void commit() throws SQLException {
        // 手动提交事务
        con.commit();
    }

    /**
     * 事务回滚
     *
     * @throws SQLException
     */
    public void rollback() throws SQLException {
        con.rollback();
    }
}

批量插入数据

public class BulkCopyUtil {

    /**
     * 批量插入数据
     *
     * @param tableName 表名
     * @param list      数据集合
     * @throws SQLException
     */
    public static <T> void insertBatch(String tableName, List<T> list) throws Exception {
        // 查询出空值用于构建 CachedRowSetImpl 对象以省去列映射的步骤
        DataBase dataBase = new DataBase();
        // 从源表中获取数据作为 ResultSet
        ResultSet resultSet = dataBase.executeSQL("select * from " + tableName + " where 1=0");
        CachedRowSetImpl crs = new CachedRowSetImpl();
        crs.populate(resultSet);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] dbFieldNames = new String[columnCount];
        for (int i = 1; i <= columnCount; i++) {
            dbFieldNames[i-1] = metaData.getColumnName(i);
        }
        // 循环批量插入
        for (T t : list) {
            if (ObjectUtil.isNotEmpty(t)) {
                // 移动指针到“插入行”，插入行是一个虚拟行
                crs.moveToInsertRow();
                // 向 CachedRowSet 对象插入一条数据
                populate(crs, t, dbFieldNames);
                // 插入虚拟行
                crs.insertRow();
                // 移动指针到当前行
                crs.moveToCurrentRow();
            }
        }
        // 进行批量插入
        bulkCopyHelp(dataBase, crs, tableName, list.size());
    }

    /**
     * 批量插入数据
     *
     * @param dataBase  数据库连接相关操作
     * @param crs       CachedRowSet
     * @param tableName 表名
     * @param size      拷贝列表大小
     */
    public static void bulkCopyHelp(DataBase dataBase, CachedRowSetImpl crs, String tableName, int size) throws
            SQLException {
        // 数据库地址
        String url = PropertiesUtil.getString("spring.datasource.url");
        // 数据库用户名
        String name = PropertiesUtil.getString("spring.datasource.username");
        // 数据库密码
        String password = PropertiesUtil.getString("spring.datasource.password");
        // 数据库连接字符串
        String urlStr = url + ";user=" + name + ";password=" + password;

        // 使用 KeepIdentity 选项和 BatchSize 设置大容量复制对象
        SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
        copyOptions.setKeepIdentity(true);
        copyOptions.setBatchSize(size);
        // 开启数据库事务
        copyOptions.setUseInternalTransaction(true);
        // 设置超时时间
        copyOptions.setBulkCopyTimeout(60000);

        SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(urlStr);
        bulkCopy.setBulkCopyOptions(copyOptions);
        // 设置拷贝目标表名
        bulkCopy.setDestinationTableName(tableName);
        // 将 crs 写入目标
        try {
            // 设置 AutoCommit 模式为 false
            dataBase.setAutoCommit(false);
            // 执行数据库操作
            bulkCopy.writeToServer(crs);
            // 手动提交事务
            dataBase.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            // 回滚
            dataBase.rollback();
        }
        crs.close();
        bulkCopy.close();
    }

    /**
     * 这里主要是通过表的列名，通过反射，拿到待插入对象的属性值
     *
     * @param crs
     * @param record
     * @param dbFieldNames
     * @param <T>
     * @throws Exception
     */
    private static <T> void populate(CachedRowSetImpl crs, T record, String[] dbFieldNames) throws Exception {
        Class<?> clazz = record.getClass();
        for (String fieldName : dbFieldNames) {
            StringBuilder getMethodName = new StringBuilder("get");
            if (fieldName.contains("_")) {
                String[] singleWords = fieldName.split("_");
                for (String singleWord : singleWords) {
                    getMethodName.append(upperFirstChar(singleWord));
                }
            } else {
                getMethodName.append(upperFirstChar(fieldName));
            }
            Method method = clazz.getMethod(getMethodName.toString(), null);
            Object value = method.invoke(record, null);
            updateCrs(crs, fieldName, value);
        }
    }

    /**
     * 根据数据值的类型，将值设置到rowset里--这里value是否为空，都要做crs.update操作，否则会出bug
     *
     * @param crs
     * @param dbFieldName
     * @param value
     * @throws SQLException
     */
    private static void updateCrs(CachedRowSetImpl crs, String dbFieldName, Object value) throws SQLException {
        if (value instanceof String) {
            crs.updateString(dbFieldName, (String) value);
        } else if (value instanceof Integer) {
            crs.updateInt(dbFieldName, (int) value);
        } else if (value instanceof Double) {
            crs.updateDouble(dbFieldName, (double) value);
        } else if (value instanceof Long) {
            crs.updateLong(dbFieldName, (long) value);
        } else if (value instanceof Float) {
            crs.updateFloat(dbFieldName, (float) value);
        } else if (value instanceof Timestamp) {
            crs.updateTimestamp(dbFieldName, (Timestamp) value);
        } else if (value instanceof java.util.Date) {
            crs.updateDate(dbFieldName, new java.sql.Date(((java.util.Date) value).getTime()));
        } else {
            crs.updateObject(dbFieldName, value);
        }
    }
	/**
     * 首字母大写
     * @param camelCaseStr
     * @return
     */
    private static String upperFirstChar(String camelCaseStr) {
        return camelCaseStr.substring(0, 1).toUpperCase() + camelCaseStr.substring(1);
    }
}

备注（数据库连接取值）

# 配置数据源
spring:
  datasource:
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://ip:port;DatabaseName=collection
    username: sa
    password: 123456