jOOQ Query OrderBy as String

Question

I'm getting the order by clause as a String from the application configuration.

Example

String orderByString = "NAME DESC, NUMBER ASC";

Now I want to use this order by in jOOQ query:

Result<KampagneRecord> records = repository.dsl()
        .selectFrom(KAMPAGNE)
        .orderBy(orderByString)
        .fetch();

Unfortunately orderBy does not accept a String.

Is there a way to add the order by clause to the query?

Answer 1

You could use the fact that jOOQ does not validate your plain SQL templating, and just wrap your string in a DSL.field(String):

Result<KampagneRecord> records = repository.dsl()
        .selectFrom(KAMPAGNE)
        .orderBy(field(orderByString))
        .fetch();

Of course, you will have to make sure that syntactical correctness is guaranteed, and SQL injection is prevented.

Some edge cases that rely on jOOQ being able to transform your SQL's ORDER BY clause might stop working, but in your simple query example, this would not apply.

An alternative solution, in very simple cases, is to preprocess your string. It seems as though this would work:

String orderByString = "NAME DESC, NUMBER ASC";

List<SortField<?>> list =
Stream.of(orderByString.split(","))
    .map(String::trim)
    .map(s -> s.split(" +"))
    .map(s -> {
        Field<?> field = field(s[0]);
        return s.length == 1
                ? field.sortDefault()
                : field.sort("DESC".equalsIgnoreCase(s[1])
                      ? SortOrder.DESC
                      : SortOrder.ASC
                  );
    })
    .collect(Collectors.toList());

System.out.println(list);

This list can now be passed to the orderBy() clause.