Trouble converting between java.sql.Timestamp & java.time.Instant with JOOQ

Question

I'm having trouble converting between java.sql.Timestamp and java.time.Instant using JOOQ converters.

Here's a simplified version of the code I'm working with.

public class User {

    private static final Converter<Timestamp, Instant> MY_CONVERTER= Converter.of(
        Timestamp.class,
        Instant.class,
        t -> t == null ? null : t.toInstant(),
        i -> i == null ? null : Timestamp.from(i)
    )

    public static Table<?> table = DSL.table("user");

    public static Field<String> name = DSL.field(DSL.name(table.getName(), "name"), String.class);
    public static Field<Instant> name = DSL.field(DSL.name(table.getCreated(), "created"), SQLDataType.TIMESTAMP.asConvertedDataType(Converter.of(MY_CONVERTER)));    
}

private class UserDto {

    private String name;
    private Instant created;

    // getters, setters, etc.
}

public class UserWriter {

    // constructor with injected DefaultDSLContext etc..

    public void create(UserDto user) {

        dslContext.insertInto(User.table, User.firstName, User.lastName)
            .values(user.getName(), user.getCreated())
            .execute();
    }
}

public class UserReader {

    // constructor with injected DefaultDSLContext etc..

    public Result<Record> getAll() {
        return dslContext.select().from(User.table).fetch();
    }
}

public class UserService {

    // constructor with injected UserReader etc..

    public Collection<UserDto> getAll() {
        return userReader
                .getAll()
                .stream()
                .map(Users::from)
                .collect(Collectors.toList());
    }
}

public class Users {

    public static UserDto from(Record record) {
        UserDto user = new UserDto();
        user.setName(record.get(User.name));
        user.setCreated(record.get(User.created);
        return user;
    }
}

When I create a new User the converter is called and the insertion works fine. However, when I select the Users the converter isn't called and the record.get(User.created) call in the Users::from method returns a Timestamp (and therefore fails as UserDto.setCreated expects an Instant).

Any ideas?

Thanks!

Update: It seems that the issue is that (when I specify fields to select, the converter is called). So my question is if I want to retrieve all the fields AND perform the conversion, do I need to specify all the fields in select(à or is there another, less verbose, way ? — ChambreNoire
– ChambreNoire, Commented Dec 3, 2021 at 11:27

Lukas Eder · Accepted Answer · 2021-12-06 11:55:46Z

2

Why the converter isn't applied

From the way you phrased your question (you didn't post the exact SELECT statement that you've tried), I'm assuming you didn't pass all the column expressions explicitly. But then, how would jOOQ be able to find out what columns your table has? You declared some column expressions in some class, but that class isn't following any structure known to jOOQ. The only way to get jOOQ to fetch all known columns is to make them known to jOOQ, using code generation (see below).

You could, of course,let User extend the internal org.jooq.impl.TableImpl class and use internal API to register the Field values. But why do that manually, if you can generate this code?

Code generation

I'll repeat the main point of my previous question, which is: Please use the code generator. I've now written an entire article on why you should do this. Once jOOQ knows all of your meta data via code generation, you can just automatically select all columns like this:

UserRecord user = ctx
    .selectFrom(USER)
    .where(USER.ID.eq(...))
    .fetchOne();

Not just that, you can also configure your data types as INSTANT using a <forcedType>, so you don't need to worry about data type conversion every time.

I cannot stress this enough, and I'm frequently surprised how many projects try to use jOOQ without code generation, which removes so much of jOOQ's power. The main reason to not use code generation is if your schema is dynamic, but since you have that User class, it obviously isn't dynamic.

edited Dec 6, 2021 at 11:55

answered Dec 3, 2021 at 12:43

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ChambreNoire Over a year ago

Yeah, I'd arrived at this conclusion myself - although many thanks for taking the time to explain! I'm not too familiar with JOOQ (although I've been meaning to use it for a while) and this is an existing project that I'm currently not at liberty to complete re-wire (due to time constraints) however I will certainly look into this when I get some time.

Lukas Eder Over a year ago

@AnthonyWebster You don't have to rewrite everything in one go. You can migrate query by query, as you have to touch them anyway. Setting up code generation will create some extra overhead at first, but then, your work will improve a lot, and you'll be more efficient further down the line.

Lukas Eder Over a year ago

@AnthonyWebster: As this code generation thing is a frequent topic with stack overflow questions about problems that would not have happened with code generation, see e.g. this one, I have now written a more complete article about the many benefits of using the code generator

Collectives™ on Stack Overflow

Trouble converting between java.sql.Timestamp & java.time.Instant with JOOQ

1 Answer 1

Why the converter isn't applied

Code generation

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Why the converter isn't applied

Code generation

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related