Development versions: | Unsupported versions: 2.6

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 Factory'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 factory
Factory create = new Factory(connection, SQLDialect.ORACLE, settings);

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

The query executed with a Factory 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-2.5.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 Factory

Using a default schema

Another option to switch schema names is to use a default schema for the Factory's underlying Connection. Many RDBMS support a USE or SET SCHEMA command, which you can call like this:

// Set the default schema
Schema MY_BOOK_WORLD = ...
create.use(MY_BOOK_WORLD);

// Run queries with factory having a default schema
create.selectFrom(AUTHOR).fetch();

Queries generated from the above Factory will produce this kind of SQL statement:

-- the schema name is omitted from all SQL constructs.
SELECT * FROM AUTHOR

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 factory
Factory create = new Factory(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 factory
Factory create = new Factory(connection, SQLDialect.ORACLE, settings);

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

The query executed with a Factory 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!

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