Dynamic batch insert SQL query with JOOQ

Question

I am using JOOQ for SQL building in a dynamic way, as I prefer using the good old JDBCTemplate for execution and maintaining myself the domain objects instead of generating them with JOOQ. I have the following batch insert statement: INSERT INTO my_table(field1, field2, ..., field20) values(<VALUES>);

I would like to construct this SQL in a dynamic way. For constructing a select in this manner I have used SelectQuery and methods such as addOrderBy, addConditions, join, etc.

Is there any way to add the column names (i.e. field1, field2, ..., field20) to an InsertQuery? I would like to do something like this:

InsertQuery<Record> insertQuery = ctx.insertQuery(table("my_table"));
insertQuery.addColumns("field1", "field2", ..., "field20");
for (List<Object> values : valuesList) {
    insertQuery.addValues(values);
}
getJdbcTemplate().batchUpdate(insertQuery.getSQL(), insertQuery.getValues(), batchSize);

Answer 1

You can use:

ctx.insertInto(table)
   .columns(columns)
   .valuesOfRows(valuesList.stream().collect(Rows.toRowList(...)))
   .execute();

Note that in jOOQ's terms, this is not a batch INSERT statement (multiple individual statements, single JDBC roundtrip), but a bulk INSERT statement (single individual statements with multiple rows). It corresponds to what you've attempted. If you prefer a Batch statement, then just create a dummy INSERT like this:

ctx.insertInto(table)
   .columns(columns)
   .values(Collections.nCopies(n, null))
   .getSQL();

And extract its SQL. Since you're not about to execute the query with jOOQ, you don't need to pass all bind values to jOOQ, only to JdbcTemplate.

Regarding the execution of jOOQ queries with JdbcTemplate

This isn't about your actual question, but I still like to mention it everywhere someone tries to execute a jOOQ query on JdbcTemplate, JDCB, JPA, etc. You'll get much more out of jOOQ if you execute the queries with jOOQ, see this blog post I've written.