Converter not applied on Record

[bjoern...@gmail.com]

I think this is a bug.

Have following table definition:

    public final static Table TableName = DSL.table(DSL.name(CEntityCarPostingLink.class.getSimpleName()));
    public final static Field<CUUID> ObjectID = DSL.field(DSL.name("ObjectID"), SQLDataType.BLOB.nullable(false).asConvertedDataType(new CUUIDConverter())); private final CUUID m_ObjectID;
            JooqContext.createTableIfNotExists(TableName)
                .column(ObjectID)
                .constraints(DSL.constraint(TableName.toString() + "_" + ObjectID.toString()).primaryKey(ObjectID))
                .execute();

The code for the converter is:

public final class CUUIDConverter implements Converter<byte[], CUUID> {
    @Override public CUUID from(byte[] T) { return T == null ? null : new CUUID(T); }
    @Override public byte[] to(CUUID U) { return U == null ? null : U.bytes(); }
    @Override public Class<byte[]> fromType() { return byte[].class; }
    @Override public Class<CUUID> toType() { return CUUID.class; }
}

When writing to the database, i.e. doing insert or update, I feed an instance of CUUID and Jooq converts it to a byte-array and writes to database.

Yet, when reading from database, I get a ClassCastException.

In the debugger I think I pinpointed to

...
42 package org.jooq.impl;
...
113 @SuppressWarnings({ "rawtypes", "unchecked" })
114 abstract class AbstractRecord extends AbstractStore implements Record {
...
222
223    @Override
224    public final <T> T get(Field<T> field) {
225        return (T) get(indexOrFail(fieldsRow(), field));
226    }
227
...

Shouldn't the get-Method invoke the Converter / Binding assigned to the field, instead of doing simply a cast to T?


Best
  Bjoern

[bjoern...@gmail.com]

Forgot to add the code snipped used to read from the database.

            Result<Record> r = dc.selectFrom(CEntityCarPostingLink.TableName).limit(100).fetch();
            final LinkedList<CEntityCarPostingLink> result = new LinkedList<>();
            for (Record a : r) { result.add(new CEntityCarPostingLink(a.getValue(CEntityCarPostingLink.ObjectID))); }

[Lukas Eder]

Hi Bjoern,

Thanks a lot for your message.

First off, a remark that isn't related to your bug about this line:

.constraints(DSL.constraint(TableName.toString() + "_" + ObjectID.toString()).primaryKey(ObjectID))

I'd be careful when using jOOQ's QueryPart.toString() methods. By default, they render with all names being quoted. Perhaps, a better method to choose here is Table.getName() and Field.getName().

Can you please post the stack trace of your ClassCastEception?

Thanks,
Lukas

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

[bjoern...@gmail.com]

Here is the stack trace:

java.lang.ClassCastException: [B cannot be cast to name.liwuest.data.CUUID
    at name.liwuest.car.CEntityCarPosting.getLinks(CEntityCarPosting.java:149) ~[bin/:?]
    at name.liwuest.car.CCarPosting.run(CCarPosting.java:57) [bin/:?]
    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_121]

The CCE occurs right after jooq's get-method. Actually, when I invoke

Object o = a.get(CEntityCarPostingLink.ObjectID);

it works and o is of type byte[], while the method signature reads "CUUID get(Field)".

[Lukas Eder]

Oh, I see. So there are two issues.

1. jOOQ indeed has a bug: https://github.com/jOOQ/jOOQ/issues/5846. 

2. Your usage of selectFrom() could be improved

Right now, from what I understand, you're using the plain SQL API, where you're passing only the table name to the selectFrom() method. This works of course, but then jOOQ will materialise the database type byte[] in your records, i.e. the type it receives automatically from JDBC.

You could work around this problem by either:

- Explicitly selecting your column through 

select(CEntityCarPostingLink.ObjectID)

.from(CEntityCarPostingLink.TableName)

- Explicitly applying the converter on your getValue call: 

a.getValue(CEntityCarPostingLink.ObjectID, 

           CEntityCarPostingLink.ObjectID.getDataType().getConverter())

You should probably prefer the first solution, as SELECT * should be avoided for performance reasons anyway. More info about that here:

https://blog.jooq.org/2017/03/08/many-sql-performance-problems-stem-from-unnecessary-mandatory-work/

I hope this helps,

Lukas

--

[bjoern...@gmail.com]

Thank you, doing the explicit "select().from()" did the trick.

May I kindly ask how to perform a "SELECT * ..." using this approach? Indeed, that is what I want to achieve with my query (but trimmed off some parts to make it more readable). Now I am doing the select on all four columns of the table, but maybe a "SELECT *" would also be more robust in my case.


One remark: do you think it is smart to have all those overloaded methods (for J5+)? Especially since varargs. It makes thinks (in code completion) hard to read, JavaDoc bloatet, etc.. Also, now I may have to import a Record2, a Record4, a Record5, and what if at any time I would need a Record326?

Am Freitag, 7. Juli 2017 21:09:48 UTC+2 schrieb Bjoern Wuest:

[Lukas Eder]

Hi Bjoern,

Do note that SELECT * is not explicitly supported by jOOQ yet. The feature is on the roadmap, but it isn't really essential to every day jOOQ usage: https://github.com/jOOQ/jOOQ/issues/2769

What you can certainly do is pass several fields to the select() clause in one go, e.g.:

ctx.select(MY_TABLE.fields())

This mostly has the same effect as SELECT *, except that it will be explicitly listing the fields. If you're using the code generator to generate MY_TABLE, then using selectFrom(MY_TABLE) will generate all those fields as well in the SQL statement, as they are known to jOOQ.

jOOQ only generates SELECT * in the generated SQL, if you don't specify any explicit SELECT columns and one of the tables in the FROM clause has unknown columns.

However, if you want to apply your custom converter, you simply have to tell jOOQ what columns you're selecting, explicitly.

2017-07-11 13:42 GMT+02:00 <bjoern...@gmail.com>:

One remark: do you think it is smart to have all those overloaded methods (for J5+)? Especially since varargs. It makes thinks (in code completion) hard to read, JavaDoc bloatet, etc.. Also, now I may have to import a Record2, a Record4, a Record5, and what if at any time I would need a Record326?

Those are many questions, and yes of course, it is "smart". :) Whether you like that is a different story, but this approach has added tons of value since jOOQ 3.0, and I'm taking bets you'll like it eventually.

jOOQ has been exploring the possibilities of modelling SQL as an internal domain-specific language for quite a while now. If you use SQL as an external DSL, the parser would offer exactly the same number of "overloads", except that being an external DSL with specific tooling made for that language, you wouldn't see the "bloat" in things like code completion, because the tooling can apply domain knowledge. But then again, you'd be using an *external* DSL, so you don't have any of that Java compile-time type safety that jOOQ users love so much about jOOQ.

Let's look at your different ideas individually:

(for J5+)

I'm not sure what Java 5 has to do with this. Generics? They're a great enabler for the jOOQ API, indeed.

Especially since varargs.

You probably mean select() here, right? You'd prefer to have only that single select(Field<?>...) method, not the select(Field<T1>, Field<T2>, ..., Field<TN>) overloads.

But guess what, this type safety is really useful in many cases, e.g.

- Unions are type safe

- The IN predicate is type safe

- Correlated subqueries are type safe

- ROW predicates are type safe

- Results (up to degree 22) are type safe

Especially the latter is extremely useful in languages whose type inference mechanisms are stronger than Java's. Take Kotlin and jOOQ 3.10, for instance:

for ((first, last, title) in ctx

   .select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)

   .from(a)

   .join(b).on(a.ID.eq(b.AUTHOR_ID))

   .orderBy(1, 2, 3))

       println("$title by $first $last")

In the example above, the type of the local variables "first", "last", and "title" can be inferred by the Kotlin compiler to be String. Awesome, isn't it? Even without type inference (if you prefer explicit type declarations) you could still profit from type safety. Whenever you change an expression in your SQL statement (or even when you add / remove a column), your compiler will indicate an error. I personally wouldn't want to miss that.

Details here:

https://blog.jooq.org/2017/05/18/10-nice-examples-of-writing-sql-in-kotlin-with-jooq/

It makes thinks (in code completion) hard to read

Yes, code completion does offer many options. You probably mean select(). That's a relatively low price to pay for the nice features we're getting out of it, see comments above.

JavaDoc bloatet

Hmm, could anything be improved here? 

Also, now I may have to import a Record2, a Record4, a Record5, and what if at any time I would need a Record326?

We chose the "pragmatic" approach and stopped at degree 22 (like the Scala language). If you select a 23rd column, then the varargs select(Field<?>...) method will be the only applicable method to the compiler, and you'll get a Record without any degree and column type information.

I hope this helps.

Lukas