18

I have a table Table1 with 6 columns.

Here is the sql statement that i need to map.

Select *,count(ID) as IdCount from Table1;

Now, the sql query result will be 7 columns ( 6 Table1 columns and 1 IdCount column). But when i implement the same in Jooq with this query, it only gets a single column "IDCount".

SelectQuery q = factory.selectQuery();
        q.addSelect(Table1.ID.count().as("IdCount"));
        q.addFrom(Table1.TABLE1);

Now, the resultant recordset have only a single column "IdCount" while what i need is all the columns and one additional column "IdCount". I want 7 columns in Jooq too.

Shekhar
  • 787
  • 2
  • 7
  • 18

1 Answers1

27

Option 1 (using the asterisk):

The * (asterisk, star) operator has been added to jOOQ 3.11 through DSL.asterisk() (unqualified asterisk) or through Table.asterisk() (qualified asterisk). It can be used like any other column being projected.

Prior to jOOQ 3.11, there were the following other options as well:

Option 2 (with the DSL syntax):

List<Field<?>> fields = new ArrayList<Field<?>>();
fields.addAll(Arrays.asList(Table1.TABLE1.fields()));
fields.add(Table1.ID.count().as("IdCount"));

Select<?> select = ctx.select(fields).from(Table1.TABLE1);

Option 3 (with the "regular" syntax, which you used):

SelectQuery q = factory.selectQuery();
q.addSelect(Table1.TABLE1.fields());
q.addSelect(Table1.ID.count().as("IdCount"));
q.addFrom(Table1.TABLE1);

Option 4 (added in a later version of jOOQ):

// For convenience, you can now specify several "SELECT" clauses
ctx.select(Table1.TABLE1.fields())
   .select(Table1.ID.count().as("IdCount")
   .from(Table1.TABLE1);

All of the above options are using the Table.fields() method, which of course relies on such meta information being present at runtime, e.g. by using the code generator.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Great answer, very helpful thanks, but just FYI, in the version of jOOQ I'm using (jOOQ 3.6) the fields method returns a `Field[]`, so in option 1 you have to write `fields.addAll(Arrays.asList(Table1.TABLE1.fields()))` – Adrian Smith Jan 12 '17 at 20:46
  • https://www.jooq.org/doc/3.11/manual/sql-building/sql-statements/select-statement/select-clause/#N4CC5A - asterisk is supported by jooq. I could not make it work but it should :) – Maksim Gumerov Aug 30 '18 at 18:57
  • @MaksimGumerov: I'm assuming your referring to this problem here: https://github.com/jOOQ/jOOQ/issues/7841. I will reply to that issue shortly. Thanks for documenting it! – Lukas Eder Sep 03 '18 at 07:48