本文参考：
1. Overpower PreparedStatement
2. Bind variables - The key to application performance
此文作为本人23种设计模式学习笔记系列之一， 个人制定的对设计模式的学习方法是在实际项目中用一种模式， 写一篇笔记， 希望能够早日写完23中设计模式的笔记， 完成对所有设计模式的初步掌握
- 本文中的代码所使用的模式由代理模式修正为装饰器模式。 装饰器模式和代理模式从实现方式的角度看是完全一样的， 主要以使用目的区分。 具体可以参见相关博文设计模式拾荒之装饰器模式： 代理模式的双胞胎兄弟
设计模式用前须知
PrepareStatement 的好处
有一定java数据库开发经验的人都知道PreparedStatement 相对于Statement的优点 ：
- 因为预解析（Parse），编译(Compile)了sql语句,计划（Plan）了sql的数据获取路径， 所以通常来说PreparedStatement都会比Statement快， 至少不会比Statement慢
- 防止了sql 注入,因为它对可执行sql和数据进行了分离 ， 数据参数会被在sql语句转化为执行计划以后， 另作单独的数据包传输过去，所以避免了拼接可能引发的sql注入问题
- 简化了设置非标准类型参数的方法，例如 Date,Time,Timestamp,BigDecimal,InputStream (Blob) ， Reader (Clob)
- 例如
preparedStatement = connection.prepareStatement("INSERT INTO Person (name,email,birthdate,photo) VALUES (?,?,?)");
preparedStatement.setString(1,person.getName());
preparedStatement.setString(2,person.getEmail());
preparedStatement.setTimestamp(3,new Timestamp(person.getBirthdate().getTime()));
preparedStatement.executeUpdate();
如何打印PrepareStatement
但是使用PreparedStatement 对于需要保留sql 语句执行记录的场景可能会遇到问题( 例如本人所在的公司交易系统应用对所有的sql 语句都会记log )， 如果想打印以上的PreparedStatement 真正被执行的sql 语句，获得了如下形式的内容
例如 INSERT INTO Person (name,birthdate) VALUES (John,john@hotmail.com,19660606)
则有可能遇到问题。 因为JDBC API 并没有定义专门用于获取PreparedStatement 执行语句内容的方法， 而PreparedStatemet.toString() 获得的结果取决于使用JDBC Driver,如果使用的是Postgresql 8.x and MysqL 5.x， 那直接在参数被set 后，调用 System.out.println(preparedStatement); 即可获得想要sql 语句内容。 如果是其他的JDBC Driver 例如 Oracle,执行该语句只会获得一串对象码。
所以问题情境是，当前开发的系统中，使用了PreparedStatement 的地方都没有办法打印带参数的sql 语句， 只能打印出带问号的部分， 在应用测试阶段不便于问题的排查， 所以需要一种最低侵入式， 改动范围最小的方法支持PreparedStatement 语句的打印 。
- 可能的解决思路：
- 继承 oracle jdbc 对 PreparedStatement实现类， 重写set 方法， 在set被调用时记录参数内容， 然后自行添加toString 方法， 拼接处整的sql 语句内容
- 事实证明不可行， 因为ojdbc driver 的 PreparedStatement 的实现类的访问权限是包内权限， 所以无法继承。
- 使用Log4jdbc 或 P6Spy 等来自StackOverFlow 推荐的日志工具
- 缺点： 为了一个小需求给项目添加了新的依赖， 还需要一系列设置， 太麻烦。
- 利用设计模式之装饰器模式（Decorator）,实现一个DebuggableStatement接口， 以下列方式打印PreparedStatement
- 缺点： 除需要向原有的项目中添加几个类以外， 几乎没有。
- 继承 oracle jdbc 对 PreparedStatement实现类， 重写set 方法， 在set被调用时记录参数内容， 然后自行添加toString 方法， 拼接处整的sql 语句内容
Connection con = DriverManager.getConnection(url);
// 当DebugLevel 为OFF时， StatementFactory.getStatement(con,sql,debug);返回的依旧是PreparedStatement
DebugLevel debug = DebugLevel.ON;
String sql = "SELECT name,rate FROM EmployeeTable WHERE rate > ?";
//下面通过一个工厂类而不是Connection来获取PreparedStatement,//PreparedStatement ps = con.prepareStatement(sql);
PreparedStatement ps = StatementFactory.getStatement(con,debug);
ps.setInt(1,25);
//如果 ps 是一个实现了DebuggableStatement的对象,便可以打印出实际执行的sql语句
//otherwise,an object identifier is displayed
System.out.println(" debuggable statement= " + ps.toString());
优点：
代码说明：
class DebuggableStatement implements PreparedStatement{
private PreparedStatement ps; //preparedStatement being proxied for.
private String sql; //original statement going to database.
private String filteredsql; //statement filtered for rogue '?' that are not bind variables.
private DebugObject[] variables; //array of bind variables
private sqlFormatter formatter; //format for dates
private long startTime; //time that statement began execution
private long executeTime; //time elapsed while executing statement
private DebugLevel debugLevel; //level of debug
....
@Override
public boolean getMoreResults(int current) throws sqlException {
return ps.getMoreResults();
....
}
注意到DebuggableStatement第一个成员对象ps 是PreparedStatement ， 而我们实现PreparedStatement 的方法内容都无须自行编写， 都是直接调用ps对象的对应方法。 而ps 对象的获取是在如下的构造方法中， 通过connection 获取。
protected DebuggableStatement(Connection con,String sqlStatement,sqlFormatter formatter,DebugLevel debugLevel) throws sqlException{
//set values for member variables
if (con == null)
throw new sqlException("Connection object is null");
this.ps = con.prepareStatement(sqlStatement); //被代理对象的获取
this.sql = sqlStatement;
this.debugLevel = debugLevel;
this.formatter = formatter;
至此， DebuggableStatement 的实现方式就已经完全清晰了， 至于sql 的拼接就是在每一个set 中将具体的参数值保存在一个数组中， 最后toString 的时候， 填充即可
这两个类只是为了实现sql 语句中， 对于日期（Date）等非基本类型的参数打印的格式化实现而已，非常简单。
完整的代码
- DebuggableStatement
class DebuggableStatement implements PreparedStatement{
private PreparedStatement ps; //preparedStatement being proxied for.
private String sql; //original statement going to database.
private String filteredsql; //statement filtered for rogue '?' that are not bind variables.
private DebugObject[] variables; //array of bind variables
private sqlFormatter formatter; //format for dates
private long startTime; //time that statement began execution
private long executeTime; //time elapsed while executing statement
private DebugLevel debugLevel; //level of debug
/** Construct new DebugableStatement. Uses the sqlFormatter to format date,time,timestamp outputs @param con Connection to be used to construct PreparedStatement @param sqlStatement sql statement to be sent to database. @param debugLevel DebugLevel can be ON,OFF,VERBOSE. */
protected DebuggableStatement(Connection con,DebugLevel debugLevel) throws sqlException{
//set values for member variables
if (con == null)
throw new sqlException("Connection object is null");
this.ps = con.prepareStatement(sqlStatement);
this.sql = sqlStatement;
this.debugLevel = debugLevel;
this.formatter = formatter;
//see if there are any '?' in the statement that are not bind variables
//and filter them out.
boolean isString = false;
char[] sqlString = sqlStatement.tocharArray();
for (int i = 0; i < sqlString.length; i++){
if (sqlString[i] == '\'') // 为了判断要替换的问号是否是否在单引号内部
isString = !isString;
//substitute the ? with an unprintable character if the ? is in a
//string.
if (sqlString[i] == '?' && isString) // 单引号中的问号不能替换
sqlString[i] = '\u0007';
}
filteredsql = new String(sqlString);
//find out how many variables are present in statement.
int count = 0;
int index = -1;
while ((index = filteredsql.indexOf("?",index+1)) != -1){
count++;
}
//show how many bind variables found
if (debugLevel == DebugLevel.VERBOSE)
System.out.println("count= " + count);
//create array for bind variables
variables = new DebugObject[count];
}
/** * Facade for PreparedStatement */
public void addBatch() throws sqlException{
ps.addBatch();
}
/** * Facade for PreparedStatement */
public void addBatch(String sql) throws sqlException{
ps.addBatch();
}
/** * Facade for PreparedStatement */
public void cancel() throws sqlException{
ps.cancel();
}
/** * Facade for PreparedStatement */
public void clearBatch() throws sqlException{
ps.clearBatch();
}
/** * Facade for PreparedStatement */
public void clearParameters() throws sqlException{
ps.clearParameters();
}
/** * Facade for PreparedStatement */
public void clearWarnings() throws sqlException{
ps.clearWarnings();
}
/** * Facade for PreparedStatement */
public void close() throws sqlException{
ps.close();
}
/** * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @return results of query */
public boolean execute() throws sqlException{
//execute query
Boolean results = null;
try{
results = (Boolean)executeVerboseQuery("execute",null);
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results.booleanValue();
}
/** * This method is only here for convenience. If a different sql string is executed * than was passed into Debuggable,unknown results will occur. * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @param sql should be same string that was passed into Debuggable * @return results of query */
public boolean execute(String sql) throws sqlException{
//execute query
Boolean results = null;
try{
results = (Boolean)executeVerboseQuery("execute",new Class[]{sql.getClass()});
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results.booleanValue();
}
/** * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @return results of query */
public int[] executeBatch() throws sqlException{
//execute query
int[] results = null;
try{
results = (int[])executeVerboseQuery("executeBatch",null);
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results;
}
/** * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @return results of query */
public ResultSet executeQuery() throws sqlException{
//execute query
ResultSet results = null;
try{
// results = (ResultSet)executeVerboseQuery("executeQuery",null);
results = ps.executeQuery();
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results;
}
/** * This method is only here for convenience. If a different sql string is executed * than was passed into Debuggable,unknown results will occur. * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @param sql should be same string that was passed into Debuggable * @return results of query */
public ResultSet executeQuery(String sql) throws sqlException{
//execute query
ResultSet results = null;
try{
results = (ResultSet)executeVerboseQuery("executeQuery",new Class[]{sql.getClass()});
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results;
}
/** * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @return results of query */
public int executeUpdate() throws sqlException{
//execute query
Integer results = null;
try{
results = (Integer)executeVerboseQuery("executeUpdate",null);
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results.intValue();
}
/** * This method is only here for convenience. If a different sql string is executed * than was passed into Debuggable,unknown results will occur. * Executes query and Calculates query execution time if DebugLevel = VERBOSE * @param sql should be same string that was passed into Debuggable * @return results of query */
public int executeUpdate(String sql) throws sqlException{
//execute query
Integer results = null;
try{
results = (Integer)executeVerboseQuery("executeUpdate",new Class[]{sql.getClass()});
}catch(Exception e){
throw new sqlException("Could not execute sql command - Original message: " + e.getMessage());
}
return results.intValue();
}
/** * Facade for PreparedStatement */
public Connection getConnection() throws sqlException{
return ps.getConnection();
}
@Override
public boolean getMoreResults(int current) throws sqlException {
return ps.getMoreResults();
}
@Override
public ResultSet getGeneratedKeys() throws sqlException {
return ps.getGeneratedKeys();
}
@Override
public int executeUpdate(String sql,int autoGeneratedKeys) throws sqlException {
return ps.executeUpdate(sql,autoGeneratedKeys);
}
@Override
public int executeUpdate(String sql,int[] columnIndexes) throws sqlException {
return ps.executeUpdate(sql,columnIndexes);
}
@Override
public int executeUpdate(String sql,String[] columnNames) throws sqlException {
return ps.executeUpdate( sql,columnNames);
}
@Override
public boolean execute(String sql,int autoGeneratedKeys) throws sqlException {
return ps.execute(sql,autoGeneratedKeys);
}
@Override
public boolean execute(String sql,int[] columnIndexes) throws sqlException {
return ps.execute(sql,columnIndexes);
}
@Override
public boolean execute(String sql,String[] columnNames) throws sqlException {
return ps.execute(sql,columnNames);
}
@Override
public int getResultSetHoldability() throws sqlException {
return ps.getResultSetHoldability();
}
@Override
public boolean isClosed() throws sqlException {
return ps.isClosed();
}
@Override
public void setPoolable(boolean poolable) throws sqlException {
ps.setPoolable(poolable);
}
@Override
public boolean isPoolable() throws sqlException {
return ps.isPoolable();
}
@Override
public void cloSEOnCompletion() throws sqlException {
ps.cloSEOnCompletion();
}
@Override
public boolean isCloSEOnCompletion() throws sqlException {
return ps.isCloSEOnCompletion();
}
/** * Facade for PreparedStatement */
public int getFetchDirection() throws sqlException{
return ps.getFetchDirection();
}
/** * Facade for PreparedStatement */
public int getFetchSize() throws sqlException{
return ps.getFetchSize();
}
/** * Facade for PreparedStatement */
public int getMaxFieldSize() throws sqlException{
return ps.getMaxFieldSize();
}
/** * Facade for PreparedStatement */
public int getMaxRows() throws sqlException{
return ps.getMaxRows();
}
/** * Facade for PreparedStatement */
public ResultSetMetaData getMetaData() throws sqlException{
return ps.getMetaData();
}
/** * Facade for PreparedStatement */
public boolean getMoreResults() throws sqlException{
return ps.getMoreResults();
}
/** * Facade for PreparedStatement */
public int getQueryTimeout() throws sqlException{
return ps.getQueryTimeout();
}
/** * Facade for PreparedStatement */
public ResultSet getResultSet() throws sqlException{
return ps.getResultSet();
}
/** * Facade for PreparedStatement */
public int getResultSetConcurrency() throws sqlException{
return ps.getResultSetConcurrency();
}
/** * Facade for PreparedStatement */
public int getResultSetType() throws sqlException{
return ps.getResultSetType();
}
/** * Facade for PreparedStatement */
public String getStatement(){
return sql;
}
/** * Facade for PreparedStatement */
public int getUpdateCount() throws sqlException{
return ps.getUpdateCount();
}
/** * Facade for PreparedStatement */
public sqlWarning getWarnings() throws sqlException{
return ps.getWarnings();
}
/** * Tests Object o for parameterIndex (which parameter is being set) and places * object in array of variables. * @param parameterIndex which PreparedStatement parameter is being set. * Sequence begins at 1. * @param o Object being stored as parameter * @exception throw if index exceeds number of variables. */
private void saveObject(int parameterIndex,Object o)throws ParameterIndexOutOfBoundsException {
if(parameterIndex > variables.length)
throw new ParameterIndexOutOfBoundsException("Parameter index of " +
parameterIndex + " exceeds actual parameter count of " + variables.length);
variables[parameterIndex-1] = new DebugObject(o);
}
/** Adds name of the Array's internal class type(by using x.getBaseTypeName()) to the debug String. If x is null,NULL is added to debug String. @param i index of parameter @param x parameter Object */
public void setArray(int i,java.sql.Array x) throws sqlException{
saveObject(i,x);
ps.setArray(i,x);
}
/** Debug string prints NULL if InputStream is null,or adds "stream length = " + length */
public void setAsciiStream(int parameterIndex,InputStream x,int length) throws sqlException{
saveObject(parameterIndex,(x==null?"NULL":"<stream length= " + length+">"));
ps.setAsciiStream(parameterIndex,x,length);
}
/** Adds BigDecimal to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setBigDecimal(int parameterIndex,BigDecimal x) throws sqlException{
saveObject(parameterIndex,x);
ps.setBigDecimal(parameterIndex,or adds "stream length= " + length. @param parameterIndex index of parameter @param x parameter Object @param length length of InputStream */
public void setBinaryStream(int parameterIndex,(x==null?"NULL":"<stream length= " + length+">"));
ps.setBinaryStream(parameterIndex,length);
}
/** Adds name of the object's class type(Blob) to the debug String. If object is null,NULL is added to debug String. @param parameterIndex index of parameter @param x parameter Object */
public void setBlob(int parameterIndex,Blob x) throws sqlException{
saveObject(parameterIndex,x);
ps.setBlob(parameterIndex,x);
}
/** Adds boolean to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setBoolean(int parameterIndex,boolean x) throws sqlException{
saveObject(parameterIndex,new Boolean(x));
ps.setBoolean(parameterIndex,x);
}
/** Adds byte to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setByte(int parameterIndex,byte x) throws sqlException{
saveObject(parameterIndex,new Byte(x));
ps.setByte(parameterIndex,x);
}
/** Adds byte[] to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setBytes(int parameterIndex,byte[] x) throws sqlException{
saveObject(parameterIndex,(x==null?"NULL":"byte[] length="+x.length));
ps.setBytes(parameterIndex,x);
}
/** Debug string prints NULL if reader is null,or adds "stream length= " + length. @param parameterIndex index of parameter @param reader parameter Object @param length length of InputStream */
public void setCharacterStream(int parameterIndex,Reader reader,(reader==null?"NULL":"<stream length= " + length+">"));
ps.setCharacterStream(parameterIndex,reader,length);
}
/** Adds name of the object's class type(Clob) to the debug String. If object is null,NULL is added to debug String. @param i index of parameter @param x parameter Object */
public void setClob(int i,Clob x) throws sqlException{
saveObject(i,x);
ps.setClob(i,x);
}
public void setCursorName(String name) throws sqlException{
ps.setCursorName(name);
}
/** Debug string displays date in YYYY-MM-DD HH24:MI:SS.# format. @param parameterIndex index of parameter @param x parameter Object */
public void setDate(int parameterIndex,java.sql.Date x) throws sqlException{
saveObject(parameterIndex,x);
ps.setDate(parameterIndex,x);
}
/** this implementation assumes that the Date has the date,and the calendar has the local info. For the debug string,the cal date is set to the date of x. Debug string displays date in YYYY-MM-DD HH24:MI:SS.# format. @param parameterIndex index of parameter @param x parameter Object @param cal uses x to set time */
public void setDate(int parameterIndex,java.sql.Date x,Calendar cal) throws sqlException{
cal.setTime(new java.util.Date(x.getTime()));
saveObject(parameterIndex,cal);
ps.setDate(parameterIndex,cal);
}
/** Adds double to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setDouble(int parameterIndex,double x) throws sqlException{
saveObject(parameterIndex,new Double(x));
ps.setDouble(parameterIndex,x);
}
/** * Facade for PreparedStatement */
public void setEscapeProcessing(boolean enable) throws sqlException{
ps.setEscapeProcessing(enable);
}
/** * Facade for PreparedStatement */
public void setFormatter(sqlFormatter formatter){
this.formatter = formatter;
}
/** * Facade for PreparedStatement */
public void setFetchDirection(int direction) throws sqlException{
ps.setFetchDirection(direction);
}
/** * Facade for PreparedStatement */
public void setFetchSize(int rows) throws sqlException{
ps.setFetchSize(rows);
}
/** Adds float to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setFloat(int parameterIndex,float x) throws sqlException{
saveObject(parameterIndex,new Float(x));
ps.setFloat(parameterIndex,x);
}
/** Adds int to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setInt(int parameterIndex,int x) throws sqlException{
saveObject(parameterIndex,new Integer(x));
ps.setInt(parameterIndex,x);
}
/** Adds long to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setLong(int parameterIndex,long x) throws sqlException{
saveObject(parameterIndex,new Long(x));
ps.setLong(parameterIndex,x);
}
/** * Facade for PreparedStatement */
public void setMaxFieldSize(int max) throws sqlException{
ps.setMaxFieldSize(max);
}
/** * Facade for PreparedStatement */
public void setMaxRows(int max) throws sqlException{
ps.setMaxRows(max);
}
/** Adds a NULL to the debug String. @param parameterIndex index of parameter @param sqlType parameter Object */
public void setNull(int parameterIndex,int sqlType) throws sqlException{
saveObject(parameterIndex,"NULL");
ps.setNull(parameterIndex,sqlType);
}
/** Adds a NULL to the debug String. @param parameterIndex index of parameter @param sqlType parameter Object @param typeName type of Object */
public void setNull(int parameterIndex,int sqlType,String typeName) throws sqlException{
saveObject(parameterIndex,sqlType,typeName);
}
@Override
public void setURL(int parameterIndex,URL x) throws sqlException {
ps.setURL(parameterIndex,x);
}
@Override
public ParameterMetaData getParameterMetaData() throws sqlException {
return ps.getParameterMetaData();
}
@Override
public void setRowId(int parameterIndex,RowId x) throws sqlException {
ps.setRowId(parameterIndex,x);
}
@Override
public void setNString(int parameterIndex,String value) throws sqlException {
ps.setNString(parameterIndex,value);
}
@Override
public void setNCharacterStream(int parameterIndex,Reader value,long length) throws sqlException {
ps.setNCharacterStream(parameterIndex,value,length);
}
@Override
public void setNClob(int parameterIndex,NClob value) throws sqlException {
ps.setNClob(parameterIndex,value);
}
@Override
public void setClob(int parameterIndex,long length) throws sqlException {
ps.setClob(parameterIndex,length);
}
@Override
public void setBlob(int parameterIndex,InputStream inputStream,long length) throws sqlException {
ps.setBlob(parameterIndex,inputStream,long length) throws sqlException {
ps.setNClob(parameterIndex,length);
}
@Override
public void setsqlXML(int parameterIndex,sqlXML xmlObject) throws sqlException {
ps.setsqlXML(parameterIndex,xmlObject);
}
/** Adds name of the object's class type to the debug String. If object is null,NULL is added to debug String. @param parameterIndex index of parameter @param x parameter Object */
public void setObject(int parameterIndex,Object x) throws sqlException{
saveObject(parameterIndex,(x==null?"NULL":x.getClass().getName()));
ps.setObject(parameterIndex,x);
}
/** Adds name of the object's class type to the debug String. If object is null,NULL is added to debug String. @param parameterIndex index of parameter @param x parameter Object @param targetsqlType database type */
public void setObject(int parameterIndex,Object x,int targetsqlType) throws sqlException{
saveObject(parameterIndex,targetsqlType);
}
/** Adds name of the object's class type to the debug String. If object is null,NULL is added to debug String. @param parameterIndex index of parameter @param x parameter Object @param targetsqlType database type @param scale see PreparedStatement */
public void setObject(int parameterIndex,int targetsqlType,int scale) throws sqlException{
saveObject(parameterIndex,targetsqlType,scale);
}
@Override
public void setAsciiStream(int parameterIndex,long length) throws sqlException {
ps.setAsciiStream(parameterIndex,length);
}
@Override
public void setBinaryStream(int parameterIndex,long length) throws sqlException {
ps.setBinaryStream(parameterIndex,length);
}
@Override
public void setCharacterStream(int parameterIndex,long length) throws sqlException {
ps.setCharacterStream(parameterIndex,length);
}
@Override
public void setAsciiStream(int parameterIndex,InputStream x) throws sqlException {
ps.setAsciiStream(parameterIndex,x);
}
@Override
public void setBinaryStream(int parameterIndex,InputStream x) throws sqlException {
ps.setBinaryStream(parameterIndex,x);
}
@Override
public void setCharacterStream(int parameterIndex,Reader reader) throws sqlException {
ps.setCharacterStream(parameterIndex,reader);
}
@Override
public void setNCharacterStream(int parameterIndex,Reader value) throws sqlException {
ps.setNCharacterStream(parameterIndex,Reader reader) throws sqlException {
ps.setClob(parameterIndex,reader);
}
@Override
public void setBlob(int parameterIndex,InputStream inputStream) throws sqlException {
ps.setBlob(parameterIndex,inputStream);
}
@Override
public void setNClob(int parameterIndex,Reader reader) throws sqlException {
ps.setNClob(parameterIndex,reader);
}
/** * Facade for PreparedStatement */
public void setQueryTimeout(int seconds) throws sqlException{
ps.setQueryTimeout(seconds);
}
/** From the javadocs: A reference to an sql structured type value in the database. A Ref can be saved to persistent storage. The output from this method call in DebuggableStatement is a string representation of the Ref object by calling the Ref object's getBaseTypeName() method. Again,this will only be a String representation of the actual object being stored in the database. @param i index of parameter @param x parameter Object */
public void setRef(int i,Ref x) throws sqlException{
saveObject(i,x);
ps.setRef(i,x);
}
/** Adds short to debug string in parameterIndex position. @param parameterIndex index of parameter @param x parameter Object */
public void setShort(int parameterIndex,short x) throws sqlException{
saveObject(parameterIndex,new Short(x));
ps.setShort(parameterIndex,x);
}
/** Adds String to debug string in parameterIndex position. If String is null "NULL" is inserted in debug string. ****note**** In situations where a single ' is in the string being inserted in the database. The debug string will need to be modified to reflect this when running the debug statement in the database. @param parameterIndex index of parameter @param x parameter Object */
public void setString(int parameterIndex,String x) throws sqlException{
saveObject(parameterIndex,x);
ps.setString(parameterIndex,x);
}
/** Debug string displays Time in HH24:MI:SS.# format. @param parameterIndex index of parameter @param x parameter Object */
public void setTime(int parameterIndex,Time x) throws sqlException{
saveObject(parameterIndex,x);
ps.setTime(parameterIndex,x);
}
/** This implementation assumes that the Time object has the time and Calendar has the locale info. For the debug string,the cal time is set to the value of x. Debug string displays time in HH24:MI:SS.# format. @param parameterIndex index of parameter @param x parameter Object @param cal sets time based on x */
public void setTime(int parameterIndex,Time x,cal);
ps.setTime(parameterIndex,cal);
}
/** Debug string displays timestamp in YYYY-MM-DD HH24:MI:SS.# format. @param parameterIndex index of parameter @param x parameter Object */
public void setTimestamp(int parameterIndex,Timestamp x) throws sqlException{
saveObject(parameterIndex,x);
ps.setTimestamp(parameterIndex,x);
}
/** This implementation assumes that the Timestamp has the date/time and Calendar has the locale info. For the debug string,the cal date/time is set to the default value of Timestamp which is YYYY-MM-DD HH24:MI:SS.#. Debug string displays timestamp in DateFormat.LONG format. @param parameterIndex index of parameter @param x parameter Object @param cal sets time based on x */
public void setTimestamp(int parameterIndex,Timestamp x,cal);
ps.setTimestamp(parameterIndex,cal);
}
/** Method has been deprecated in PreparedStatement interface. This method is present only to satisfy interface and does not do anything. Do not use... @deprecated */
public void setUnicodeStream(int parameterIndex,int length) throws sqlException{
//ps.setUnicodeStream(parameterIndex,length);
}
/** this toString is overidden to return a String representation of the sql statement being sent to the database. If a bind variable is missing then the String contains a ? + (missing variable #) @return the above string representation */
public String toString(){
StringTokenizer st = new StringTokenizer(filteredsql,"?");
int count = 1;
StringBuffer statement = new StringBuffer();
while(st.hasMoreTokens()){
statement.append(st.nextToken());
if(count <= variables.length){
if(variables[count-1] != null && variables[count-1].isValueAssigned()){
try{
statement.append(formatter.format(variables[count-1].getDebugObject()));
}catch(sqlException e){
statement.append("sqlException");
}
}else{
statement.append("? "+"(missing variable # " + count+" ) ");
}
}
count++;
}
//unfilter the string in case there where rogue '?' in query string.
char[] unfiltersql = statement.toString().tocharArray();
for(int i = 0; i < unfiltersql.length; i++){
if (unfiltersql[i] == '\u0007')
unfiltersql[i] = '?';
}
//return execute time
if (debugLevel == DebugLevel.ON)
return new String(unfiltersql);
else
return new String(unfiltersql) +
System.getProperty("line.separator") +
System.getProperty("line.separator") +
"query executed in " + executeTime + " milliseconds" +
System.getProperty("line.separator");
}
private Object executeVerboseQuery(String methodName,Class[] parameters)
throws sqlException,NoSuchMethodException,InvocationTargetException,IllegalAccessException{
//determine which method we have
Method m = ps.getClass().getDeclaredMethod(methodName,parameters);
//debug is set to on,so no times are calculated
if (debugLevel == DebugLevel.ON)
return m.invoke(ps,parameters);
//calculate execution time for verbose debugging
start();
Object returnObject = m.invoke(ps,parameters);
end();
//return the executions return type
return returnObject;
}
private void start(){
startTime = System.currentTimeMillis();
}
private void end(){
executeTime = System.currentTimeMillis()-startTime;
}
@Override
public <T> T unwrap(Class<T> iface) throws sqlException {
return ps.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws sqlException {
return ps.isWrapperFor(iface);
}
private class DebugObject{
private Object debugObject;
private boolean valueAssigned;
public DebugObject(Object debugObject){
this.debugObject = debugObject;
valueAssigned = true;
}
public Object getDebugObject(){
return debugObject;
}
public boolean isValueAssigned(){
return valueAssigned;
}
}
}
- StatementFactory
import java.sql.*;
public class StatementFactory {
/* Default debug level */
private static DebugLevel defaultDebug = DebugLevel.OFF;
/* Default sql formatter */
private static sqlFormatter defaultFormatter = new DefaultsqlFormatter();
/** * StatementFactory returns either a regular PreparedStatement or a DebuggableStatement * class depending on the DebugLevel. If DebugLevel is OFF then a PreparedStatement is * returned. If DebugLevel is ON or VERBOSE then a DebuggableStatement is returned. This * minimizes overhead when debugging is not needed without effecting the code. */
public StatementFactory() {
}
/** * Use this method if you want a class to override the global nature of a * property file approach. This gives a class an option of a formatter and * the debug value other than the global setting. * @param con Connection to jdbc data source. * @param stmt sql statement that will be executed. * @param formatter sqlFormatter that matches the database type (i.e. OracleFormatter) * @param debug sets the debug level for this statement. DebugLevel can be OFF,ON,VERBOSE * @return PreparedStatement returns a DebuggableStatement if debug = ON or VERBOSE. Returns a standard * PreparedStatement if debug = OFF. * @exception sqlException thrown if problem with connection. */
public static PreparedStatement getStatement(Connection con,String stmt,DebugLevel debug) throws sqlException{
if (con == null)
throw new sqlException("Connection passed to StatementFactory is null");
if(debug != DebugLevel.OFF){
return new DebuggableStatement(con,stmt,formatter,debug);
}else{
return con.prepareStatement(stmt);
}
}
/** * Use this if you want a class to override the global nature of a property * file approach. This gives a class an option of a formatter other than the global setting. * @param con Connection to jdbc data source. * @param stmt sql statement that will be executed. * @param formatter sqlFormatter that matches the database type (i.e. OracleFormatter) * @return PreparedStatement returns a DebuggableStatement if debug = ON or VERBOSE. Returns a standard * PreparedStatement if debug = OFF. * @exception sqlException thrown if problem with connection. */
public static PreparedStatement getStatement(Connection con,sqlFormatter formatter) throws sqlException{
return StatementFactory.getStatement(con,defaultDebug);
}
/** * Use this if you want a class to override the global nature of a property * file approach. This gives a class the option of turning debug code * on or off no matter what the global value. This will not effect the * global setting. * @param con Connection to jdbc data source. * @param stmt sql statement that will be executed. * @param debug sets the debug level for this statement. DebugLevel can be OFF,DebugLevel debug) throws sqlException{
return StatementFactory.getStatement(con,defaultFormatter,debug);
}
/** * this is the typical way to retrieve a statement. This method uses the static * formatter and debug level. * @param con Connection to jdbc data source. * @param stmt sql statement that will be executed. * @return PreparedStatement returns a DebuggableStatement if debug = ON or VERBOSE. Returns a standard * PreparedStatement if debug = OFF. * @exception sqlException thrown if problem with connection. */
public static PreparedStatement getStatement(Connection con,String stmt) throws sqlException{
return StatementFactory.getStatement(con,defaultDebug);
}
/** * typically set from property file so change is made in one place. * default is to false which immulates a preparedstatement. * This will change debug value in all places. * @param debug sets the debug level for this statement. DebugLevel can be OFF,VERBOSE */
public static void setDefaultDebug(DebugLevel debug){
defaultDebug = debug;
}
/** * typically set from property file so change is made in one place. * This will change formatter in all places. * @param formatter sets the sqlFormatter to the database type used in this * application. */
public static void setDefaultFormatter(sqlFormatter formatter){
defaultFormatter = formatter;
}
}
- sqlFormatter
import java.sql.*; /** * Base class for all database Formatters such as OracleFormatter. */ public abstract class sqlFormatter { /** * Formats a blob to the following String "'<Blob length = " + blob.length()+">'" * This method's output will not translate directly into the database. It is informational only. * @param blob The blob to be translated * @return The String representation of the blob * @exception sqlException */ protected String format(Blob blob) throws sqlException{ return "'<Blob length = " + blob.length()+">'"; } /** * Formats a clob to the following String "'<Clob length = " + clob.length()+">'" * This method's output will not translate directly into the database. It is informational only. * @param clob The clob to be translated * @return The String representation of the clob * @exception sqlException */ protected String format(Clob clob)throws sqlException{ return "'<Clob length = " + clob.length()+">'"; } /** * Formats an Array to the following String "array.getBaseTypeName()" * This method's output will not translate directly into the database. It is informational only. * @param array The array to be translated * @return The base name of the array * @exception sqlException * */ protected String format(Array array)throws sqlException{ return array.getBaseTypeName(); } /** * Formats a Ref to the following String "ref.getBaseTypeName()" * This method's output will not translate directly into the database. It is informational only. * @param ref The ref to be translated * @return The base name of the ref * @exception sqlException */ protected String format(Ref ref)throws sqlException{ return ref.getBaseTypeName(); } /** * Checks the String for null and returns "'" + string + "'". * @param string String to be formatted * @return formatted String (null returns "NULL") */ protected String format(String string)throws sqlException{ if(string.equals("NULL")) return string; else return "'" + string + "'"; } /** * If object is null,Blob,Clob,Array,Ref,or String this returns the value from the protected methods * in this class that take those Classes. * @param o Object to be formatted * @return formatted String */ public String format(Object o) throws sqlException{ if (o == null) return "NULL"; if (o instanceof Blob) return format((Blob)o); if (o instanceof Clob) return format((Clob)o); if (o instanceof Array) return format((Array)o); if (o instanceof Ref) return format((Ref)o); if (o instanceof String) return format((String)o); return o.toString(); } }
- OraclesqlFormatter
/** * Title: <p> * Description: <p> * Copyright: Copyright (c) Troy Thompson,Bob Byron<p> * Company: JavaUnderground<p> * @author Troy Thompson,Bob Byron * @version 1.1 */
import java.util.Calendar;
import java.math.BigDecimal;
import java.io.*;
import java.sql.*;
/** * OraclesqlFormatter formats Oracle specific types. These include * Calendar,Date,and TimeStamps. Generic types are handled * by sqlFormatter. */
public class OraclesqlFormatter extends sqlFormatter{
/** * Format of Oracle date: 'YYYY-MM-DD HH24:MI:SS.#' */
final String ymd24="'YYYY-MM-DD HH24:MI:SS.#'";
/** * Formats Calendar object into Oracle TO_DATE String. * @param cal Calendar to be formatted * @return formatted TO_DATE function */
private String format(Calendar cal){
return "TO_DATE('" + new Timestamp(cal.getTime().getTime()) + "',"+ymd24+")";
}
/** * Formats Date object into Oracle TO_DATE String. * @param date Date to be formatted * @return formatted TO_DATE function */
private String format(Date date){
return "TO_DATE('" + new Timestamp(date.getTime()) + "',"+ymd24+")";
}
/** * Formats Time object into Oracle TO_DATE String. * @param time Time to be formatted * @return formatted TO_DATE function */
private String format(Time time){
Calendar cal = Calendar.getInstance();
cal.setTime(new java.util.Date(time.getTime()));
return "TO_DATE('" + cal.get(Calendar.HOUR_OF_DAY) + ":" +
cal.get(Calendar.MINUTE) + ":" + cal.get(Calendar.SECOND) + "." +
cal.get(Calendar.MILLISECOND) + "','HH24:MI:SS.#')";
}
/** * Formats Timestamp object into Oracle TO_DATE String. * @param timestamp Timestamp to be formatted * @return formatted TO_DATE function */
private String format(Timestamp timestamp){
return "TO_DATE('" + timestamp.toString() + "',"+ymd24+")";
}
/** * Formats object to an Oracle specific formatted function. * @param o Object to be formatted. * @return formatted Oracle function or "NULL" if o is null. * @exception */
public String format(Object o) throws sqlException{
if (o == null) return "NULL";
if (o instanceof Calendar) return format((Calendar)o);
if (o instanceof Date) return format((Date)o);
if (o instanceof Time) return format((Time)o);
if (o instanceof Timestamp) return format((Timestamp)o);
//if object not in one of our overridden methods,send to super class
return super.format(o);
} }
- DefaultsqlFormatter
import java.util.Calendar;
import java.math.BigDecimal;
import java.io.*;
import java.sql.*;
public class DefaultsqlFormatter extends sqlFormatter {
final String ymd24="'YYYY-MM-DD HH24:MI:SS.#'";
private String format(Calendar cal){
return "TO_DATE('" + new Timestamp(cal.getTime().getTime()) + "',"+ymd24+")";
}
private String format(Date date){
return "TO_DATE('" + new Timestamp(date.getTime()) + "',"+ymd24+")";
}
private String format(Time time){
Calendar cal = Calendar.getInstance();
cal.setTime(new java.util.Date(time.getTime()));
return "TO_DATE('" + cal.get(Calendar.HOUR_OF_DAY) + ":" +
cal.get(Calendar.MINUTE) + ":" + cal.get(Calendar.SECOND) + "." +
cal.get(Calendar.MILLISECOND) + "','HH24:MI:SS.#')";
}
private String format(Timestamp timestamp){
return "TO_DATE('" + timestamp.toString() + "','YYYY-MM-DD HH24:MI:SS.#')";
}
public String format(Object o) throws sqlException{
if (o == null) return "NULL";
if (o instanceof Calendar) return format((Calendar)o);
if (o instanceof Date) return format((Date)o);
if (o instanceof Time) return format((Time)o);
if (o instanceof Timestamp) return format((Timestamp)o);
//if object not in one of our overridden methods,send to super class
return super.format(o);
}
}
- DebugLevel
public class DebugLevel {
//private constructor keeps all instances within class
private DebugLevel(){
}
//only allowed values for debugging
/** * Turn debugging off */
public static DebugLevel OFF = new DebugLevel();
/** * Turn debugging on */
public static DebugLevel ON = new DebugLevel();
/** * Set debugging to verbose */
public static DebugLevel VERBOSE = new DebugLevel();
}
