How to implement order by field value with jOOQ's dynamic queries

Question

I am trying to convert something like this in jOOQ:

select foo from bar
order by field(foo, 'value1', 'something-else', 'value3')

on a select query, like:

SelectQuery<Record> query = ...
query.addSelect(BAR.FOO);
query.addFrom(BAR);
query.addOrderBy( ... ? ... )

How does one add the last bit?

---

Background

What I am trying to accomplish is basically described here: MySQL - ORDER BY values within IN(). In my case, I have a generic batch load function that uses 'where field in(..)' and I want to preserve order. This works as I need it to using plain SQL, but I need to add this bit to a dynamically constructed query with jOOQ.

Answer 1

Whenever you hit jOOQ's limits, resort to plain SQL. You can write your own field function like this:

class MyDSL {

    public static Field<Integer> field(Field<String> search, String in1) {
        return field(search, DSL.val(in1));
    }

    public static Field<Integer> field(Field<String> search, Field<String> in1) {
        return DSL.field("field({0}, {1})", Integer.class, search, in1);
    }

    public static Field<Integer> field(Field<String> search, 
                                       String in1, 
                                       String in2) {
        return field(search, val(in1), val(in2));
    }

    public static Field<Integer> field(Field<String> search, 
                                       Field<String> in1, 
                                       Field<String> in2) {
        return DSL.field("field({0}, {1}, {2})", Integer.class, search, in1, in2);
    }

    // ... or, support a varargs function variant, too
}

And now use that in all your statements:

query.addOrderBy( MyDSL.field(BAR.FOO, "value1", "something-else", "value3") );

Answer 2

This seems to do the trick. Not sure if there is a better answer,

Field[] args = new Field[]{DSL.field("foo"), 
      DSL.val("value1"), DSL.val("something-else"), DSL.val("value3")}
query.addOrderBy(DSL.function("field", SQLDataType.INTEGER, args));