Menu
Are there any advantages to use something like HikariCP with embedded databases like SQLite, H2 or HSQLDB? (the last two can also run in server mode but I'm not interested in that)
Apart from the performance benefit (that I suppose would be negligible with embedded databases) I'm also interested in other facilities provided by the connection pool that could make the code more concise and/or robust.
I think that this question is different from this other one in that it's more specific since it focuses on embedded databases, and, to a lesser degree, on HikariCP.
739
asked Oct 15 '17 07:10
Which of the following is advantage of using JDBC connection pool? Explanation: Since the JDBC connection takes time to establish. Creating connection at the application start-up and reusing at the time of requirement, helps performance of the application.
Database connection pooling is a way to reduce the cost of opening and closing connections by maintaining a “pool” of open connections that can be passed from database operation to database operation as needed.
JDBC 3.0 API Framework. The JDBC 3.0 API provides a general framework with "hooks" to support connection pooling rather than specifying a particular connection pooling implementation. In this way, third-party vendors or users can implement the specific caching or pooling algorithms that best fit their needs.
You reuse a prior database connection, in a new context to avoid the cost of setting up a new database connection for each request. The primary reason to avoid using database connections is that you're application's approach to solving problems isn't structured to accommodate a database connection pool.
One of the most common issues undermining connection pool benefits is the fact that pooled connections can end up being stale. This most often happens due to inactive connections being timed out by network devices between the JVM and the database. As a result, there will be stale connections in the pool.
Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them. Connection pooling can greatly increase the performance of your Java application, while reducing overall resource usage.
Connection pools exist primarily because opening a new connection from scratch is an expensive operation. Typically, it involves TCP/IP handshake, encryption and protocol negotiation and authentication. There is some overhead when closing connection, too.
So this ultimately boils down to implementation: is opening a new connection slow enough to warrant a reuse? A connection pool makes sense if the total time for opening connections is considerable compared to time consumed by other operations, or if opening a connection incurs a critical latency. For embedded databases, the difference should be minimal as they run in the same memory space as the program itself.
However, a connection pool also has a useful side effect because it limits the maximum number of simultaneous connections. On a server with no connection pooling, an attacker can easily send a flood of requests, exhausting memory or causing a denial of service.
From perspective of code clarity and abstraction, connection pools are also nice, because they are completely transparent. If you someday decide to move from embedded to client/server, you don't have to change anything.
168
I can think of only one case,
If you use in development embedded database and in production not embedded database
An embedded database is useful during the development phase of a project
Then you can reuse same code for connection pooling with different database without code change (using dependency injection).
In Spring case choose the second option:
Spring Jdbc's embedded database support can be extended in two ways:
Implement EmbeddedDatabaseConfigurer to support a new embedded database type, such as Apache Derby.
Implement DataSourceFactory to support a new DataSource implementation, such as a connection pool, to manage embedded database connections.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
