| |
"Optimize Usage of Your Database"
Vol. 5, Issue 10, p. 74
Listing 1
public class ConnectionObject
{
Connection conn;
boolean connectionOk;
ConnectionObject(Connection c)
{
conn = c;
}
public Connection getConnection()
{
return conn;
}
}
Listing 2
public class TheConnPool
{
int totalConnections = 0;
Vector pool;
TheConnPool(int max)
{
pool = new Vector();
for(int i=0; i<max; i++)
{
ConnectionObject connObj = createConnectionObject();
pool.addElement(connObj);
// Add connection to pool
totalConnections++;
}
}
public synchronized Connection getConnection()
{
if(pool.size() == 0) /* No more available
connections in pool */
return null;
ConnectionObject connObj =
(ConnectionObject)pool.elementAt(0);
pool.removeElementAt(0);
return conn;
}
public synchronized void putConnection(
ConnectionObject connObj)
{
pool.addElement(connObj);
}
public ConnectionObject createConnectionObject()
{
Connection c = .... /* Create a JDBC connection
using the DriverManager
class as you normally do */
return new ConnectionObject(c);
}
}
Listing 3
public class LightweightServer
{
public static void main(String[] args)
{
........
TheConnPool connPool =
new TheConnPool(MAX_CONNECTIONS);
ServerSocket serverSocket =
new ServerSocket(10101);
while( true )
{
Socket clientSocket =
serverSocket.accept();
WorkThread wt =
new WorkThread(clientSocket, connPool);
wt.start();
}
.........
}
}
class WorkThread extends Thread
{
Socket clientSocket = null;
TheConnPool connPool;
WorkThread(Socket cs, TheConnPool cp)
{
clientSocket = cs;
connPool = cp;
}
public synchronized void run()
{
// Get connection object from pool
ConnectionObject connObj =
connPool.getConnectionObject();
// Establish input/output streams for
// communication with client
InputStream is = new
BufferedInputStream(
clientSocket.getInputStream());
java.io.PrintStream ps = new
java.io.PrintStream(
clientSocket.getOutputStream());
// Read data sent by client
byte[] buffer = new byte[1024];
is.read(buffer, 0, 1024);
String inputString = new
String(buffer);
...//Parse the input string and examine
//the first element
// According to the protocol this
// element should tell us which
// application has made the request
if(firstElement.equals("AUTH"))
/* Called by the Apache or the Servlet
authentication client */
processedResult =
executeAuthenticate(connObj,input-
String);
else
if(firstElement.equals("LEVEL"))
processedResult =
executeLevelUpdate(connObj,input-
String);
// Called by the level servlet
// More else ifs for handling other client types
connPool.putConnection(conn); // Return connection to pool
// Send result to the client
ps.print(processedResult);
}
private synchronized String executeAuthenticate(
ConnectionObject connObj, String i)
{
// Extract Connection from connObj and create required Statement
Connection conn = connObj.getConnection();
/* Extract username/password
Verify this against database
using username/password and
Connection c Prepare result
string and send it back
*/
}
private synchronized String executeLevelUpdate(
ConnectiononnObj connObj, String i)
{
// Extract Connection from
connObj and create required Statement
Connection conn = connObj.getConnection();
/* Extract username and level
Connect to database and
set new level for this member
Send a Success or Failure code */
}
}
Listing 4
String HOST = "dummy.server.com";
int PORT = 10101;
String sep = "[SEP]";
Socket clientSocket = new Socket(HOST, PORT);
os = new PrintWriter(
new OutputStreamWriter(
clientSocket.getOutputStream()));
is = new BufferedReader(
new InputStreamReader(
clientSocket.getInputStream()));
String sendVal = "LEVEL" + sep + username +
sep + level;
os.println(sendVal);
os.flush();
String recVal = is.readLine();
os.close();
is.close();
clientSocket.close();
Listing 5
#include <arpa/inet.h>
#include <sys/socket.h>
main()
{
struct hostent *host;
unsigned long address;
unsigned short port = 10101;
char *hostName = "dummy.server.com";
int sock;
char buffer[60];
int bufferlen;
char reslt[2048];
int RESLT_LEN = 2047;
struct sockaddr_in addr;
/* Lookup the host, create a socket and establish connection on port 10101 */
host = gethostbyname(hostName);
address = ((struct in_addr *)host->h_addr_list[0])->s_addr;
addr.sin_addr.s_addr = address;
addr.sin_port = htons(port);
addr.sin_family = AF_INET;
sock=socket(AF_INET, SOCK_STREAM, 0);
connect(sock, (struct sockaddr *)&addr, sizeof(struct sockaddr_in));
/* Put data to be sent to server in variable 'buffer' */
strcpy(buffer, "AUTH[SEP]username[SEP]password";
/* Write 'buffer' to the output stream */
bufferlen=strlen(buffer);
write(sock, buffer,bufferlen);
/* Read result sent by the server */
read(sock, reslt, RESLT_LEN);
}
Listing 6
TheConnPool(int max)
{
....
MonitorThread mt = new MonitorThread(this);
mt.start();
}
public synchrnonized void addConnectionObjects(int n)
{
for(int i=0; i<n; i++)
{
ConnectionObject connObj = createConnectionObject();
pool.addElement(connObj); // Add connection to pool
totalConnections++;
}
}
public synchronized void removeConnectionObjects(int n)
{
for(int i=0; i<n; i++)
{
ConnectionObject connObj = pool.elementAt(0);
try{
Connection conn = connObj.getConnection();
conn.close();
}catch(Exception ex){}
pool.removeElementAt(0);
totalConnections--;
}
}
Listing 7
class MonitorThread extends Thread
{
int SOFT_LIMIT=5;
int MID_LIMIT=10;
int HARD_LIMIT=20;
int SLEEP_TIME = 60000; /* One minute */
TheConnPool tc;
MonitorThread(TheConnPool t)
{
tc = t;
}
public void run()
{
while(true)
{
int connsAvailable = tc.pool.size();
int totalConns = tc.totalConnections;
int connsBeingUsed = totalConns - connsAvailable;
boolean increaseConns = false, reduceConns = false;
int increaseBy = 0, reduceBy = 0;
if(connsBeingUsed < SOFT_LIMIT && totalConns > SOFT_LIMIT)
{
reduceConns = true;
reducyBy = totalConns - SOFT_LIMIT;
}
else
if(connsBeingUsed < MID_LIMIT && totalConns > MID_LIMIT)
{
reduceConns = true;
reducyBy = totalConns - MID_LIMIT;
}
else
if(connsAvailable == 0 && totalConns < MID_LIMIT)
{
increaseConns = true;
increaseBy = MID_LIMIT - totalConns;
}
else
if(connsAvailable == 0 && totalConns < HARD_LIMIT)
{
increaseConns = true;
increaseBy = HARD_LIMIT - totalConns;
}
if(reduceConns)
tc.removeConnectionObjects(reduceBy);
else
if(increaseConns)
tc.increaseConnectionObjects(increaseBy);
try{
sleep(SLEEP_TIME);
}catch(InterruptedException ie){}
}
}
}
Listing 8
public class ConnectionObject
{
Connection conn;
boolean connectionOk;
Hashtable stmts;
public setStatements(Hashtable h)
{
stmts = h;
}
public Statement getStatement(String nm)
{
return (Statement)stmts.get(nm);
}
......
Listing 9
public class TheConnPool
{
public ConnectionObject createConnectionObject()
{
Connection c = .... /* Create a JDBC connection using the
DriverManager class as you normally do */
ConnectionObject connObj = new ConnectionObject(c);
Hashtable stmts = new Hashtable();
PreparedStatement pstmt =
c.prepareStatement(
"UPDATE member_table " +
" SET level = ? WHERE username = ?");
stmts.put("LEVEL_STMT", pstmt);
CallableStatement cstmt =
c.prepareCall(
"{call Auth_Api.authenticate(?,?,?,?)}");
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.registerOutParameter(4, Types.VARCHAR);
stmts.put("LEVEL_STMT", pstmt);
stmts.put("AUTH_STMT", cstmt);
connObj.setStatements(stmts);
return connObj;
}
Listing 10
private synchronized String executeAuthenticate(
ConnectionObject connObj,
String i)
{
// Extract required Statement connObj
CallableStatement cstmt = (CallableStatement)connObj.getStatement(
"AUTH_STMT");
// Execute the query using this pre-created statement
}
Listing 11
public class AuthWorker implements ServerWorker
{
public String execute(ConnectionObject connObj, String inp)
{
String result = "";
.... // Parse the username/password
try{
PreparedStatement pstmt =
connObj.getStatement("AUTH_STMT");
.....
}catch(Exception ex){}
return result;
}
}
Listing 12
public synchronized void run()
{
// Get connection object from pool
ConnectionObject connObj =
connPool.getConnectionObject();
....
// First element contains the name of
// the class that can handle the request.
Class c = Class.forName(firstElement);
// Create an instance of this class and invoke the execute method
ServerWorker sw = (ServerWorker)c.newInstance();
String result = sw.execute(connObj, inputString);
....
|
|
