Saving fractional seconds to TIME datatype in DB using JOOQ

user_1656501 0
Viewed 230

I need to store data from an external API to MariaDB. One of the fields comes in as java.time.LocalTime and is stored using the TIME type. It can contain fractions of seconds. I am using JOOQ for SQL building/execution.

The DB field is created using TIME(6) and the data is correctly listed there as 'hh:mm:ss.ssssss'. Whenever I save data from my application however, the fractional part gets zeroed out. Eg: 12:34:56.123456 -> 12:34:56.000000

I realize this is due to JOOQ using java.sql.Time internally and since it extends java.util.Date, part of the precision is lost. I am trying to find a way to work around this issue so that I could still store the times as they came in.

What I have tried:

  1. Using a custom org.jooq.Converter which converts between java.sql.Time and java.time.LocalTime but unlike the default org.jooq.impl.TimeToLocalTimeConverter, preserves the millisecond part of the time object. This allows me to save and retrieve values at millisecond precision (3 places after comma).

  2. From some resources on the internet it seemed that it should be possible to use LocalTime and LocalDateTime directly with newer JDBC drivers. In order to try that I attempted to use a org.jooq.Converter which converts from java.time.LocalTime to java.time.LocalTime, basically just letting objects through. I was hoping JOOQ would then just use this Converter and pass the LocalTime to the DB directly but unfortunately it does not work like this. The value still gets processed by the default implementation and the fractional part of the data is lost.

  3. In order to get past JOOQ default functionality, I tried to create a custom org.jooq.Binding which would use the incoming object directly. However when I tried to do that, using the JOOQ custom binding example as base, I was unable to implement necessary methods. Eg where the example has:

    public void get(BindingGetStatementContext<JsonElement> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

...I would need to do something like:

    public void get(BindingGetStatementContext<LocalTime> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getLocalTime(ctx.index()));
    }

But java.sql.CallableStatement has no such method. Am I misunderstanding what should be possible?

I know there is an open issue in JOOQ which will support for fractional seconds in the future but that seems to be still a long way off and I am looking for a solution which would work now.

jooq mariadb jdbc time java
1 Answers

You cannot solve this problem using a Converter, as jOOQ will then internally still proceed to use the JDBC type java.sql.Time behind the scenes. You need to implement your own Binding. In case your JDBC driver supports JSR 310 types, the method you had trouble with would have to look like this:

public void get(BindingGetStatementContext<LocalTime> ctx) throws SQLException {
    ctx.value(ctx.statement().getObject(ctx.index(), LocalTime.class));
}

Notice, you should not use the Converter in this case, because you're already making sure that the Binding<T, U>'s <U> type (where T = java.sql.Time and U = java.time.LocalTime) is correctly fetched from JDBC.

user_5217990
Related