R2DBC调用存储过程时不返回数据,或者数据没有传递给控制器

移动开发 2026-07-08

我的第一个基于Spring Reactor的 Kotlin应用,使用r2dbc:
控制器:

@RestController
class RootController (private val svc: ProcessMsgService) {

    @PostMapping(
        value = ["/"]
        , consumes = [MediaType.APPLICATION_XML_VALUE, MediaType.TEXT_XML_VALUE]
        , produces = [MediaType.APPLICATION_XML_VALUE]
    )
    fun acceptXml (@RequestBody iMsg: String): Mono<ResponseEntity<String>> {
        val oXml = XML {compact() }

        val msg = XML1_0.decodeFromString(MyType.serializer(), iMsg)
        return svc.getRespFromDb (msg).map { oMsg ->
            ResponseEntity.ok()
                        .contentType(MediaType.APPLICATION_XML)
                        .body ( oXml.encodeToString (oMsg))
        }
    }
}

服务:

@Service
class ProcessMsgService (private val databaseClient: DatabaseClient) {

    fun getRespFromDb (msg: MyType): Mono<MyType> {
        return procDoc (msg)
// there are some more choices (removed here for brewity) in actual code, that is why this addittional fun required
    }

    @Transactional(readOnly = true)
    fun procDoc (msg: MyType): Mono<MyType> {
        return execDbCall (msg)
                .map { r ->
                    msg.copy (directionAttr = "Rs",   // was "Rq" in the request
                        respTextAttr = "Success",
                        msgData = msg.msgData.copy ()
                    )  // execDbCall' result consuming removed for brewity                }
    }

    fun execDbCall (doc: MyType): Mono<DbRespBA> {
        val sql = "BEGIN delme.dumb(in1 => :p_in1, in2 => null, out1 => :p_out1, out2 => :p_out2); END;"
        val pIn1 = "in-1"


        return databaseClient.sql(sql)
            .bind("p_in1", Parameters.`in`(R2dbcType.VARCHAR, pIn1))
            .bind("p_out1", Parameters.out(R2dbcType.VARCHAR))
            .bind("p_out2", Parameters.out(R2dbcType.VARCHAR))
            .map { outParameters, _ ->
                DbRespBA(
                        o1 = outParameters.get("p_out1", String::class.java) ?: "undef"
                        , o2 = outParameters.get("p_out2", String::class.java) ?: "undef"
                        )
            }
            .one() // Converts Flux to Mono<xxx>

    }

    data class DbRespBA (
        val o1: String,
        val o2: String
    )
}

MyType数据类:

@Serializable
@XmlSerialName("MyType")
data class MyType(
    @XmlSerialName("direction")
    val directionAttr: String,

    @XmlSerialName("resp_text")
    val respTextAttr: String? = null,

    @XmlSerialName("MsgData")
    val msgData: MsgData   // defined below in the actual code, but seems not be very important in the question
)

delme.dumb过程(如涉及Oracle)只是填充输出参数o1和 o2,并将调用日志写入某个表,除此之外别无他事,因此省略了它的实现。

问题在于控制器中的acceptXml返回的是空内容(只有HTTP 200)。delme.dumb过程显然被调用,我在日志表中看到了它的记录。到底哪里出错?显然,我把响应式调用搞糊涂了,但到底是在什么地方混乱?

将上面的procDoc替换为不依赖数据库的实现

    fun procDoc  (msg: MyType): Mono<MyType> {
        return (Mono.just (
                    msg.copy (directionAttr = "Rs",
                        respTextAttr = "Success",
                        msgData = msg.msgData.copy ()
                    )
                )
        )
    }

工作正常,核心的响应已经生成

解决方案

好吧,供其他有兴趣的人参考:

    fun placeOrder(request: OrderRequest): Mono<OrderResponse> =
        Mono.usingWhen(
            connectionFactory.create().toMono(),

            { connection ->
                // Use the standard JDBC escape syntax { call ... } — Oracle R2DBC
                // maps this to a CallableStatement and wires up OUT parameters
                // correctly, which is what makes OutParameters results available.
                val sql = """
                    { call PKG_ORDERS.PLACE_ORDER(
                        p_customer_id  => :customerId,
                        p_product_code => :productCode,
                        p_quantity     => :quantity,
                        p_order_id     => :orderId,
                        p_status       => :status,
                        p_total_price  => :totalPrice,
                        p_message      => :message
                    ) }
                """.trimIndent()

                Flux.from(
                    connection.createStatement(sql)
                        // ── IN parameters ──────────────────────────────────────
                        .bind("customerId",  request.customerId)
                        .bind("productCode", request.productCode)
                        .bind("quantity",    request.quantity)
                        // ── OUT parameters ─────────────────────────────────────
                        // Registered with Parameters.out(...) so Oracle R2DBC
                        // knows to read them back after execution.
                        .bind("orderId",    Parameters.out(R2dbcType.NUMERIC))
                        .bind("status",     Parameters.out(R2dbcType.VARCHAR))
                        .bind("totalPrice", Parameters.out(R2dbcType.NUMERIC))
                        .bind("message",    Parameters.out(R2dbcType.VARCHAR))
                        .execute()
                )
                // flatMap over every Result segment emitted by execute()
                .flatMap { result: Result ->
                    // filter(OutParameters::class) picks only the OUT-param segment;
                    // row segments and update-count segments are skipped.
                    result.filter { segment -> segment is Result.OutSegment }
                          .map { readable ->
                              // readable is an OutParameters instance here
                              OrderResponse(
                                  orderId    = readable.get("orderId",    java.math.BigDecimal::class.java)?.toLong()   ?: 0L,
                                  status     = readable.get("status",     String::class.java)                           ?: "",
                                  totalPrice = readable.get("totalPrice", java.math.BigDecimal::class.java)?.toDouble() ?: 0.0,
                                  message    = readable.get("message",    String::class.java)                           ?: ""
                              )
                          }
                }
                .next()  // we expect exactly one OUT-parameters segment → take it
                .doOnNext { response ->
                    log.info("Procedure returned: orderId={}, status={}", response.orderId, response.status)
                }
                .doOnError { ex ->
                    log.error("Procedure call failed", ex)
                }
            },

            { connection -> connection.close().toMono() },
            { connection, _ -> connection.close().toMono() },
            { connection -> connection.close().toMono() }
        )

DatabaseClient是一段更为复杂的旅程(参数命名规则惊人),目前尚未完成……

站内所有文章版权归属LeftHeroAI导航站,无授权禁止任何主体转载、抄袭、复制内容,亦不得私自架设镜像站点。一经侵权,本站将通过法律途径追责。

相关文章