debuggablestatement.java

Local Lucene ==================== Provide geographical based searching to lucene in an efficent ma

/**
 * Title:        <p>
 * Description:  <p>
 * Copyright:    Copyright (c) Troy Thompson Bob Byron<p>
 * Company:      JavaUnderground<p>
 * @author       Troy Thompson Bob Byron
 * @version 1.1
 */
package com.javaunderground.jdbc;

import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.StringTokenizer;

/**
 * PreparedStatements have no way to retrieve the statement that was executed on
 * the database. This is due to the nature of prepared statements, which are
 * database driver specific. This class proxies for a PreparedStatement and
 * creates the SQL string that is created from the sets done on the
 * PreparedStatement.
 * <p>
 * Some of the objects such as blob, clob, and Ref are only represented as
 * Strings and are not the actual objects populating the database. Array is
 * represented by the object type within the array.
 * 
 * Example code: int payPeriod = 1; String name = "Troy Thompson"; ArrayList
 * employeePay = new ArrayList(); ResultSet rs = null; PreparedStatement ps =
 * null; Connection con = null; try{
 * Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url =
 * "jdbc:odbc:Employee"; con = DriverManager.getConnection(url); String sql =
 * "SELECT e.name,e.employee_number,e.pay_rate,e.type,"+ "
 * e.hire_date,h.pay_period,h.hours,h.commissions"+ " FROM Employee_tbl
 * e,hours_tbl h "+ " WHERE h.pay_period = ?"+ " AND e.name = ?"+ " AND
 * h.employee_number = e.employee_number"; ps =
 * StatementFactory.getStatement(con,sql); // <-- insert this to debug //ps =
 * con.prepareStatement(sql); ps.setInt(1,payPeriod); ps.setString(2,name);
 * System.out.println(); System.out.println(" debuggable statement= " +
 * ps.toString()); rs = ps.executeQuery(); }catch(SQLException e){
 * e.printStackTrace(); }catch(ClassNotFoundException ce){ ce.printStackTrace(); }
 * finally{ try{ if(rs != null){rs.close();} if(ps != null){ps.close();}
 * if(!con.isClosed()) con.close(); }catch(SQLException e){ e.printStackTrace(); } }
 * </p>
 * *****notes***** One of the main differences between databases is how they
 * handle dates/times. Since we use Oracle, the debug string for Dates, Times,
 * Timestamps are using an Oracle specific SqlFormatter called
 * OracleSqlFormatter.
 * 
 * The following is in our debug class: static{
 * StatementFactory.setDefaultDebug(DebugLevel.ON);
 * StatementFactory.setDefaultFormatter(new OracleSqlFormatter()); }
 * 
 */
public 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, 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;

		// 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);
		} 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();
	}

	/**
	 * 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 Thrown
	 *                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, x);
	}

	/**
	 * Debug string prints NULL if InputStream is null, or adds "stream length= " +
	 * length.
	 * 
	 * @param parameterIndex
	 *            index of parameter
	 * @param x
	 *            parameter Object
	 * @param length
	 *            length of InputStream
	 */
	public void setBinaryStream(int parameterIndex, InputStream x, int length)
			throws SQLException {
		saveObject(parameterIndex, (x == null ? "NULL" : "<stream length= "
				+ length + ">"));
		ps.setBinaryStream(parameterIndex, x, 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