14

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?

Eddified
  • 3,085
  • 8
  • 36
  • 47

1 Answers1

12

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.

yelsayed
  • 5,236
  • 3
  • 27
  • 38
qualidafial
  • 6,674
  • 3
  • 28
  • 38
  • 1
    BindBeanList doesn't seem to be working with insert statements (works fine with select statement). I had to use SqlBatch only in the end. – Harshdeep Jan 09 '19 at 07:14
  • 3
    Wow. Would have been swell if `@BindBeanList` was included in the docs. :| – Madbreaks Jul 17 '19 at 17:49
  • 2
    _"@SqlBatch was replaced with @SqlUpdate"_ -- not according to the API docs: http://jdbi.org/apidocs/org/jdbi/v3/sqlobject/statement/SqlBatch.html – Madbreaks Jul 17 '19 at 18:15