Along with the previously implemented export API, it is easy to export results from org.jooq.Result into CSV, let users modify them in Excel or any other office software, and upload the CSV again. Other ideas for future versions of jOOQ will also include loading data from XML and JSON data sources, “merging” data (i.e. including DELETE operations), etc. Feedback is very welcome.
// The typical jOOQ factory Factory create = new Factory(connection, SQLDialect.ORACLE); // Configure and execute a Loader object Loader<TAuthor> loader = create.loadInto(AUTHOR) .onDuplicateKeyError() .onErrorAbort() .commitAll() .loadCSV("1;'Kafka'\n" + "2;Frisch") .fields(Author.ID, Author.LAST_NAME) .quote('\'') .separator(';') .ignoreRows(0) .execute(); // The resulting Loader object then holds various // information about the loading process: // 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 SQLException 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();
7 thoughts on “Loading CSV data with jOOQ”
Thank for your work.
In many (all) the examples you use the Table class like AUTHOR
Yet there is often no classes but just the table name.
in this example, it is difficult to know how from a table name to loadCSV
loadInto ne fonctionne pas avec un string
comment utiliser loadInto alors qu’on n’a pas de classe AUTHOR mais seulement le nom de la table ?
This is a recurrent difficulty in the literature.
Use jOOQ classless associated with tables is not clear. (I think)
often we do not know what to replace the table in the doc to implement the function described
But great job thank you again.
PS: sorry for my approximative english
Pas de probleme pour l’anglais… Je comprends ;-)
When I first wrote the manual, I had not yet thought about the many use-cases where jOOQ is used without jOOQ-codegen, i.e. without any generated source code. This leads to some problems both in the API as well as in the documentation. In general, you can always refer to this section, however:
It explains how to work with jOOQ when you don’t have generated sources, but you’d like to use “plain SQL” tables, fields, conditions, functions, etc. Let me know if that works well for you in loading CSV files as well!
As I see in logs, it inserts rows one by one. Is it possible to do it in batch operation, like it is described here: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution/?
Of course, it’s all documented here:
Ok, already found :)