Jooq database/schema name mapping

Viewed 191

I use jooq to generate objects against a local database, but when running "for real" later in production the actual databases will have different names. To remedy this I use the <outputSchemaToDefault>true</outputSchemaToDefault> config option (maven).

At the same time, we have multiple databases (schemas), and are using a connection pool to the server like "jdbc:mysql://localhost:3306/" (without specifying a database here).

How do I tell jooq which database to use when running queries?

I have tried all config I can think of:

new Settings()
        .withRenderSchema(true)     // true/false seems to make no difference.
        .withRenderCatalog(true)    // true/false seems to make no difference.
        .withRenderMapping(new RenderMapping()
                .withDefaultSchema("my_database")     // Seems to have no effect.
                // The above 3 configs always give me an error saying "no database selected".

                // Adding this gives me 'my_database.my_table' does not exist - while it actually does.
                .withSchemata(new MappedSchema()
                        .withInputExpression(Pattern.compile(".*"))
                        .withOutput("my_database")
                ));

I have also tried using a database/schema name, as in not configuring outputSchemaToDefault. But then, adding the MappedSchema code above, but that gives me errors with "'my_databasemy_database.my_table' does not exist", which is correct. I have no clue why that code gives me the database/schema name twice?

Edit: When jooq tells me that the db.table does not exist, if I put a break point in a good place and get the sql from jooq and run exactly that against my database it does work. But jooq fails to run it.

Also, I'm using version 3.15.3 of jooq.

1 Answers

I solved it. Instead of using .withInputExpression(Pattern.compile(".*")), it seems to work with .withInput("").

I'm still not sure why it works, or if this is the "correct" way of solving it. But at least it is a way forward.

No clue why using the pattern, I got the name twice though. But that one I'll leave alone.

Related