CODEWITHSUNDEEP
javaoracle-databasesql-order-byjooq
Migerusantte
Migerusantte

Reputation: 323

Result from query with join loses order when converted to POJO list

I'm using JOOQ to create a native query to an Oracle 12 database. When I convert the result to my POJO the order from the order by clause is lost.

Basically I'm joining two tables, Tasks and Variables. A Task can have many Variables.

I'm converting the results to my POJO this way:

select.fetchGroups(Task.class, Variable.class);

public class Task{

    private String id;

    private String name;

    private String formKey;

        private List<Variable> variables = new ArrayList<Variable>();


getters()/setters()

...

hashCode()

...

equals()

}

public class Variable{

    private String vId;
    private String vName;
    private String vValue;
    private String vType;

getters()/setters()

...

hashCode()

...

equals()


}

I'm trying to "plug" my order clause at the end of the select manually (as didn't find a way to build it up with the jooq sintax, some insights on that will be appreciated):

The jooq dslcontext

@Autowired
private DSLContext dsl;

The list that is populated

Map<Task, List<Variable>> response;

The plain order by sentence.

String orderClause = " case when V.NAME_ = '"+sortColumn+"' then V.TEXT_ end "+sortDirection;

(If I log the plain SQL of this, the result is correctly ordered)

SelectSeekStep1<Record, Object> selectOrdered = select.orderBy(DSL.field(orderClause));

response = selectOrdered.fetchGroups(Task.class, Variable.class);

At this point my response variable is already a map, but the order is lost.

There's no error messages or exceptions, the Map is returned as expected, but the order is lost.

Please tell me if I'm missing some critical detail, and I'll add it.

Upvotes: 0

Views: 282

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

The various fetchGroups() methods use a LinkedHashMap behind the scenes, which they return to you, and they iterate the records in fetch order, so any order you provide the jOOQ query with is stable with respect to these methods.

From what you've shown, you're ordering by some V columns, which probably stands for Variable. You cannot possibly expect that order to be retained when grouping by Task in the client, although the ordering is stable per Task. I'll illustrate. If this is what you're getting from the database (because you order by variable):

TASK    VARIABLE
1       A
2       B
3       C
1       D
2       E

Then, these fetchGroups() methods will produce the following client side grouping:

TASK = 1
  VARIABLE = [A, D]
TASK = 2
  VARIABLE = [B, E]
TASK = 3
  VARIABLE = [C]

Upvotes: 1

Related Questions