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!

CodePudding user response：

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. 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.