jdbchelper.java

把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 (bought by wamnet.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], [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.Time;
import java.sql.Timestamp;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

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",
 *                              "myPool");
 *      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)
 *      {
 *      LOG.error(
 *            "***SQLException - Column name: " + helper.getColumnName());
 *      e.printStackTrace();
 *      throw e;
 *      }
 *  catch (Exception e)
 *      {
 *      LOG.error("Exception caught in Xyx.abc()", 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>getShort</TD>     <TD>Short</TD></TR>
 *  <TR><TD>getboolean</TD>   <TD>boolean</TD></TR>
 *  <TR><TD>getBoolean</TD>   <TD>Boolean</TD></TR>
 *  <TR><TD>getNullableBoolean</TD><TD>Boolean or null</TD></TR>
 *  <TR><TD>getfloat</TD>     <TD>float</TD></TR>
 *  <TR><TD>getFloat</TD>     <TD>Float or null</TD></TR>
 *  <TR><TD>getdouble</TD>    <TD>double</TD></TR>
 *  <TR><TD>getDouble</TD>    <TD>Double</TD></TR>
 *  <TR><TD>getString</TD>    <TD>trimmed 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>getTime</TD>      <TD>java.sql.Time 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
  {

  /** log4j category for debugging and errors */
  private static final Category LOG =
          Category.getInstance(JDBCHelper.class.getName());
  /** log4j category for logging the SQL */
  private static final Category SQLLOG =
          Category.getInstance(JDBCHelper.class.getName() +".sql");

  /**
   * The delimiter characters - single quote.
   */
  public static final String SINGLE_QUOTE = "'";
  /**
   * The delimiter characters - double quote.
   */
  public static final String DOUBLE_QUOTE = "\"";


  /*** These ivars support the two methods of creating connections */
  private String     i_driverClass = null;
  private String     i_url = null;
  private Properties i_properties = new Properties();
  private String     i_dataSourceName = null;
  private DataSource i_dataSource = null;
  /*** These ivars maintain the connection and SQL processing */
  private Connection i_connection = null;
  private Statement  i_statement = null;
  private ResultSet  i_resultSet = null;
  private String     i_sqlString = "";
  /*** These ivars aid with error messages for SQL debugging */
  private String     i_columnName = "";
  private int        i_columnIndex = 0;

  /*** The following ivars provide support for how SQL processing is done. */
  /** this variable has been replaced by i_commitButDontClose */
  // private boolean    i_shouldClose = true;
  /** this is false when I am in a JDBCHelperPool */
  private boolean    i_commitButDontClose = false;
  private boolean    i_closeButDontCommit = false;
  /** This should be true if shouldAutoCommit is false */
  private boolean    i_shouldCommitOnClose = true;
  /** This value is passed to connection.setAutoCommit(boolean) */
  private boolean    i_shouldAutoCommit = false;
  /** This is true for drivers (like freetds) that ties TX to the statement */
  private boolean    i_reuseStatement = false;

  /*** This variable provides support for a JDBCHelper pool. */
  private boolean    i_inPool = false;
  private JDBCHelperPool i_jdbcHelperPool = null;


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


  public static int s_instanceCount = 0;
  private int i_serialNum = ++s_instanceCount;


  /***************  Constructors  ***************/

  /**
   * Create a JDBCHelper 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'
   * @deprecated Use J2EE DataSource instead
   */
  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 get
   * a connection from a JDBC connection pool.
   *
   * @param poolName a value of type 'String'
   */
  public JDBCHelper(String dataSourceName)
    {
    if (dataSourceName == null)
        {
        throw new IllegalArgumentException("dataSourceName cannot be null");
        }
    i_dataSourceName = dataSourceName;
    }


  /**
   * Create a JDBC helper that will use the supplied information to
   * create a JDBC connection.
   *
   * @param poolName a value of type 'String'
   * @deprecated Use J2EE DataSource instead
   */
  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);
    }


  /*************   Instance Methods   *************/


  /**
   * Return whether the close() method should actually close the connection
   * or not.  This is used by the JDBCHelperPool to keep connections open.
   * @return Value of shouldClose.
   */
  public boolean getCommitButDontClose ()
    {
    return i_commitButDontClose;
    }

  /**
   * Set the value of commitButDontClose.  This should be set to true when
   * you want the connection commit, but not actually close during the close()
   * method.
   * This is used by the JDBCHelperPool to keep it's connections open.
   * @param v  Value to assign to shouldClose.
   */
  public void setCommitButDontClose (boolean v)
    {
    i_commitButDontClose = v;
    }

  /**
   * Return whether the commit() method should actually commit or not.
   * This is needed for EJB server options that don't allow commits.
   * @return Value of shouldClose.
   */
  public boolean getCloseButDontCommit ()
    {
    return i_closeButDontCommit;
    }

  /**
   * Set the value of commitButDontClose.  This should be set to true when you
   * want the connection to be closed but not manually committed.
   * This is needed for EJB server options that don't allow commits.
   * @param v  Value to assign to shouldClose.
   */
  public void setCloseButDontCommit (boolean v)
    {
    i_closeButDontCommit = v;
    }

  /**
   * Return the value of shouldCommitOnClose.    This should be true if
   * shouldAutoCommit is false.
   * @return Value of shouldCommitOnClose.
   */
  public boolean getShouldCommitOnClose ()
    {
    return i_shouldCommitOnClose;
    }

  /**
   * Set the value of shouldCommitOnClose.  This should be true if
   * shouldAutoCommit is false.
   * @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.  This value is passed to
   * Connection#setAutoCommit(boolean) when the connection is created.
   * @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.  This is set to true for drivers
   * (like freeTDS) that tie transactions to the statement instead of
   * the connection.
   * @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
   */
  public void executeQuery(String sqlString)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          NamingException,
          SQLException
    {
    this.validateConnection();
    if (SQLLOG.isDebugEnabled())
        { // log the SQL
        SQLLOG.debug("[" + sqlString + "] " + this);
        }
    i_sqlString = sqlString;

    this.initStatement();

    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,
          NamingException,
          SQLException
    {
    this.validateConnection();
    i_sqlString = sqlStatement;
    if (SQLLOG.isDebugEnabled())
        { // log the SQL
        SQLLOG.debug(
            "[" + 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,
          NamingException,
          SQLException
    {
    this.closeStatement();