Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13

SQL interpreter

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Starting with jOOQ 3.13, a SQL interpreter has been implemented, which can interpret a subset of the SQL language (mostly DDL statements) and maintain an up-to-date in-memory representation of your database meta model.

The interpreter is made available through a variety of DSLContext.meta() methods, which can be used for example as follows:

// Using the parser
Meta meta1 = create.meta(
  "create table t (i int)",
  "alter table t add primary key (i)",
  "create table u (i int references t)"
);

Meta meta2 = create.meta(
  createTable("t").column("i", INTEGER),
  alterTable("t").add(primaryKey("i")),
  createTable("u").column("i", INTEGER).constraint(foreignKey("i").references("t"))
);

Interpretation is incremental. On any pre-existing org.jooq.Meta model, apply() can be called to derive a new version of the model. This includes being able to combine different source of models, including JDBC java.sql.DatabaseMetaData based ones.

// Get live access to your current Connection's DatabaseMetaData
Meta meta1 = create.meta();

// Create a new model representation with an interpreted, additional table
// The query is not executed! The DDL is only interpreted in jOOQ, and a derived meta model is created from it
Meta meta2 = meta1.apply("create table t (i int)");

// Create another new model representation, with a table removed
// Again, none of these queries are executed.
Meta meta3 = meta2.apply("drop table u cascade");

Finally, if you want to export the meta model to one of the different supported formats, you can use:

// The JAXB annotated XML version of your "information schema":
InformationSchema is = meta.informationSchema();

// A set of DDL queries that can be used to reproduce your schema on any database and dialect:
Queries queries = meta.ddl();

Different sources for the meta model can be used, including jOOQ API built DDL statements, or a set of SQL strings that will be parsed using the SQL parser, dynamically. These sources could be database change management scripts, such as those managed by Flyway, or by jOOQ.

The resulting type is the runtime org.jooq.Meta model, which gives access to the ordinary jOOQ API, including catalogs and schemas or tables. These objects can then, in turn, be used with any other jOOQ API, for instance to count all the rows in all tables in a database:

for (Table<?> table : meta.getTables())
  System.out.println(table + " has " + create.fetchCount(table) + " rows");

For a set of interpreter configuration flags, please refer to the section about the interpreter settings.

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo