How to Establish a Connection Pool in JDBC

1. Connection Pool
2. Connection Pool with JDBC

JDBC is a specification from Sun Microsystems that provides an API for Java applications to perform different operations like read and write with various databases. JDBC provides a language for the database connectivity standards, and you can write programs required for the database access.

The core purpose of JDBC is to interact with databases efficiently. The interfaces and classes of JDBC allow the application to send a request to the specified database.

The efficient interaction between database and program can be done with the help of the Open Database Connectivity (ODBS) driver. JDBC uses this driver to communicate with different databases such as MS-Access, SQL server database, Oracle, and MySQL.

Connection Pool

It’s a mechanism to create and maintain a collection of JDBC connection objects. The main objective of maintaining the pool of connection objects is to improve the reusability and performance of the application.

It’s a process where we maintain a cache of a database connection. As the user requests, these objects are created in advance and stored in a pool; they acquire them from the pool.

Further, creating a database connection is always an expensive and time-consuming operation, and when there are multiple connections, it’s an expensive job to do so.

But the connection pool is here to help us; it reuses the connections each time requested instead of recreating the connection. In the JDBC connection pool, a pool of connection objects is created at run time when the application server starts.

A pool manager further manages these objects, dispersing connections as clients request.

A connection pooling framework requires the following three main tasks.

1. Creating the connection objects
2. Managing and validating the usage of created objects
3. Destroy or release objects

Connection Pool with JDBC

In Java, when you connect an application with data, the method DriverManager.getConnection() creates a JDBC connection.

The URL used depends upon the specific database and JDBC driver. But remember, it always begins with the jdbc:.

Connection con = DriverManager.getConnection(
    "jdbc: JDBC Vendor: More data jdbc vendor needed", "Login", "Password");
try {
  /* You can use this block for connection */
} finally {
  // Do not forget to close the connection when you are done with it.
  try {
    con.close();
  } catch (Throwable e) {
    /* Propagate the original exception instead of this one that you want just logged */
    logger.warn("User defined message for the exception", e);
  }
}

After a successful connection is established, this statement can be created.

try (Statement stmt = conn.createStatement()) {
  stmt.executeUpdate("INSERT INTO Table(Table Name) VALUES ('my name')");
}