MSQL Boolean type being represented as java.lang.Byte

[Salimane Adjao Moustapha]

I'm faced with another problem related to MySQL boolean field becoming java.lang.Byte type in the classes.

I've already added this to my pom.xml

<generator>
    <name>org.jooq.util.DefaultGenerator</name>
    <database>
        <name>org.jooq.util.mysql.MySQLDatabase</name>
        <includes>.*</includes>
        <excludes></excludes>
        <dateAsTimestamp>true</dateAsTimestamp>
        <inputSchema>mydb_development</inputSchema>
        <forcedTypes>
            <forcedType>
                <name>BOOLEAN</name>
                <expression>.*\.BOOLCOLUMN</expression>
            </forcedType>
        </forcedTypes>
    </database>
    <target>
        <packageName>com.packages.jooq.gen</packageName>
        <directory>src/main/java</directory>
    </target>
    <generate>
        <relations>true</relations>
        <deprecated>false</deprecated>
        <instanceFields>true</instanceFields>
        <pojos>false</pojos>
        <immutablePojos>false</immutablePojos>
    </generate>
</generator>

But still the column BOOLCOLUMN is still of type java.lang.Byte in the classes.

Any ideas ? 

[Lukas Eder]

Hmm, intuitively, I'd assume that you might be running into a case-sensitivity issue here. Are you sure your column is all upper-case? If not, try adding (?i: ... ) around the regex. I.e.

            <forcedType>
                <name>BOOLEAN</name>
                <expression>(?i:.*\.BOOLCOLUMN)</expression>
            </forcedType>

--
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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[salimane....@meltwater.com]

You were right, It now works.

By the way, all the query generated have the database name in front of the columns like 

select `database_name`.`table`.`column2` from `database_name`.`table` where `database_name`.`table`.`column1` = 'aaaa';

Is there a way to disable that ?

If you have different environment with different database names, that could be a problem.

Thanks

[Lukas Eder]

2014-04-30 17:50 GMT+02:00 <salimane....@meltwater.com>:

You were right, It now works.

Great!

You have several options - e.g. by using Settings:

- http://www.jooq.org/javadoc/latest/org/jooq/conf/Settings.html#renderSchema

- http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/

Or by using codegen-time or runtime schema mapping:

- http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/runtime-schema-mapping/

- http://www.jooq.org/doc/latest/manual/code-generation/schema-mapping/

Cheers

Lukas

[Salimane Adjao Moustapha]

I went with renderSchema.

Thanks

[Lukas Eder]

Perfect! Glad it helped,

Cheers

Lukas

2014-04-30 20:04 GMT+02:00 Salimane Adjao Moustapha <sali...@gmail.com>:

I went with renderSchema.

Thanks

[Ben Hood]

Hey Lukas,

I was wondering if there is more declarative way of doing this, i.e.
configuring the generator to map all instances of the MYSQL.BIT type
to boolean, rather than just on a named column basis.

Cheers,

Ben

[Lukas Eder]

Hi Ben,

Yes, there is. Consider this page of the manual:

http://www.jooq.org/doc/latest/manual/code-generation/data-type-rewrites/

    <forcedType>
      <!-- Specify any data type from org.jooq.impl.SQLDataType -->
      <name>BOOLEAN</name>

      <!-- Add a Java regular expression matching fully-qualified columns. Use the pipe to separate several expressions.
           
           If provided, both "expressions" and "types" must match. -->
      <expression>.*\.IS_VALID</expression>
      
      <!-- Add a Java regular expression matching data types to be forced to have this type.
      
           Data types may be reported by your database as:
           - NUMBER
           - NUMBER(5)
           - NUMBER(5, 2)
           - any other form.
           
           It is thus recommended to use defensive regexes for types.
           
           If provided, both "expressions" and "types" must match. -->
      <types>.*</types>
    </forcedType>

You will want to omit the <expression> element and provide only a <types> element. I'm guessing that a good choice might be <types>(?i:BIT)</types>

Cheers

Lukas

[Ben Hood]

On Wed, Jun 25, 2014 at 10:03 AM, Lukas Eder <lukas...@gmail.com> wrote:
> You will want to omit the <expression> element and provide only a <types>
> element. I'm guessing that a good choice might be <types>(?i:BIT)</types>

On the first go, using

<forcedType>
<name>BOOLEAN</name>
<types>(?i:BIT)</types>
</forcedType>

did not appear to match the MySQL BIT type - I'll probably have to dig
in a bit (no pun intended) further to find out what the concrete
representation of the MySQL BOOL type is in JOOQ.

[Lukas Eder]

Hi,

It should be bit, but to be sure, check what is reported by INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE

Cheers

Lukas

[Ben Hood]

On Wed, Jun 25, 2014 at 10:56 AM, Lukas Eder <lukas...@gmail.com> wrote:
> It should be bit, but to be sure, check what is reported by
> INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE

select column_type from information_schema.columns
where table_name = 'members' and column_name = 'admin';

column_type
-----------
tinyint(1)

So armed with this knowledge, I am now able to use the following
stanza in the generator config:

<forcedType>
<name>BOOLEAN</name>
<types>(?i:TINYINT)</types>
</forcedType>

Thank you very much for your help :-)

[Lukas Eder]

Hmm, true. In fact, this reminds me that MySQL actually doesn't have a BOOLEAN type. BOOLEAN is indeed just a synonym for TINYINT:

http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

From a defensive programming perspective, you might want to make that regex

    (?i:TINYINT(\s*\(\d+\))?(\s*UNSIGNED)?)

Just to be sure ;-)

[Ben Hood]

On Wed, Jun 25, 2014 at 11:12 AM, Lukas Eder <lukas...@gmail.com> wrote:
> From a defensive programming perspective, you might want to make that regex
> (?i:TINYINT(\s*\(\d+\))?(\s*UNSIGNED)?)
>

Ahhhh ..... some ninja regex hacking ..... given we're talking about
MySQL, that's probably just enough rope to hang yourself ;-)

[Lukas Eder]

 

From: http://geek-and-poke.com/geekandpoke/2013/12/3/yesterdays-regex

OK, back on-topic now :-)

[Ben Hood]

On Wed, Jun 25, 2014 at 12:10 PM, Lukas Eder <lukas...@gmail.com> wrote:

 

From: http://geek-and-poke.com/geekandpoke/2013/12/3/yesterdays-regex

OK, back on-topic now :-)

:-) 

[pawank...@gmail.com]

Anyone looking for a programmatic way of doing it rather than XML can view this sample from jOOQ:

https://www.jooq.org/doc/3.9/manual/code-generation/codegen-advanced/codegen-config-forced-types/