Fetch MySQL TINYINT(1) as Boolean

[sca...@live.com]

Since jOOQ does not map TINYINT(1) to java.lang.Boolean by default, is it possible to add this mapping programmatically? The solutions I found do not fit my case, which is:

DSL.using( SQLDialect.MYSQL ).fetchOne( resultSet ).intoMap()

The problem is that the fields list is completely dynamic, so I can't set proper Bindings or Converters on Field instances. Rather I need this setting to be global, attached to some configuration or something.

[Lukas Eder]

Hello,

There's a common misconception among MySQL users that TINYINT(1) is:

- A good hint for the type to have boolean semantics

- An actual type

When you specify TINYINT(1), the type will still be a TINYINT. Observe the following:

CREATE TABLE t(i TINYINT(1));

INSERT INTO t VALUES (1);

INSERT INTO t VALUES (2);

INSERT INTO t VALUES (10);

SELECT * FROM t;

You'll get:

 i

--

 1

 2

10

That explains why it would be unwise for jOOQ to consider that "length" information for any type deduction.

The "global" type conversion mechanism that you could use would be to create your own general-purpose Binding and use that for every dynamically created column expression (you'd have to know these columns in advance, though).

Otherwise, you could patch jOOQ's internal MetaDataFieldProvider and put the desired logic there.

In any case, I think your idea is very interesting. jOOQ should have an SPI that allows for overriding such default types at runtime. I've created a feature request for this:

https://github.com/jOOQ/jOOQ/issues/6619

Cheers,

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.

[friso.v...@gmail.com]

Hi all,

On Friday, September 29, 2017 at 2:56:36 PM UTC+2, Lukas Eder wrote:

That explains why it would be unwise for jOOQ to consider that "length" information for any type deduction.

The "global" type conversion mechanism that you could use would be to create your own general-purpose Binding and use that for every dynamically created column expression (you'd have to know these columns in advance, though).

(sorry for the late reply) 

While it might be unwise for JOOQ to do so, I found that in our specific setting, we only use tinyint as a boolean. Therefore I added it to our mapping: 

<!-- Associate data type rewrites with database columns -->

<forcedTypes>

<forcedType>

<name>BOOLEAN</name>

<types>tinyint</types>

</forcedType>

</forcedTypes> 

This works for us, might be useful for others so I thought I'd mention it here

Groeten,

Friso

[sca...@live.com]

My schema is dynamic, so I don't have any table-to-class mappings. And it's not possible to set such forcedType in a DSL config, is it?

[Lukas Eder]

2017-11-17 17:41 GMT+01:00 <sca...@live.com>:

My schema is dynamic, so I don't have any table-to-class mappings. And it's not possible to set such forcedType in a DSL config, is it?

Unfortunately, right now, it isn't - at least not for your particular "plain SQL" / "direct JDBC" usage. The pending feature request is here, I've increased its priority for jOOQ 3.11:

https://github.com/jOOQ/jOOQ/issues/4473

If you're willing to patch your jOOQ version, you will be able to implement the feature directly in org.jooq.impl.MetaDataFieldProvider, which creates Field<?> and DataType<?> references from JDBC's java.sql.ResultSetMetaData

I hope this helps,

Lukas

[Lukas Eder]

For the record, more recent jOOQ versions have added support for additional ResultSet fetching methods, such as DSLContext.fetchOne(ResultSet, Field<?>...), which can be used to pass bindings to the internal Cursor.

jOOQ 3.12 also implements ResultQuery.coerce(Field<?>...):

https://github.com/jOOQ/jOOQ/issues/4473

... and DSLContext.resultQuery(ResultSet):

https://github.com/jOOQ/jOOQ/issues/8316

Which can be used to attach bindings to a ResultQuery that is backed by a JDBC ResultSet

I hope this helps,

Lukas