CODEWITHSUNDEEP
javajooq
Emery
Emery

Reputation: 92

How to use Jooq for a table that doesn't exist yet?

I have a database (postgresql) with a Table Cdr. Every month a new table is created in the database that has the same schema as Table Cdr, named Cdr__ (eg. Cdr_2016_04).

I'm trying to use Jooq to query the database, based on a date provided. The trouble is that:

context.select(CDR.fields())
            .from("cdr_2017_01")
            .where(CDR.FIELD1.eq("somevalue")
                .and(CDR.FIELD2.notEqual("value 2")
                .and(CDR.FIELD2.notEqual("value 3")))
            .fetchInto(Cdr.class);

doesn't work because it indicates that

org.jooq.exception.DataAccessException: ...from cdr_2017_01 where ("public"."cdr"."field1" = ? and "public"."cdr"."field2" <> ? and "public"."cdr"."field2" <> ?)]; ERROR: missing FROM-clause entry for table "cdr"

I've tried using an alias to make it 'cdr_2017_01 as cdr', but that fails because cdr already exists. It seems that because I use CDR.FIELD1, and CDR.fields(), jooq requires that the table in the from clause be CDR. Is there some way to make this generic, or should I not be using Jooq for this kind of query?

Upvotes: 2

Views: 1966

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

The error

The error you're getting originates from your usage of the generated CDR literal in your field references, e.g. CDR.FIELD1. All these fields will be fully qualified with the "cdr" table, not the "cdr_2017_01" table that you put in the from clause.

You can turn on debug logging to see formatted SQL strings that jOOQ generates and sends to the server.

A workaround

The simplest workaround is to alias your "cdr_2017_01" table back to "cdr" like this:

context.select(CDR.fields())
        .from("cdr_2017_01 as cdr")
        .where(CDR.FIELD1.eq("somevalue")
            .and(CDR.FIELD2.notEqual("value 2")
            .and(CDR.FIELD2.notEqual("value 3")))
        .fetchInto(Cdr.class);

This will work to some extent with most queries.

A better solution

You should use the runtime schema / table mapping feature for this. It is designed exactly for these kinds of multi-tenancy / partitioning use-cases.

You can then write something along the lines of:

DSL.using(connection, new Settings()
       .withRenderMapping(new RenderMapping()
           .withSchemata(new MappedSchema()
               .withInput("my_schema_name")
               .withTables(new MappedTable()
                   .withInput(CDR.getName())
                   .withOutput(CDR.getName() + "_2017_01")))))
   .select(CDR.fields())
   .from(CDR)
   .where(CDR.FIELD1.eq("somevalue")
       .and(CDR.FIELD2.notEqual("value 2")
       .and(CDR.FIELD2.notEqual("value 3")))
   .fetchInto(Cdr.class);

Another option, of course, might be to use PostgreSQL's partitioning feature, in case of which you might not need to explicitly specify table names in the client:

https://www.postgresql.org/docs/current/static/ddl-partitioning.html

Upvotes: 1

Related Questions