How to save jooq table as a variable and then reference it

java, jooq, mysql, sql

Solution

Unfortunately, there is (currently, as of jOOQ 3.2) no way to typesafely dereference the `I` column from your derived table `e`, as there is no way the Java compiler can know that there is an `I` column. You will have to resort to unsafe methods:

// Enforcing typesafety using coercion:
.join(e)
.on(s.NAME.equal(e.field("I").coerce(String.class))

// Circumventing typesafety using raw types
.join(e)
.on(s.NAME.equal((Field) e.field("I")))

A bit more typesafety can be achieved by reusing the `I` field as such:

Source s = SOURCE.as("s");
Field<String> I = SOURCE.ID.as("I");
TableLike<?> e = create.select()
        .from(SOURCE)
        .where(SOURCE.NAME.isNotNull())
        .asTable().as("e");

create.selectCount()
      .from(s)
      .join(e)
      .on(s.NAME.equal(e.field(I)))
      .fetchOne().value1();

Problem

This is my sql statement: ``` select count(*) from source s join(select id as I from source where name is not null) e on s.name = e.I ``` Here is what I have in java ``` Source s = SOURCE.as("s"); TableLike<?> e = create.select(SOURCE.ID.as("I")) .from(SOURCE) .where(SOURCE.NAME.isNotNull() .asTable().as("e"); create.selectCount() .from(s) .join(e) .on(s.NAME.equal(e.I)) .fetchOne().value1(); ``` I think that TableLike is not the right type because I get an error when I try to do e.I

Original source