Executing the generated SQL and mapping the result set

Executing the generated SQL and mapping the result set to a POJO via jOOQ can be done via the fetching methods available in the jOOQ API. For instance, the next snippet of code relies on the fetchInto() flavor:

public List<Office> findOfficesInTerritory(String territory) {

  List<Office> result = ctx.selectFrom(table("office"))

    .where(field("territory").eq(territory))

    .fetchInto(Office.class); 

  return result;

}

What happened there?! Where did ResultQuery go? Is this black magic? Obviously not! It's just that jOOQ has immediately fetched results after constructing the query and mapped them to the Office POJO. Yes, the jOOQ's fetchInto(Office.class) or fetch().into(Office.class) would work just fine out of the box. Mainly, jOOQ executes the query and maps the result set to the Office POJO by wrapping and abstracting the JDBC complexity in a more object-oriented way. If we don't want to immediately fetch the results after constructing the query, then we can use the ResultQuery object like this:

// 'query' is the ResultQuery object

List<Office> result = query.fetchInto(Office.class);

The Office POJO is available in the code bundled with this book.

Important Note

jOOQ has a comprehensive API for fetching and mapping a result set into collections, arrays, maps, and so on. We will detail these aspects later on in Chapter 8, Fetching and Mapping.

The complete application is named DSLBuildExecuteSQL. Since this can be used as a stub application, you can find it available for Java/Kotlin in combination with Maven/Gradle. These applications (along with, in fact, all the applications in this book) use Flyway for schema migration. As you'll see later, Flyway and jOOQ make a great team.

So, let's quickly summarize this chapter before moving on to exploit the astonishing jOOQ Code Generator feature.