JdbcTemplate should support JDBC 4.1 getObject(columnIndex, type) [SPR-11600]

[spring-projects-issues]

Philippe Marschall opened SPR-11600 and commented

JDBC 4.2 defines the following type mappings

ANSI SQL	Java SE 8
DATE	LocalDate
TIME	LocalTime
TIMESTAMP	LocalDateTime
TIME WITH TIMEZONE	OffsetTime
TIMESTAMP WITH TIMEZONE	OffsetDateTime

with can be used through ResultSet#getObject(int, Class), ResultSet#getObject(String, Class),PreparedStatement#setObject(int, Object)

So I would expect the following to work

jdbcTemplate.queryForObject("SELECT DATE_COLUMN FROM JAVA_TIME", LocalDate.class);

but it doesn't, it fails with:

org.springframework.dao.TypeMismatchDataAccessException: Type mismatch affecting row number 0 and column type 'DATE': Value [1988-12-25] is of type [java.sql.Date] and cannot be converted to required type [java.time.LocalDate]

Instead I have to do

jdbcTemplate.queryForObject("SELECT DATE_COLUMN FROM JAVA_TIME",
        (rs, rowNum) -> rs.getObject(1, LocalDate.class));

The issue seems to be that JdbcUtils.getResultSetValue(ResultSet, int, Class<?>) calls JdbcUtils.getResultSetValue(ResultSet, int) without the required type. I don't know if this an oversight or intentional.

---

Affects: 4.0.2

Issue Links:

• 'Invalid column type' exception generated when writing a Boolean data member to Oracle database [SPR-14581] #19150 'Invalid column type' exception generated when writing a Boolean data member to Oracle database
• Java boolean is not handled correctly when used with Oracle JDBC driver [SPR-14116] #18688 Java boolean is not handled correctly when used with Oracle JDBC driver
• Can't insert into nvarchar2 using SimpleJdbcInsert whereas it works with SimpleJdbcTemplate [SPR-8571] #13215 Can't insert into nvarchar2 using SimpleJdbcInsert whereas it works with SimpleJdbcTemplate

Referenced from: commits 3fef358, 3767938, c9432ce

[spring-projects-issues]

Juergen Hoeller commented

That JdbcUtils code doesn't take JDK 7's JDBC 4.1 into account yet, which is where getObject(columnIndex, type) showed up for the first time. We'll make sure to revise that code properly, using the new getObject variant when working with a JDBC 4.1+ driver (which unfortunately isn't trivial since common drivers are known to incompletely support the latest JDBC editions).

Juergen

[spring-projects-issues]

Juergen Hoeller commented

JdbcUtils uses JDBC 4.1 getObject(int, Class) for unknown ResultSet value types on JDK 7+ now, just falling back to a regular getObject(int) call as a last resort.

I've also applied a general JDBC 3.0+ baseline upgrade, removing defensive measures such as catch blocks with pre-JDBC 3.0 assumptions.

Juergen

[spring-projects-issues]

Philippe Marschall commented

Thank you

[spring-projects-issues]

Pavel Alexeev commented

Does it already supported?

In spring-jdbc 4.3.2.RELEASE i still got

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM lu_production_calendar WHERE  specified_date >= ? AND  specified_date <= ? ORDER BY specified_date ASC LIMIT ?]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.OffsetDateTime. Use setObject() with an explicit Types value to specify the type to use.
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:711) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:761) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
	at ru.rlh.egais.portal.backend.jdbc.EgaisNamedParameterJdbcTemplateImpl.query(EgaisNamedParameterJdbcTemplateImpl.java:74) ~[core-@[email protected]:na]
	at ru.rlh.egais.portal.backend.dao.impl.lookup.ProductionCalendarDaoImpl.search(ProductionCalendarDaoImpl.java:54) ~[core-@[email protected]:na]
	at ru.rlh.egais.portal.backend.service.lookup.ProductionCalendarService.search(ProductionCalendarService.java:26) ~[core-@[email protected]:na]

According to source: https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/core/StatementCreatorUtils.java#L358 it known how to handle java.sql.Types.TIMESTAMP (java.util.Date, java.sql.Timestamp, Calendar) but not Types.TIMESTAMP_WITH_TIMEZONE (OffsetDateTime)?

[spring-projects-issues]

Philippe Marschall commented

Yes it does, but your driver might not (yet). For Postgres you need version 9.4.1208 or later. You may have to set spring.jdbc.getParameterType.ignore to true in spring.properties.

[spring-projects-issues]

Pavel Alexeev commented

Sure I use 9.4.1209 JDBC 42.

According to http://static.javadoc.io/org.springframework/spring-jdbc/3.2.12.RELEASE/org/springframework/jdbc/core/StatementCreatorUtils.html#IGNORE_GETPARAMETERTYPE_PROPERTY_NAME it is not case. Exception happened on setValue.