This is an update to an earlier article.

Having spent some time using my jOOQ DAO code, I made a few changes to it.

Encapsulating the DSLContext

These changes were mainly to try to eliminate the need to declare an instance of jOOQ’s DSLContext class in any code outside of the DAO class. I wanted to encapsulate all DSLContext operations in the DAO class.

Sorting Using the Database

I also experimented with some different sorting approaches.

In my case, I can sort the way I need, using MySQL and my database’s default charset and collation:

Charset = utf8mb4 (reference)

Collation = utf8mb4_0900_ai_ci (reference)

The collation uses ai (accent insensitive) and ci (case insensitive), which is good enough for what I need, for sorting text containing accented characters. For example, these are sorted together: eglise, Eglise, église, Église.

Without an appropriate collation (e.g. just relying on binary sorting), the characters with leading accents would be sorted after words beginning with z.

So, my DAO class contains a method like this:

1
2
3
4

public <P extends IciPojo, R extends Record> List<P> fetchPojoList(Select<R> query,
        Class<P> pojo) {
    return dsl.fetch(query).into(pojo);
}

…which can be called like this example.

Sorting using Java Comparators

If you can’t get the results you want using your DB collation and DB sorting, you can use a Java collator. jOOQ will use this to sort your data after fetching it from the database.

For example, with this DAO method:

1
2
3
4

public <R extends Record> List<R> fetchAllSorted(Table table, Class<R> record,
        Comparator<R> cmprtr) {
    return dsl.fetch(table).sortAsc(cmprtr);
}

…you can create your own Java comparator and pass that to jOOQ:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

import java.text.Collator;
import java.util.Comparator;
import java.util.Locale;

...

final Collator french = Collator.getInstance(Locale.forLanguageTag("fr-FR"));

final Comparator<ActorRecord> actorComparator = Comparator
        .comparing(ActorRecord::getLastName, french)
        .thenComparing(ActorRecord::getFirstName, french);

List<ActorRecord> recs = dao.fetchAllSorted(
        ACTOR,
        ActorRecord.class,
        actorComparator);

See the code here.

My example, above, defines a collator using French sorting rules and then applies these rules to each actor’s LastName and FirstName.

Sorting happens in Java, not in the DB, using this approach.

It’s worth noting that in this specific example, this sorting is actually more precise (specific to French language sorting rules) than using the database utf8mb4_0900_ai_ci collation.