Share jOOQ on Facebook
Share jOOQ on Twitter

This page in other versions: 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Old, end-of-life releases: 3.2 | 3.1 | 3.0 | 2.6 | 2.5

Mapping your DEV schema to a productive environment

You may wish to design your database in a way that you have several instances of your schema. This is useful when you want to cleanly separate data belonging to several customers / organisation units / branches / users and put each of those entities' data in a separate database or schema.

In our AUTHOR example this would mean that you provide a book reference database to several companies, such as My Book World and Books R Us. In that case, you'll probably have a schema setup like this:

  • DEV: Your development schema. This will be the schema that you base code generation upon, with jOOQ
  • MY_BOOK_WORLD: The schema instance for My Book World
  • BOOKS_R_US: The schema instance for Books R Us

Mapping DEV to MY_BOOK_WORLD with jOOQ

When a user from My Book World logs in, you want them to access the MY_BOOK_WORLD schema using classes generated from DEV. This can be achieved with the org.jooq.conf.RenderMapping class, that you can equip your Configuration's settings with. Take the following example:

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInput("DEV")
                          .withOutput("MY_BOOK_WORLD")));

// Add the settings to the DSLContext
DSLContext create = DSL.using(connection, SQLDialect.ORACLE, settings);

// Run queries with the "mapped" Configuration
create.selectFrom(AUTHOR).fetch();

The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement:

SELECT * FROM MY_BOOK_WORLD.AUTHOR

Even if AUTHOR was generated from DEV.

Mapping several schemata

Your development database may not be restricted to hold only one DEV schema. You may also have a LOG schema and a MASTER schema. Let's say the MASTER schema is shared among all customers, but each customer has their own LOG schema instance. Then you can enhance your RenderMapping like this (e.g. using an XML configuration file):

<settings xmlns="http://www.jooq.org/xsd/jooq-runtime-3.9.0.xsd">
  <renderMapping>
    <schemata>
      <schema>
        <input>DEV</input>
        <output>MY_BOOK_WORLD</output>
      </schema>
      <schema>
        <input>LOG</input>
        <output>MY_BOOK_WORLD_LOG</output>
      </schema>
    </schemata>
  </renderMapping>
</settings>

Note, you can load the above XML file like this:

Settings settings = JAXB.unmarshal(new File("jooq-runtime.xml"), Settings.class);

This will map generated classes from DEV to MY_BOOK_WORLD, from LOG to MY_BOOK_WORLD_LOG, but leave the MASTER schema alone. Whenever you want to change your mapping configuration, you will have to create a new Configuration.

Using a default schema

If you wish not to render any schema name at all, use the following Settings property for this:

Settings settings = new Settings()
    .withRenderSchema(false);

// Add the settings to the Configuration
DSLContext create = DSL.using(connection, SQLDialect.ORACLE, settings);

// Run queries that omit rendering schema names
create.selectFrom(AUTHOR).fetch();

Mapping of tables

Not only schemata can be mapped, but also tables. If you are not the owner of the database your application connects to, you might need to install your schema with some sort of prefix to every table. In our examples, this might mean that you will have to map DEV.AUTHOR to something MY_BOOK_WORLD.MY_APP__AUTHOR, where MY_APP__ is a prefix applied to all of your tables. This can be achieved by creating the following mapping:

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInput("DEV")
                          .withOutput("MY_BOOK_WORLD")
                          .withTables(
         new MappedTable().withInput("AUTHOR")
                          .withOutput("MY_APP__AUTHOR"))));

// Add the settings to the Configuration
DSLContext create = DSL.using(connection, SQLDialect.ORACLE, settings);

// Run queries with the "mapped" configuration
create.selectFrom(AUTHOR).fetch();

The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement:

SELECT * FROM MY_BOOK_WORLD.MY_APP__AUTHOR

Table mapping and schema mapping can be applied independently, by specifying several MappedSchema entries in the above configuration. jOOQ will process them in order of appearance and map at first match. Note that you can always omit a MappedSchema's output value, in case of which, only the table mapping is applied. If you omit a MappedSchema's input value, the table mapping is applied to all schemata!

Using regular expressions

All of the above examples were using 1:1 constant name mappings where the input and output schema or table names are fixed by the configuration. With jOOQ 3.8, regular expression can be used as well for mapping, for example:

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInputExpression(Pattern.compile("DEV_(.*)"))
                          .withOutput("PROD_$1")
                          .withTables(
         new MappedTable().withInputExpression(Pattern.compile("DEV_(.*)"))
                          .withOutput("PROD_$1"))));

// Add the settings to the Configuration
DSLContext create = DSL.using(connection, SQLDialect.ORACLE, settings);

// Run queries with the "mapped" configuration
create.selectFrom(AUTHOR).fetch();

The only difference to the constant version is that the input field is replaced by the inputExpression field of type java.util.regex.Pattern, in case of which the meaning of the output field is a pattern replacement, not a constant replacement.

Hard-wiring mappings at code-generation time

Note that the manual's section about code generation schema mapping explains how you can hard-wire your schema mappings at code generation time

The jOOQ Logo