Dynamic batch insert SQL query with JOOQ-CodePudding

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);

CodePudding user response：

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.