root/trunk/src/java/org/jcoderz/commons/util/DbUtil.java

/*
 * $Id$
 *
 * Copyright 2006, The jCoderZ.org Project. All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are
 * met:
 *
 *    * Redistributions of source code must retain the above copyright
 *      notice, this list of conditions and the following disclaimer.
 *    * Redistributions in binary form must reproduce the above
 *      copyright notice, this list of conditions and the following
 *      disclaimer in the documentation and/or other materials
 *      provided with the distribution.
 *    * Neither the name of the jCoderZ.org Project nor the names of
 *      its contributors may be used to endorse or promote products
 *      derived from this software without specific prior written
 *      permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS AND CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
 * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
 * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
 * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
 * ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.jcoderz.commons.util;

import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
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.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Utility class for some DB routines.
 *
 * @author Albrecht Messner
 */
public final class DbUtil
{
   /** class name for use in logger */
   private static final String CLASSNAME = DbUtil.class.getName();

   /** logging facility */
   private static final Logger logger = Logger.getLogger(CLASSNAME);

   /** The oracle driver class for Oracle 8.x and 9.x */
   private static final String ORACLE_DRIVER_CLASSNAME
         = "oracle.jdbc.OracleDriver";
   /**
    * private constructor to avoid instantiation.
    */
   private DbUtil ()
   {
      // utility class -- no instances are allowed.
   }

   /**
    * Close a statement according to resource handling idiom.
    * @param stmt the statement to be closed
    */
   public static void close (Statement stmt)
   {
      if (stmt != null)
      {
         try
         {
            stmt.close();
         }
         catch (SQLException x)
         {
            logger.log(Level.FINE, "Error during resource cleanup: "
                  + "java.sql.Statement.close()", x);
         }
      }
   }

   /**
    * Close a DB connection according to resource handling idiom.
    * @param con the connection to be closed
    */
   public static void close (Connection con)
   {
      if (con != null)
      {
         try
         {
            con.close();
         }
         catch (SQLException x)
         {
            logger.log(Level.FINE, "Error during resource cleanup: "
                  + "java.sql.Connection.close()", x);
         }
      }
   }

   /**
    * Close a result set according to resource handling idiom.
    * @param resultSet the result set to close
    */
   public static void close (ResultSet resultSet)
   {
      if (resultSet != null)
      {
         try
         {
            resultSet.close();
         }
         catch (SQLException x)
         {
            logger.log(Level.FINE, "Error during resource cleanup: "
                  + "java.sql.ResultSet.close()", x);
         }
      }
   }

   /**
    * Sets a parameter in a PreparedStatement to either a value or null.
    *
    * @param pstmt the prepared statement
    * @param paramIndex the parameter index of the parameter to be set
    * @param value the value to which the parameter must be set
    * @throws SQLException if a database access error occurs.
    */
   public static void setVarcharOrNull (
         PreparedStatement pstmt, int paramIndex, String value)
         throws SQLException
   {
      setParameterOrNull(pstmt, paramIndex, value, Types.VARCHAR);
   }

   /**
    * Sets a parameter in a PreparedStatement to either a value or null.
    *
    * @param pstmt the prepared statement
    * @param paramIndex the parameter index of the parameter to be set
    * @param value the value to which the parameter must be set
    * @throws SQLException if a database access error occurs.
    */
   public static void setCharOrNull (
         PreparedStatement pstmt, int paramIndex, String value)
         throws SQLException
   {
      setParameterOrNull(pstmt, paramIndex, value, Types.CHAR);
   }

   /**
    * Sets a parameter in a PreparedStatement to either a value or null.
    *
    * @param pstmt the prepared statement
    * @param paramIndex the parameter index of the parameter to be set
    * @param value the value to which the parameter must be set
    * @throws SQLException if a database access error occurs.
    */
   public static void setClobOrNull (
         PreparedStatement pstmt, int paramIndex, String value)
         throws SQLException
   {
      setParameterOrNull(pstmt, paramIndex, value, Types.CLOB);
   }

   /**
    * Sets a parameter in a PreparedStatement to either a value or null.
    *
    * @param pstmt the prepared statement
    * @param paramIndex the parameter index of the parameter to be set
    * @param value the value to which the parameter must be set
    * @throws SQLException if a database access error occurs.
    */
   public static void setTimestampOrNull (
         PreparedStatement pstmt, int paramIndex, Timestamp value)
         throws SQLException
   {
      setParameterOrNull(pstmt, paramIndex, value, Types.TIMESTAMP);
   }

   /**
    * Sets a parameter in a PreparedStatement to either a value or null.
    *
    * @param pstmt the prepared statement
    * @param paramIndex the parameter index of the parameter to be set
    * @param value the value to which the parameter must be set
    * @throws SQLException if a database access error occurs.
    */
   public static void setIntegerOrNull (
         PreparedStatement pstmt, int paramIndex, Integer value)
         throws SQLException
   {
      setParameterOrNull(pstmt, paramIndex, value, Types.INTEGER);
   }

   /**
    * Sets a parameter in a PreparedStatement to either a value or null.
    *
    * @param pstmt the prepared statement
    * @param paramIndex the parameter index of the parameter to be set
    * @param value the value to which the parameter must be set
    * @throws SQLException if a database access error occurs.
    */
   public static void setLongOrNull (
         PreparedStatement pstmt, int paramIndex, Long value)
         throws SQLException
   {
      // Sorry, can't delegate this one to setParameterOrNull because
      // it's got a different type than setIntegerOrNull, but the same
      // SQL type code.
      if (value != null)
      {
         pstmt.setLong(paramIndex, value.longValue());
      }
      else
      {
         pstmt.setNull(paramIndex, Types.INTEGER);
      }
   }

   /**
    * Registers the oracle JDBC driver.
    *
    * NOTE: only use either in testcases that need a direct JDBC connection
    * or in stand-alone applications, never in the Application Server.
    */
   public static void registerOracleDriver ()
   {
      try
      {
         final Class driverClass
               = Class.forName(ORACLE_DRIVER_CLASSNAME);
         final Driver driverObject = (Driver) driverClass.newInstance();
         DriverManager.registerDriver(driverObject);
      }
      catch (Exception e)
      {
         final RuntimeException rte = new RuntimeException(
               "Failed to register oracle driver "
               + ORACLE_DRIVER_CLASSNAME, e);
         throw rte;
      }
   }

   /**
    * This method wraps the {@link PreparedStatement} given as argument
    * into a delegate object that calls the <code>executeBatch</code>
    * method every <code>maxBatchSize</code>th time a batch is added to
    * the statement with the <code>addBatch</code> method.
    *
    * @param pstmt the prepared statement to wrap
    * @param maxBatchSize the maximum batch size at which an executeBatch
    *       should be called
    * @return a PreparedStatement implementation that does automatic
    *       batch updates
    */
   public static PreparedStatement getLimitedBatchSizePreparedStatement (
         PreparedStatement pstmt, int maxBatchSize)
   {
      return null; // new LimitedBatchSizePreparedStatement(pstmt, maxBatchSize);
   }

   private static void setParameterOrNull (
         PreparedStatement pstmt, int paramIndex, Object value, int type)
         throws SQLException
   {
      if (value != null)
      {
         switch (type)
         {
            case Types.VARCHAR:
            case Types.CHAR:
               pstmt.setString(paramIndex, (String) value);
               break;
            case Types.CLOB:
               final String strVal = (String) value;
               pstmt.setCharacterStream(
                     paramIndex, new StringReader(strVal), strVal.length());
               break;
            case Types.TIMESTAMP:
               final Timestamp tstampVal = (Timestamp) value;
               pstmt.setTimestamp(paramIndex, tstampVal);
               break;
            case Types.INTEGER:
               final Integer intVal = (Integer) value;
               pstmt.setInt(paramIndex, intVal.intValue());
               break;
            default:
               throw new RuntimeException("Unexpected SQL type "
                     + type + " encountered");
         }
      }
      else
      {
         pstmt.setNull(paramIndex, type);
      }
   }

   private static final class LimitedBatchSizePreparedStatement
// FIXME: does not work with JDK1.6.0     implements PreparedStatement
   {
      private final PreparedStatement mPreparedStatement;
      private final int mMaxBatchSize;
      private final List mResultList = new ArrayList();

      private int mBatchCount = 0;

      private LimitedBatchSizePreparedStatement
            (PreparedStatement pstmt, int maxBatchSize)
      {
         mPreparedStatement = pstmt;
         mMaxBatchSize = maxBatchSize;
      }

      /** {@inheritDoc} */
      public void addBatch ()
            throws SQLException
      {
         if (mBatchCount >= mMaxBatchSize)
         {
            internalExecuteBatch();
         }
         mPreparedStatement.addBatch();
         mBatchCount++;
      }

      /** {@inheritDoc} */
      public void addBatch (String sql)
            throws SQLException
      {
         if (mBatchCount >= mMaxBatchSize)
         {
            internalExecuteBatch();
         }
         mPreparedStatement.addBatch(sql);
         mBatchCount++;
      }

      /** {@inheritDoc} */
      public int[] executeBatch ()
            throws SQLException
      {
         internalExecuteBatch();

         int totalBatchSize = 0;
         for (final Iterator it = mResultList.iterator(); it.hasNext(); )
         {
            final int[] updateResult = (int[]) it.next();
            totalBatchSize += updateResult.length;
         }

         final int[] result = new int[totalBatchSize];
         int offset = 0;
         for (final Iterator it = mResultList.iterator(); it.hasNext(); )
         {
            final int[] updateResult = (int[]) it.next();
            System.arraycopy(
                  updateResult, 0, result, offset, updateResult.length);
            offset += updateResult.length;
         }
         mResultList.clear();
         return result;
      }

      /** {@inheritDoc} */
      public void clearBatch ()
            throws SQLException
      {
         mPreparedStatement.clearBatch();
         mBatchCount = 0;
         mResultList.clear();
      }

      /** {@inheritDoc} */
      public String toString ()
      {
         return "[LimitedBatchSizePreparedStatement: mBatchSize=" 
               + mMaxBatchSize + ", mPreparedStatement=" 
               + mPreparedStatement + "]";
      }

      private void internalExecuteBatch ()
            throws SQLException
      {
         mBatchCount = 0;
         final int[] result = mPreparedStatement.executeBatch();
         mResultList.add(result.clone());
      }

      // ==============================================================
      // only delegates below this point
      // ==============================================================

      /** {@inheritDoc} */
      public void cancel ()
            throws SQLException
      {
         mPreparedStatement.cancel();
      }

      /** {@inheritDoc} */
      public void clearParameters ()
            throws SQLException
      {
         mPreparedStatement.clearParameters();
      }

      /** {@inheritDoc} */
      public void clearWarnings ()
            throws SQLException
      {
         mPreparedStatement.clearWarnings();
      }

      /** {@inheritDoc} */
      public void close ()
            throws SQLException
      {
         mPreparedStatement.close();
      }

      /** {@inheritDoc} */
      public boolean execute ()
            throws SQLException
      {
         return mPreparedStatement.execute();
      }

      /** {@inheritDoc} */
      public boolean execute (String sql)
            throws SQLException
      {
         return mPreparedStatement.execute(sql);
      }

      /** {@inheritDoc} */
      public boolean execute (String sql, int autoGeneratedKeys)
            throws SQLException
      {
         return mPreparedStatement.execute(sql, autoGeneratedKeys);
      }

      /** {@inheritDoc} */
      public boolean execute (String sql, int[] columnIndexes)
            throws SQLException
      {
         return mPreparedStatement.execute(sql, columnIndexes);
      }

      /** {@inheritDoc} */
      public boolean execute (String sql, String[] columnNames)
            throws SQLException
      {
         return mPreparedStatement.execute(sql, columnNames);
      }

      /** {@inheritDoc} */
      public ResultSet executeQuery ()
            throws SQLException
      {
         return mPreparedStatement.executeQuery();
      }

      /** {@inheritDoc} */
      public ResultSet executeQuery (String sql)
            throws SQLException
      {
         return mPreparedStatement.executeQuery(sql);
      }

      /** {@inheritDoc} */
      public int executeUpdate ()
            throws SQLException
      {
         return mPreparedStatement.executeUpdate();
      }

      /** {@inheritDoc} */
      public int executeUpdate (String sql)
            throws SQLException
      {
         return mPreparedStatement.executeUpdate(sql);
      }

      /** {@inheritDoc} */
      public int executeUpdate (String sql, int autoGeneratedKeys)
            throws SQLException
      {
         return mPreparedStatement.executeUpdate(sql, autoGeneratedKeys);
      }

      /** {@inheritDoc} */
      public int executeUpdate (String sql, int[] columnIndexes)
            throws SQLException
      {
         return mPreparedStatement.executeUpdate(sql, columnIndexes);
      }

      /** {@inheritDoc} */
      public int executeUpdate (String sql, String[] columnNames)
            throws SQLException
      {
         return mPreparedStatement.executeUpdate(sql, columnNames);
      }

      /** {@inheritDoc} */
      public Connection getConnection ()
            throws SQLException
      {
         return mPreparedStatement.getConnection();
      }

      /** {@inheritDoc} */
      public int getFetchDirection ()
            throws SQLException
      {
         return mPreparedStatement.getFetchDirection();
      }

      /** {@inheritDoc} */
      public int getFetchSize ()
            throws SQLException
      {
         return mPreparedStatement.getFetchSize();
      }

      /** {@inheritDoc} */
      public ResultSet getGeneratedKeys ()
            throws SQLException
      {
         return mPreparedStatement.getGeneratedKeys();
      }

      /** {@inheritDoc} */
      public int getMaxFieldSize ()
            throws SQLException
      {
         return mPreparedStatement.getMaxFieldSize();
      }

      /** {@inheritDoc} */
      public int getMaxRows ()
            throws SQLException
      {
         return mPreparedStatement.getMaxRows();
      }

      /** {@inheritDoc} */
      public ResultSetMetaData getMetaData ()
            throws SQLException
      {
         return mPreparedStatement.getMetaData();
      }

      /** {@inheritDoc} */
      public boolean getMoreResults ()
            throws SQLException
      {
         return mPreparedStatement.getMoreResults();
      }

      /** {@inheritDoc} */
      public boolean getMoreResults (int current)
            throws SQLException
      {
         return mPreparedStatement.getMoreResults(current);
      }

      /** {@inheritDoc} */
      public ParameterMetaData getParameterMetaData ()
            throws SQLException
      {
         return mPreparedStatement.getParameterMetaData();
      }

      /** {@inheritDoc} */
      public int getQueryTimeout ()
            throws SQLException
      {
         return mPreparedStatement.getQueryTimeout();
      }

      /** {@inheritDoc} */
      public ResultSet getResultSet ()
            throws SQLException
      {
         return mPreparedStatement.getResultSet();
      }

      /** {@inheritDoc} */
      public int getResultSetConcurrency ()
            throws SQLException
      {
         return mPreparedStatement.getResultSetConcurrency();
      }

      /** {@inheritDoc} */
      public int getResultSetHoldability ()
            throws SQLException
      {
         return mPreparedStatement.getResultSetHoldability();
      }

      /** {@inheritDoc} */
      public int getResultSetType ()
            throws SQLException
      {
         return mPreparedStatement.getResultSetType();
      }

      /** {@inheritDoc} */
      public int getUpdateCount ()
            throws SQLException
      {
         return mPreparedStatement.getUpdateCount();
      }

      /** {@inheritDoc} */
      public SQLWarning getWarnings ()
            throws SQLException
      {
         return mPreparedStatement.getWarnings();
      }

      /** {@inheritDoc} */
      public void setArray (int i, Array x)
            throws SQLException
      {
         mPreparedStatement.setArray(i, x);
      }

      /** {@inheritDoc} */
      public void setAsciiStream (int parameterIndex, InputStream x, int length)
            throws SQLException
      {
         mPreparedStatement.setAsciiStream(parameterIndex, x, length);
      }

      /** {@inheritDoc} */
      public void setBigDecimal (int parameterIndex, BigDecimal x)
            throws SQLException
      {
         mPreparedStatement.setBigDecimal(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setBinaryStream (
            int parameterIndex, InputStream x, int length)
            throws SQLException
      {
         mPreparedStatement.setBinaryStream(parameterIndex, x, length);
      }

      /** {@inheritDoc} */
      public void setBlob (int i, Blob x)
            throws SQLException
      {
         mPreparedStatement.setBlob(i, x);
      }

      /** {@inheritDoc} */
      public void setBoolean (int parameterIndex, boolean x)
            throws SQLException
      {
         mPreparedStatement.setBoolean(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setByte (int parameterIndex, byte x)
            throws SQLException
      {
         mPreparedStatement.setByte(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setBytes (int parameterIndex, byte[] x)
            throws SQLException
      {
         mPreparedStatement.setBytes(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setCharacterStream (int parameterIndex, Reader reader,
            int length)
            throws SQLException
      {
         mPreparedStatement.setCharacterStream(parameterIndex, reader, length);
      }

      /** {@inheritDoc} */
      public void setClob (int i, Clob x)
            throws SQLException
      {
         mPreparedStatement.setClob(i, x);
      }

      /** {@inheritDoc} */
      public void setCursorName (String name)
            throws SQLException
      {
         mPreparedStatement.setCursorName(name);
      }

      /** {@inheritDoc} */
      public void setDate (int parameterIndex, Date x)
            throws SQLException
      {
         mPreparedStatement.setDate(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setDate (int parameterIndex, Date x, Calendar cal)
            throws SQLException
      {
         mPreparedStatement.setDate(parameterIndex, x, cal);
      }

      /** {@inheritDoc} */
      public void setDouble (int parameterIndex, double x)
            throws SQLException
      {
         mPreparedStatement.setDouble(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setEscapeProcessing (boolean enable)
            throws SQLException
      {
         mPreparedStatement.setEscapeProcessing(enable);
      }

      /** {@inheritDoc} */
      public void setFetchDirection (int direction)
            throws SQLException
      {
         mPreparedStatement.setFetchDirection(direction);
      }

      /** {@inheritDoc} */
      public void setFetchSize (int rows)
            throws SQLException
      {
         mPreparedStatement.setFetchSize(rows);
      }

      /** {@inheritDoc} */
      public void setFloat (int parameterIndex, float x)
            throws SQLException
      {
         mPreparedStatement.setFloat(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setInt (int parameterIndex, int x)
            throws SQLException
      {
         mPreparedStatement.setInt(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setLong (int parameterIndex, long x)
            throws SQLException
      {
         mPreparedStatement.setLong(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setMaxFieldSize (int max)
            throws SQLException
      {
         mPreparedStatement.setMaxFieldSize(max);
      }

      /** {@inheritDoc} */
      public void setMaxRows (int max)
            throws SQLException
      {
         mPreparedStatement.setMaxRows(max);
      }

      /** {@inheritDoc} */
      public void setNull (int parameterIndex, int sqlType)
            throws SQLException
      {
         mPreparedStatement.setNull(parameterIndex, sqlType);
      }

      /** {@inheritDoc} */
      public void setNull (int paramIndex, int sqlType, String typeName)
            throws SQLException
      {
         mPreparedStatement.setNull(paramIndex, sqlType, typeName);
      }

      /** {@inheritDoc} */
      public void setObject (int parameterIndex, Object x)
            throws SQLException
      {
         mPreparedStatement.setObject(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setObject (int parameterIndex, Object x, int targetSqlType)
            throws SQLException
      {
         mPreparedStatement.setObject(parameterIndex, x, targetSqlType);
      }

      /** {@inheritDoc} */
      public void setObject (int parameterIndex, Object x, int targetSqlType,
            int scale)
            throws SQLException
      {
         mPreparedStatement.setObject(parameterIndex, x, targetSqlType, scale);
      }

      /** {@inheritDoc} */
      public void setQueryTimeout (int seconds)
            throws SQLException
      {
         mPreparedStatement.setQueryTimeout(seconds);
      }

      /** {@inheritDoc} */
      public void setRef (int i, Ref x)
            throws SQLException
      {
         mPreparedStatement.setRef(i, x);
      }

      /** {@inheritDoc} */
      public void setShort (int parameterIndex, short x)
            throws SQLException
      {
         mPreparedStatement.setShort(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setString (int parameterIndex, String x)
            throws SQLException
      {
         mPreparedStatement.setString(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setTime (int parameterIndex, Time x)
            throws SQLException
      {
         mPreparedStatement.setTime(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setTime (int parameterIndex, Time x, Calendar cal)
            throws SQLException
      {
         mPreparedStatement.setTime(parameterIndex, x, cal);
      }

      /** {@inheritDoc} */
      public void setTimestamp (int parameterIndex, Timestamp x)
            throws SQLException
      {
         mPreparedStatement.setTimestamp(parameterIndex, x);
      }

      /** {@inheritDoc} */
      public void setTimestamp (int parameterIndex, Timestamp x, Calendar cal)
            throws SQLException
      {
         mPreparedStatement.setTimestamp(parameterIndex, x, cal);
      }

      /** {@inheritDoc} */
      public void setUnicodeStream (int parameterIndex, InputStream x,
            int length)
            throws SQLException
      {
         mPreparedStatement.setUnicodeStream(parameterIndex, x, length);
      }

      /** {@inheritDoc} */
      public void setURL (int parameterIndex, URL x)
            throws SQLException
      {
         mPreparedStatement.setURL(parameterIndex, x);
      }
   }
}