SqlServer数据批量写入（sql bulk copy）

近期工作中遇到一个迁移老系统的任务，大致内容就是将用户上传的excel文件，按照一定的规则做处理，将处理后的数据入库，数据库是sqlserver。老系统运行在.net系统上，单次跑任务需要八九个小时，给我的任务是移到java平台且性能提升10倍。为了完成任务，所以就需要有很多效率高一些的操作，这里记录一下关于数据insert入库的处理方案。

单个Excel文件最终产生的数据库行数就有400w到500w，由于sqlserver对于sql预编译的参数数量有限制，最高2100个参数，所以这里直接用insert语句插入显然是不行的。经过调研，了解到了sqlserver有sql bulk copy这么个api可以用来解决大批量数据的插入性能问题，这里简单记录一下使用过程以及其中遇到的问题。

先放一下最终的关键代码，如下：

public <T extends BaseBulkCopyEntity> void bulkCopy(String tableName, List<T> records) {
        log.info("start sql bulk copy, table: {}, dataSet size: {}", tableName, records.size());
        Connection conn = SqlConnPool.getConnection();
        //这里我是把每个表的当前id的最大值记录了下来，可以减少db次数，同样的逻辑还有下面的         ResultSet
        AtomicInteger currentId = BulkCopyCache.getId(tableName);
        int rollbackId = currentId.get();
        try {
            ResultSet resultSet = BulkCopyCache.getResultSet(tableName);
            String[] columnNames = BulkCopyCache.getColumnName(tableName);

            CachedRowSetImpl crs = new CachedRowSetImpl();
            crs.populate(resultSet);
            for (T record : records) {
                int id = currentId.incrementAndGet();
                record.setID(id);
                crs.moveToInsertRow();
                populate(crs, record, columnNames);
                crs.insertRow();
                crs.moveToCurrentRow();
            }
            SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(conn);
            SQLServerBulkCopyOptions sqlServerBulkCopyOptions = new SQLServerBulkCopyOptions();
            sqlServerBulkCopyOptions.setKeepIdentity(true);
            sqlServerBulkCopyOptions.setBatchSize(records.size());
            bulkCopy.setBulkCopyOptions(sqlServerBulkCopyOptions);
            bulkCopy.setDestinationTableName(tableName);
            bulkCopy.writeToServer(crs);
            crs.close();
            bulkCopy.close();
        } 
    }

//这里主要是通过表的列名，通过反射，拿到待插入对象的属性值
private <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(CommonUtils.upperFirstChar(singleWord));
                }
            }else {
                getMethodName.append(CommonUtils.upperFirstChar(fieldName));
            }
            Method method = clazz.getMethod(getMethodName.toString(), null);
            Object value = method.invoke(record, null);
            updateCRS(crs, fieldName, value);
        }
 }

//根据数据值的类型，将值设置到rowset里--这里value是否为空，都要做crs.update操作，否则会出bug
private 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);
        }
    }

这里因为对api的不熟悉，踩过坑，其中就有对CachedRowSetImpl类不熟悉导致的。一开始在updateCRS方法里，会对value做判断，如果为null，就直接return，导致最终的结果混乱。后面排查过后，改成了现在这样。

这种插入方式确实比insert快很多，我这单批次最少都是1w起步。都是根据网上不多的博客以及自己慢慢试错才搞出来的，中间踩过很多坑，这里记录一下，希望对后面有需要的人有所帮助。