How to use JDBI to insert multiple rows with a single INSERT?

Question

I'm trying to figure out how to do multi-row insert statements with JDBI.

Here is what I've got:

@SqlBatch("INSERT INTO my_table(col1, col2) VALUES (:col1, :col2)")
@BatchSize(size=300)
public abstract int[] insertRows(@BindBean MyObj ... objs);

... which works fine, but results in as many INSERT statements as there are rows being inserted. I.e. if there are two rows being inserted, it results in something like this:

INSERT INTO my_table(col1, col2) VALUES ('a', 'b');
INSERT INTO my_table(col1, col2) VALUES ('c', 'd');

... when what I want looks like this:

INSERT INTO my_table(col1, col2) VALUES ('a', b'), ('c', 'd');

I'd like it to take a variable number of objects as input. I don't think JDBI can do this, at least not easily... But can it?

Answer 1

The @BindBeanList annotation in v3 should accomplish you what you want:

@SqlUpdate("insert into my_table (col1, col2) values <values>")
int insertRows(@BindBeanList(propertyNames = {"col1", "col2"}) MyObj... objs);

The difference is I replaced @SqlBatch with @SqlUpdate, and int[] return type became just int since this is now a single statement.