CODEWITHSUNDEEP
javasqljooq
hookumsnivy
hookumsnivy

Reputation: 241

JOOQ: Querying similar tables

I have a situation where I have 2 tables that are almost identical. One table is used for making viewing and editing the data and then the data is published. When the data is published it goes into another table. Essentially WIDGET and PUBLISHED_WIDGET I have implemented all sorts of custom searching, sorting, filtering and paging queries for 1 table and now I have to implement it for the other. I'm trying to find out a way I can abstract it out and use TableLike<COMMON_WIDGET>.

Example:

create table widget (
   id int not null auto_increment,
   name varchar(64) not null,
   lang varchar(2),
   updated_by varchar(64),
   updated_on timestamp
//...
);

create table published_widget (
   id int not null auto_increment,
   name varchar(64) not null,
   lang varchar(2),
   updated_by varchar(64),
   updated_on timestamp
//...
);

I want to be able to do something like this:

public class WidgetDao {
  private final TableLike<CommonWidget> table;
public Widget find(String rsql) {
  dslContext.selectFrom(table)
    .where(table.ID.eq("...").and(table.NAME.eq("...")
//      ...
}

Is this possible?

Upvotes: 1

Views: 178

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

Table mapping

You can use the runtime table mapping feature for this. Choose one of your tables as your "base table" (e.g. WIDGET), and then use a derived configuration with this Settings:

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInput("MY_SCHEMA")
                          .withTables(
         new MappedTable().withInput("WIDGET")
                          .withOutput("PUBLISHED_WIDGET"))));

And then:

public Widget find(String rsql) {
  // Alternatively, store this derived Configuration in your DAO for caching purposes
  dslContext.configuration()
            .derive(settings)
            .dsl()
            .selectFrom(WIDGET)
            .where(WIDGET.ID.eq("...").and(WIDGET.NAME.eq("..."))
            .fetch();
  // ...
}

Such Settings will rename (not alias) the table globally, for a Configuration

Table.rename()

Generated tables have a rename() operation on them, that allows you to do exactly what you want on an ad-hoc basis, not globally. Depending on your use-case, that might be more suitable. Again, this is not the same thing as aliasing (which affects generated SQL).

And again, you'll pick one of your similar/identical tables as your base table, and rename that for your purposes:

public Widget find(String rsql) {
  Widget table = WIDGET.rename(PUBLISHED_WIDGET.getQualifiedName());

  dslContext.selectFrom(table)
            .where(table.ID.eq("...").and(table.NAME.eq("..."))
            .fetch();
  // ...
}

This method currently (jOOQ 3.14) only exists on generated tables, not on org.jooq.Table, see: https://github.com/jOOQ/jOOQ/issues/5242

Upvotes: 2

Related Questions