使用jdbc:outbound-gateway插入数据库报错，请求技术支持

使用Spring Integration的int-jdbc:outbound-gateway插入数据库数据时，通过:headers[id]或:headers.id获取Header值会报错，但硬编码固定值可正常插入。同时需要解决如何通过该组件插入Blob类型的payload数据。

配置代码 #

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:int="http://www.springframework.org/schema/integration"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa"
    xmlns:int-http="http://www.springframework.org/schema/integration/http"
    xmlns:task="http://www.springframework.org/schema/task"
    xmlns:int-jdbc="http://www.springframework.org/schema/integration/jdbc"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:int-jms="http://www.springframework.org/schema/integration/jms"
    xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
                        http://www.springframework.org/schema/integration https://www.springframework.org/schema/integration/spring-integration.xsd
                        http://www.springframework.org/schema/data/jpa https://www.springframework.org/schema/data/jpa/spring-jpa.xsd
                        http://www.springframework.org/schema/integration/http https://www.springframework.org/schema/integration/http/spring-integration-http.xsd
                        http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task.xsd
                        http://www.springframework.org/schema/jdbc https://www.springframework.org/schema/jdbc/spring-jdbc.xsd
                        http://www.springframework.org/schema/integration/jdbc https://www.springframework.org/schema/integration/jdbc/spring-integration-jdbc.xsd
                        http://www.springframework.org/schema/tx https://www.springframework.org/schema/tx/spring-tx.xsd
                        http://www.springframework.org/schema/integration/jms http://www.springframework.org/schema/integration/jms/spring-integration-jms.xsd">

    <import
        resource="classpath:integration/spring-integration-database-context.xml" />

    <!-- Channels -->
    <int:gateway id="requestGatewayIntegration"
        service-interface="ca.bell.bmf.customer.gateway.CustomerProfileGateway"
        default-request-channel="objectToJsonChannel"
        error-channel="errorChannel11" />

    <int:channel id="objectToJsonChannel">
        <int:interceptors>
            <bean class="ca.bell.bmf.customer.service.MyChannelInterceptor" />
        </int:interceptors>

    </int:channel>
    
    
    <int-jdbc:outbound-gateway
    update="Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION) 
    values(:headers[id],:headers[id],'1211','2','3','Test')"
    request-channel="objectToJsonChannelTMF639" reply-channel="responseobjectToJsonChannelTMF639" data-source="dataSource" />   

</beans>

正常工作的硬编码配置 #

<int-jdbc:outbound-gateway update="Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION)  values('242452','','1211','2','3','Test')" request-channel="objectToJsonChannelTMF639" reply-channel="responseobjectToJsonChannelTMF639" data-source="dataSource" />

报错信息 #

from source: ''int-jdbc:outbound-gateway''' while handling 'GenericMessage [payload=CPMPayload ': error occurred in message handler [bean 'org.springframework.integration.jdbc.JdbcOutboundGateway#0'; defined in: 'URL [file:/C:/Users/19.3/Git/profile/process/target/classes/integration/Profile-context.xml]'; from source: ''int-jdbc:outbound-gateway'']; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION)      values(?,?,'1211','2','3','Test')]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type. Failing over to the next subscriber.

一、解决Header取值与类型不匹配问题 #

报错Invalid column type的核心原因是Header中id的值类型与数据库表INT_CHANNEL_MESSAGE的MESSAGE_ID、GROUP_KEY列类型不兼容，Spring无法自动完成类型转换。可以尝试以下方法：

1. 显式指定参数类型 #

在配置中添加parameter-types属性，按照SQL占位符的顺序，逐个指定参数对应的JDBC类型，确保和数据库列类型一致：

<int-jdbc:outbound-gateway
    update="Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION) 
    values(:headers[id],:headers[id],'1211','2','3','Test')"
    request-channel="objectToJsonChannelTMF639" 
    reply-channel="responseobjectToJsonChannelTMF639" 
    data-source="dataSource"
    parameter-types="VARCHAR,VARCHAR,DATE,INTEGER,INTEGER,VARCHAR"/>

注意：parameter-types的顺序必须和SQL中占位符的顺序完全对应，前两个对应Header的id，后续依次匹配其他列的类型。

2. 转换Header值类型 #

如果Header中的id是UUID、数字等非字符串类型，直接插入会导致类型不匹配。可以在发送消息前提前转换类型，或者在SpEL表达式中直接转换：

update="Insert into INT_CHANNEL_MESSAGE (...) values(:headers[id].toString(),:headers[id].toString(),'1211','2','3','Test')"

3. 验证Header存在性 #

先确认消息Header中确实存在id这个键，且值不为空。可以添加日志拦截器打印Header内容，排查键名是否拼写错误，:headers.id或:headers['id']的写法都是合法的，只要键存在即可。

二、插入Blob类型的Payload数据 #

要插入Blob类型的数据，需要在SQL中引用:payload，同时指定参数类型为BLOB，并且确保Payload本身是字节数组或可转换为字节流的对象。

配置示例 #

假设INT_CHANNEL_MESSAGE表有一个MESSAGE_CONTENT列是Blob类型，配置修改如下：

<int-jdbc:outbound-gateway
    update="Insert into INT_CHANNEL_MESSAGE (MESSAGE_ID, GROUP_KEY,CREATED_DATE,MESSAGE_PRIORITY,MESSAGE_SEQUENCE,REGION,MESSAGE_CONTENT) 
    values(:headers[id],:headers[id],'1211','2','3','Test',:payload)"
    request-channel="objectToJsonChannelTMF639" 
    reply-channel="responseobjectToJsonChannelTMF639" 
    data-source="dataSource"
    parameter-types="VARCHAR,VARCHAR,DATE,INTEGER,INTEGER,VARCHAR,BLOB"/>

额外处理：Payload为字符串的情况 #

如果Payload是JSON字符串等文本类型，需要先转换为字节数组，可以添加转换器完成这个步骤：

<!-- 将Payload转换为字节数组 -->
<int:transformer input-channel="objectToJsonChannelTMF639" output-channel="payloadToBytesChannel">
    <int:transformer expression="payload.toString().getBytes()"/>
</int:transformer>

<int:channel id="payloadToBytesChannel"/>

<!-- 修改outbound-gateway的请求通道为转换后的通道 -->
<int-jdbc:outbound-gateway
    update="Insert into INT_CHANNEL_MESSAGE (...) values(...,:payload)"
    request-channel="payloadToBytesChannel" 
    data-source="dataSource"
    parameter-types="VARCHAR,VARCHAR,DATE,INTEGER,INTEGER,VARCHAR,BLOB"/>

如果Payload是自定义对象，需要自定义转换器将对象序列化为字节数组（比如用Jackson将对象转为JSON字节流）。

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