jndi+rmi+proxool应用负载下的远程数据源2

问题的由来和分析，请看http://mazhen2010.iteye.com/blog/654366

远程数据源应用于正式环境有一段时间了。期间未收到bug提交。
查看了从6月2日-6月12日的日志，发现两种error错误。经调查这两种错误都是由于业务逻辑错误造成的，和数据源无关。（意外收获，还能检查逻辑漏洞）
学习了微软的was性能测试工具，想做个全面的性能测试。

以TTLB Avg为参考值，远程数据源相较普通方式性能提升18.11%
5分钟内点击量增加271次，每秒连接数增加0.91次
名词解释：TTFB Avg：从第一个请求发出到测试工具接收到服务器应答数据的第一个字节之间的平均时间。
TTLB Avg：从第一个请求发出到测试工具接收到服务器应答数据的最后一个字节之间的平均时间

[b]虽然应用到了实际项目中，但这个项目pv不是很高。现在不太敢用到大型项目中，把服务端代码公布出来，如果有兴趣，可以看看，帮我找找问题，共同探讨。[/b]

[b]为每个proxool别名创建一个对象，并保存在内存中[/b]
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.util.Hashtable;

import org.apache.log4j.Logger;
import org.logicalcobwebs.proxool.ProxoolFacade;

public class ConnectionFactoryPools extends UnicastRemoteObject implements Factory {

/**
*
*/
private static final long serialVersionUID = -9094038611699927527L;
private static Logger log = Logger.getLogger(ConnectionFactoryPools.class);
private static Hashtable<String, ConnectionFactory> factoryPools = null;
static {
String[] aliases = ProxoolFacade.getAliases();
if (aliases.length > 0) {
factoryPools = new Hashtable<String, ConnectionFactory>();
for (int i = 0; i < aliases.length; i++) {
ConnectionFactory factory = new ConnectionFactory(aliases[i]);
factoryPools.put(aliases[i], factory);
}

}
}

public ConnectionFactoryPools() throws RemoteException {}

public String authenticateAlias(ConnectionParam param) throws RemoteException {

log.info("Authenticate alias=" + param.getAlias());
String aliasKey = "90";
if (factoryPools != null && param.getAlias() != null) {
ConnectionFactory factory = factoryPools.get(param.getAlias());
if (factory != null) {
int flag = factory.checkConConfig(param);
if (flag == 0) {
aliasKey = factory.toString();
}
} else {
aliasKey = "91";
}
}
return aliasKey;
}

/**
* <p>Title: createQueryConnection</p>
* <p>Description: 创建查询的connection环境</p>
* @author mazhen
* @return 0:参数正常 1：参数不全 2: 连接池中没有数据源
* 3：用户名错误 4：密码错误 5：没有指定别名的数据源
* 90:工厂池为空 91:指定的数据库别名没有工厂对象 99:异常
*/
public String createQueryConnection(String alias, String aliasKey, String sql) throws RemoteException {

String conKey = "90";
if (factoryPools != null && aliasKey != null && alias != null) {
ConnectionFactory factory = factoryPools.get(alias);
if (factory != null && aliasKey.equals(factory.toString())) {
conKey = factory.createQueryConnection(sql);
} else {
conKey = "91";
}
}
return conKey;
}

/**
* <p>Title: createQueryConnection</p>
* <p>Description: 创建查询的connection环境</p>
* @author mazhen
* @return 0:参数正常 1：参数不全 2: 连接池中没有数据源
* 3：用户名错误 4：密码错误 5：没有指定别名的数据源
* 90:工厂池为空 91:指定的数据库别名没有工厂对象 99:异常
*/
public String createQueryConnection(ConnectionParam param, String sql) throws RemoteException {

String conKey = "90";
if (factoryPools != null && param.getAlias() != null) {
ConnectionFactory factory = factoryPools.get(param.getAlias());
if (factory != null) {
conKey = factory.createQueryConnection(param, sql);
} else {
conKey = "91";
}
}
return conKey;
}

public void releaseQueryConnection(String alias, String conKey) throws RemoteException {

MConnection con = (MConnection) NamingManager.lookup(conKey);
boolean flag = false;
if (factoryPools != null && alias != null) {
ConnectionFactory factory = factoryPools.get(alias);
if (factory != null) {
flag = factory.activateConnectionInPools(con.getSql());
}
}
//工厂激活链接失败，则强制关闭链接
if (flag == false) {
con.releaseQueryConfig();
}
}

/**
* <p>Title: createProcedureConnection</p>
* <p>Description: 创建存储过程的connection环境</p>
* @author mazhen
* @return 0:参数正常 1：参数不全 2: 连接池中没有数据源
* 3：用户名错误 4：密码错误 5：没有指定别名的数据源
* 90:工厂池为空 91:指定的数据库别名没有工厂对象 99:异常
*/
public String createProcedureConnection(ConnectionParam param, String procedure) throws RemoteException {
String conKey = "90";
if (factoryPools != null && param.getAlias() != null) {
ConnectionFactory factory = factoryPools.get(param.getAlias());
if (factory != null) {
conKey = factory.createProcedureConnection(param, procedure);
} else {
conKey = "91";
}
}
return conKey;
}
}

[b]
proxool别名工厂，创建链接和释放。会缓存常用sql的链接，以提升效率[/b]

import java.rmi.RemoteException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Properties;
import java.util.Map.Entry;
import java.lang.ClassNotFoundException;

import org.apache.log4j.Logger;
import org.logicalcobwebs.proxool.ConnectionPoolDefinitionIF;
import org.logicalcobwebs.proxool.ProxoolFacade;

import com.changyou.proxool.PBEStringDecryptor;

public class ConnectionFactory {

//private boolean isEncrypt = true; //Is password been encrypted?
private int maxCount = 2; //use proxool's MinimumConnectionCount
private String alias = null;
private String user = null;
private String password = null;
private static Hashtable<String, MConnectionVo> connectionPools = null;

private static Logger log = Logger.getLogger(ConnectionFactory.class);

public ConnectionFactory(String alias) {

log.info("Initialising data source '" + alias + "' start.");
try {
ConnectionPoolDefinitionIF def = ProxoolFacade.getConnectionPoolDefinition(alias);
maxCount = def.getMinimumConnectionCount();

Properties p = def.getDelegateProperties();
Iterator j = p.keySet().iterator();
while (j.hasNext()) {
String name = (String)j.next();
String value = p.getProperty(name);
//比较用户名
if ((name.toLowerCase().indexOf("user") > -1)) {
this.user = value;
}
//比较密码
if ((name.toLowerCase().indexOf("password") > -1)
|| (name.toLowerCase().indexOf("passwd") > -1)) {
this.password = value;
}
}

this.alias = alias;
log.info("Initialising data source '" + alias + "' success.");
} catch(Exception e) {
maxCount = 2;
alias = null;
user = null;
password = null;
log.error("Initialising data source '" + alias + "' failed.");
log.error(e.toString());
}
connectionPools = new Hashtable<String, MConnectionVo>();
}
/**
* <p>Title: createQuerySource</p>
* <p>Description: 利用sql创建数据库链接和preparedStatement</p>
* @author mazhen
* @param sql
* @return 0:参数正常 1：参数不全 2: 连接池中没有数据源
* 3：用户名错误 4：密码错误 5：没有指定别名的数据源 99:异常
*/
public synchronized String createQueryConnection(ConnectionParam param, String sql) {

String conKey = "99";
int flag = checkConConfig(param);
if (flag == 0) {
//首先看看常用sql的链接池中是否有这个sql的链接
String hasCon = getConnectionFromPools(sql);
if (hasCon != null) {
//如果池中有此sql链接，则直接掉用池中的链接
conKey = hasCon;
} else {
//没有链接或者链接被占用
try {
Connection connection = DriverManager.getConnection("proxool." + alias);
PreparedStatement statement = connection.prepareStatement(sql);
MConnection mcon = new MConnectionImpl(connection, statement);
mcon.setSql(sql);
conKey = mcon.toString();
NamingManager.bind(conKey, mcon);
//如果池中没有此sql的链接，则添加一个，如果有则重置最后访问时间
addConnectionToPools(sql, mcon);

} catch (Exception e) {
log.error("Created query connection with user and password!alias=" + alias);
log.error("sql=" + sql);
log.error(e.toString());
}
}


} else {
conKey = new Integer(flag).toString();
}
return conKey;
}

public synchronized String createQueryConnection(String sql) {

String conKey = "99";

//首先看看常用sql的链接池中是否有这个sql的链接
String hasCon = getConnectionFromPools(sql);
if (hasCon != null) {
//如果池中有此sql链接，则直接掉用池中的链接
conKey = hasCon;
} else {
//没有链接或者链接被占用
try {
Connection connection = DriverManager.getConnection("proxool." + alias);
PreparedStatement statement = connection.prepareStatement(sql);
MConnection mcon = new MConnectionImpl(connection, statement);
mcon.setSql(sql);
conKey = mcon.toString();
NamingManager.bind(conKey, mcon);
//如果池中没有此sql的链接，则添加一个，如果有则重置最后访问时间
addConnectionToPools(sql, mcon);

} catch (Exception e) {
log.error("Created query connection without user and password!alias=" + this.alias);
log.error("sql=" + sql);
log.error(e.toString());
}
}
return conKey;
}

/**
* <p>Title: createProcedureConnection</p>
* <p>Description: 检查数据库链接别名，用户名，密码是否正确</p>
* @author mazhen
* @param procedure
* @return 0:参数正常 1：参数不全 2: 连接池中没有数据源
* 3：用户名错误 4：密码错误 5：没有指定别名的数据源 99:异常
*/
public synchronized String createProcedureConnection(ConnectionParam param, String sql) {

String conKey = "99";
int flag = checkConConfig(param);
if (flag == 0) {
//首先看看常用sql的链接池中是否有这个sql的链接
String hasCon = getConnectionFromPools(sql);
if (hasCon != null) {
//如果池中有此sql链接，则直接掉用池中的链接
conKey = hasCon;
} else {
//没有链接或者链接被占用
try {
Connection connection = DriverManager.getConnection("proxool." + alias);
CallableStatement statement = connection.prepareCall(sql);
MConnection mcon = new MConnectionImpl(connection, statement);
mcon.setSql(sql);
conKey = mcon.toString();
NamingManager.bind(conKey, mcon);
//如果池中没有此sql的链接，则添加一个，如果有则重置最后访问时间
addConnectionToPools(sql, mcon);

} catch (Exception e) {
log.error("Created Procedure connection with user and password!alias=" + alias);
log.error("sql=" + sql);
log.error(e.toString());
}
}


} else {
conKey = new Integer(flag).toString();
}
return conKey;
}

public synchronized String createProcedureConnection(String sql) {

String conKey = "99";

//首先看看常用sql的链接池中是否有这个sql的链接
String hasCon = getConnectionFromPools(sql);
if (hasCon != null) {
//如果池中有此sql链接，则直接掉用池中的链接
conKey = hasCon;
} else {
//没有链接或者链接被占用
try {
Connection connection = DriverManager.getConnection("proxool." + alias);
CallableStatement statement = connection.prepareCall(sql);
MConnection mcon = new MConnectionImpl(connection, statement);
mcon.setSql(sql);
conKey = mcon.toString();
NamingManager.bind(conKey, mcon);
//如果池中没有此sql的链接，则添加一个，如果有则重置最后访问时间
addConnectionToPools(sql, mcon);

} catch (Exception e) {
log.error("Created Procedure connection without user and password!alias=" + this.alias);
log.error("sql=" + sql);
log.error(e.toString());
}
}
return conKey;
}

/**
* <p>Title: checkConConfig</p>
* <p>Description: 检查数据库链接别名，用户名，密码是否正确</p>
* @author mazhen
* @return 0:参数正常 1：参数不全 2: 连接池中没有数据源
* 3：用户名错误 4：密码错误 5：没有指定别名的数据源 99:异常
*/
public int checkConConfig(ConnectionParam param) {
int flag = 99;
if (param.getAlias() == null || param.getUser() == null || param.getPassword() == null) {
//1:参数不全
flag = 1;
} else {
if (param.getAlias().equals(this.alias) && user != null && password != null) {

//比较用户名
if (param.getUser().equals(user) == false) {
//3:用户名错误
flag = 3;
}

//比较密码
String dePassword = param.getPassword();
try {
//利用了运行时异常和编译时异常，在有PBEStringEncryptor时，则解密
//没有PBEStringEncryptor这个接口时，虽然PBEStringDecryptor编译错误，但是当前工厂可以编译通过，且不走PBEStringDecryptor的分支
if (Class.forName("org.jasypt.encryption.pbe.PBEStringEncryptor").isInterface() == true) {
dePassword = PBEStringDecryptor.decrypt(dePassword);
if (dePassword == null) {
dePassword = param.getPassword();
}
}
} catch (ClassNotFoundException c) {
log.info("Don't need decrypt! alias=" + param.getAlias());
} catch (Exception e) {
log.error("PBEStringEncryptor decrypt password faild! alias=" + param.getAlias());
log.error(e.toString());
}
if (password.equals(dePassword) == false) {
//4:密码错误
flag = 4;
}

//0:参数正确
if (flag != 3 && flag != 4) {
flag = 0;
}
}
}
return flag;
}

/**
* <p>Title: getConnectionFromPools</p>
* <p>Description: 查找连接池中是否存在此sql的链接</p>
* @author mazhen
* @return null:连接池中没有此sql的链接 con:连接的字符串
*/
private String getConnectionFromPools(String sql) {
String returnValue = null;
MConnectionVo vo = connectionPools.get(sql);
if (vo != null) {

//验证此链接是否还活着？
try {
if (vo.getCon().checkClose() == false) {
if (vo.isUsing() == false) {
vo.setUsing(true);
vo.setLastUsedTime(System.currentTimeMillis());
returnValue = vo.getCon().toString();
}
} else {
connectionPools.remove(sql);
}
} catch(RemoteException e) {
connectionPools.remove(sql);
log.error("Check connection is openning faild!alias=" + this.alias);
log.error("sql=" + sql);
log.error(e.toString());
}

}
return returnValue;
}

/**
* <p>Title: addConnectionToPools</p>
* <p>Description: 向连接池中添加链接</p>
* @author mazhen
*/
private void addConnectionToPools(String sql, MConnection con) {
//如果此sql对应的链接在池中存在
MConnectionVo vo = connectionPools.get(sql);
if (vo != null) {
vo.setLastUsedTime(System.currentTimeMillis());
return;
}

if (connectionPools.size() < this.maxCount) {
vo = new MConnectionVo();
vo.setCon(con);
connectionPools.put(sql, vo);
} else {

//找到访问时间最久远的sql
Iterator it = connectionPools.entrySet().iterator();
String lastUsedSql = null;
long lastUsedTime = System.currentTimeMillis();
while (it.hasNext()) {
Entry<String, MConnectionVo> entry = (Entry) it.next();
MConnectionVo value = entry.getValue();
if (lastUsedTime > value.getLastUsedTime()) {
lastUsedTime = value.getLastUsedTime();
lastUsedSql = entry.getKey();
}
}

//release last connection
try {
vo = connectionPools.get(lastUsedSql);
vo.getCon().releaseQueryConfig();
vo.getCon().releaseProcedureConfig();
vo = null;
} catch(Exception e) {
log.error("Release last connection faild!sql=" + sql);
} finally {
vo = null;
}

vo = new MConnectionVo();
vo.setCon(con);
connectionPools.remove(lastUsedSql);
connectionPools.put(sql, vo);

}
}

/**
* <p>Title: activateConnectionInPools</p>
* <p>Description: 激活链接，供下次访问使用</p>
* @author mazhen
* @return true:激活成功 false:激活失败
*/
public boolean activateConnectionInPools(String sql) {
boolean returnValue = false;
MConnectionVo vo = connectionPools.get(sql);
if (vo != null) {
vo.setUsing(false);
returnValue = true;
}
return returnValue;
}
}

[b]链接对象，处理数据库操作[/b]

import java.rmi.server.UnicastRemoteObject;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;
import java.util.HashMap;
import java.sql.ResultSetMetaData;
import java.sql.Types;

import javax.sql.rowset.CachedRowSet;

import org.apache.log4j.Logger;

//import com.sun.rowset.CachedRowSetImpl;
//import com.sun.rowset.internal.Row;

public class MConnectionImpl extends UnicastRemoteObject implements MConnection {

/**
*
*/
private static final long serialVersionUID = -2338044775116389138L;
private String sql = null;
private static Logger log = Logger.getLogger(MConnectionImpl.class);

public MConnectionImpl(Connection con, PreparedStatement statement) throws RemoteException {
this.connection = con;
this.preparedStatement = statement;
}

public MConnectionImpl(Connection con, CallableStatement statement) throws RemoteException {
this.connection = con;
this.callableStatement = statement;
}

private Connection connection = null;
private PreparedStatement preparedStatement = null;
private CallableStatement callableStatement = null;
private ResultSet resultSet = null;
private CachedRowSet rowSet = null;

public void releaseQueryConfig() throws RemoteException {
try {
if (connection != null) {
NamingManager.unbind(this.toString());
connection.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch(Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
}

public void releaseProcedureConfig() throws RemoteException {
try {
if (connection != null) {
NamingManager.unbind(this.toString());
connection.close();
}
if (callableStatement != null) {
callableStatement.close();
}
} catch(Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
}

public void setStringPreparedSmt(int index, String parameter) throws RemoteException {
try {
if (this.preparedStatement != null) {
this.preparedStatement.setString(index, parameter);
}
} catch (Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}

}

public void setIntPreparedSmt(int index, int parameter) throws RemoteException {
try {
if (this.preparedStatement != null) {
this.preparedStatement.setInt(index, parameter);
}
} catch (Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}

}

public void setDatePreparedSmt(int index, Date parameter) throws RemoteException {
try {
if (this.preparedStatement != null) {
this.preparedStatement.setDate(index, parameter);
}
} catch (Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}

}

public void execPreparedSmt() throws RemoteException {
try {
//long st_temp = System.currentTimeMillis();
this.resultSet = this.preparedStatement.executeQuery();
//long end_temp = System.currentTimeMillis();
//System.out.println("execute used "+(end_temp - st_temp));
} catch(Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
}

public int execPreparedSmtForUpdate() throws RemoteException {
int returnValue = -1;
try {
//long st_temp = System.currentTimeMillis();
returnValue = this.preparedStatement.executeUpdate();
//long end_temp = System.currentTimeMillis();
//System.out.println("execute used "+(end_temp - st_temp));
} catch(Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
return returnValue;
}

// public void setStringCallableSmt(int index, String parameter) throws RemoteException {
// try {
// if (this.callableStatement != null) {
// this.callableStatement.setString(index, parameter);
// }
// } catch (Exception e) {
// e.printStackTrace();
// }
//
// }
//
// public void execCallableSmt() throws RemoteException {
// try {
// this.resultSet = this.preparedStatement.executeQuery();
// } catch(Exception e) {
// e.printStackTrace();
// }
// }

public String getStringResult(String column) throws RemoteException {
String returnValue = null;
try {
if (this.resultSet.next()) {
returnValue = this.resultSet.getString(column);
}
} catch(SQLException e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
return returnValue;
}

public int getIntResult(String column) throws RemoteException {
int returnValue = -1;
try {
if (this.resultSet.next()) {
returnValue = this.resultSet.getInt(column);
}
} catch(SQLException e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
return returnValue;
}

public Collection getResultSet() throws RemoteException {

Vector returnValue = new Vector();
try {
ResultSetMetaData meta = resultSet.getMetaData();
String name = null;
while (resultSet.next()) {
java.util.Map row = new HashMap();
for (int column = 1; column <= meta.getColumnCount(); column++) {
int type = meta.getColumnType(column);
name = meta.getColumnName(column);
//System.out.println(name+"/"+type);
if (Types.CHAR == type || Types.VARCHAR == type
|| Types.CLOB == type) {
String value = resultSet.getString(column);
row.put(name, value);
} else if (Types.INTEGER == type || Types.TINYINT == type
|| Types.NUMERIC == type) {
int value = resultSet.getInt(column);
row.put(name, value);
} else if (Types.DATE == type || Types.TIME == type
|| Types.TIMESTAMP == type) {
java.util.Date value = resultSet.getDate(column);
row.put(name, value);
}

}
returnValue.add(row);
}
} catch(Exception e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
return returnValue;
}

public String getSql() throws RemoteException {
return sql;
}

public void setSql(String sql) throws RemoteException {
this.sql = sql;
}

public boolean checkClose() throws RemoteException {
boolean flag = true;
try {
flag = this.connection.isClosed();
} catch(SQLException e) {
log.error("Sql=" + sql);
log.error(e.toString());
}
return flag;
}
}

主要就是这3个类，其他还有些接口就不放上来了。