How a clause (order by) can be removed from a jOOQ query

java, jooq, sql

Solution

This currently cannot be done through the public API. In a future jOOQ 4.0, there might be a cleaner separation of the DSL API and the Model API, where such a model API would allow you to freely manipulate all your query parts, including removing objects again from `SELECT` clauses.

Right now, you have at least two options to implement dynamic SQL:

Don't add the `ORDER BY` clause until you know whether you need it:

SelectQuery<?> select = create
    .select()
    .from("myTable")
    .where("fk = 1")
    .getQuery();

if (someCondition)
    select.addOrderBy(DSL.field("pk"));

Alternative piece of logic:

List<SortField<?>> orderBy = new ArrayList<>();

if (someCondition)
    orderBy.add(DSL.field("pk").asc());

create.select()
      .from("myTable")
      .where("fk = 1")
      .orderBy(orderBy);

Post-process / transform your query using an `ExecuteListener` or a `VisitListener`. This is more of a workaround in edge-cases, though.

In your particular case, you should probably go with option 1. Another, more functional approach to tackling dynamic SQL generation is documented here.

Problem

Is possible to remove a clause (the `order by` clause in my case) from a dynamically constructed query in jOOQ. Suppose that after creating the query: ``` DSLContext create = DSL.using(SQLDialect.POSTGRES); SelectQuery<Record> query = create.select().from("myTable").where("fk = 1").orderBy(DSL.field("pk")).getQuery(); System.out.println(query.getSQL()); // select * from myTable where (fk = 1) order by pk asc ``` I want to change the `order by` clause or remove it to get only ``` select * from myTable where (fk = 1) ``` Is possible make this with jOOQ?. If is not possible and anyone knows a query builder library that allows this will be also welcome.

Original source