databasemanager.java

这是一个J2EE的一个小内容

/**
 * Utility class for database specific operations
 *
 */

/**
 * Change History:
 * Author                      Date                    Version         Details
 * Jerome Josephraj            28 October  2002        1.00.01         Created
 */

package com.ddj.wsstruts.util;

import com.ddj.wsstruts.constant.SystemConstants;
import java.util.Vector;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;


//Log4J imports

public class DatabaseManager
{
    // define a static category variable so that it references the
    // category instance of the same name as this class.
    static final org.apache.log4j.Category category = org.apache.log4j.Category.getInstance(DatabaseManager.class.getName());
    private java.sql.PreparedStatement preparedStatement = null;
    
    
    public synchronized void closePreparedStatement() throws java.sql.SQLException
    {
        if(preparedStatement!=null)
        {
            //preparedStatement.close();
        }
    }
    
    /*
     * Method to get statement.
     * This gets a database as a java object. This then gets casted to a silverstream database
     * From the database a connection object is created to get the database connection
     * These steps are required to enable database pooling in silver stream
     *
     * @param connectionsObjects - A vector object
     * @exception                - Any general exception
     */
    public Statement getStatement(Object connectionsObjects) 
    throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        
        Vector connections = null;
        Statement stmt = null;
        Connection con = null;
        
        //Convert the object to a vector
        connections = (Vector) connectionsObjects;
        
        //Get connection Objects
        con = (Connection) connections.firstElement();
        
        stmt = con.createStatement();
        
        debug("debug"," After create statement ");
        debug("debug",SystemConstants.METHOD_END);
        
        return (Statement) stmt;
    }
    
    /*
     * Method to get a reference of a PreparedStatement.
     * This gets a database as a java object. This then gets casted to a silverstream database
     * From the database a connection object is created to get the database connection
     * These steps are required to enable database pooling in silver stream
     *
     * @param connectionsObjects - A vector object
     * @exception                - Any general exception
     */
    public java.sql.PreparedStatement getPreparedStatement(Object connectionsObjects, String sql) throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        
        Vector connections                  = null;
        Connection con                      = null;
        
        //Convert the object to a vector
        connections = (Vector) connectionsObjects;
        
        //Get connection Objects
        con = (Connection) connections.firstElement();
        
        preparedStatement = con.prepareStatement(sql);
        
        debug("debug"," After creating prepare statement ");
        debug("debug",SystemConstants.METHOD_END);
        
        return preparedStatement;
    }
    
    /*
     * Method to execute sql select statements
     * This method calls getStatement method to get a sql Statement object
     *
     * @param sSql - A string objects containing the sql to execute
     * @param connectionObjects - A vector object containing database connection
     *                            object
     * @exception  - Any general exception
     *
     */
    public ResultSet executeQuery(String sSql,Object connectionObjects) 
    throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        ResultSet resultSet = null;
        
        //Get the resultset
        Statement stmt = (Statement) this.getStatement(connectionObjects);
        resultSet = (ResultSet) stmt.executeQuery(sSql);
        debug("debug",SystemConstants.METHOD_END);
        
        return resultSet;
    }
    
    /*
     * Method to execute sql select statements
     * This method calls getPreparedStatement method to get a sql PreparedStatement object
     *
     * @param sSql - A string objects containing the sql to execute
     * @param connectionObjects - A vector object containing database connection
     *                            object
     * @param data - A Vector containing the data for the search criteria
     * @exception  - Any general exception
     *
     */
    public ResultSet executeQuery(String sSql) 
    throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        ResultSet resultSet = null;
        String url = "jdbc:odbc:DDJNewsContent";

        Connection con;
        Statement stmt;
        try 
        {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } 
        catch(Exception e)
        {
            e.printStackTrace();
        }
        
        con = java.sql.DriverManager.getConnection(url);
        con.setAutoCommit(false);
        stmt = con.createStatement();
        resultSet = (ResultSet) stmt.executeQuery(sSql);
        debug("debug",SystemConstants.METHOD_END);

        return resultSet;
    }
    
    /*
     * Method to execute any sql update statements
     * This method calls getStatement method to get a sql Statement object
     *
     * @param sSql - A string objects containing the sql to execute
     * @param connectionObjects - A vector object containing database connection
     *                            object
     * @exception  - Any general exception
     *
     */
    public int executeUpdate(String sSql,Object connectionObjects) 
    throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        
        Statement stmt = (Statement) this.getStatement(connectionObjects);
        int executedOrNotValue = stmt.executeUpdate(sSql);
        debug("debug"," executedOrNotValue is "+executedOrNotValue);
        debug("debug",SystemConstants.METHOD_END);
        return executedOrNotValue;
    }
    
    /*
     * Method to execute any sql update statements
     * This method calls getStatement method to get a sql Statement object
     *
     * @param sSql - A string objects containing the sql to execute
     * @param connectionObjects - A vector object containing database connection
     *                            object
     * @exception  - Any general exception
     *
     */
    public int executePreparedUpdate(String sSql,Object connectionObjects,Vector data,Vector dataType) 
    throws Exception
    {
        try
        {
            debug("debug",SystemConstants.METHOD_START);
            preparedStatement = getPreparedStatement(connectionObjects,sSql);
            for(int i=0;i<data.size();i++)
            {
                preparedStatement = this.appendData(preparedStatement,i,data,dataType);
            }
            int executedOrNotValue = preparedStatement.executeUpdate();
            debug("debug"," executedOrNotValue is "+executedOrNotValue);            
            debug("debug",SystemConstants.METHOD_END);
            return executedOrNotValue;
        }
        finally
        {
            closePreparedStatement();
        }
    }
    
    /*
     * Method to execute sql insert statements
     * This method calls getStatement method to get a sql Statement object
     *
     * @param sSql - A string objects containing the sql to execute
     * @param connectionObjects - A vector object containing database connection
     *                            object
     * @exception  - Any general exception
     *
     */
    protected int executeInsertUpdate(String sSql, Object connectionObjects) 
    throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        int recordsUpdated=0;
        
        Statement stmt = (Statement) this.getStatement(connectionObjects);
        recordsUpdated = stmt.executeUpdate(sSql);
        
        debug("debug",SystemConstants.METHOD_END);
        return recordsUpdated;
    }
    
    
    /*
     * Method to close database connection
     * This method should be called only for any sql selects
     * For any database update/insert/delete operations a commit or rollback
     * has to be executed first in action class and use closeConnection
     * method in action class to close the database connection
     *
     * @param connectionObjects - A vector object containing database connection  object
     * @exception  - Any general exception
     */
    public void closeConnection(Object connectionObjects) 
    throws Exception
    {
        Vector connections  = null;
        Connection con      = null;
        
        //Convert the object to a vector
        connections = (Vector) connectionObjects;
        if(connections!=null)
        {
            //Get connection Objects
            con = (Connection) connections.firstElement();
            
            if(con!=null)
            {
                //Close the connection
                if (! con.isClosed())
                {
                    con.close();
                }
            }
        }
    }
    
    
    /*
     * Method to get database connection.
     * This method is used primarily by Utility classes which access a static reference of AgiDatabase to get a database connection.
     * This AgiDatabase value is set through the server-start object of SilverStream.
     * This helps in preventing creation of Utility Classes specific to an Application Server
     *
     * @return connections - A vector object containing database connection object
     *
     */
    
    public static Vector getConnection() throws Exception
    {
        debug("debug",SystemConstants.METHOD_START);
        
        //Connection con = getDatabase().getConnection(true);
        com.bitmechanic.sql.ConnectionPoolManager pool =  new com.bitmechanic.sql.ConnectionPoolManager(120);
        
        //Load the driver in VM
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        
    //The following properties should come from a property file
              //For now these are hard coded.
          pool.addAlias("jdbcalias","sun.jdbc.odbc.JdbcOdbcDriver",
                        "jdbc:odbc:DDJNewsContent",
                        "dba",
                        "sql",
                        20,
                        500,
                        10);

        if(category.isDebugEnabled())
        {
            category.info("After adding to pool alias");
        }              


          //
          // put the pool in the application's hashtable so we can look at it
          // later and collect stats from it
          //putValue("pool", pool);

          if(category.isDebugEnabled())
          {
            category.info("After putting the pool values");
          }              

        //Get the connection object
        Connection conn = java.sql.DriverManager.getConnection(com.bitmechanic.sql.ConnectionPoolManager.URL_PREFIX + "jdbcalias", null, null);                  
              
        
        Vector connections = new Vector(1);
        connections.addElement(conn);
        
        debug("debug",SystemConstants.METHOD_END);
        return connections;
    }
    
    
    /*
     * Method to get a static database reference.
     * This method is used primarily to get a static reference of AgiDatabase whose value is set through the server-start object
     * of SilverStream.
     *
     * @return database - A static reference of AgiDatabase, set through the Server-Start class of SilverStream.
     *
     */
    /*
    public static Object getDatabase()
    {
        //return database;
    }
     */
    
    /*
     * Method to set a static database reference.
     * This method is used primarily to set a static reference of AgiDatabase through the server-start object of SilverStream
     * so that it can be used by Utility classes without making them specific to an Application Server.
     *
     * @params refDatabase - A static reference of AgiDatabase, whose value is passed through the Server-Start class of SilverStream.
     *
     */
    /*
    public static void setDatabase(AgiDatabase refDatabase)
    {
        database = refDatabase;
    }
     */
    
    /*
     * Method to set the appropriate property on a PreparedStatement reference with the correct data at the correct position
     *
     * @params preparedStatement - A reference of PreparedStatement to set the data passed
     * @params position          - The position to set the data passed into.
     * @params data              - A Vector of data to be appended.
     *
     * @exception                - SQLException for any exception thrown by the database while setting data via the PreparedStatement
     */
    public synchronized java.sql.PreparedStatement appendData(java.sql.PreparedStatement preparedStatement, int position, Vector data, Vector dataType) throws java.sql.SQLException
    {
        debug("debug",SystemConstants.METHOD_START);
        
        if(data.elementAt(position) instanceof String)
        {
            preparedStatement.setString(position+1,(String)data.elementAt(position));
        }
        else if(data.elementAt(position) instanceof java.sql.Date)
        {
            preparedStatement.setDate(position+1,(java.sql.Date)data.elementAt(position));
        }
        else if(data.elementAt(position) instanceof java.sql.Timestamp)
        {
            preparedStatement.setTimestamp(position+1,(java.sql.Timestamp)data.elementAt(position));
        }
        else if(data.elementAt(position) instanceof Integer)
        {
            preparedStatement.setInt(position+1,((Integer)data.elementAt(position)).intValue());
        }
        else if(data.elementAt(position) instanceof Double)
        {
            preparedStatement.setDouble(position+1,((Double)data.elementAt(position)).doubleValue());
        }
        else
        {
            preparedStatement.setObject(position+1,null,((Integer)dataType.elementAt(position)).intValue());
        }
        
        debug("debug",SystemConstants.METHOD_END);
        return preparedStatement;
        
    }
    
    public static void debug(String debugType,String message)
    {
        if(category.isDebugEnabled())
        {
            if(debugType.equals("debug"))
            {
                category.debug(message);
            }
            else if(debugType.equals("error"))
            {
                category.error(message);
            }
            else if(debugType.equals("info"))
            {
                category.info(message);
            }
        }
    }
}