Helidon DB Client - Add JDBC Batch support

[lambdaupb]

For larger inserts it is important to avoid network roundtrips per row inserted.

This can either be done generating new sql strings per row count or using the JDBC PreparedStatement.addBatch() feature and relying on the driver to do that in the background.

I would propose adding API to expose this JDBC feature or something equivalent.

Environment Details

• Helidon Version: 2.4.1
• Helidon SE

[spericas]

Some brainstorming to support batch.

1. Batch of unrelated statements

        dbClient().batch(b -> b
                .createInsert("INSERT INTO pokemons VALUES (?,?)")
                        .addParam("name1").addParam("type").add(b)
                .createNamedUpdate("update1")
                        .addParam("name", "name1")
                        .addParam("type", "type1").execute())
                .thenAccept(count -> response.send("Modified: " + count + " values"))
                .exceptionally(throwable -> sendError(throwable, response));

2. Batch of same statement with multiple param settings

        dbClient().batch(b -> b
                .createInsertMulti("INSERT INTO pokemons VALUES (?,?)", getPokemons())
                .prepare((s, p) -> {
                    s.addParam(p.getName());
                    s.addParam(p.getType());
                })
                .execute())
                .thenAccept(count -> response.send("Modified: " + count + " values"))
                .exceptionally(throwable -> sendError(throwable, response));

[spericas]

Some initial thoughts and design available here https://github.com/spericas/helidon/tree/dbclient-batch

[lambdaupb]

Hey, thanks for exploring this improvement.

I moved the project that prompted this request away from helidon dbclient for now and can't really provide feedback on the development any more. Wrangling the reactive model was just a tad too brittle and hard to debug, especially considering loom on the horizon.

The problem was real however and I would recommend keeping this issue around at low priority.

Thanks again for the quick responses and your work on this project.

[spericas]

Closing for now