JOOQ generated classes have schema defaulted to PUBLIC

Question

This is a follow up question to this SO Question. I am using jooq codegen for auto generation of jooq classes from jpa entities. I am planning to use jooq as a SQL query builder and execute the actual queries with JPA EntityManager. But jooq is generating the tables from entities with schema defaulted to PUBLIC.

For example if my query has to be

select SCHEMA_A.colA, SCHEMA_A.colB from SCHEMA_A.tableA;

jooq is generating

select PUBLIC.colA, PUBLIC.colB from PUBLIC.tableA;

This is making the query fail when I fire the following query because the schema is invalid.

entityManager.createNativeQuery(sqlString).getResultList();

What configuration do I need to add to make the autogenerated code contain the actual schema name?

Codegen:

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>

        <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>3.9.1</version>

            <!-- The plugin should hook into the generate goal -->
            <executions>
                <execution>
                    <phase>generate-sources</phase>
                    <goals>
                        <goal>generate</goal>
                    </goals>
                </execution>
            </executions>

            <dependencies>
                <dependency>
                    <groupId>org.jooq</groupId>
                    <artifactId>jooq-meta-extensions</artifactId>
                    <version>3.9.1</version>
                </dependency>

                <dependency>
                    <groupId>com.yaswanth</groupId>
                    <artifactId>domain</artifactId>
                    <version>${project.version}</version>
                </dependency>
            </dependencies>

            <configuration>

                <!-- Generator parameters -->
                <generator>

                    <database>
                        <name>org.jooq.util.jpa.JPADatabase</name>
                        <outputSchema>[SCHEMA_A]</outputSchema>
                        <properties>
                            <!-- A comma separated list of Java packages, that contain your entities -->
                            <property>
                                <key>packages</key>
                                <value>com.yaswanth.domain.entity</value>
                            </property>
                        </properties>
                    </database>

                    <target>
                        <packageName>com.yaswanth.domain.entity.jooq</packageName>
                        <directory>target/generated-sources/jooq</directory>
                    </target>
                </generator>
            </configuration>
        </plugin>
    </plugins>
</build>

jooq spring beans config

<bean id="dslContext" class="org.jooq.impl.DefaultDSLContext">
    <constructor-arg ref="jooqConfig" />
</bean>

<bean id="jooqConfig" class="org.jooq.impl.DefaultConfiguration">
    <property name="SQLDialect" value="MYSQL" />
    <property name="dataSource" ref="myDataSource" />
    <property name="settings" ref="settings"/>
</bean>

<bean id="settings" class="org.jooq.conf.Settings">
    <property name="renderSchema" value="true" />
</bean>

Can you try two things for me: 1) remove the brackets `[ ]` from your schema name in ``, 2) add an XML element `PUBLIC` to see if that changes anything? — Lukas Eder, May 04 '17 at 16:03
It works like a charm! I have included the [ ] brackets as a futile last attempt to make this work. Including the PUBLIC schema is the key thing. But should it not be obvious given that the h2 db which stores the metadata information about the entities is supposed to be handled by the user? I mean to say PUBLIC is the default everytime and should not be included by the user. — yaswanth, May 04 '17 at 17:14
Also if I add the schema property in @Table annotation on the entities, would the codegen detect the schema from there? — yaswanth, May 04 '17 at 17:25
I've added an answer. Yes, you could set the `@Table(schema="...")` property. That should be the ideal way forward (if Hibernate correctly picks it up behind the scenes) — Lukas Eder, May 04 '17 at 17:35

score 1 · Accepted Answer · answered May 04 '17 at 17:34

The <outputSchema/> element cannot stand alone, it must be paired with an <inputSchema/> element. The reason is that if you leave out the <inputSchema/> element, then all schemata in your database are used for code generation, and it would be unclear what the standalone <outputSchema/> element means.

This misconfiguration should probably be reported as a warning in the log files: https://github.com/jOOQ/jOOQ/issues/6186

More information about the code generator's schema mapping feature here: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-catalog-and-schema-mapping

Note: This is how the jOOQ code generator behaves in general, the fact that the JPADatabase seems to be a bit particular here doesn't matter. Even with JPA annotated entities, you could have a database that includes several schemata if you specify the @Table(schema = "..."), for instance.

JOOQ generated classes have schema defaulted to PUBLIC

1 Answers1