Chapter 10: Exporting, Batching, Bulking, and Loading

Manipulating large amounts of data requires serious skills (know-how and programming skills) in exporting, batching, bulking, and loading data. Each of these areas requires a significant amount of code and a lot of time to be implemented and tested against real datasets. Fortunately, jOOQ provides comprehensive APIs that cover all these operations and expose them in a fluent style, while hiding the implementation details. In this context, our agenda includes the following:

• Exporting data in text, JSON, XML, CSV, charts, and INSERT statements
• Batching INSERT, UPDATE, DELETE, MERGE, and Record
• Bulking queries
• Loading JSON, CSV, arrays, and Record

Let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter10.

Exporting data

Exporting (or formatting) data is achievable via the org.jooq.Formattable API. jOOQ exposes a suite of format() and formatFoo() methods that can be used to format Result and Cursor (remember fetchLazy() from Chapter 8, Fetching and Mapping) as text, JSON, XML, CSV, XML, charts, and INSERT statements. As you can see in the documentation, all these methods come in different flavors capable of exporting data into a string or a file via the Java OutputStream or Writer APIs.

Exporting as text

I'm sure that you have already seen in your console output something similar to the following:

Figure 10.1 – Tabular text data

This textual tabular representation can be achieved via the format() method. A flavor of this method takes an integer argument representing the maximum number of records to include in the formatted result (by default, jOOQ logs just the first five records of the result formatted via jOOQ's text export, but we can...

Batching

Batching can be the perfect solution for avoiding performance penalties caused by a significant number of separate database/network round trips representing inserts, deletes, updates, merges, and so on. For instance, without batching, having 1,000 inserts requires 1,000 separate round trips, while employing batching with a batch size of 30 will result in 34 separate round trips. The more inserts (statements) we have, the more helpful batching is.

Batching via DSLContext.batch()

The DSLContext class exposes a suite of batch() methods that allow us to execute a set of queries in batch mode. So, we have the following batch() methods:

BatchBindStep batch(String sql)
BatchBindStep batch(Query query)
Batch batch(String... queries)
Batch batch(Query... queries)
Batch batch(Queries queries)
Batch batch(Collection<? extends Query> queries)
Batch batch(String sql, Object[]... bindings)
Batch batch(Query query, Object[]... bindings)

Behind the scenes, jOOQ implements...

Bulking

Writing bulk queries in jOOQ is just a matter of using the jOOQ DSL API. For instance, a bulk insert SQL looks like this:

INSERT IGNORE INTO `classicmodels`.`order` (
  `order_date`, `required_date`, `shipped_date`, 
  `status`, `comments`, `customer_number`, `amount`) 
VALUES (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), 
       (?, ?, ?, ?, ?, ?, ?)

This can be expressed in jOOQ by chaining the values() call:

ctx.insertInto(ORDER)
   .columns(ORDER.ORDER_DATE, ORDER.REQUIRED_DATE,
            ORDER.SHIPPED_DATE, ORDER.STATUS, 
            ORDER.COMMENTS,ORDER.CUSTOMER_NUMBER, 
            ORDER.AMOUNT)
    .values(LocalDate.of(2004,10,22), LocalDate.of(2004,10,23),
     LocalDate...

Loading (the Loader API)

Whenever we need to load (import) our database tables with data coming from different sources (CSV, JSON, and so on), we can rely on the jOOQ Loader API (org.jooq.Loader). This is a fluent API that allows us to smoothly tackle the most important challenges, such as handling duplicate keys, bulking, batching, committing, and error handling.

The Loader API syntax

Typically, we have a file containing the data to be imported in a common format such as CSV or JSON, and we customize the Loader API general syntax to fit our needs:

ctx.loadInto(TARGET_TABLE)
   .[options]
   .[source and source to target mapping]
   .[listeners]
   .[execution and error handling]

While TARGET_TABLE is obviously the table in which the data should be imported, let's see what options we have.

Options

We can mainly distinguish between three types of options that can be used for customizing the import process...

Summary

In this chapter, we've covered four important topics: exporting, batching, bulking, and loading. As you saw, jOOQ comes with dedicated APIs for accomplishing each of these tasks that require a lot of complex code under the hood. Frequently, jOOQ simplifies the complexity (as usual) and allows us to focus on what we have to do and less on how we do it. For instance, it is amazing to see that it takes seconds to write a snippet of code for loading a CSV or a JSON file into the database while having fluent and smooth support for error handling control, diagnosis output, bulking, batching, and committing control.

In the next chapter, we will cover the jOOQ keys.