JDBC Timestamp to Java 8 OffsetDateTime

[Bruno Leite]

Hi,

I'm trying to use Projections.bean() to convert the result from a simple select query to a DTO (SomeObject class) but I'm getting the following error when dealing with Java 8 OffsetDateTime properties:

java.sql.Timestamp is not compatible with java.time.OffsetDateTime

The SomeObject class looks like this (other properties omitted for the sake of clarity):

public class SomeObject {

    private String id;
    private OffsetDateTime expiresOn;

    // getters and setters omitted
}

Generating the Q classes directly from JDBC metadata results in a QSomeObject class containing the expiresOn property as Timestamp (as expected):

public class QSomeObject extends com.querydsl.sql.RelationalPathBase<QSomeObject> {

    ...
    public final DateTimePath<java.sql.Timestamp> expiresOn = createDateTime("expiresOn", java.sql.Timestamp.class);
    ...

}

The code that performs the select query:

static final QSomeObject qSomeObject = QSomeObject.someObject;

static final QBean<SomeObject> bean = Projections.bean(SomeObject.class, qSomeObject.id, qSomeObject.expiresOn);

public SomeObject find(String someId) {
    return sqlQueryFactory.select(bean)
            .from(qSomeObject)
            .where(qSomeObject.id.eq(someId))
            .fetchOne();
}

Any idea what I might be missing here?

Is there any built-in mechanism to convert from Timestamp to OffsetDateTime? If not, is there any convenient way to do it manually?

Cheers,

Bruno

[Richard Richter]

Hi Bruno

I have good experiences with JPA 2.1 and their converters, we used LocalDate(Time) and Instant types directly on the entities. You're using java.sql.Timestamp on the entity but want something else on the projection. I'd say use Timestamp on the "input" of that bean (be it setter, or type in the constructor projection, etc...) and your required type on the "output" of the bean (getter). Or even have two sets of accessors, second for your type - something like getExpiresOnAsOffsetDateTime(). That is manual conversion, something like:
        return OffsetDateTime.ofInstant(
            Instant.ofEpochMilli(timestamp.getTime()), ZoneId.systemDefault());
(Not sure what zone you want to use, but you will know better.)

Shortly - as you use SQL type on the entity, I'd not bother with any magic and just manually converted it on the bean, using the matching type when filling it with data (using Querydsl) and your expected type when read elsewhere in the code.

Cheers

Virgo