sqlutil.java

Jodd是一个开源的公用Java基础类库

package jodd.db;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jodd.db.pool.ConnectionPool;

/**
 * Smart-and-slick SQL facade.
 * Class is <b>not</b> fool-proof and threadsafe.
 *
 * @2do Add more <code>PreparedStatement</code> setXXX() methods
 */
public class SqlUtil {

	// ---------------------------------------------------------------- properties

	private String[] debugSql;
	private String[] debugSqlParams;
	private Connection conn = null;
	private Statement st = null;				// because of speed optimization...
	private PreparedStatement pst = null;		// instanceof and castings are avoid
	private ResultSet resultSet = null;

	// ---------------------------------------------------------------- constructors

	private ConnectionPool pool = null;
	
	private Connection providedConnection = null;

	/**
	 * Default constructor from ConnectionPool.
	 *
	 * @param cp     connection pool instance
	 */
	public SqlUtil(ConnectionPool cp) {
		pool = cp;
	}
	/**
	 * Default constructor from common Connection.
	 *
	 * @param conenction opened db connection
	 */
	public SqlUtil(Connection conenction) {
		providedConnection = conenction;
	}

	// ---------------------------------------------------------------- init

	/**
	 * Sets and prepares a SQL command by using forward only and read only
	 * prepared statement.
	 *
	 * @param sql    sql query string.
	 *
	 * @exception SQLException
	 * @see #setSql(String, int, int)
	 */
	public void setSql(String sql) throws SQLException {
		setSql(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	}

	/**
	 * Sets and prepares a SQL command by using prepared statements.
	 * <p>
	 *
	 * It first closes existing resultset, if any availiable. Then it
	 * closes existing prepared statement. After, obtains connection
	 * from database if not already taken. Sets commit mode, if necessary.
	 *
	 * @param sql        sql query string.
	 * @param resultType result type
	 * @param resultSetConcurrency
	 *                   result concurrency
	 *
	 * @exception SQLException
	 */
	public void setSql(String sql, int resultType, int resultSetConcurrency) throws SQLException {
		if (resultSet != null) {
			resultSet.close();
			resultSet = null;
		}
		if (pst != null) {
			pst.close();
			pst = null;
		}
		if (conn == null) {
			if (pool != null) {
				conn = pool.getConnection();
			} else {
				conn = providedConnection;
			}
		}
		setCommitMode();
		pst = conn.prepareStatement(sql, resultType, resultSetConcurrency);
		createDebugSql(sql);
	}

	/**
	 * Sets and prepares a SQL command by using forward only and read only
	 * statement.
	 *
	 * @param sql    sql query string.
	 *
	 * @exception SQLException
	 * @see #setStaticSql(String, int, int)
	 */
	public void setStaticSql(String sql) throws SQLException {
		setSql(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	}


	/**
	 * Sets and prepares a SQL command by using simple statements.
	 * <p>
	 *
	 * It first closes existing resultset, if any availiable. Then it
	 * closes existing statement. After, obtains connection
	 * from database if not already taken. Sets commit mode, if necessary.
	 *
	 * @param sql        sql query string.
	 * @param resultType result type
	 * @param resultSetConcurrency
	 *                   concurency type
	 *
	 * @exception SQLException
	 */
	public void setStaticSql(String sql, int resultType, int resultSetConcurrency) throws SQLException {
		if (resultSet != null) {
			resultSet.close();
			resultSet = null;
		}
		if (st != null) {
			st.close();
			st = null;
		}
		if (conn == null) {
			if (pool != null) {
				conn = pool.getConnection();
			} else {
				conn = providedConnection;
			}
		}
		setCommitMode();
		st = conn.createStatement(resultType, resultSetConcurrency);
		debugSql = new String[] { sql };
		debugSqlParams = new String[] {""};
	}

	// ---------------------------------------------------------------- execute

	/**
	 * Executes SELECT statements, but works with all statements. Previously closes
	 * existing result set.
	 *
	 * @return ResultSet
	 * @exception SQLException
	 */
	public ResultSet executeQuery() throws SQLException {
		if (resultSet != null) {
			resultSet.close();
			resultSet = null;
		}
		if (st != null) {
			resultSet = st.executeQuery(debugSql[0]);
			return resultSet;
		}
		if (pst != null) {
			resultSet = pst.executeQuery();
			return resultSet;
		}
		return null;
	}

	/**
	 * Executes UPDATE, INSERT or DELETE statements.
	 *
	 * @return result of executeUpdate()
	 * @exception SQLException
	 */
	public int executeUpdate() throws SQLException {
		if (st != null) {
			return st.executeUpdate(debugSql[0]);
		}
		if (pst != null) {
			return pst.executeUpdate();
		}
		return -1;
	}


	// ---------------------------------------------------------------- close

	/**
	 * Closes result set and the statement. Connection is not closed.
	 *
	 * @param rs     ResultSet created by executeQuery(), or null if ResultSet is not being used.
	 *
	 * @see #close()
	 * @deprecated use close() instead
	 */
	public void close(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException sex) {
		}

		try {
			if (pst != null) {
				pst.close();
			}
		} catch (SQLException sex) {
		} finally {
			pst = null;
		}

		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException sex) {
		} finally {
			st = null;
		}
	}

	/**
	 * Closes result set (if previously created) and the statement. Connection is not closed.
	 */
	public void close() {
		// result set
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException sex) {
		} finally {
			resultSet = null;
		}

		// prepared statement
		try {
			if (pst != null) {
				pst.close();
			}
		} catch (SQLException sex) {
		} finally {
			pst = null;
		}

		// statement
		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException sex) {
		} finally {
			st = null;
		}
	}

	/**
	 * Closes result set, preparead statements, sets default commit mode for
	 * connection and returns connection to db pool.
	 *
	 * @param rs     ResultSet created by executeQuery(), or null if ResultSet is not being used.
	 *
	 * @deprecated use closeAll() instead.
	 */
	public void closeAll(ResultSet rs) {
		close(rs);
		resetCommitMode();
		if (conn != null) {
			if (pool != null) {
				pool.freeConnection(conn);
			} else {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
			conn = null;			
		}
	}

	/**
	 * Closes result set (if created), statements and returns connection to db
	 * pool when no ResultSets are used.
	 */
	public void closeAll() {
		close();
		resetCommitMode();
		if (conn != null) {
			if (pool != null) {
				pool.freeConnection(conn);
			} else {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
			conn = null;			
		}
	}

	// ---------------------------------------------------------------- transactions

	private static Boolean defaultAutoCommit = null;
	/**
	 * Sets default auto-commit that SqlUtil <b>must</b> provide before executing
	 * new statement, neverness if <code>setAutoCommit</code> is used or not. By
	 * using this method and setting default auto-commit value to be the same as
	 * it is for database, it is guaranteed that if it is not explicitly set
	 * different, the default mode will be used.
	 *
	 * <p>
	 * If default auto-commit mode is not used, connection will be examined on
	 * first usage, and that commit status will be taken as the default one.
	 *
	 * @param autoCommit default auto-commit mode
	 */
	public static void setDefaultAutoCommit(boolean autoCommit) {
		defaultAutoCommit = Boolean.valueOf(autoCommit);
	}
	/**
	 * Returns default auto-commit mode or <code>null</code> if nothing set.
	 *
	 * @return default auto-commit mode
	 */
	public static Boolean getDefaultAutoCommit() {
		return defaultAutoCommit;
	}


	private Boolean storedAutoCommit = null;			// stored auto commit for the current session

	private Boolean currentAutoCommit = null;			// current value of auto-commit

	/**
	 * Sets this connection's auto-commit mode to the given state. If a
	 * connection is in auto-commit mode, then all its SQL statements will be
	 * executed and committed as individual transactions. Otherwise, its SQL
	 * statements are grouped into transactions that are terminated by a call to
	 * either the method <code>commit</code> or the method <code>rollback</code>.
	 *
	 * <p>
	 * If this method is not used, then default database commit mode is used,
	 * and this class doesn't change anything.
	 *
	 * <p>
	 * NOTE: if this method is called <b>before</b> very first
	 * <code>setSql()</code> or <code>setStaticSql()</code>, than it will just
	 * 'remember' the commit state that will be actually set in those commands,
	 * after connection is retrieved from database. This is prefered method.
	 *
	 * <p>
	 * If it is called after, then this method will directly change connection
	 * commit mode, with all consequences.
	 *
	 * @param autoCommit true to enable auto-commit mode; false to disable it
	 *
	 * @exception SQLException
	 */
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		currentAutoCommit = Boolean.valueOf(autoCommit);
		setCommitMode();
	}

	/**
	 * Retrieves the current auto-commit mode for this object.
	 * If <code>null</code> is returned then default database mode is used.
	 *
	 * @return the current auto-commit mode
	 */
	public Boolean getAutoCommit() {
		return currentAutoCommit;
	}

	/**
	 * Returns the stored auto-commit mode for the connection. This value should
	 * match database initial commit mode. If <code>null</code> is returned, then
	 * connection is not yet obtained. Therefore, this method has to be called
	 * after <code>setSql</code> methods.
	 *
	 * @return default commit mode
	 */
	public Boolean getStoredAutoCommit() {
		return storedAutoCommit;
	}

	/**