Using Spring's JDBC support to get output parameters

Question

I'm using Spring's JDBC support to run SQL queries and updates on an Oracle database. I'd like to insert a row and then get the key that it was assigned (using an Oracle sequence). In normal JDBC code, I would include a RETURNING INTO clause and then register an output parameter (well described here)

However, I would like to just use Spring to handle all my JDBC work for me. For non-insert SQL statements, I'm currently using a MapSqlParameterSource object and registering all my input parameters. Can I also register an output parameter like this and have it returned to me? I looked over the Chapter 11 portion of the Spring docs, and I saw there was support for an output parameter if I'm using stored procedures, but I would like to avoid doing that if possible. Thanks.

Answer 1

I don't think the Spring JDBC support API provides explicit support for OUT parameters, so you may need to step back a bit and use the more general query API provided by JdbcTemplate:

Object execute(PreparedStatementCreator psc, PreparedStatementCallback action)

This lets you perform arbitrary JDBC operations within the scope of a Spring-managed connection and PrepatedStatement. The downside is that the handling and tidyup of things like ResultSets becomes your problem.

Answer 2

Check out this code - it may do what you want, not sure if Oracle supports this syntax though.

getReturnedGeneratedKeys() Spring JDBC Excample