All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.12 | Unsupported versions: 3.2 | 3.1 | 3.0 | 2.6

The below CSV data represents two author records that may have been exported previously, by jOOQ's exporting functionality, and then modified in Microsoft Excel or any other spreadsheet tool:

ID,AUTHOR_ID,TITLE <-- Note the CSV header. By default, the first line is ignored
1,1,1984
2,1,Animal Farm

With jOOQ, you can load this data using various parameters from the loader API. A simple load may look like this:

DSLContext create = DSL.using(connection, dialect);

// Load data into the BOOK table from an input stream
// holding the CSV data.
create.loadInto(BOOK)
      .loadCSV(inputstream, encoding)
      .fields(BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE)
      .execute();

Here are various other examples:

// Ignore the AUTHOR_ID column from the CSV file when inserting
create.loadInto(BOOK)
      .loadCSV(inputstream, encoding)
      .fields(AUTHOR.ID, null, AUTHOR.TITLE)
      .execute();

// Specify behaviour for duplicate records.
create.loadInto(BOOK)

      // choose any of these methods
      .onDuplicateKeyUpdate()
      .onDuplicateKeyIgnore()
      .onDuplicateKeyError() // the default

      .loadCSV(inputstream)
      .fields(BOOK.ID, null, BOOK.TITLE)
      .execute();

// Specify behaviour when errors occur.
create.loadInto(BOOK)

      // choose any of these methods
      .onErrorIgnore()
      .onErrorAbort() // the default

      .loadCSV(inputstream, encoding)
      .fields(BOOK.ID, null, BOOK.TITLE)
      .execute();

// Specify transactional behaviour where this is possible
// (e.g. not in container-managed transactions)
create.loadInto(BOOK)

      // choose any of these methods
      .commitEach()
      .commitAfter(10)
      .commitAll()
      .commitNone() // the default

      .loadCSV(inputstream, encoding)
      .fields(BOOK.ID, null, BOOK.TITLE)
      .execute();

Any of the above configuration methods can be combined to achieve the type of load you need. Please refer to the API's Javadoc to learn about more details. Errors that occur during the load are reported by the execute method's result:

Loader<Author> loader = /* .. */ .execute();

// The number of processed rows
int processed = loader.processed();

// The number of stored rows (INSERT or UPDATE)
int stored = loader.stored();

// The number of ignored rows (due to errors, or duplicate rule)
int ignored = loader.ignored();

// The errors that may have occurred during loading
List<LoaderError> errors = loader.errors();
LoaderError error = errors.get(0);

// The exception that caused the error
DataAccessException exception = error.exception();

// The row that caused the error
int rowIndex = error.rowIndex();
String[] row = error.row();

// The query that caused the error
Query query = error.query();
The jOOQ Logo