Call an Oracle Function from Spring with CallableStatement

Posted by in Software, Tutorial

Following the previous post, I wondered if there’s a more elegant way to call a function. It turns out there is and it’s based on CallableStatement:

The CallableStatementCreator sets up the statement and the CallableStatementCallback executes the statement and returns the value up to the execute() call.

Having a function:

We can build a query string like:

The CallableStatementCreator instance is then:

This states the first parameter is a result, the second and 3rd parameter are function parameters.

NOTE: Be careful because you can specify indices or keywords for parameter positioning, but you’re not allowed to mix the two!

The CallableStatementCallback is:

This will execute the statement and will return the result (first parameter of the statement).

The jdbcOperations.execute() function will return the result from CallableStatementCallback‘s doInCallableStatement().

The full java code looks like this:

This is much more powerful. You can customise all function’s parameters the same as with a classic SQL query.

HTH,


A little experiment: If you find this post and ad below useful, please check the ad out :-)