Spring Data R2DBC调用MySQL存储过程无返回数据求助

使用Spring Webflux构建API时，通过Spring R2DBC调用MySQL存储过程后无法获取任何数据。刚接触R2DBC，已参考相关规范、驱动文档及版本说明处理参数化语句与绑定，但问题仍未解决。

表结构与存储过程代码 #

CREATE TABLE cars (make varchar(100), model varchar(100), year int, value decimal(10, 2));
INSERT INTO cars VALUES('Porsche', '911 GT3', 2017, 169700),('Porsche', 'Cayman GT4', 2017, 118000),('Porsche', 'Panamera', 2017, 113200);

CREATE PROCEDURE get_car_stats_by_year2(IN year_filter INT,OUT cars_number INT,OUT min_value DECIMAL(10,2),OUT avg_value DECIMAL(10,2),OUT max_value DECIMAL(10,2))
BEGIN 
 SELECT COUNT(*), MIN(value), AVG(value), MAX(value) INTO cars_number, min_value, avg_value, max_value 
 FROM cars 
 WHERE year = year_filter 
 ORDER BY make, value DESC;
END;

CALL get_car_stats_by_year2(2017, @number, @min, @avg, @max);
SELECT @number, @min, @avg, @max;

Java实现代码 #

@Repository
public class CarsRepository {
    private ConnectionFactory connectionFactory;

    public CarsRepository(ConnectionFactory connectionFactory) {
        this.connectionFactory = connectionFactory;
    }

    public Flux<CarStats> getCarStatsByYear(int year) {
        return Mono.from(this.connectionFactory.create())
                .flatMapMany(connection -> {
                    return connection.createStatement("CALL get_car_stats_by_year2(?, ?number, ?min, ?avg, ?max)")
                            .bind(0, Parameters.in(R2dbcType.INTEGER, year))
                            .bind("number", Parameters.out(R2dbcType.INTEGER))
                            .bind("min", Parameters.out(R2dbcType.FLOAT))
                            .bind("avg", Parameters.out(R2dbcType.FLOAT))
                            .bind("max", Parameters.out(R2dbcType.FLOAT))
                            .execute();
                })
                .filter(Result.OutSegment.class::isInstance) //No OutSegment found
                .map(segment -> {
                    Result.OutSegment data = (Result.OutSegment) segment;
                    System.err.println("OutParameters: " + data.outParameters().getMetadata());
                    return CarStats.builder()
                            .number(Integer.parseInt(data.outParameters().get(0).toString()))
                            .min(Float.parseFloat(data.outParameters().get(1).toString()))
                            .avg(Float.parseFloat(data.outParameters().get(2).toString()))
                            .max(Float.parseFloat(data.outParameters().get(3).toString()))
                            .build();
                });
    }
}

日志检测结果 #

添加日志后发现返回的Segment不属于任何已知类型：

...flatMapMany(connection -> ...)
.doOnNext(result -> {
    System.err.println("Result.OutSegment.class::isInstance: " + Result.OutSegment.class.isInstance(result));
    System.err.println("Result.RowSegment.class::isInstance: " + Result.RowSegment.class.isInstance(result));
    System.err.println("Result.UpdateCount.class::isInstance: " + Result.UpdateCount.class.isInstance(result));
    System.err.println("Result.Message.class::isInstance: " + Result.Message.class.isInstance(result)); 
})
.filter(Result.OutSegment.class::isInstance) ...

Result.OutSegment.class::isInstance: false
Result.RowSegment.class::isInstance: false
Result.UpdateCount.class::isInstance: false
Result.Message.class::isInstance: false

排查与解决方案 #

核心问题分析 #

1. OUT参数处理方式错误：MySQL R2DBC驱动对命名OUT参数的支持有限，当前写法无法正确识别OUT参数绑定，导致无法获取返回值。
2. Result Segment类型判断错误：驱动执行CALL语句后，不会直接返回OutSegment，而是需要通过查询用户变量获取结果，对应返回的是RowSegment。
3. 数据类型不匹配：存储过程中使用DECIMAL类型，代码中用FLOAT绑定会导致类型转换异常，且丢失精度。
4. 连接资源泄漏：代码未显式关闭连接，会导致连接池资源耗尽。

修改后的代码示例 #

@Repository
public class CarsRepository {
    private final ConnectionFactory connectionFactory;

    public CarsRepository(ConnectionFactory connectionFactory) {
        this.connectionFactory = connectionFactory;
    }

    public Flux<CarStats> getCarStatsByYear(int year) {
        return Mono.from(connectionFactory.create())
                .flatMap(connection -> 
                    connection.createStatement("CALL get_car_stats_by_year2(?, @number, @min, @avg, @max); SELECT @number, @min, @avg, @max")
                            .bind(0, year)
                            .execute()
                            // 跳过CALL语句返回的空结果，获取后续SELECT的结果
                            .skip(1)
                            .flatMap(result -> result.map((row, metadata) -> 
                                CarStats.builder()
                                        .number(row.get(0, Integer.class))
                                        .min(row.get(1, BigDecimal.class))
                                        .avg(row.get(2, BigDecimal.class))
                                        .max(row.get(3, BigDecimal.class))
                                        .build()
                            ))
                            // 确保连接最终关闭，避免泄漏
                            .doFinally(signal -> connection.close())
                );
    }
}

额外注意事项 #

• 调整CarStats字段类型：将min、avg、max字段类型改为BigDecimal，对应MySQL的DECIMAL类型，避免精度丢失。
• 更新驱动版本：确保使用最新版的r2dbc-mysql驱动，旧版本对存储过程的支持存在缺陷。
• 验证存储过程逻辑：在MySQL客户端手动执行CALL和SELECT语句，确认存储过程本身能返回正确结果。

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