How to get all the result columns from database with other custom(concat, sum, count) columns in Jooq

Question

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.

Answer 1

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.