Spring Convert JSR310 to SQL type if possible

Some driver can not infer the SQL type from JSR310 type in setObject method, we should convert JSR310 type to SQL type via corresponding valueOf method.

postgresql-42.2.9.jar:

Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.LocalDateTime. Use setObject() with an explicit Types value to specify the type to use.
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:971) ~[postgresql-42.2.9.jar:42.2.9.jre7]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.1.jar:?]
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:415) ~[classes/:5.2.2.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:233) ~[classes/:5.2.2.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:147) ~[classes/:5.2.2.RELEASE]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:285) ~[spring-jdbc-5.2.2.jar:5.2.2.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate$2.setValues(NamedParameterJdbcTemplate.java:372) ~[spring-jdbc-5.2.2.jar:5.2.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:944) ~[spring-jdbc-5.2.2.jar:5.2.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.2.jar:5.2.2.RELEASE]

Comment From: quaff

Actually postgresql-42.2.9.jar works fine but postgresql-42.2.9.jre7.jar will throw exception, The point is lots of legacy even some latest driver doesn't support JSR310.

Comment From: quaff

lastest DB2 jcc-11.5.0.0.jar

Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.26.14
    at com.ibm.db2.jcc.am.b7.a(b7.java:802) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.b7.a(b7.java:66) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.b7.a(b7.java:140) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k4.b(k4.java:2471) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k4.c(k4.java:2452) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.t4.ab.n(ab.java:914) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.t4.ab.a(ab.java:120) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.t4.p.a(p.java:50) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.t4.aw.b(aw.java:220) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.bm(k5.java:3599) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.a(k5.java:4644) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.b(k5.java:4182) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.a(k5.java:4827) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.b(k5.java:4182) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.be(k5.java:827) ~[jcc-11.5.0.0.jar:?]
    at com.ibm.db2.jcc.am.k5.executeUpdate(k5.java:801) ~[jcc-11.5.0.0.jar:?]

Comment From: marschall

I have issues with this change: - LocalTime has nanosecond resolution but java.sql.Time only has millisecond resolution -> we silently truncate data - java.sql.Timestamp is bound to the JVM time zone, it can therefore not represent values that fall into a DST offset transition. LocalDateTime does not have these issues -> we silently mutate data

The only way to properly support JSR-310 is with native driver support, I created a repository to demonstrate these issues https://github.com/marschall/state-of-jpa-310

Comment From: jhoeller

Sam and I have been discussing the same concerns. From my perspective, it is indeed the driver vendor who has to step up here in case of any remaining gaps, in particular since JDBC 4.2 requires full support for those JSR-310 types: https://jcp.org/aboutJava/communityprocess/maintenance/jsr221/JDBC4.2MR-January2014.pdf