Caused by: org.jooq.exception.DataTypeException: Cannot convert from 1479365948726 (class java.lang.Long) to class java.time.ZonedDateTime

[excr...@gmail.com]

Hi,

I'm trying to run a simple select like so:

List<Foo> foos = create.select()
   .from(FOO)
   .limit(pageable.getPageSize())
   .offset(pageable.getOffset())
   .fetchInto(Foo.class);

I'm getting this error:

org.jooq.exception.MappingException: An error ocurred when mapping record to class Foo
	at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:658)

        ...

Caused by: org.jooq.exception.DataTypeException: Cannot convert from 1479365948726 (class java.lang.Long) to class java.time.ZonedDateTime
	at org.jooq.tools.Convert$ConvertAll.fail(Convert.java:1118)
	at org.jooq.tools.Convert$ConvertAll.toDate(Convert.java:1070)
	at org.jooq.tools.Convert$ConvertAll.from(Convert.java:789)
	at org.jooq.tools.Convert.convert0(Convert.java:316)
	at org.jooq.tools.Convert.convert(Convert.java:308)
	at org.jooq.tools.Convert.convert(Convert.java:380)
	at org.jooq.impl.AbstractRecord.get(AbstractRecord.java:243)
	at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:694)
	at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:614)

I'm using jooq 3.8.6 with a postgresql database. Does anyone have any ideas why? My Foo.java entity does have some ZonedDateTime fields. I'm guessing jooq doesn't support these? If that's the case, which date/time type should I be using?

[Patryk Najda]

Hi,

it seems like you're missing a converter which would convert from the type which is stored in your DB (from it's value (1479365948726)I guess it's a TIMESTAMP)

to a type which is used in your POJO (ZonedDateTime).

Please have a look at this article regarding Converters: http://www.jooq.org/doc/2.6/manual/sql-execution/fetching/data-type-conversion/

Let me know if this helped you.

[excr...@gmail.com]

Hi, thanks for the response!

I had tried making a converter but it isn't working. I still get the same error. Might you have any idea what I'm doing wrong? Is there something specific I have to do to get it to trigger? I did a maven build again to regenerate the sources. Do I need to change the query somehow or is there something else I'm missing or have done wrong? Thanks for any input.

My pom.xml:

<generator>

<database>

<name>org.jooq.util.postgres.PostgresDatabase</name>

<includes>.*</includes>

<excludes />

<inputSchema>public</inputSchema>

<forcedTypes>

<forcedType>

<userType>java.time.ZonedDateTime</userType>

<converter>com.foo.domain.util.ZonedDateTimeConverter</converter>

<expression>.*</expression>

<types>TIMESTAMP</types>

</forcedType>

</forcedTypes>

</database>

<target>

<packageName>com.foo.jooq</packageName>

<directory>target/generated-sources</directory>

</target>

</generator>

My ZonedDateTimeConverter.java converter implementation:

public class ZonedDateTimeConverter implements Converter<Long, ZonedDateTime> {

@Override

public ZonedDateTime from(Long aLong) {

Instant instant = Instant.ofEpochSecond(aLong);

ZonedDateTime zonedDateTime = ZonedDateTime.ofInstant(instant, ZoneId.systemDefault());

return zonedDateTime;

}

@Override

public Long to(ZonedDateTime zonedDateTime) {

return zonedDateTime.toEpochSecond();

}

@Override

public Class<Long> fromType() {

return Long.class;

}

@Override

public Class<ZonedDateTime> toType() {

return ZonedDateTime.class;

[Lukas Eder]

The problem is probably here:

<forcedType>

<userType>java.time.ZonedDateTime</userType>

<converter>com.foo.domain.util.ZonedDateTimeConverter</converter>

<expression>.*</expression>

<types>TIMESTAMP</types>

</forcedType>

You're applying this converter to TIMESTAMP columns, but the column of interest is really a BIGINT (java.lang.Long)...

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

... on a side-note: In case you're using PostgreSQL: Do note that the regular expression is case-sensitive, and in PostgreSQL, data types (as most objects) are lower-case!

[excr...@gmail.com]

Really? That confuses me because the actual column the date is stored in is timestamp, not bigint. I tried changing types to "bigint" and I get this. Not really sure what it means.

[ERROR] COMPILATION ERROR :

[INFO] -------------------------------------------------------------

[ERROR] /jooq/Sequences.java:[31,34] type argument java.time.ZonedDateTime is not within bounds of type-variable T

[ERROR] /jooq/Sequences.java:[31,87] type argument java.time.ZonedDateTime is not within bounds of type-variable T

[ERROR] /jooq/Sequences.java:[31,180] incompatible types: org.jooq.DataType<java.lang.Long> cannot be converted to org.jooq.DataType<java.time.ZonedDateTime>

[INFO] 3 errors

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Excrulon]

Giving up for the night, but want to leave this here to make sure I'm thinking about this whole thing correctly.

I was under the impression that a converter is for when you come across a certain type of column data type and you want to change it to a java object.

Example... a table in my postgres db has columns of type timestamp. So I would declare timestamp in the types tag. This makes it so whenever I run a jooq query and one of the columns is of type timestamp, it will automatically trigger the converter class I have defined for that type and run the proper overridden method that tells how to convert it to my target result, ZonedDateTime.

You're saying it should be bigint over timestamp though... so am I completely wrong on all of this?

Thanks.

[Lukas Eder]

OK, I'm sorry - maybe the confusion was on my side, as you had longs in your stack trace, and your converter uses Long as well

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

2016-11-18 11:52 GMT+01:00 Excrulon <excr...@gmail.com>:

Giving up for the night, but want to leave this here to make sure I'm thinking about this whole thing correctly.

I was under the impression that a converter is for when you come across a certain type of column data type and you want to change it to a java object.

Yes. 

Example... a table in my postgres db has columns of type timestamp. So I would declare timestamp in the types tag.

Yes indeed.

This makes it so whenever I run a jooq query and one of the columns is of type timestamp, it will automatically trigger the converter class I have defined for that type and run the proper overridden method that tells how to convert it to my target result, ZonedDateTime.

Exactly.

You're saying it should be bigint over timestamp though... so am I completely wrong on all of this?

No, I was confused by your code, which is why I brought up BIGINT. But certainly, there's something wrong somewhere because your converter uses java.lang.Long, rather than java.sql.Timestamp as the binding for the <T> type variable.

Perhaps we don't have all the information to help you just yet?

[excr...@gmail.com]

Thanks for the response. What other information can I provide that would help? I feel like I've tried everything that the jooq docs say and I've provided everything I can think of.

The only reason I had my converter using Long is because the stacktrace error said it was failing to convert Long. I tried changing my converter to use Timestamp instead of Long:

public class ZonedDateTimeConverter implements Converter<Timestamp, ZonedDateTime> {

@Override

public ZonedDateTime from(Timestamp timestamp) {

ZonedDateTime zonedDateTime = ZonedDateTime.ofInstant(timestamp.toInstant(), ZoneId.systemDefault());

return zonedDateTime;

}

@Override

public Timestamp to(ZonedDateTime zonedDateTime) {

return new Timestamp(zonedDateTime.toInstant().getEpochSecond() * 1000L);

}

@Override

public Class<Timestamp> fromType() {

return Timestamp.class;

}

@Override

public Class<ZonedDateTime> toType() {

return ZonedDateTime.class;

}

}

I've tried to make my <types> tag in my pom both timestamp and TIMESTAMP.

No matter what I do, my converter doesn't trigger. Is there something specific I have to change in my query?

List<Foo> foos = create.select()

.from(FOO)

.limit(pageable.getPageSize())

.offset(pageable.getOffset())

.fetchInto(Foo.class);

I've attached a file, converter.png, showing the code it drops into before failing with that error. As you can see, there is no condition for ZonedDateTime, so of course it fails. I assume that if the converter is working, it will intercept before the code even gets here and this won't be a problem.

Thanks for your time.

[excr...@gmail.com]

Okay! I finally fixed it. The issue was simply that...

<types>TIMESTAMP</types>

should have been

<types>.*TIMESTAMP.*</types>

[Lukas Eder]

I'm sorry for the delay. You're right, I missed that, of course. Your data type is probably a "timestamp with time zone", not a "timestamp", which is why the previous regex didn't match.

Sorry, I should've seen that. But I'm glad the converters are now applied correctly.

Lukas

--