jdbchelper.java.mod

把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.tools.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.List;
import java.util.Properties;
import java.util.Vector;

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>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
  {
  // setup the log category for use with log4j
  static Category LOG = Category.getInstance(JDBCHelper.class.getName());

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

  /**
   * An empty string
   */
  public static final String EMPTY_STRING = "\"\"";

  /**
   * Constants used for creating sql "IN" clause.
   */
  private static final String IN_EMPTY   = "()";  // keep in sync w/ next two
  private static final char IN_BEGIN = '(';
  private static final char IN_END   = ')';
  private static final char LIST_BEGIN = '[';
  private static final char LIST_END   = ']';


  /**
   * These are internal variables that should be set once w/ the info to
   * create new instances and connect to the database.
   */
  private static String s_jdbcDriverClass;
  private static String s_jdbcDriverURL;
  private static Properties s_jdbcDriverInfo;
  private static String s_jtsDriverClass;
  private static String s_jtsDriverURL;
  private static Properties s_jtsDriverInfo;


  /** This is an instance variable to aid with error messages */
  private String     i_columnName = EMPTY_STRING;
  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 = EMPTY_STRING;
  private boolean    i_shouldCommitOnClose = true;
  private boolean    i_shouldAutoCommit = false;
  private boolean    i_reuseStatement = false;

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


  /*************   static Class methods   *************/
  /**
   * Initialize the driver information for standard JDBC database connections.
   * This should only be called once.
   *
   * @param driverClass the driver's class name
   * @param url the URL for the driver
   * @param poolName the connection pool name
   */
  public static synchronized void initializeJDBCDriver(String driverClass, String url, String poolName)
    {
    s_jdbcDriverClass = driverClass;
    s_jdbcDriverURL = url;

    s_jdbcDriverInfo = new Properties();
    s_jdbcDriverInfo.put ("connectionPoolID", poolName);
    }

  /**
   * Return a new JDBCHelper instance that is setup with a standard JDBC connection.
   * This is dependent on the initializeJDBCDriver method having been called.
   *
   * @return a new JDBCHelper instance
   * @see #initializeJDBCDriver
   */
  public static JDBCHelper newJDBCHelper()
    {
    return new JDBCHelper(s_jdbcDriverClass,
                          s_jdbcDriverURL,
                          s_jdbcDriverInfo);
    }

  /**
   * Initialize the driver information for JTS JDBC database connections.
   * This should only be called once.
   *
   * @param driverClass the driver's class name
   * @param url the URL for the driver
   * @param poolName the connection pool name
   */
  public static synchronized void initializeJTSDriver(String driverClass, String url, String poolName)
    {
    s_jtsDriverClass = driverClass;
    s_jtsDriverURL = url;

    s_jtsDriverInfo = new Properties();
    s_jtsDriverInfo.put ("connectionPoolID", poolName);
    }

  /**
   * Return a new JDBCHelper instance that is setup with a JTS JDBC connection.
   * This is dependent on the initializeJTSDriver method having been called.
   *
   * @return a new JTS JDBCHelper instance
   * @see #initializeJTSDriver
   */
  public static JDBCHelper newJTSHelper()
    {
    return new JDBCHelper(s_jtsDriverClass,
                          s_jtsDriverURL,
                          s_jtsDriverInfo);
    }


  /**
   * Convenience method that performs a JTS Update based on the SQL passed
   * in and returns the number of rows updated. The underlying purpose
   * of this method is to centralize the database access and exception
   * handling.
   *
   * @param sqlString SQL statement to be used for perform
   *                    insert/update/delete
   * @param message Message to be logged
   * @return Number of rows affected by the database call
   * @exception SQLException if an error occurs
   * @see #newJTSHelper
   * @see #executeUpdate(String)
   */
  public static int executeUpdate(String sqlString, String message)
          throws SQLException
    {
    JDBCHelper dbHelper = null;
    int resultCount = 0;

    try
        {
        // update info in the DB
        dbHelper = JDBCHelper.newJTSHelper();
        // Capture the resultcount to pass back
        resultCount = dbHelper.executeUpdate(sqlString);
        }
    // check to see if a column was in exception
    catch (SQLException e)
        {
        if (dbHelper != null)
            {
            LOG.error(message
                      + " - ***SQLException - Column name: "
                      + dbHelper.getColumnName());
            }
        throw e;
        }
    // catch the rest of the exceptions
    catch (ClassNotFoundException e)
        {
        throw new SQLException(message
                               + "JDBCHelper.executeUpdate - Database access failure : " + e);
        }
    catch (InstantiationException e)
        {
        throw new SQLException(message
                               + "JDBCHelper.executeUpdate - Database access failure : " + e);
        }
    catch (IllegalAccessException e)
        {
        throw new SQLException(message
                               + "JDBCHelper.executeUpdate - Database access failure : " + e);
        }
    finally
        {
        // all done, close up
        if (dbHelper != null)
            {
            dbHelper.close();
            }
        }

    // return the number of rows affected
    return resultCount;
    }


  /*************   Constructors and instance methods   *************/
  /**
   * Create a JDBC helper that will use the supplied information to get
   * a connection.
   *
   * @param driverClass the driver's class name
   * @param url the URL for the driver
   * @param connectionInfo the connection information
   */
  private JDBCHelper(String driverClass, String url, Properties connectionInfo)
    {
    super();
    i_driverClass = driverClass;
    i_url = url;
    i_properties = connectionInfo;  // possibly should do clone, but not for now.
    }


  /**
   * Create a JDBC helper that will use the supplied information to get
   * a connection from a JDBC connection pool.
   *
   * @param driverClass the driver's class name
   * @param url the URL for the driver
   * @param poolName the connection pool name
   */
  public JDBCHelper(String driverClass, String url, String poolName)
    {
    super();
    i_driverClass = driverClass;
    i_url = url;
    i_properties.put ("connectionPoolID", poolName);
    }


  /**
   * Create a JDBC helper that will use the supplied information to
   * create a JDBC connection.
   *
   * @param driverClass the driver's class name
   * @param url the URL for the driver
   * @param user the database username for login
   * @param password the database password for login
   */
  public JDBCHelper(String driverClass,
                    String url,
                    String user,
                    String password)
    {
    super();
    i_driverClass = driverClass;
    i_url = url;
    i_properties.put ("user", user);
    i_properties.put ("password", password);
    }


  /**
   * 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 boolean informing us whether we are inside a "transaction" or not.
   * @return Value of isInsideTransaction.
   */
  public boolean isInsideTransaction()
    {
    return i_isInsideTransaction;
    }


  /**
   * 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 java.sql.Connection#nativeSQL
   */
  public void executeQuery(String sqlString)
          throws ClassNotFoundException,
          InstantiationException,
          IllegalAccessException,
          SQLException
    {
    this.validateConnection();
    if (LOG.isDebugEnabled())
        {
        LOG.debug("[" + sqlString + "]");
        }
    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();
    if (LOG.isDebugEnabled())
        {
        LOG.debug("[" + sqlStatement + "]");
        }

    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'