JDBC result set retrieve LocalDateTime

Question

I run a simple query to retrieve a row from a MySQL database. I get ResultSet and I need to retrieve a LocalDateTime object from it. My DB table.

CREATE TABLE `some_entity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) NOT NULL,
  `text` varchar(255) DEFAULT NULL,
  `created_date_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

I need to retrieve some entity by id.

String SELECT = "SELECT ID, TITLE, TEXT, CREATED_DATE_TIME FROM some_entity WHERE some_entity.id = ?";
PreparedStatement selectPreparedStatement = connection.prepareStatement(SELECT);
try {
    selectPreparedStatement.setLong(1, id);
    ResultSet resultSet = selectPreparedStatement.executeQuery();
    if (resultSet.next()) {
        Long foundId = resultSet.getLong(1);
        String title = resultSet.getString(2);
        String text = resultSet.getString(3);
        LocalDateTime createdDateTime = null;// How do I retrieve it???
    }
} catch (SQLException e) {
    throw new RuntimeException("Failed to retrieve some entity by id.", e);
}

this might help [Parse DateTime to LocalDateTime from resulset](https://stackoverflow.com/questions/29773390/getting-the-date-from-a-resultset-for-use-with-java-time-classes) — Ramon jansen gomez, Aug 01 '18 at 12:03
Yes, I see. 1) Retrieve timestamp from the resultSet, 2) convert it into LocalDateTime... — Yan Khonski, Aug 01 '18 at 12:13
If the JDBC driver is reasonably current and has been updated to work with Java 8 then you should be able to use `resultSet.getObject(4, LocalDateTime.class)` — Gord Thompson, Aug 01 '18 at 13:13

Tomasz Linkowski · Accepted Answer · 2018-08-01T13:22:14.400

40

Try retrieving this as java.sql.Timestamp and then converting to LocalDateTime using Timestamp.toLocalDateTime:

LocalDateTime createdDateTime = resultSet.getTimestamp(4).toLocalDateTime()

EDIT: As Gord Thompson pointed out in his comment, there's an even better solution when working with a recent enough JDBC driver:

resultSet.getObject(4, LocalDateTime.class)

This skips creating a redundant java.sql.Timestamp instance.

edited Aug 01 '18 at 13:22

answered Aug 01 '18 at 12:05

Tomasz Linkowski

4,386
23
38

Even if I use DateTime for MySQL, I should call `getTimestamp()`? Not `getDate()`? – Yan Khonski Aug 01 '18 at 12:06
1

[`java.sql.Date`](https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html) has no time component: "To conform with the definition of SQL `DATE`, the millisecond values wrapped by a `java.sql.Date` instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated." I do not guaratee that `java.sql.Timestamp` will work, though, but it seems to be the only type that represents date-time in `java.sql` package. – Tomasz Linkowski Aug 01 '18 at 12:10
7

+1 for the edit. It also avoids the problem of Timestamp potentially corrupting certain date/time values, e.g., `2018-03-11 02:00:00` (2 AM) in `America/Toronto` will magically be changed to 3 AM. – Gord Thompson Aug 01 '18 at 13:31
`resultSet.getObject(4, LocalDateTime.class)` using postgres and latest jdbc lib didn't work for me. `resultSet.getTimestamp(4).toLocalDateTime()` did work – Tony Murphy Jan 27 '20 at 20:52
2

I tried this technique recently. However, even though the above code should work, there's a MySQL driver bug that incorrectly applies a timezone offset to the `LocalDateTime` value. This bug is still open as of MySQL Connector Java version 8.0.19. Reference: https://bugs.mysql.com/bug.php?id=93444 – rdguam May 18 '20 at 00:04

JDBC result set retrieve LocalDateTime

1 Answers1