JDBC · Lesson 9
Connection Pooling
Reusing Database Connections
Opening a database connection involves network and authentication work, so doing it for every request is expensive. A connection pool creates a bounded set of connections and lends them to callers temporarily. Closing a pooled connection returns it to the pool rather than closing the physical database connection.
Borrowing and Returning
Application code should acquire a connection, use it briefly, and close it in a try block. A pool such as HikariCP usually manages validation, limits, and idle connections outside the repository.
import java.sql.Connection;
import javax.sql.DataSource;
public class CourseService {
private final DataSource dataSource;
public CourseService(DataSource dataSource) {
this.dataSource = dataSource;
}
public boolean courseExists(long courseId) throws Exception {
try (Connection connection = dataSource.getConnection()) {
return connection.prepareStatement("SELECT 1 FROM courses WHERE id = " + courseId)
.executeQuery().next();
}
}
}
In production, parameterize the query with PreparedStatement; the short example emphasizes the borrow-and-return lifecycle.
Pool Settings
| Setting | Meaning | Risk when wrong |
|---|---|---|
| Maximum size | Concurrent connections | Too high can overload the database |
| Connection timeout | Wait for a connection | Requests can hang without a bound |
| Idle timeout | Retire unused connections | Stale connections or churn |
| Leak detection | Find long-held connections | Slow requests and pool exhaustion |
Common Mistake and Practices
Common Mistake: Keeping a pooled connection in a field or forgetting to close it. The pool eventually has no available connections even though the application appears healthy.
- Always close borrowed connections.
- Keep database work short.
- Configure pool size from measured capacity.
- Set connection and query timeouts.
- Monitor active, idle, and pending connections.
Real-World Use Case
An API server uses a bounded pool to serve many concurrent course requests without opening hundreds of database sockets. Metrics alert the team when requests wait too long for a connection.
Quiz Check