Establishing a connection to a database can be done in two primary ways.
Traditional Connection Approach:
In this method, the Class.forName() method is first called to load the database driver. Subsequently, DriverManager.getConnection() is invoked to establish a connection.
Connection Pool Approach:
This strategy involves pre-establishing a number of database connection objects upon application startup and storing them in a managed pool. When a cliant request arrives, a connection object is retrieved from the pool for servicing. Upon request completion, the client program calls close(), which returns the object to the pool. Requests exceeding the pool's capacity are queued. The application can dynamically adjust the number of connections in the pool based on usage metrics.
Problems with the Traditional Approach
Each execution of a Data Manipulation Language (DML) or Data Query Language (DQL) operation necessitates the creation and subsequent destruction of a Connection object. Since Connection objects are ruesable, this continual creation is unnecessary. The processes of object creation and destruction consume significant system resources. Connection pooling addresses this issue by enabling the repeated use of connection objects.
Advantages of Using a Connection Pool
- Pre-initializing a set of connection objects avoids the overhead of creating them on-demand during periods of high concurrent traffic, reducing computational load and improving application response times.
- Reusing connection objects drastically cuts down on the frequency of their creation, thereby conserving resources.
Implementation Details
1. Define the Connection Pool Class
package com.msb.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
public class ConnectionManager {
private static String driver;
private static String url;
private static String user;
private static String password;
private static int initialPoolSize;
private static int maximumPoolSize;
private static LinkedList<Connection> connectionPool;
static {
PropertiesLoader loader = new PropertiesLoader("/dbconfig.properties");
driver = loader.getProperty("driver");
url = loader.getProperty("url");
user = loader.getProperty("user");
password = loader.getProperty("password");
initialPoolSize = Integer.parseInt(loader.getProperty("initialPoolSize"));
maximumPoolSize = Integer.parseInt(loader.getProperty("maximumPoolSize"));
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
connectionPool = new LinkedList<>();
for (int i = 0; i < initialPoolSize; i++) {
Connection conn = createNewConnection();
if (conn != null) {
connectionPool.add(conn);
System.out.println("Initialized connection " + conn.hashCode() + " added to pool.");
}
}
}
private static Connection createNewConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static Connection obtainConnection() {
Connection conn = null;
if (!connectionPool.isEmpty()) {
conn = connectionPool.removeFirst();
System.out.println("Connection obtained from pool: " + conn.hashCode());
} else {
conn = createNewConnection();
System.out.println("Pool empty, created new connection: " + conn.hashCode());
}
return conn;
}
public static void releaseConnection(Connection conn) {
if (conn != null) {
try {
if (!conn.isClosed()) {
if (connectionPool.size() < maximumPoolSize) {
try {
conn.setAutoCommit(true);
System.out.println("Set auto-commit to true for connection: " + conn.hashCode());
} catch (SQLException e) {
e.printStackTrace();
}
connectionPool.addLast(conn);
System.out.println("Connection returned to pool: " + conn.hashCode());
} else {
conn.close();
System.out.println("Pool full, connection closed: " + conn.hashCode());
}
} else {
System.out.println("Connection already closed: " + conn.hashCode());
}
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("Cannot release a null connection.");
}
}
}
2. Modify the Base Data Access Object (DAO)
The BaseDAO is updated to utilize the connection pool for acquiring and releasing connections.
package com.msb.dao;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public abstract class BaseDataAccessObject {
protected int executeUpdate(String sql, Object... params) {
Connection conn = null;
PreparedStatement stmt = null;
int affectedRows = 0;
try {
conn = ConnectionManager.obtainConnection();
stmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
affectedRows = stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
}
ConnectionManager.releaseConnection(conn);
}
return affectedRows;
}
protected List<?> executeQuery(Class<?> modelClass, String sql, Object... params) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<Object> resultList = null;
try {
conn = ConnectionManager.obtainConnection();
stmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
rs = stmt.executeQuery();
resultList = new ArrayList<>();
Field[] fields = modelClass.getDeclaredFields();
for (Field f : fields) {
f.setAccessible(true);
}
while (rs.next()) {
Object instance = modelClass.getDeclaredConstructor().newInstance();
for (Field f : fields) {
String columnName = f.getName();
Object columnValue = rs.getObject(columnName);
f.set(instance, columnValue);
}
resultList.add(instance);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
}
ConnectionManager.releaseConnection(conn);
}
return resultList;
}
}
3. Store Configuration Parameters in a Properties File
Create a configuration file named dbconfig.properties in the source directory.
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
user=root
password=root
initialPoolSize=1
maximumPoolSize=1
4. Implement a Properties Utility Class
A helper class loads configuration from the properties file.
package com.msb.util;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
public class PropertiesLoader {
private Properties config;
public PropertiesLoader(String filePath) {
config = new Properties();
InputStream input = this.getClass().getResourceAsStream(filePath);
try {
config.load(input);
} catch (IOException e) {
e.printStackTrace();
}
}
public String getProperty(String key) {
return config.getProperty(key);
}
}
The ConnectionManager class is then modified to use PropertiesLoader to read its configuration parameters from the properties file, as shown in its static initializer block above.