Spring SimpleJdbcCall实现带输出参数的存储过程批量执行

Got it, let's break this down step by step. I've tackled similar scenarios before using Spring's SimpleJdbcCall, so I'll walk you through exactly how to make this work—from setting up the call to handling batch data and retrieving output parameters.

First, Let's Define a Sample Stored Procedure #

To ground this, let's assume you have a stored procedure that accepts a batch of input data, applies conditional logic to filter which records get inserted, and returns the count of successfully inserted rows as an output parameter. Here's a SQL Server example (adjust syntax for your database like Oracle/MySQL):

CREATE PROCEDURE BatchInsertWithCondition
    @InputData dbo.YourCustomTableType READONLY, -- Custom table type for batch input
    @SuccessCount INT OUTPUT -- Output parameter: number of rows inserted
AS
BEGIN
    SET NOCOUNT ON;
    SET @SuccessCount = 0;

    -- Conditional logic: only insert records where Col1 meets the criteria
    INSERT INTO YourTargetTable (Col1, Col2, Col3)
    SELECT Col1, Col2, Col3
    FROM @InputData
    WHERE Col1 > 10; -- Your custom condition here

    SET @SuccessCount = @@ROWCOUNT;
END

Note: You'll need to pre-create the YourCustomTableType in your database (matching the structure of your input data).

Step 1: Configure SimpleJdbcCall #

In your Spring service, initialize SimpleJdbcCall by linking it to your data source, specifying the procedure name, and declaring the output parameter.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.SqlOutParameter;
import java.sql.Types;
import javax.sql.DataSource;

@Service
public class BatchInsertService {

    private final SimpleJdbcCall batchInsertCall;

    // Inject DataSource via constructor (or use JdbcTemplate directly)
    public BatchInsertService(DataSource dataSource) {
        this.batchInsertCall = new SimpleJdbcCall(dataSource)
                .withProcedureName("BatchInsertWithCondition")
                // Declare the output parameter—name must match the procedure's output param
                .declareParameters(
                        new SqlOutParameter("SuccessCount", Types.INTEGER)
                );
    }
}

Step 2: Convert Java Data to Database-Compatible Batch Format #

Most databases require you to convert Java collections into a database-specific table type (like SQL Server's SQLServerDataTable or Oracle's Array of Struct). Let's use SQL Server as an example:

First, define your entity class:

public class YourDataEntity {
    private Integer col1;
    private String col2;
    private LocalDateTime col3;

    // Getters and setters
}

Then, write a helper method to convert a list of entities to a SQLServerDataTable:

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import java.util.List;

private SQLServerDataTable convertToDataTable(List<YourDataEntity> dataList) {
    SQLServerDataTable dataTable = new SQLServerDataTable();
    // Match column names/types to your custom table type
    dataTable.addColumnMetadata("Col1", Types.INTEGER);
    dataTable.addColumnMetadata("Col2", Types.VARCHAR);
    dataTable.addColumnMetadata("Col3", Types.TIMESTAMP);

    for (YourDataEntity entity : dataList) {
        dataTable.addRow(
                entity.getCol1(),
                entity.getCol2(),
                entity.getCol3()
        );
    }
    return dataTable;
}

Step 3: Execute the Call and Retrieve the Output Parameter #

Now, put it all together to execute the stored procedure, pass the batch data, and get the output count.

public int performBatchInsert(List<YourDataEntity> dataList) {
    // Convert your Java list to the database-compatible table type
    SQLServerDataTable inputData = convertToDataTable(dataList);

    // Build the parameter source—name must match the procedure's input param
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("InputData", inputData);

    // Execute the stored procedure call
    Map<String, Object> resultMap = batchInsertCall.execute(params);

    // Extract the output parameter from the result map
    return (Integer) resultMap.get("SuccessCount");
}

Key Notes for Different Databases #

• Oracle: Instead of SQLServerDataTable, you'll use Struct objects wrapped in an Array (use jdbcTemplate.getNativeConnection() to create these).
• MySQL: MySQL doesn't support table types natively, so you can use batch updates with JdbcTemplate.batchUpdate() or pass a JSON array and parse it in the procedure.
• Parameter Sensitivity: Ensure parameter names exactly match those in your stored procedure (case sensitivity depends on your database's configuration).
• Error Handling: Wrap the call in a try-catch block to handle SQL exceptions (e.g., constraint violations) and log appropriately.

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