0

I am aware that JDBC 4.2 drivers support reading and writing java.time objects. I tried storing and retrieving an Instant object using MariaDB 2.6 driver. I am using Apache Commons DB Utils to run the queries.

I can store the Instants just fine, but not able to retrieve them. Is this a bug with the driver or am I doing something wrong?

Storing & reading code

String sql = "insert into TimeTest(time) values(?)";
Instant ts = Instant.from(Instant.now());
sqlQueryRunner.update(dbConnection,sql, ts);

            ResultSetHandler<Instant[]> h = new ResultSetHandler<Instant[]>() {
    public Instant[] handle(ResultSet rs) throws SQLException {
        if (!rs.next()) {
            return null;
        }

        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();
        Instant[] result = new Instant[cols];

        for (int i = 0; i < cols; i++) {
            **//Does not work**
            //result[i] = rs.getObject(i+1, Instant.class);

            **//works fine**
            result[i] = rs.getObject(i+1, Timestamp.class).toInstant();
        }

        return result;
    }
};

The TimeTest is a table having a datetime field time:

CREATE TABLE `TimeTest` (
  `time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Error when reading Instant

Caused by: java.sql.SQLException: Type class 'java.time.Instant' is not supported
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
nikel
  • 3,402
  • 11
  • 45
  • 71
  • Can you share your full stacktrace please? – akortex Aug 13 '21 at 08:46
  • *`if(!rs.next()) { return null;}`* When you call `next` again, you're going to skip the first row with that. Get rid of that and find out with `getObject().getClass().getName()` what *has* been stored. You want `Instant[] result = null;if(rs.next()) { ...}` – g00se Aug 13 '21 at 08:54
  • 2
    JDBC **does not** define support for `java.time.Instant`, it only defines support for `java.time.LocalDate`, `java.time.LocalTime`, `java.time.LocalDateTime`, `java.time.OffsetTime` and `java.time.OffsetDateTime`. Anything else is non-standard. – Mark Rotteveel Aug 13 '21 at 10:08
  • 1
    I have removed your secondary questions, as that would make your question unfocussed and thus closeworthy. However, suffice to say that the JDBC Expert Group decided that moving forward there won't be anymore new type-specific getXXX/setXXX methods in the JDBC API, and getObject/setObject is sufficient. – Mark Rotteveel Aug 13 '21 at 10:10
  • I haven't looked at the code, but maybe it handles `setObject` by calling the `toString()` on unsupported types, and then it works 'by accident' for `Instant`. – Mark Rotteveel Aug 13 '21 at 10:14
  • @MarkRotteveel : You are right , table B-4 in jdbc spec : https://download.oracle.com/otn-pub/jcp/jdbc-4_2-mrel2-spec/jdbc4.2-fr-spec.pdf?AuthParam=1629289162_e006c0567cc02d56b7ee6a420cbf3c22 does not mention Instant. if you can answer , i will accept. – nikel Aug 18 '21 at 12:20
  • I intentionally did not post an answer, as I don't know the internals of the MariaDB Connector/J (nor have the intent or time to find out). For all I know, the MariaDB Connector/J does want to provide non-standard support for `java.time.Instant`, and this is actually a bug. I only intended to point out that your assumption that JDBC defines support for all `java.time` types is wrong, and thus your expectations might be wrong. – Mark Rotteveel Aug 18 '21 at 12:30
  • That answers the query :) , i will post it as an answer and accept then – nikel Aug 18 '21 at 12:45

0 Answers0