Spring JDBC: error while using Oracle

Question

We have a Spring application which uses NamedParameterJdbcTemplate to persist messages to an Oracle database. The sql is a simple insert. The insert works and the database is updated, yet neverthless the following exception is thrown.

04:02:58.276 [org.springframework.jms.listener.DefaultMessageListenerContainer#0-1] DEBUG o.s.jdbc.core.StatementCreatorUtils - JDBC 3.0 getParameterType call not supported
java.sql.SQLException: Unsupported feature
    at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMetaData.java:166) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
    at org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:231) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:213) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:63) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.access$000(NamedParameterBatchUpdateUtils.java:32) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils$1.setValues(NamedParameterBatchUpdateUtils.java:47) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:890) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:890) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:324) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:319) [spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]

I wonder if anyone can help in identifying the issue, thanks.

Answer 1

When you set null in a column the setNull function needs the database ParameterType. The oracle driver does not support the feature ps.getParameterMetaData().getParameterType(paramIndex) and an exception is thrown and the debug message in the log: "JDBC 3.0 getParameterType call not supported" Then the fallback method is used.

One problem with this implementation of spring-jdbc (older versions did not do this) is that catch exception is very slow and for every column you set a null value this exception is thrown and catched. The result is a query from milleseconds went tot 100's of milliseconds...

I have no solution yet... https://jira.springsource.org/browse/SPR-10385

https://forums.oracle.com/thread/587880

Edit: when you use oracle driver ojdbc7.jar then parameterMetaData.getParameterType is implemented

Edit2: Let's vote for the improvement ;-) https://jira.springsource.org/browse/SPR-11100

Update: fixed in 3.2.6, 4.0 GA (SPR-11100)