Importing data from XML, CSV

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

The jOOQ User Manual. Multiple Pages : Advanced topics : Importing data from XML, CSV

# Importing with jOOQ

If you are using jOOQ for scripting purposes or in a slim, unlayered application server, you might be interested in using jOOQ's importing functionality (see also exporting functionality). You can import data directly into a table from any of these formats:

# CSV

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
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:

Factory create = new Factory(connection, SQLDialect.ORACLE);

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

Here are various other examples:

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

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

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

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

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

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

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

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

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

      .loadCSV(inputstream)
      .fields(ID, null, 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<TAuthor> 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();

# XML

This will be implemented soon...

The jOOQ User Manual. Multiple Pages : Advanced topics : Importing data from XML, CSV

previous : next