jOOQ Generator: trying to rewrite NUMBER data type to INTEGER (java.lang.Integer)

[Rafael Ponte]

Hi guys,

I'm working on Oracle 11g XE with jOOQ 3.10.6 (trial version) and I'm trying to use the jOOQ's Data Type Rewrite feature but It's not working as I'd like to.

When I generate my classes via jOOQ generator it is considering all columns with type NUMBER (without explict precision and scale) as BigDecimal in Java code. Unfortunately all my tables have PK columns as NUMBER (again, no precision and no scale) and I can't change it! But what I really want is those attributes be of type java.lang.Integer (don't worry, I understand the risks related to precision here).

To solve that, I configured jOOQ generator config file to rewrite all columns which their names start with "ID_" and are of type NUMBER (without explict precision and scale), as you can see below:

<forcedTypes>
 <forcedType>
 <name>INTEGER</name>
 <expression>.*\.ID_.*</expression>
 <types>NUMBER</types>
 </forcedType>
</forcedTypes>

But it's generating all class attributes as BigInteger instead of Integer. For some reason I think it's related to this default jOOQ behavior. The best I've got was generating those attributes as java.lang.Long through this configuration:

<forcedTypes>
 <forcedType>
 <name>BIGINT</name>
 <expression>.*\.ID_.*</expression>
 <types>NUMBER</types>
 </forcedType>
</forcedTypes>

I can't understand why it works for BIGINT but not for INTEGER.

Someone could give me any tip?

Thanks,

[Lukas Eder]

Hi Rafael,

Thanks for your detailed description. The approach you've chosen is correct, but the problem is that Oracle's idea of an INTEGER data type is really best represented by BigInteger, so the data type rewriting doesn't really rewrite the type to SQLDataType, but to [Dialect]DataType. In this case, OracleDataType.INTEGER is a BigInteger. Try it in Oracle:

CREATE TABLE t (i INTEGER);

SELECT data_type, data_length, data_precision, data_scale 

FROM user_tab_cols 

WHERE table_name = 'T';

You'll get

DATA_TYPE  DATA_LENGTH  DATA_PRECISION  DATA_SCALE

--------------------------------------------------

NUMBER     22                           0

So, it's really a BigInteger. In order to get jOOQ to generate a java.lang.Integer, you can rewrite the type to something like NUMBER(8) or BINARY_INTEGER or PLS_INTEGER.

I hope this helps,

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.

[Rafael Ponte]

Hi,

Thanks for this explanation, very helpful.

It makes sense! I didn't know jOOQ used the OracleDataType instead of SQLDataType, that's a very important info!

I'll try these suggested data types today and later I give a feedback.

Thanks, Lukas!

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.

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

--

Rafael Ponte

TriadWorks | Formação Java
http://cursos.triadworks.com.br

[Lukas Eder]

Well, that just helps explaining it. You have to think of it this way: You're re-writing the data type to some other type, prior to feeding the data type to the code generator. The code generator will think that the dictionary views produced the type you're providing, so it'll treat them as vendor-specific.

I guess this could be clarified in the manual...

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.

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

--

Rafael Ponte

TriadWorks | Formação Java
http://cursos.triadworks.com.br

--

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.

[Rafael Ponte]

Hi,

Thank you for this extra explanation! Indeed, that's what I thought when I tried to configure data type rewritting but I wasn't totally sure about how it worked. As you well said, it's worth clarifying it in the documentation so that avoiding misunderstanding.

Now, returing to my problem: using PLS_INTEGER worked as expected! So follows my final configuration:

<forcedTypes>
 <forcedType>
 <name>PLS_INTEGER</name>

 <expression>.*\.ID_.*</expression>
 <types>NUMBER</types>
 </forcedType>
</forcedTypes>

Thanks again, Lukas!

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.

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

--

Rafael Ponte

TriadWorks | Formação Java
http://cursos.triadworks.com.br

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

[Lukas Eder]

2018-05-15 18:34 GMT+02:00 Rafael Ponte <rpo...@gmail.com>:

Hi,

Thank you for this extra explanation! Indeed, that's what I thought when I tried to configure data type rewritting but I wasn't totally sure about how it worked. As you well said, it's worth clarifying it in the documentation so that avoiding misunderstanding.

OK, done: https://github.com/jOOQ/jOOQ/issues/7492

The manual will be updated with the next nightly build.

 

Now, returing to my problem: using PLS_INTEGER worked as expected! So follows my final configuration

Cool, I'm glad this worked for you!