优化Java代码实现SQL Server高效CSV导入，对标SSMS速度

一、SSMS导入高效的底层原因 #

• 批量数据传输协议：SSMS依赖SQL Server的Bulk Copy Program (BCP) 底层机制，通过Tabular Data Stream (TDS)协议以二进制格式批量推送数据，跳过常规SQL语句的解析、编译流程，大幅降低服务器端开销。
• 日志优化：导入时自动启用批量日志恢复模式（针对简单/完整恢复模式的数据库），仅记录批量操作元数据而非每条记录的详细日志，减少磁盘IO压力。
• 服务器端直接处理：若文件在服务器本地，SSMS会让SQL Server直接读取文件，避免客户端-服务器的传输损耗；即使文件在客户端，也采用流式传输而非逐条处理。
• 资源预优化：提前对目标表施加表级锁（减少行锁竞争）、临时禁用非聚集索引（导入后重建），消除导入过程中的额外资源消耗。

二、Java实现的优化方案 #

1. 优先使用SQL Server JDBC批量加载API（最接近SSMS效率） #

SQL Server JDBC驱动提供SQLServerBulkCopy类，直接对接BCP功能，是最优选择。结合Apache Commons CSV处理复杂CSV格式的示例代码：

import com.microsoft.sqlserver.jdbc.ISQLServerBulkRecord;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerConnection;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;

public class BulkCopyCsvImport {
    private static final String CSV_FILE_PATH = "file.csv";
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDB;encrypt=true;trustServerCertificate=true;";
    private static final String DB_USER = "sa";
    private static final String DB_PWD = "yourPassword";
    private static final String TARGET_TABLE = "TableA";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
             CSVParser parser = CSVFormat.DEFAULT.withHeader().parse(new FileReader(CSV_FILE_PATH))) {

            List<CSVRecord> csvRecords = new ArrayList<>();
            for (CSVRecord record : parser) {
                csvRecords.add(record);
            }

            SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy((SQLServerConnection) conn);
            bulkCopy.setDestinationTableName(TARGET_TABLE);

            // 实现批量数据记录接口
            ISQLServerBulkRecord bulkRecord = new ISQLServerBulkRecord() {
                private int currentRow = -1;

                @Override
                public int getColumnCount() {
                    return 2; // 目标表列数
                }

                @Override
                public String getColumnName(int columnIdx) {
                    return columnIdx == 0 ? "Column1" : "Column2";
                }

                @Override
                public int getColumnType(int columnIdx) {
                    return columnIdx == 0 ? java.sql.Types.VARCHAR : java.sql.Types.VARCHAR; // 匹配表列类型
                }

                @Override
                public boolean next() {
                    currentRow++;
                    return currentRow < csvRecords.size();
                }

                @Override
                public Object getValue(int columnIdx) {
                    CSVRecord record = csvRecords.get(currentRow);
                    return columnIdx == 0 ? record.get("Column1") : record.get("Column2");
                }

                // 可选方法默认实现
                @Override
                public String getServerTypeName(int column) { return null; }
                @Override
                public boolean isAutoIncrement(int column) { return false; }
                @Override
                public int getPrecision(int column) { return 0; }
                @Override
                public int getScale(int column) { return 0; }
            };

            bulkCopy.writeToServer(bulkRecord);
            bulkCopy.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2. 优化现有JdbcTemplate批量插入逻辑 #

如果暂时无法切换API，修复当前代码的核心问题：

• 开启批量语句重写：SQL Server JDBC驱动默认关闭rewriteBatchedStatements，需在数据源URL中添加该参数，驱动会将多条INSERT合并为单条批量语句，减少网络往返。
• 事务与批量大小优化：手动控制事务范围，避免频繁提交；调整BATCH_SIZE至1000-5000（需配合超时参数）。
• 专业CSV解析：用OpenCSV或Apache Commons CSV替代String.split，处理带引号、转义符的合法CSV字段。

优化后的JdbcTemplate代码：

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.support.TransactionTemplate;
import com.opencsv.CSVReader;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.List;

public class OptimizedJdbcTemplateImport {
    private static final String CSV_FILE_PATH = "file.csv";
    private static final String SQL_INSERT = "INSERT INTO TableA WITH (TABLOCK) (Column1, Column2) VALUES (?, ?)";
    private static final int BATCH_SIZE = 2000;

    public static void main(String[] args) {
        // 数据源URL需添加：rewriteBatchedStatements=true&queryTimeout=600
        JdbcTemplate jdbcTemplate = new JdbcTemplate(/* 配置好的数据源 */);
        TransactionTemplate transactionTemplate = new TransactionTemplate(jdbcTemplate.getTransactionManager());

        transactionTemplate.execute(status -> {
            try (CSVReader reader = new CSVReader(new FileReader(CSV_FILE_PATH))) {
                List<Object[]> batchArgs = new ArrayList<>(BATCH_SIZE);
                reader.readNext(); // 跳过表头

                String[] line;
                while ((line = reader.readNext()) != null) {
                    batchArgs.add(new Object[]{line[0], line[1]});
                    if (batchArgs.size() >= BATCH_SIZE) {
                        jdbcTemplate.batchUpdate(SQL_INSERT, batchArgs);
                        batchArgs.clear();
                    }
                }

                if (!batchArgs.isEmpty()) {
                    jdbcTemplate.batchUpdate(SQL_INSERT, batchArgs);
                }
                return null;
            } catch (Exception e) {
                status.setRollbackOnly();
                e.printStackTrace();
                return null;
            }
        });
    }
}

3. 数据库端辅助优化 #

• 切换恢复模式：导入前将数据库设为简单恢复模式，导入完成后恢复原模式，减少日志生成。
• 临时禁用索引：导入前禁用目标表的非聚集索引，导入后重建索引，避免每次插入更新索引的开销。
• 表级锁提示：在INSERT语句中添加WITH (TABLOCK)，强制数据库使用表级锁，降低锁竞争。

内容的提问来源于stack exchange，提问作者Deocareza NoevieJean