jdbchelper.java.robmoore.done

把java对象映射成数据库表中的一条记录

/*
 *
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.1 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 *
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations under
 * the License.
 *
 * The Original Code is jRelationalFramework.
 *
 * The Initial Developer of the Original Code is is.com.
 * Portions created by is.com are Copyright (C) 2000 is.com.
 * All Rights Reserved.
 *
 * Contributor: Jonathan Carlson ([e-mail])
 * Contributor: Craig Laurent ([e-mail])
 * Contributor: Tim Dawson ([e-mail])
 * Contributor: _____________________________________
 *
 * Alternatively, the contents of this file may be used under the terms of
 * the GNU General Public License (the "GPL") or the GNU Lesser General
 * Public license (the "LGPL"), in which case the provisions of the GPL or
 * LGPL are applicable instead of those above.  If you wish to allow use of
 * your version of this file only under the terms of either the GPL or LGPL
 * and not to allow others to use your version of this file under the MPL,
 * indicate your decision by deleting the provisions above and replace them
 * with the notice and other provisions required by either the GPL or LGPL
 * License.  If you do not delete the provisions above, a recipient may use
 * your version of this file under either the MPL or GPL or LGPL License.
 *
 */
package com.is.util.sql;


import java.math.BigDecimal;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import java.util.Properties;
import java.util.Vector;

import javax.sql.DataSource;

import org.apache.log4j.Category;


/**
 *  Instances of this class execute JDBC queries and give access to data
 *  columns.
 *
 *  <p>Note: **AutoCommit has been turned off**.  You must explicitly call
 *  commit() or rollback(). (If you do not, the close method will commit any
 *  uncommitted transactions).
 *
 *  <h2>How to Use</h2>
 *
 *  In your class that needs to execute a query put something like this:
 *
 *  <CODE><PRE>
 *  Vector result = new Vector(20);
 *  JDBCHelper helper = null;
 *  try
 *      {
 *      helper = new JDBCHelper("weblogic.jdbc.pool.Driver",
 *                              "jdbc:weblogic:pool",
 *                              "tmsPool");
 *      helper.executeQuery("SELECT * FROM Status");
 *      while (helper.next())
 *          {
 *          StatusValue aStatusValue =
 *                 new StatusValue(helper.getInteger("StatusId"));
 *          aStatusValue.setCode(helper.getString("Code"));
 *          aStatusValue.setActive(helper.getboolean("Active"));
 *          aStatusValue.setSortOrder(helper.getint("SortOrder"));
 *          result.addElement(aStatusValue);
 *          } // while
 *      helper.close();
 *      }
 *  catch (SQLException e)
 *      {
 *      System.out.println(
 *            "***SQLException - Column name: " + helper.getColumnName());
 *      e.printStackTrace();
 *      throw e;
 *      }
 *  catch (Exception e)
 *      {
 *      System.out.println("***" + e);
 *      e.printStackTrace();
 *      throw e;
 *      }
 *  </PRE></CODE>
 *
 *  <P>Quick summary of the column value getter methods:<BR>
 *  (This is here to show the naming convention)
 *
 *  <TABLE>
 *  <TR><TH>method name</TH>  <TH>return type</TH></TR>
 *  <TR><TD>getint</TD>       <TD>int</TD></TR>
 *  <TR><TD>getInteger</TD>   <TD>Integer or null</TD></TR>
 *  <TR><TD>getlong</TD>      <TD>long</TD></TR>
 *  <TR><TD>getLong</TD>      <TD>Long</TD></TR>
 *  <TR><TD>getboolean</TD>   <TD>boolean</TD></TR>
 *  <TR><TD>getBoolean</TD>   <TD>Boolean or null</TD></TR>
 *  <TR><TD>getfloat</TD>     <TD>float</TD></TR>
 *  <TR><TD>getdouble</TD>    <TD>double</TD></TR>
 *  <TR><TD>getDouble</TD>    <TD>Double</TD></TR>
 *  <TR><TD>getFloat</TD>     <TD>Float or null</TD></TR>
 *  <TR><TD>getString</TD>    <TD>String or null</TD></TR>
 *  <TR><TD>getRawString</TD> <TD>String or null</TD></TR>
 *  <TR><TD>getDate</TD>      <TD>java.sql.Date or null</TD></TR>
 *  <TR><TD>getTimestamp</TD> <TD>java.sql.Timestamp or null</TD></TR>
 *  <TR><TD>getBigDecimal</TD><TD>BigDecimal</TD></TR>
 *  </TABLE>
 *
 * Note: the i_reuseStatement field was added to account for a JDBCDriver
 * (The FreeTDS SQLServer driver) that rolls back the connection whenever a
 * statement is closed.  (From my knowledge no other drivers work this way,
 * but this driver does allow reuse of statements).  Note that using the
 * executeQuery(aPreparedStatement) or executeUpdated(aPreparedStatement)
 * methods will close the statement regardless of whether i_reuseStatement
 * is true or not.
 */
public class JDBCHelper
    implements Cloneable
  {
  /**
   * The delimiter characters - single quote.
   */
  public static final String SINGLE_QUOTE = "'";
  /**
   * The delimiter characters - double quote.
   */
  public static final String DOUBLE_QUOTE = "\"";


  /** This is an instance variable to aid with error messages */
  private String     i_columnName = "";
  private int        i_columnIndex = 0;
  private String     i_driverClass = null;
  private String     i_url = null;
  private Properties i_properties = new Properties();
  private Connection i_connection = null;
  private Statement  i_statement = null;
  private ResultSet  i_resultSet = null;
  private String     i_sqlString = "";
  private boolean    i_shouldClose = true;
  private boolean    i_shouldCommitOnClose = true;
  private boolean    i_shouldAutoCommit = false;
  private boolean    i_reuseStatement = false;
  private DataSource i_dataSource = null;
  private boolean    i_inPool = false;
  private JDBCHelperPool i_jdbcHelperPool = null;

  /** This set by the beginTransaction() and endTransaction() methods */
  private boolean    i_isInsideTransaction = false;

  /** log4j category for SQL statements */
  private Category   i_sqlCategory= Category.getInstance("JDBCHelper.willExecute");
  /** log4j category for debugging */
  private Category   i_category = Category.getInstance("JDBCHelper");

  /**
   * Create a JDBC helper that will use the supplied information to get a
   * connection from a JDBC connection pool.  (This may be specific to
   * Weblogic).
   *
   * @param poolName a value of type 'String'
   */
  public JDBCHelper(String driverClass, String url, String poolName)
    {
    i_driverClass = driverClass;
    i_url = url;
    i_properties.put ("connectionPoolID", poolName);
    }


  /**
   * Create a JDBC helper that will use the supplied information to get
   * a connection from a JDBC connection pool.
   *
   * @param poolName a value of type 'String'
   */
  public JDBCHelper(DataSource dataSource)
    {
    if (dataSource == null)
        {
        throw new IllegalArgumentException("dataSource cannot be null");
        }
    i_dataSource = dataSource;
    }


  /**
   * Create a JDBC helper that will use the supplied information to
   * create a JDBC connection.
   *
   * @param poolName a value of type 'String'
   */
  public JDBCHelper(String driverClass,
                    String url,
                    String user,
                    String password)
    {
    i_driverClass = driverClass;
    i_url = url;
    i_properties.put ("user", user);
    i_properties.put ("password", password);
    }

  /**
   * Return whether the close() method should actually close the connection or
   * not.
   * @return Value of shouldClose.
   */
  public boolean getShouldClose ()
    {
    return i_shouldClose;
    }

  /**
   * Set the value of shouldClose.  This should be set to false when you
   * don't want the connection to actually close during the close() method.
   * This is used by the JDBCHelperPool.
   * @param v  Value to assign to shouldClose.
   */
  public void setShouldClose (boolean v)
    {
    i_shouldClose = v;
    }

  /**
   * Return the value of shouldCommitOnClose.
   * @return Value of shouldCommitOnClose.
   */
  public boolean getShouldCommitOnClose ()
    {
    return i_shouldCommitOnClose;
    }

  /**
   * Set the value of shouldCommitOnClose.
   * @param v  Value to assign to shouldCommitOnClose.
   */
  public void setShouldCommitOnClose (boolean v)
    {
    i_shouldCommitOnClose = v;
    }

  /**
   * Return the value of shouldAutoCommit.
   * @return Value of shouldAutoCommit.
   */
  public boolean getShouldAutoCommit ()
    {
    return i_shouldAutoCommit;
    }

  /**
   * Set the value of shouldAutoCommit.
   * @param v  Value to assign to shouldAutoCommit.
   */
  public void setShouldAutoCommit (boolean v)
    {
    i_shouldAutoCommit = v;
    }

  /**
   * Return the value of reuseStatement.
   * @return Value of reuseStatement.
   */
  public boolean getReuseStatement ()
    {
    return i_reuseStatement;
    }

  /**
   * Set the value of reuseStatement.
   * @param v  Value to assign to reuseStatement.
   */
  public void setReuseStatement (boolean v)
    {
    i_reuseStatement = v;
    }

  /**
   * Return the statement last used.
   */
  public Statement getStatement ()
    {
    return i_statement;
    }

  /**
   * Return boolean informing us whether we are inside a "transaction" or not.
   * @return Value of isInsideTransaction.
   */
  public boolean isInsideTransaction()
    {
    return i_isInsideTransaction;
    }

  public JDBCHelperPool getJDBCHelperPool()
    {
    return i_jdbcHelperPool;
    }
  public void setJDBCHelperPool(JDBCHelperPool v)
    {
    i_jdbcHelperPool = v;
    }
  public void returnToPool()
    {
    if (i_jdbcHelperPool != null)
        {
        i_jdbcHelperPool.returnJDBCHelper(this);
        }
    }


  /**
   * Execute the SQL string. The native sql of the query is logged.
   * It is up to the user to make sure this gets closed appropriately.
   *
   * @param sqlString a value of type 'String'
   * @exception SQLException if a database access error occurs
   * @see #LOG_QUERY
   * @see java.sql.Connection#nativeSQL
   */
  public void executeQuery(String sqlString)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    this.validateConnection();
    if (i_sqlCategory.isDebugEnabled())
        { // log the SQL
        i_sqlCategory.debug("[" + i_connection.nativeSQL(sqlString) + "] " + this);
        }
    i_sqlString = sqlString;
    if (i_resultSet != null)
        { // The connection is being reused, but not the resultSet
        i_resultSet.close();
        }
    if (i_statement == null)
        {
        i_statement = i_connection.createStatement();
        }
    else if (!i_reuseStatement)
        {
        i_statement.close();
        i_statement = i_connection.createStatement();
        }
    i_resultSet = i_statement.executeQuery(i_sqlString);
    }

  /**
   * Gets a PreparedStatement for use with executeQuery(aPreparedStatement).
   *
   * @param sqlStatement a SQL statement that may contain one or more '?' IN
   * parameter placeholders
   */
  public PreparedStatement prepareStatement(String sqlStatement)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    this.validateConnection();
    i_sqlString = sqlStatement;
    if (i_sqlCategory.isDebugEnabled())
        { // log the SQL
        i_sqlCategory.debug(
            "[" + i_connection.nativeSQL(sqlStatement) + "] " + this);
        }
    return i_connection.prepareStatement(sqlStatement);
    }

  /**
   * Executes a prepared statement created by prepareStatement().
   *
   * @param stmt prepared statement to execute. All IN parameter values
   * must have been set.
   * @exception SQLException if an error occurs
   */
  public void executeQuery(PreparedStatement stmt)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    if (i_resultSet != null)
        { // The connection is being reused, but not the resultSet
        i_resultSet.close();
        }
    if (i_statement != null)
        { // The connection is being reused, but not the statement
        i_statement.close();
        }
    i_statement = stmt;
    i_resultSet = stmt.executeQuery();
    }


  /**
   * Execute an update/insert/delete.
   *
   * @param sqlString a value of type 'String'
   * @return a value of type 'int'
   * @exception SQLException if an error occurs
   */
  public int executeUpdate(String sqlString)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    Statement statement;

    this.validateConnection();
    if (i_sqlCategory.isDebugEnabled())
        { // log the SQL
        i_sqlCategory.debug(
            "[" + i_connection.nativeSQL(sqlString) + "] " + this);
        }
    if (i_statement == null)
        {
        i_statement = i_connection.createStatement();
        }
    else if (!i_reuseStatement)
        {
        i_statement.close();
        i_statement = i_connection.createStatement();
        }
    return i_statement.executeUpdate(sqlString);
    }


  /**
   * Execute an update/insert/delete on a PreparedStatement
   *
   * @param sqlString a value of type 'String'
   * @return either the row count for INSERT, UPDATE or DELETE statements;
   * or 0 for SQL statements that return nothing
   * @exception SQLException if an error occurs
   */
  public int executeUpdate(PreparedStatement stmt)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    Statement statement;

    this.validateConnection();
    if (i_sqlCategory.isDebugEnabled())
        { // log the SQL
        i_sqlCategory.debug(
            "[" + i_connection.nativeSQL(i_sqlString) + "] " + this);
        }
    if (i_statement != null)
        { // The connection is being reused, but not the statement
        i_statement.close();
        }
    i_statement = stmt;
    return stmt.executeUpdate();
    }


  /**
   * Make sure the connection exists and is open.
   */
  private void validateConnection()
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    if (i_inPool)
        {
        throw new SQLException(
            "This JDBCHelper instance (" + this + ") has been returned to "
            + "the pool. You must retrieve and use another one.");
        }

    i_category.debug(
        "Checking to see if connection is valid: " + i_connection);
    if (i_connection == null || i_connection.isClosed())
        {
        i_statement = null;
        i_resultSet = null;

        i_category.debug("About to get a new connection.");
        if ( i_dataSource != null )
            {
            i_connection = i_dataSource.getConnection();
            }
        else
            {
            i_category.debug("i_driverClass: " + i_driverClass);
            Class.forName (i_driverClass).newInstance();
            i_connection = DriverManager.getConnection (i_url, i_properties);
            }
        i_connection.setAutoCommit(this.getShouldAutoCommit());
        i_category.debug("Connection was created: " + i_connection);