Issue using Expression language with jdbc:outbound-channel-adapter

[Amol Nayak]

I am having a simple table structure that stores the message id and the message payload in the table and both are of type varchar.

i am having the following in the configuration

<jdbc:outbound-channel-adapter data-source="dataSource" channel="outboundJdbcChannelOne"
	query="insert into MESSAGES (MESSAGE_ID,PAYLOAD) values (:headers[id].toString(),:payload)"
	sql-parameter-source-factory="spelSource"/>
									
	<bean id="spelSource" class="org.springframework.integration.jdbc.ExpressionEvaluatingSqlParameterSourceFactory"/>

However, the insersion fails as the expression headers[id].toString() fails.
Reason being, when the SQL is provided to NamedParameterJdbcTemplate, the named parameter is evaluated to
headers[id].toString (without the () ) by the NamedParameterUtils as "(" and ")" is a PARAMETER_SEPARATOR and this expression fails to yield a result.

Am i mising something or on a wrong track, how do i pass spring expression parameters to the query where i need to invoke an adhoc method (not a usual getter, something like toString() for instance) on the object whose return value is to be used to insert or update a record?

[Amol Nayak]

Can anybody help me on this please?

[Gary Russell]

Try this...

<jdbc:outbound-channel-adapter data-source="dataSource" channel="outboundJdbcChannelOne"
	query="insert into MESSAGES (MESSAGE_ID,PAYLOAD) values (:id,:payload)"
	sql-parameter-source-factory="spelSource"/>
		
<bean id="spelSource" class="org.springframework.integration.jdbc.ExpressionEvaluatingSqlParameterSourceFactory">
	<property name="parameterExpressions">
		<map>
			<entry key="id" value="headers['id'].toString()"/>
			<entry key="payload" value="payload"/>
		</map>
	</property>
</bean>

The parameter names in the SQL map to expressions in the parameter source factory.

[Amol Nayak]

Thanks Gary . Yup that's how i managed to do it this morning and it worked fine.

[ghillert]

Hi Amol,

Just FYI - I created a Jira to document the usage of SpEL expressions with JDBC Adapters in the Reference Manual.

https://jira.springsource.org/browse/INT-2244

In order to improve the situation even further, please monitor: https://jira.springsource.org/browse/INTSAMPLES-33

In the next day or so, I will add a new JDBC Example to the Spring Integration Samples project at:

https://github.com/SpringSource/spri...ration-samples that will in fact use SpEL Expressions.

Thank you very much for raising this question!

Cheers,

[Amol Nayak]

Thanks Gunnar
I'll be more than happy to contribute the sample i am working on. Let me do that tonight once i get back home, i'll put in some comments and make the code more readable and attach it here.

[Amol Nayak]

Hello Gunnar,
I have created a jdbc project in the integration samples and added a sample outbound-channel-adapter to it which is using ExpressionEvaluatingSqlParameterSourceFactory.
You can find the project here, https://github.com/amolnayak311/spring-integration-samples.

Thanks
Amol.