PreparedStatement#getParameterMetaData() calls may trigger unwanted side effects [SPR-11386]

[spring-projects-issues]

Philippe Marschall opened SPR-11386 and commented

When upgrading from Spring 3.2.0 to Spring 4.0.0 we suddenly get the following exception.

Caused by: java.sql.SQLException: Connection is not associated with a managed connection.org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6@47538759
	at org.jboss.jca.adapters.jdbc.WrappedConnection.lock(WrappedConnection.java:154)
	at org.jboss.jca.adapters.jdbc.WrappedStatement.lock(WrappedStatement.java:114)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setNull(WrappedPreparedStatement.java:539)
	at org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:285)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:214)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:148)
	at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:66)
	at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:47)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:699)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:642)

The tracked the issue down to:

1. StatementCreatorUtils now calls PreparedStatement#getParameterMetaData() for null bind parameters
2. PreparedStatement#getParameterMetaData() throws an java.lang.ArrayIndexOutOfBoundsException if the SQL query contains a comment
3. the JBoss connection pool (IronJacamar) disassociates the PreparedStatement with the connection
4. StatementCreatorUtils swallows the exception and goes on to call PreparedStatement#setNull()
5. the JBoss connection pool throws an exception because the PreparedStatement is no longer valid

The issue can be reproduced with the following code.

jdbcTemplate.queryForList("SELECT /* t */ 1 FROM dual where dummy = ?", Integer.class, new Object[] {null});

The quick fix we currently use is add "Oracle JDBC driver" to StatementCreatorUtils#driversWithNoSupportForGetParameterTypes through reflection but this is a hack at best.

Not using comments is not an option for us as we tag most of our queries with comments for monitoring in Enterprise Manager Cloud Control.

Not using null is not always easy as sometimes we have dynamic SQL where it's just easier to use NVL or IS NULL instead of several different queries.

We're aware that this is actually an Oracle bug and not a Spring bug but we need a better work around until we get a fix from Oracle. We're aware that JBoss actually certified the 12c driver.

Environment:

• Java 7u45
• ojdbc7 12.1.0.1
• JBoss EAP 6.1.0

---

Affects: 3.2.6, 4.0 GA

Issue Links:

• performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils [SPR-11100] #15726 performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils
• StandardEnvironment's system environment access produces warning with stacktrace on WebSphere [SPR-11297] #15921 StandardEnvironment's system environment access produces warning with stacktrace on WebSphere
• SpringProperties: the ClassLoader might be null, if class is loaded by the bootstrap class loader [SPR-11721] #16343 SpringProperties: the ClassLoader might be null, if class is loaded by the bootstrap class loader
• Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls) [SPR-13825] #18398 Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls)

Referenced from: commits 8a6b095, 036bd79, 60b24cf, 6634c19, 60c1905

Backported to: 3.2.8

[spring-projects-issues]

Juergen Hoeller commented

So I assume it worked for you before with the default setNull(index, Types.NULL) calls in Spring 3.2.0?

Does it also work after you initially got that exception, that is, once StatementCreatorUtils automatically added the entry to driversWithNoSupportForGetParameterTypes?

Juergen

[spring-projects-issues]

Juergen Hoeller commented

FYI, I'm considering a property along the lines of #15921, indicating that Spring should ignore getParameterType completely. According to SpringProperties rules (see #15921), this could be set as a system property, as an entry in a local spring.properties file, or programmatically on the SpringProperties class.

Would something like this work for you?

Juergen

[spring-projects-issues]

Philippe Marschall commented

StatementCreatorUtils does not add it to driversWithNoSupportForGetParameterTypes because on line 257 ps.getConnection().getMetaData() throws an exception was well. So it fails again the second time.

We actually got a patch from Oracle by now that throws a SQLFeatureNotSupportedException instead of a ArrayIndexOutOfBoundsException. That patch is working for us. So this is considerably lower priority for us now. We still have to deploy a patched Oracle driver but since they are not in Central anyway that's doable.

A property would work but we don't really like the approach. We feel it's orthogonal to traditional spring configuration and feel the downsides of such approaches are well proved by Java SE. In general we would prefer a property on JdbcTemplate. We take what we can get though.

[spring-projects-issues]

Juergen Hoeller commented

I've introduced a "spring.jdbc.getParameterType.ignore" property, making Spring ignore ParameterMetaData's getParameterType completely when set to "true". This basically restores JDBC 2.0 style behavior as known from previous versions of Spring - and may also serve as a performance optimization if getParameterType can be skipped in any case since regular setNull calls are known to work.

This will be available in the upcoming 4.0.2 and 3.2.8 snapshots. Please give it a try - see http://projects.spring.io/spring-framework/ for Maven coordinates...

Juergen

[spring-projects-issues]

Juergen Hoeller commented

Ah, seems we commented at the same time there...

Some background from our side: After some research, there are recent reports for similar problems on MySQL and others, at least for specific scenarios. And we generally had to digest quite a few JIRA issues related to that getParameterType call over the last two years. So having a way to generally ignore getParameterType calls still seems worthwhile.

Point taken about orthogonal configuration. However, fundamentally, this is a system-wide setting that arguably doesn't really belong on JdbcTemplate either; if at bean level, it'd rather have to be on the DataSource itself. Anyway, since we have similar "spring.getenv.ignore" and "spring.beaninfo.ignore" properties already, designed for a similar purpose - either avoiding exceptions or serving as a performance optimization - I don't mind a third property in that category here. It's only meant as a last resort anyway.

Juergen

[spring-projects-issues]

Juergen Hoeller commented

As a side note, at JdbcTemplate level, the specific SQL types for given parameter values can be specified through overloaded methods. If we have such a specific SQL type provided, we'll never check getParameterType in the first place, and won't do any fallback setObject/setNull considerations either.

Juergen

[spring-projects-issues]

Juergen Hoeller commented

One more note: Those properties are actually more framework-level than system-level, since they can be declared in a spring.properties file right next to the Spring jars (or more specifically, in the ClassLoader that loads the Spring jars). They basically fine-tune the framework's default behavior, for any instance and any use of the framework in the current system. This also avoids potential mistakes in framework use, i.e. accidental omission of SQL types or accidental omission of a specific setting on a local JdbcTemplate instance.

Juergen

[spring-projects-issues]

Philippe Marschall commented

I tested with a 4.0.2 SNAPSHOT and can confirm it's fixed with the property.