Spring JDBC does not recognize LocalDate and LocalDateTime in javaType to sqlType Mapping

[DhavalShewale]

In class, StatementCreatorUtils, spring jdbc does not recognize LocalDate and LocalDateTime in javaTypeToSqlTypeMap and as a result they get categorized as UNKOWN_TYPE.

LocalDate and LocalDateTime have been available for years and we should try to include them in the framework.

[jhoeller]

Good catch, those should have a standard SQL type mapping (even if this mapping mechanism is only really used in our BeanPropertySqlParameterSource). I've added the following, to be committed along with a few other 5.3.x backports:

		javaTypeToSqlTypeMap.put(LocalDate.class, Types.DATE);
		javaTypeToSqlTypeMap.put(LocalTime.class, Types.TIME);
		javaTypeToSqlTypeMap.put(LocalDateTime.class, Types.TIMESTAMP);

[slatake-swi]

First of all I would like to thank the spring framework team for doing there great work & making us available newer version and maintaining the older releases as well. I am not sure, whether I am posting my query on correct page or not, apology for this.
My query is related to converting Java types to SQL types & it's related to date column for Oracle DB so thought to ask here.

Let's say, I have Date column in Oracle database table so the datatype is Date & that column has the index created. When ever we hit the database by passing the java.sql.Timestamp value in parameterized statement then that Date column is getting converted into Timestamp and which makes the indexes invalid for that Date column so this hits the performance issue. So as the workaround solution we have customized StatementCreatorUtils class & did the conversion from java.sql.Timestamp to oracle.sql.DATE if the DB type is oracle.

So can anybody please reply on this whether this is the expected behavior or am I missing something here. Reply from the experts will really help me.

I could see same topic had been discussed in past on stack overflow, adding the links here for reference.
https://stackoverflow.com/questions/6612679/non-negligible-execution-plan-difference-with-oracle-when-using-jdbc-timestamp-o
https://stackoverflow.com/questions/1945603/why-is-oracle-so-slow-when-i-pass-a-java-sql-timestamp-for-a-date-column/6620643
Thank you so much in advance.... :)

[PhilippeHaution]

In the same StatementCreatorUtils class, there is a private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, @nullable String typeName, @nullable Integer scale, Object inValue) method which performs for instance:

		else if (sqlType == Types.DATE) {
			if (inValue instanceof java.util.Date) {
				if (inValue instanceof java.sql.Date) {
					ps.setDate(paramIndex, (java.sql.Date) inValue);
				}
				else {
					ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
				}
			}
			else if (inValue instanceof Calendar) {
				Calendar cal = (Calendar) inValue;
				ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
			}
			else {
				ps.setObject(paramIndex, inValue, Types.DATE);
			}
		}

So if we pass a LocalDate inValue, we actually leave it up to the driver to manage it with the ps.setObject() call. Should we not have another check of the LocalDate type that converts it into a java.sl.Date and invokes ps.setDate() instead?

			else if (inValue instanceof LocalDate) {
				LocalDate dt = (LocalDate) inValue;
				ps.setDate(paramIndex, java.sql.Date.valueOf(dt));
			}