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?

Which version of Jdbi are you using?

qualidafial
– qualidafial

2017-12-21 19:56:55 +00:00
Commented Dec 21, 2017 at 19:56 — qualidafial
– qualidafial, Commented Dec 21, 2017 at 19:56
I'm using version 2.55

Eddified
– Eddified

2018-01-03 23:39:52 +00:00
Commented Jan 3, 2018 at 23:39 — Eddified
– Eddified, Commented Jan 3, 2018 at 23:39

yelsayed · Accepted Answer · 2021-04-16 05:27:33Z

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.

edited Apr 16, 2021 at 5:27

yelsayed

5,5826 gold badges35 silver badges39 bronze badges

answered Dec 22, 2017 at 18:03

qualidafial

6,9023 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harshdeep Over a year ago

BindBeanList doesn't seem to be working with insert statements (works fine with select statement). I had to use SqlBatch only in the end.

Madbreaks Over a year ago

Wow. Would have been swell if @BindBeanList was included in the docs. :|

Madbreaks Over a year ago

"@SqlBatch was replaced with @SqlUpdate" -- not according to the API docs: jdbi.org/apidocs/org/jdbi/v3/sqlobject/statement/SqlBatch.html

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related