|
/*
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("<html>");
out.println("<head>");
out.println("<title>Cached Connection Servlet</title>");
out.println("</head>");
out.println("<body>");
// 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() + "<p>");
} 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 + "!<p>");
out.println("You're using a cached connection!<p>");
out.println("</body>");
out.println("</html>");
}
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;
}
}
|
