Saving fractional seconds to TIME datatype in DB using JOOQ

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.

Answer:1

I have an application that targets API Level 19+. I need to check if headset is connected (either of wired or bluetooth). I see that AudioManager.isWiredHeadsetOn() is deprecated starting from API ...

I'm sure that this question has been addressed in many best practices books, but still... Most of the times I see examples of wrong usage of custom exceptions, therefore I was wondering what would be ...

I've seen some code in a project recently where some fields in a couple classes have been using the default access modifier without good reason to. It almost looks like a case of "oops, forgot to ...

I have a problem while selecting from a table containg data in utf-8 format in MySQL using java, in the WHERE clause I need to compare if a column value equals to a java String but they don't match ...