forcedType doesn't work on postgres TIMESTAMP type

[Denis Miorandi]

Hi,

    I'm using a maven plugin to generate jooq classes. Time types are mapped on * java.time.*.

This configuration works right on SQLServer, but on postgres TIMESTAMP (with no timezone) is not mapped as expected, so that I've added 

the bold section to achieve this, but it doesn't work.

Any suggestions?

<plugin>
    <groupId>org.jooq.pro</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.pro.version}</version>
    <executions>
        <execution>
            <id>generate-jooq</id>
            <phase />
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>
                <jdbc>
                    <driver>${spring.datasource.driverClassName}</driver>
                    <url>${spring.datasource.url}</url>
                    <user>${spring.datasource.username}</user>
                    <password>${spring.datasource.password}</password>
                </jdbc>
                <generator>
                    <database>
                        <name>${jooq-database-name}</name>
                        <includes>.*</includes>
                        <excludes/>
                        <dateAsTimestamp>false</dateAsTimestamp>
                        <!-- <inputSchema>PUBLIC</inputSchema> -->
                        <inputSchema>${spring.datasource.schema}</inputSchema>
                        <customTypes>
                            <customType>
                                <name>LocalDateTime</name>
                                <type>java.time.LocalDateTime</type>
                                <converter>${company-package-prefix}.jooq.converters.LocalDateTimeConverter</converter>
                            </customType>
                            <customType>
                                <name>LocalDate</name>
                                <type>java.time.LocalDate</type>
                                <converter>${company-package-prefix}.jooq.converters.LocalDateConverter</converter>
                            </customType>
                            <customType>
                                <name>LocalTime</name>
                                <type>java.time.LocalTime</type>
                                <converter>${company-package-prefix}.jooq.converters.LocalTimeConverter</converter>
                            </customType>
                            <customType>
                                <name>XmlSqlXmlBinding</name>
                                <type>java.sql.SQLXML</type>
                                <binding>it.clesius.jooq.bindings.XmlSqlXmlBinding</binding>
                            </customType>
                            <customType>
                                <name>XmlStringBinding</name>
                                <type>java.lang.String</type>
                                <binding>it.clesius.jooq.bindings.XmlStringBinding</binding>
                            </customType>
                        </customTypes>
                        <forcedTypes>
                            <forcedType>
                                <name>LocalDateTime</name>
                                <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression>
                                <types>DATETIME</types>
                            </forcedType>
                            <forcedType>
                                <name>LocalTime</name>
                                <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression>
                                <types>TIME</types>
                            </forcedType>
                            <forcedType>
                                <name>LocalDate</name>
                                <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression>
                                <types>DATE</types>
                            </forcedType>
                            <forcedType>
                                <name>LocalDateTime</name>
                                <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression>
                                <types>TIMESTAMP</types>
                            </forcedType>
                            <!--                                             <forcedType> -->
                            <!--                                                 <name>OffsetTime</name> -->
                            <!--                                                 <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression> -->
                            <!--                                                 <types>TIME_WITH_TIMEZONE</types> -->
                            <!--                                             </forcedType> -->
                            <!--                                              <forcedType> -->
                            <!--                                                 <name>OffsetDateTime</name> -->
                            <!--                                                 <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression> -->
                            <!--                                                 <types>TIMESTAMP_WITH_TIMEZONE</types> -->
                            <!--                                             </forcedType> -->
                            <forcedType>
                                <name>XmlStringBinding</name>
                                <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression>
                                <types>XML</types>
                            </forcedType>
                        </forcedTypes>
                    </database>

                    <generate>
                        <deprecated>false</deprecated>
                        <instanceFields>true</instanceFields>
                        <pojos>true</pojos>
                        <validationAnnotations>true</validationAnnotations>
                        <fluentSetters>true</fluentSetters>
                    </generate>
                    <target>
                        <packageName>${jooq-packageName}</packageName>
                        <!-- <directory>target/generated-sources/jooq-sqlserver</directory> -->
                        <directory>src/main/generated</directory>
                    </target>
                </generator>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <!-- db here -->
        <!-- <dependency> -->
        <!-- <groupId>com.h2database</groupId> -->
        <!-- <artifactId>h2</artifactId> -->
        <!-- <version>${h2.version}</version> -->
        <!-- </dependency> -->
    </dependencies>
</plugin>

[Denis Miorandi]

I missing that <types> is the vendor type not the jdbc one, so that I've tried also

<types>timestamp without time zone</types>

that is exactly copied from postgress gui, but neither this works. Is always generated as java.sql.timestamp....

[Denis Miorandi]

Is it possible to identify exactly the received string in some way?

Il giorno mercoledì 15 giugno 2016 10:17:46 UTC+2, Denis Miorandi ha scritto:

[Lukas Eder]

Hi Denis,

The type is indeed vendor-specific. Notably, you have to provide the type that jOOQ-meta extracts from the database dictionary views. If in doubt:

- Use case insensitive regular expressions (that's an issue too, unfortunately)

- Use lenient matching, e.g. (?i:.*?timestamp.*?). Mostly it's OK to match anything that contains timestamp

Unfortunately, this part of jOOQ isn't "exact science", and it cannot be changed easily either, because many users already rely on the current behaviour. There's also the possibility of a bug, of course, but I think it might just be case-sensitivity in your case.

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

[Denis Miorandi]

I'll try tks

[Denis Miorandi]

(?i).*timestamp.*

works for me. Tks Lukas. Probably it was a case issue.

[Lukas Eder]

Excellent, thanks for the feedback. Yes indeed, PostgreSQL for instance reports all case-insensitive names in lower case, unlike Oracle and many others, which report case-insensitive names in upper case...

Lukas