Managing Database Connections

1. Managing Database Connections

We've learned how to connect to databases and run queries. But in real-world applications, it's not enough to simply connect and forget. Database connections are expensive, and if we don't manage them well, our app will slow down or even crash.

2. Why connection management matters?

Each time we open a connection, JDBC has to establish a network socket, perform authentication, and allocate resources. All of this takes time and consumes memory. If our code forgets to close connections, they remain open in the background. Over time, this leads to resource leaks: dozens of "zombie" connections nobody is using. Eventually, new connections fail because the database has reached its maximum limit. That's why connection management is one of the most important parts of writing production-grade database code.

3. try-with-resources

The first line of defense is try-with-resources. First, we define our SQL query to count the number of books. Then we open a try block with three resources: a Connection, a Statement, and a ResultSet. Within the block, we verify if the result set contains data and print the results. All three resources implement the AutoCloseable interface, a contract that guarantees they have a close() method. So when the try block finishes, whether normally or through an exception, Java automatically calls close() on each one. The socket is released, memory is freed, and our app doesn't leak resources. Without this pattern, it's easy to forget a close() call in error paths, leading to serious stability issues.

4. Connection pooling

But even if we always close connections properly, there's still a performance problem. Creating a fresh connection for every query requires repeating the expensive setup each time. That's where connection pooling comes in. Think of it like a taxi stand: instead of calling a new taxi every time, you take one that's already waiting. When you're done, it returns to the stand for the next passenger. A connection pool works the same way. It keeps pre-established connections ready. When our code needs one, it borrows from the pool. When we're done, the connection returns to the pool instead of being destroyed.

5. HikariCP example

One of the most popular pooling libraries is HikariCP. It's lightweight, fast, and widely used in production systems. First, we import HikariConfig and HikariDataSource from the HikariCP library. These are the two classes we need: one to configure our pool settings, and one to manage the pool itself. Next, we set up the configuration. We create a HikariConfig object and specify our JDBC URL, username, and password using setJdbcUrl, setUsername, and setPassword methods. Finally, we create a HikariDataSource by passing our config object. This initializes the connection pool. From this point, calling ds.getConnection() gives us a connection from the pool. We still use try-with-resources, but now when the block ends, the connection isn't destroyed. It returns to the pool, ready to be reused.

6. HikariCP Console Logging

When HikariCP starts, we'll see log messages in the console: "Starting," then "Added connection," and finally "Start completed." These confirm our pool is active and managing connections.

7. Let's practice!

Now it's time to practice writing connection management code!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.