java code for Cached Connection Servlet

/*

Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O'Reilly

*/


/*
Defining the Table: Oracle 9i

The following defines a table based on Oracle 9i:

create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody CLOB
);

Defining the Table: MySQL

The following defines a table based on MySQL:

create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody TEXT
);


*/


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.MissingResourceException;
import java.util.ResourceBundle;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class CachedConnectionServlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {

response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");

// let's turn on verbose output
CacheConnection.setVerbose(true);

// now let's get a cached connection
Connection connection = CacheConnection.checkOut();

Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
// test the connection
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("DedicatedConnection.doGet() SQLException: "
+ e.getMessage() + "

");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}

// let's return the conection
CacheConnection.checkIn(connection);

out.println("Hello " + userName + "!

");
out.println("You're using a cached connection!

");
out.println("");
out.println("");
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}

class CacheConnection {
private static boolean verbose = false;

private static int numberConnections = 0;

private static Vector cachedConnections = new Vector();

private static Thread monitor = null;

private static long MAX_IDLE = 1000 * 60 * 60;

synchronized public static Connection checkOut() {
return checkOut("Database");
}

synchronized public static Connection checkOut(String baseName) {
boolean found = false;
CachedConnection cached = null;

if (verbose) {
System.out.println("There are "
+ Integer.toString(numberConnections)
+ " connections in the cache");
System.out.println("Searching for a connection not in use...");
}
for (int i = 0; !found && i < numberConnections; i++) {
if (verbose) {
System.out.println("Vector entry " + Integer.toString(i));
}
cached = (CachedConnection) cachedConnections.get(i);
if (!cached.isInUse() && cached.getBaseName().equals(baseName)) {
if (verbose) {
System.out.println("found cached entry "
+ Integer.toString(i) + " for " + baseName);
}
found = true;
}
}
if (found) {
cached.setInUse(true);
} else {
if (verbose) {
System.out.println("Cached entry not found ");
System.out.println("Allocating new entry for " + baseName);
}
cached = new CachedConnection(Database.getConnection(baseName),
true, baseName);
cachedConnections.add(cached);
numberConnections++;
}

if (monitor == null) {
monitor = new Thread(new Runnable() {
public void run() {
while (numberConnections > 0) {
runMonitor();
}
monitor = null;
if (verbose) {
System.out.println("CacheConnection monitor stopped");
}
}
});
monitor.setDaemon(true);
monitor.start();
}
return cached.getConnection();
}

synchronized public static void checkIn(Connection c) {
boolean found = false;
boolean closed = false;
CachedConnection cached = null;
Connection conn = null;
int i = 0;

if (verbose) {
System.out.println("Searching for connection to set not in use...");
}
for (i = 0; !found && i < numberConnections; i++) {
if (verbose) {
System.out.println("Vector entry " + Integer.toString(i));
}
cached = (CachedConnection) cachedConnections.get(i);
conn = cached.getConnection();
if (conn == c) {
if (verbose) {
System.out.println("found cached entry "
+ Integer.toString(i));
}
found = true;
}
}
if (found) {
try {
closed = conn.isClosed();
} catch (SQLException ignore) {
closed = true;
}
if (!closed)
cached.setInUse(false);
else {
cachedConnections.remove(i);
numberConnections--;
}
} else if (verbose) {
System.out.println("In use Connection not found!!!");
}
}

synchronized private static void checkUse() {
CachedConnection cached = null;
Connection conn = null;
int i = 0;
long now = System.currentTimeMillis();
long then = 0;

for (i = numberConnections - 1; i > -1; i--) {
if (verbose) {
System.out
.println("CacheConnection monitor checking vector entry "
+ Integer.toString(i) + " for use...");
}
cached = (CachedConnection) cachedConnections.get(i);
if (!cached.isInUse()) {
then = cached.getLastUsed();
if ((now - then) > MAX_IDLE) {
if (verbose) {
System.out.println("Cached entry "
+ Integer.toString(i)
+ " idle too long, being destroyed");
}
conn = cached.getConnection();
try {
conn.close();
} catch (SQLException e) {
System.err.println("Unable to close connection: "
+ e.getMessage());
}
cachedConnections.remove(i);
numberConnections--;
}
}
}
}

private static void runMonitor() {
checkUse();
if (numberConnections > 0) {
if (verbose) {
System.out.println("CacheConnection monitor going to sleep");
}
try {
// 1000 milliseconds/second x 60 seconds/minute x 5 minutes
monitor.sleep(1000 * 60 * 5);
} catch (InterruptedException ignore) {
if (verbose) {
System.out
.println("CacheConnection monitor's sleep was interrupted");
}
}
}
}

public void finalize() throws Throwable {
CachedConnection cached = null;
for (int i = 0; i < numberConnections; i++) {
cached = (CachedConnection) cachedConnections.get(i);
if (cached.getConnection() != null) {
if (verbose) {
System.out.println("Closing connection on Vector entry "
+ Integer.toString(i));
}
try {
cached.getConnection().close();
} catch (SQLException ignore) {
System.err.println("Can't close connection!!!");
}
}
}
numberConnections = 0;
}

public static void setVerbose(boolean v) {
verbose = v;
}
}

class CachedConnection {
private boolean inUse;

private Connection conn;

private long lastUsed;

private String baseName;

public CachedConnection() {
conn = null;
inUse = false;
lastUsed = System.currentTimeMillis();
baseName = "Database";
}

public CachedConnection(Connection conn, boolean inUse) {
this.conn = conn;
this.inUse = inUse;
this.lastUsed = System.currentTimeMillis();
this.baseName = "Database";
}

public CachedConnection(Connection conn, boolean inUse, String baseName) {
this.conn = conn;
this.inUse = inUse;
this.lastUsed = System.currentTimeMillis();
this.baseName = baseName;
}

public Connection getConnection() {
return conn;
}

public void setConnection(Connection conn) {
this.conn = conn;
}

public boolean getInUse() {
return inUse;
}

public boolean isInUse() {
return inUse;
}

public void setInUse(boolean inUse) {
if (!inUse)
lastUsed = System.currentTimeMillis();
this.inUse = inUse;
}

public String getBaseName() {
return baseName;
}

public void setBaseName(String baseName) {
this.baseName = baseName;
}

public long getLastUsed() {
return lastUsed;
}
}

class Database {
private static boolean verbose = false;

public static final Connection getConnection(String baseName) {
Connection conn = null;
String driver = null;
String url = null;
String username = null;
String password = null;
try {
ResourceBundle resb = ResourceBundle.getBundle(baseName);
driver = resb.getString("database.driver");
url = resb.getString("database.url");
username = resb.getString("database.username");
password = resb.getString("database.password");
Class.forName(driver);
} catch (MissingResourceException e) {
System.err.println("Missing Resource: " + e.getMessage());
return conn;
} catch (ClassNotFoundException e) {
System.err.println("Class not found: " + e.getMessage());
return conn;
}
try {
if (verbose) {
System.out.println("baseName=" + baseName);
System.out.println("driver=" + driver);
System.out.println("url=" + url);
System.out.println("username=" + username);
System.out.println("password=" + password);
}

conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
System.err.println(e.getMessage());
System.err.println("in Database.getConnection");
System.err.println("on getConnection");
conn = null;
} finally {
return conn;
}
}

public static void setVerbose(boolean v) {
verbose = v;
}
}