How to make multiple database operations in one thread use the same handle

[oschina]

In web development, a single request often requires multiple database operations, which are typically encapsulated in various DAOs. How can I get the same handle instance every time I use some Handle instead of getting new one from the connection pool?

For example, we can save the current handle with ThreadLocal

i have try useHandle 10 times in one thread, and the connection hold by handle like below:

HikariProxyConnection@1062714541 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@1593165620 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@917768476 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@919446210 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@360936478 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@3540494 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@1186339926 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@519979933 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@2101636817 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd
HikariProxyConnection@2119992687 wrapping com.mysql.cj.jdbc.ConnectionImpl@6bc407fd

Although the underlying layer is all the same connection, but it is clear that the jdbi re-fetches a new connection from the connection pool each time it uses the Handle

[qualidafial]

By design, useHandle() allocates a handle, yields it to the callback, and then releases it before returning. So if you want to use the same handle for everything inside a request handler, you want to move your useHandle() call right to the root, and reuse that handle for everything in your request handler.

There are two options to consider:

• Pass the Handle as an explicit parameter to all your method calls.
• Rely on the fact that nested calls to Jdbi.useHandle() on a thread return the same handle as the outermost call. If you're making multiple data changes you may also want to consider Jdbi.useTransaction() instead for atomicity.

It's hard to give you any more advice than that without knowing more about your software stack.

[oschina]

OK, thanks for you reply :)