eclipse-vertx / eclipse-vertx/vertx-sql-client
Proposal for reworking PreparedStatement
- Dominant language
- Java
- Stars
- 912
- Forks
- 212
- Avg merge
- 17h 30m
- Merged PRs (30d)
- 3
Description
## Context
Some time ago I made a change that `PreparedStatement` is not cached on the client any more(see https://github.com/eclipse-vertx/vertx-sql-client/commit/e53235cbe2bc2fd96aabe35350ab05ba840b164f), the motivation of that change is to let users control the lifecycles of `PreparedStatement` and avoid lifecycle conflicts with one-shot preparedQueries, the user-created `PreparedStatement` might share a same statement handle with the handle used by the one-shot preparedQueries internally, for example when the prepared statement is evicted from the cache and automatically closed by the client, it will unexpectedly close the relative `PreparedStatement` at the same time. Therefore I chose to have users keep a reference to the object, cache the `PreparedStatement` object themselves and it's not cached by the client any more but now I find the design very limited.
A biggest problem of this design is that the statement is escaped from the scope of the connection it belongs to. Likely said once a connection is disconnected the `PreparedStatement` is not usable any more and there is no way for users who cache the `PreparedStatement` object to know that. Additionally it's nearly impossible to chain executions of cached `PreparedStatement` and other behaviors in the same connection/session because users never know which connection the `PreparedStatement` is running on.
## Proposal
In this proposal in order to address those issues above I would like to bring back the `PreparedStatement` into the client cache, so that when users get a cached/non-cached `PreparedStatement` it's guaranteed to be always accessed from a `SqlConnection`.
A usage case is like this:
```
@Test
public void example(SqlConnectOptions connectOptions) {
connectOptions.setCachePreparedStatements(true);
Pool pool = Pool.pool(connectOptions);
pool.getConnection()
.onSuccess(conn -> {
conn.prepare("SELECT * FROM USERS")
.onSuccess(stmt -> {
// no matter whether the statement is cached or not it's always accessed from a connection
// and the stmt is cached in the client again
stmt.query()
.execute()
.onSuccess(res -> {
conn.query("INSERT INTO tmp VALUES (1)")
.execute()
.onSuccess(res2 -> {
});
});
});
});
}
```
I would like to separate the handles of `PreparedStatement` and one-shot prepared-queries when caching is enabled as well. So when statement cache is enabled, the `PreparedStatement` will be cached in another cache which is different from the cache one-shot prepared-queries is using. One thing I am not certain is whether we need complicated caching strategy(like LRU or some others...) for this kind of caching usage, I would like to only provide a unlimited-size `HashMap` based implementation and when users calling `PreparedStatement#close` the statement is closed and removed from the cache.
Contributor guide
Assessment
This issue has not been assessed yet.