How do I restrict the generated code to tables in a database?

Question

I am currently evaluating jooq. I am very new to it. I want to use jooq to access a SQL Server 2008 R2 database. When I generate the classes, the codegen tool goes and generates code for objects from all the databases on that server which is not what I want.

What settings do I use in the configuration file to restrict the tool to only one database? I checked the documentation and is not so obvious. Well, for restricting tables I guess I could use the <includes></includes> and <excludes></excludes>.

Could I use the same tags to restrict the tool to objects only in certain databases by using fully qualified table names with three parts [database].[schema].[table]?

Some other comments:

• It would be nice if the tool allowed specifying inclusion/exclusion rules per object type i.e. table, view, procedures , functions etc.

• place classes for tables/views/procedures in separate packages, if possible, instead of lumping them all together.

• One of databases had a schema <Domain>\<user name> and the code generated was invalid. Just make sure \ are generated to \\ in strings.

Thanks

Answer 1

Answers for your current questions / comments:

Could I use the same tags to restrict the tool to objects only in certain databases by using fully qualified table names with three parts [database].[schema].[table]?

Yes, the <includes/> and <excludes/> elements match both qualified and unqualified names, so you can for instance exclude database.schema.table (leave out the brackets).

It would be nice if the tool allowed specifying inclusion/exclusion rules per object type i.e. table, view, procedures , functions etc.

Indeed, that's on the roadmap: #5263

place classes for tables/views/procedures in separate packages, if possible, instead of lumping them all together.

You can override the current behaviour with a generator strategy:

• Programmatic implementation (more powerful)
• Declarative implementation (more simple)

One of databases had a schema \ and the code generated was invalid. Just make sure \ are generated to \ in strings.

Yes, the <includes/> and <excludes/> elements (as many other elements in the code generator configuration) take Java regular expressions with all the associated semantics.

A better solution to your actual problem:

In jOOQ 3.9, you can use the catalog / schema mapping feature in your code generator. There are different ways to configure it:

Only one input catalog (standard name for what SQL Server calls database)

This is the simplest configuration to get you started. It will generate everything inside of a single database:

<configuration>
  <generator>
    <database>
      <inputCatalog>database</inputCatalog>
      ...

You can further reduce the generated output to only a single schema inside that database as such:

<configuration>
  <generator>
    <database>
      <inputCatalog>database</inputCatalog>
      <inputSchema>schema</inputSchema>
      ...

Multiple input catalogs

In more sophisticated setups (or as your project grows), a more appropriate approach would be to list all catalogs and schemas explicitly:

<configuration>
  <generator>
    <database>
      <catalogs>

        <!-- This configuration generates everything inside of that catalog -->
        <catalog>
          <inputCatalog>database1</inputCatalog>
        </catalog>

        <!-- This configuration generates only some schemas inside of the catalog -->
        <catalog>
          <inputCatalog>database2</inputCatalog>
          <schemata>
            <schema>
              <inputSchema>schema1</inputSchema>
            </schema>
            <schema>
              <inputSchema>schema2</inputSchema>
            </schema>
          </schemata>
        </catalog>
      ...

Background:

By default, the jOOQ code generator always generates everything it can see. There are two ways to explicitly restrict this:

1. The code generator's catalog / schema mapping feature
2. The standard <includes/> and <excludes/> regular expressions (which apply to all objects)

With jOOQ 3.8, code generation support for multiple catalogs (in SQL Server: databases) was added, without adding support for restricting them through the catalog mapping feature. This was rectified in jOOQ 3.9, when catalog mapping was added (#4794).

Prior to #4794, the only workaround was to use <excludes/> to exclude all content from the unwanted catalogs, which resulted in the jOOQ 3.8 code generator to produce only empty catalogs.

See also this discussion on the jOOQ User Group