Simple Solution for Complex Java Web Applications: Connection Pools and JDBC  : Page 3

...
private SimpleConnectionPool(String URI, 
			      String dbuser, 
			      String dbpass,
			      String drivername, 
			      int maxconn) {
	this.URI = URI;
	this.dbuser = dbuser;
	this.dbpass = dbpass;
	this.drivername = drivername;
	this.maxconn = maxconn;

	loadJDBCDriver();
}
...

Because it's defined as private, you can't call SimpleConnectionPool from outside the class. You also need to define your constructor with an access modifier to be able to use the Singleton design pattern in this example. As you can see in the above code, after the constructor executes, you will have the variables set and the JDBC driver loaded.

The loadJDBCDriver() method is easy:

...
private void loadJDBCDriver() {
	try {
		Driver driver = (Driver)Class.forName(this.drivername).newInstance();
		DriverManager.registerDriver(driver);
	} catch (Exception e) {
		System.out.println("Can't load/register JDBC driver ");
	}
}
...

The static method getInstance() creates a single instance of the SimpleConnectionPool class and returns a reference to it. Here, the Singleton design pattern allows you to create only one class instance and then return a reference to any number of requested objects:

...
static synchronized public SimpleConnectionPool getInstance(String URI, 
							String dbuser, 
							String dbpass, 
							String drivername, 
							int maxconn) {
	if (this.ref == null) {
		this.ref = new SimpleConnectionPool(URI, dbuser, dbpass, 
		drivername, maxconn);
	}
	this.clients++;
	return this.ref;
}
...

To get a database connection as a Connection object, use the getConnection() method:

...
public synchronized Connection getConnection() {
	Connection rescon = null;
	if (!this.freeConnections.isEmpty()) {
		rescon = (Connection)this.freeConnections.get(this.freeConnections.size()-1);
		this.freeConnections.remove(rescon);
		try {
			if (rescon.isClosed()) {
				System.out.println("Removed closed 
				connection!");
				// Try again recursively
				rescon = getConnection();
			}
		} catch (SQLException e) {
			System.out.println("Removed closed connection!");
			// Try again recursively
			rescon = getConnection();
		} catch (Exception e) {
			System.out.println("Removed closed connection!");
			// Try again recursively
			rescon = getConnection();
		}
	} else {
		rescon = createConnection();
	}
	return rescon;
}
...

As you can see, the getConnection() method checks the ArrayList container to see whether it has any elements from which it can get an available connection and return it to the client. If it doesn't find any elements (no connections found), it creates a new connection to return to the client. So, getConnection() checks for available connections. In case you get an exception (for any reason), you repeat the process recursively--until you find a connection, you don't create a new connection. When ArrayList doesn't have any connections, you create a new connection using the createConnection() method:

...
private Connection createConnection() {
	Connection rescon = null;
	try {
		if (this.dbuser == null) {
			rescon = DriverManager.getConnection(this.URI);
		} else {
			rescon = DriverManager.getConnection(this.URI, 
			this.dbuser, this.dbpass);
		}
		// new connection in connection pool created
	} catch (SQLException e) {
		System.out.println("Cannot create a new connection!");
		Rescon = null;
	}
	return rescon;
}
...

Also include the returnConnection() method, which returns a connection back to your storehouse and puts it at the end of the list:

...
public synchronized void returnConnection(Connection con) {
	if ((con != null) && (this.freeConnections.size() <= 
	this.maxconn)) {
		this.freeConnections.add(con);
	}
}
...

The last method from SimpleConnectionPool that you need to consider is release(), which closes and releases all connections from your connection pool:

...
public synchronized void release() {
	Iterator allc = this.freeConnections.iterator();
	while (allc.hasNext()) {
		Connection con = (Connection)allc.next();
		try {
			con.close();
		} catch (SQLException e) {
 			System.out.println("Cannot close connection! 
 			 (Probably already closed?)");
		}
	}
	this.freeConnections.clear();
}
...