Construct jOOQ query from parts

[sca...@live.com]

Suppose I have a decomposed query in some format, like "fields=a,b,c; whereClause='1=1'; groupBy=b,c;" etc. Some parts of the query might be missing, which corresponds to missing SQL part (e.g. a select without where clause). Having that, I'd like to compose a jOOQ query which could be run by some external executor, suppose a default DSL.using( conn, dialect ).fetch( constructedQuery ).

Currently my code looks like the following:

Select select = select( fieldsList ).from( myTable );

if( hasWhereClause )
{
  select = ((SelectJoinStep) select).where( whereClause );
}

if( hasGroupBy )
{
  select = ((SelectConditionStep) select).groupBy( groupByClause );
}

if( hasOrderBy )
{
  select = ((SelectHavingStep) select).orderBy( orderByClause );
}

...

return select;

As you see, because of the class hierarchy (which I actually like and admire a bit, since I can see it could not be easily designed), I either need multiple class casts or lots of nested if-statements - and both options are not really exciting.

So maybe I'm missing something, or maybe there's an alternative builder for jOOQ queries (if not, I suppose one of the best solutions would be to have one), with which the code would look like the following:

new JooqQueryBuilder()
    .withFrom(TableLike)
    .withWhereClause(Condition)
    .withGroupBy(List<GroupByField>)
    ...

Hope I managed to clearly describe the problem, but if you need any further clarification - I would be glad to provide such. 

[Lukas Eder]

Hi,

Your use-case is covered by the model API, much better than the DSL API. More info here:

https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/dsl-and-non-dsl

It's also what the DSL delegates all calls to, internally.

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sca...@live.com]

Great, looks like exactly what I was looking for! Sorry for missing that in the documentation, it's really vast and I probably skipped this small chapter :)

Is the following a correct way to get a SelectQuery without a DSLContext? I'd prefer to keep this class a mere transformer, not aware of any contexts, dialects and moreover database connections.

SelectQuery<Record> select = DSL.select( fieldsList )
                                .from( table )
                                .getQuery();

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2017-10-30 16:55 GMT+01:00 <sca...@live.com>:

Great, looks like exactly what I was looking for! Sorry for missing that in the documentation, it's really vast and I probably skipped this small chapter :)

No worries, it's not the main use-case. In most cases, you can still do dynamic SQL with the DSL API:

https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql

Only when you need to add entire clauses conditionally, things get hairy with the DSL API.

 

Is the following a correct way to get a SelectQuery without a DSLContext? I'd prefer to keep this class a mere transformer, not aware of any contexts, dialects and moreover database connections.

SelectQuery<Record> select = DSL.select( fieldsList )
                                .from( table )
                                .getQuery();

Yes, that's one way. An alternative way is to create a DSLContext that is not connected to anything:

DSL.using(new DefaultConfiguration()).selectQuery();

 

Hope this helps,

Lukas

[sca...@live.com]

I see that using(new DefaultConfiguration()) is exactly what DSL class does, so I'll go the shorter path :)

Thanks again for your assistance!