CODEWITHSUNDEEP
jooq
Simon Martinelli
Simon Martinelli

Reputation: 36143

jOOQ Query OrderBy as String

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?

Upvotes: 4

Views: 2643

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

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.

Upvotes: 5

Related Questions