Generic queries against tables with identical schemas

[cco...@squareup.com]

I have a use case where I will have a set of tables that all share the same schema. I'd like to be able to use jOOQ to write code to generically handle these tables, but I don't see how to do this.

I would run a pre-processor to create schemas for the various tables based on the environment. While these tables are effectively static within an environment, I'd like this code to be able to run in many different environments.  I cannot combine these tables (e.g. using a 'name' column) because my particular use case requires careful control of access patterns, and I lose the sequential access I need if I interleave this data.

As an example:

create table foo (
  id                bigint(20)        not null AUTO_INCREMENT,
  baz_id            bigint            default 
  PRIMARY KEY (id),
  KEY id (baz_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table bar (
  id                bigint(20)        not null AUTO_INCREMENT,
  baz_id            bigint            default 
  PRIMARY KEY (id),
  KEY id (baz_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Both tables have identical schemas. Yet, I don't see an easy way with the jOOQ api to write generic code that could query against either table. Hopefully I'm just missing some simple trick here?

[Lukas Eder]

2015-11-19 2:57 GMT+01:00 <cco...@squareup.com>:

Both tables have identical schemas. Yet, I don't see an easy way with the jOOQ api to write generic code that could query against either table. Hopefully I'm just missing some simple trick here?

Yes, I'm pretty sure that the runtime table mapping feature will do the trick for you:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/runtime-schema-mapping

For instance, you generate only the foo table, and at runtime, you switch foo for bar.

Is that what you were looking for?

[cco...@squareup.com]

Yes, I think that will work nicely. Thank you!