What is the role of a database connection pool and why is it used?

A database connection pool is a set of pre-opened and ready-to-use connections that an application can reuse. Its role is to improve performance and reduce the overhead of establishing connections, as creating a new database connection is a time-consuming and resource-intensive operation.

Using a pool allows:

• Fast access to the database without connection setup delays.
• Controlling the maximum number of concurrent connections to prevent database overload.
• Efficient resource management and improved application scalability.

Example of using a pool in Java with HikariCP:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
HikariDataSource ds = new HikariDataSource(config);

try (Connection conn = ds.getConnection()) {
    // Work with the database
}