LocalDateTime and SQL Server JDBC 4.2 driver

7
votes

I'm trying to use new java.time classes with most recent version of Sql Server JDBC driver. As I read it should just work with methods: PreparedStatement.setObject() and ResultSet.getObject().

So I created sample code, and can't get it work with ResultSets. I don't know what I'm doing wrong here.

Connection connection = DriverManager.getConnection(connectionString);
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM myTable WHERE ? BETWEEN date_from AND date_to");
preparedStatement.setObject(1, LocalDateTime.now());   // That works

ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
    Object o = resultSet.getObject("date_from"); 
    o.getClass() returns java.sql.Timestamp

    LocalDateTime dateTime = resultSet.getObject("date_from", LocalDateTime.class);
}

This throws an exception:

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion to class java.time.LocalDateTime is unsupported.

Driver version: mssql-jdbc-6.5.4.jre8-preview.jar

SQL Server version: 2016

https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-4-2-compliance-for-the-jdbc-driver?view=sql-server-2017

How to interpret this sentence in table at bottom:

New Java classes in Java 8: LocalDate/LocalTime/LocalDateTime, OffsetTime/OffsetDateTime

New JDBC types: TIME_WITH_TIMEZONE, TIMESTAMP_WITH_TIMEZONE, REF_CURSOR

REF_CURSOR is not supported in SQL Server. Driver throws a SQLFeatureNotSupportedException exception if this type is used. The driver supports all other new Java and JDBC type mappings as specified in the JDBC 4.2 specification.

4
javasql-serverjdbcmssql-jdbc
In the statement Object o = resultSet.getObject("date_from"); , what is the type of table-column date_from? – prasad_
From exception, "date_from" cannot be converted to LocalDateTime. Can you print out what you did you get from Object o = resultSet.getObject("date_from");? You did not tell us what date_from is as we need to have a look of the format. – Yihang Wang
Database type of date_from is 'datetime'. Tried with these classes, but still get an exception: LocalDateTime, LocalDate, LocalTime, OffsetDateTime, OffsetTime – Krzysztof
Check this answer and this answer to learn how to use java.time API with JDBC. – Arvind Kumar Avinash

4 Answers

9
votes

I don't know what I'm doing wrong here.

You're not doing anything wrong. You have encountered a deficiency in Microsoft's JDBC driver for SQL Server prior to version 7.1.0, discussed here.

If you are using mssql-jdbc version 7.1.0 or later then you can use getObject(x, LocalDateTime.class) as expected.

For mssql-jdbc versions prior to 7.1.0, as others have suggested, you'll need to retrieve a Timestamp and convert it to a LocalDateTime. However, be aware that the simplistic solution ...

LocalDateTime dateTime = resultSet.getTimestamp("date_from").toLocalDateTime()

... will corrupt certain date/time values if the default time zone for the JVM observes Daylight Saving Time, a.k.a. "Summer Time". For example,

// time zone with Daylight Time
TimeZone.setDefault(TimeZone.getTimeZone("America/Edmonton"));

// test environment
Statement st = conn.createStatement();
st.execute("CREATE TABLE #tmp (id INT PRIMARY KEY, dt2 DATETIME2)");
st.execute("INSERT INTO #tmp (id, dt2) VALUES (1, '2018-03-11 02:00:00')");
ResultSet rs = st.executeQuery("SELECT dt2 FROM #tmp WHERE id=1");
rs.next();

// test code
LocalDateTime x = rs.getTimestamp("dt2").toLocalDateTime();  // bad

System.out.println(x.toString());

will print "2018-03-11T03:00". Note that the time is "03:00", not "02:00".

Instead, you'll need to retrieve the Timestamp as UTC and then convert it into a LocalDateTime for UTC, thus removing the time zone component

// time zone with Daylight Time
TimeZone.setDefault(TimeZone.getTimeZone("America/Edmonton"));

// test environment
Statement st = conn.createStatement();
st.execute("CREATE TABLE #tmp (id INT PRIMARY KEY, dt2 DATETIME2)");
st.execute("INSERT INTO #tmp (id, dt2) VALUES (1, '2018-03-11 02:00:00')");
ResultSet rs = st.executeQuery("SELECT dt2 FROM #tmp WHERE id=1");
rs.next();

// test code
Timestamp ts = getTimestamp("dt2", Calendar.getInstance(TimeZone.getTimeZone("UTC")));
LocalDateTime x = LocalDateTime.ofInstant(ts.toInstant(), ZoneId.of("UTC"));  // good

System.out.println(x.toString());

which prints "2018-03-11T02:00".

2
votes

This is because the Microsoft SQL Server JDBC driver implementation of resultSet.getObject(...) cannot auto convert from java.sql.Timestamp to LocalDateTime.

As a workaround you can get the value as java.sql.Timestamp and then convert java.sql.Timestamp to LocalDateTime by using: java.sql.Timestamp.toLocalDateTime()

LocalDateTime dateTime = resultSet.getTimestamp("date_from").toLocalDateTime()
0
votes

It looks like the mssql-jdbc driver doesn't fully implement the java.time support specified in JDBC 4.2. It doesn't support it for ResultSet.getObject, but it does support it for PreparedStatement.setObject.

The workaround, as suggested by the answer of Loc Le, is to retrieve as Timestamp and convert that to a LocalDateTime.

0
votes

If you are stuck and want to try the "old way" of doing it:

Here is a simples example of equivalent conversions. Test yourself.

@Test
public void testingConversionTimestampAndLocalDateTime(){
    Timestamp initialTimestamp = new Timestamp(System.currentTimeMillis());

    LocalDateTime localDateTime = LocalDateTime.ofInstant(initialTimestamp.toInstant(), ZoneId.of("America/Sao_Paulo"));

    ZoneOffset zoneOffset = ZoneId.of("America/Sao_Paulo").getRules().getOffset(localDateTime);
    Timestamp timestamp = Timestamp.from(localDateTime.toInstant(zoneOffset));

    Timestamp timestamp1 = Timestamp.valueOf(localDateTime);

    assertEquals(initialTimestamp, timestamp);
    assertEquals(initialTimestamp, timestamp1);
}