Allow access to the java.sql.Connection from DbExecute

[zimmi]

Environment Details

• Helidon Version: 2.3.0-SNAPSHOT
• Helidon SE
• JDK version: 16
• OS: debian

---

Problem Description

I'm trying to use the CopyManager from the PostgreSQL JDBC Driver to insert some CSV-Data into my database. That requires access to the underlying java.sql.Connection, which seems impossible to obtain from the io.helidon.dbclient.DbExecute / io.helidon.dbclient.DbTransaction passed to DbClient::execute / DbClient::inTransaction. The Connection obtained should be part of the current transaction, if possible. Thank you for the work on Helidon, it's a joy to use. :)

Edit: I'm imagining something like this:

dbClient.execute(exec -> {
    Optional<org.postgresql.core.BaseConnection> connection = exec.unwrap(org.postgresql.core.BaseConnection.class);
    // use connection ...
});

Ideally, the JdbcDbClient variant would handle further unwrapping of the connection (I actually need the org.postgresql.core.BaseConnection), but just getting the java.sql.Connection would be fine as well.

[Tomas-Kraus]

JDBC DbClient instance contains JDBC connection pool so I can add some unwrap to return Single from this pool when available. JDBC DbExecute contains CompletionStage which can be also unwrapped as Single.

Just keep in mind that you are working with reactive API and anything that you do with pure JDBC is blocking so you have to handle it yourself. Especially accessing connection in database executor context is dangerous because the same connection is being used to execute all statements there in parallel when the same future (Single instance) becomes completed.

[zimmi]

I see, thank you for taking the time @Tomas-Kraus. I don't have any pressing performance needs, so I'm blocking left and right anyway in anticipation of https://github.com/oracle/helidon/issues/2341. But I know that's not encouraged, and I don't want the API to contain any footguns because of it.

This issue in particular is no longer relevant to me, since I have worked around the issue by writing insert statements for the CSV manually. It would be a better fit for https://github.com/oracle/helidon/pull/2796, so feel free to close this one if it can't be done in a safe way with the reactive API and if nobody else has a similar use case.

[Tomas-Kraus]

I already have the code so here it is. Maybe someone will find it useful.

[Tomas-Kraus]

@zimmi I don't know whether we'll do blocking API extension or not. It does not make sense in Helidon which is reactive.

[zimmi]

Thanks a lot for the code, I'm sure it will come in handy. Listening to talks about Project Loom I got the impression that it will reduce the need for reactive streams by allowing blocking code to scale in a similar fashion. So I assumed that this is the long term goal of Helidon, especially because it's an Oracle effort as well. But I'm happy to be told otherwise.

Edit: My needs are fairly minimal as of now. I just need an organized, well documented framework with modern defaults and decent code quality that doesn't rely on reflection and lots of abstraction layers that make debugging painful. Helidon SE ticks all those boxes. The APIs are very nice and clean, the only thing that makes reading the code harder is the reactive part. I was hoping that this will go away in the future as well.

[Tomas-Kraus]

Yes, Project Loom will change a lot. With features like fibers, we can probably start simplifying some reactive APIs which won't be required anymore. But until that, we can't do much in this area. So you are right, when we are talking about long term plans, there will for sure be some changes to introduce simple blocking API.