Load CSV file to a mysql table using Load data into. How do I convert the following query into JOOQ?

Question

LOAD DATA INFILE '/testing.csv'
IGNORE INTO TABLE Test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
IGNORE 1 ROWS (@col1)
SET test_ID="100",
    test_reg_ID ="26003",
    VALUE =@col1;

testing.csv:

TABLE Test_table

Question No.1: Help me convert this into JOOQ 3.6 Question No.2: I want to avoid the empty data which falls on row 1.

Answer 1

jOOQ has a CSV import API for that purpose. Here's how you'd translate that MySQL command to jOOQ:

DSL.using(configuration)
   .loadInto(TEST_TABLE)
   .loadCSV(new File("/testing.csv"))
   .fields(TEST_TABLE.VALUE)
   .separator(',')
// Available in jOOQ 3.10 only: https://github.com/jOOQ/jOOQ/issues/5737
// .lineSeparator("\n")
   .ignoreRows(1)
   .execute();

Note that jOOQ's Loader API doesn't support those default expressions as MySQL does (see #5740):

SET test_ID="100",
    test_reg_ID ="26003"

There are a few workarounds:

1. You could patch the CSV data and prepend those columns before loading them.
2. You could use DSLContext.fetchFromCSV() and then use stream().map() to prepend the missing data, before using the alternative Record import API rather than the suggested CSV import API
3. You could run a simple UPDATE statement right after the import for this data.

A note on performance

Do note that jOOQ's loader API can be fine-tuned by specifying bulk, batch, and commit sizes. Nevertheless, the database's out-of-the-box import implementation is very likely to still be much faster than any client side import that has to go through JDBC.