dbconnect.java

ORACLE AQ sample 演示的如何出列

package oracle.otnsamples.AQ.DataBase;


/**
 * @author  Rajat Gupta
 * @version 1.0
 *
 * Name of the Application        :  DBConnect.java
 * Development Environment        :  Oracle 9i JDeveloper
 * Creation/Modification History  :
 *
 *    Rajat Gupta       15-Jan-2001      Created
 *
 */

// SQL Imports
import javax.sql.DataSource;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Connection;
import java.sql.Statement;

// Naming Imports
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.InitialContext;

// Util Imports
import java.util.Hashtable;
import java.util.Properties;
import java.util.Vector;

// Net Imports
import java.net.URL;

// W3C Imports
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.w3c.dom.NamedNodeMap;

// Oracle XML Imports
import org.xml.sax.SAXException;

import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLParser;
import oracle.xml.parser.v2.XMLParseException;

// IO Imports
import java.io.IOException;

// Oracle Schema Imports
import oracle.xml.parser.schema.XSDBuilder;
import oracle.xml.parser.schema.XMLSchema;

// Other files
import oracle.otnsamples.AQ.Helper.CreateURL;


/**
 * This class has been implemented as a Singleton Class. This means that at any given
 * time, the server will hold only one instance of this class. There is a static method
 * through which the sample gets the instance of the class. It parses the
 * DataBaseParameters.xml file and gets the DSN name of the databases. The DataSources are
 * then maintained as a global variable.
 * It has two important methods namely, executeUpdate and executeQuery, which are used by
 * the calling classes to do some database operations.
 * When a select query is invoked on the database then a Vector is returned. The Vector
 * contains a String Array which holds the values of the row.
 *   For ex. if a query "select DNAME, DEPTNO, LOC from DEPT" is executed in the database
 *   then a Vector will be generated. Each row of the Vector will contain a String Array
 *   which will hold values for a particular row. In this case, the first element of the
 *   String Array will have DNAME, the second element of the String Array will have DEPTNO
 *   and the third element of the String Array will have LOC values. The size of the Vector
 *   will be equal to the number of rows selected for that particular query.
 */
public class DBConnect{

  /**
   * DataSourceName for Retail and Customer Database.
   */
  private DataSource        m_retailDataSource;

  /**
   * DataSource Name for Computer Database
   */
  private DataSource        m_computerDataSource;

  /**
   * DataSource Name for Printer Database
   */
  private DataSource        m_printerDataSource;

  /**
   * Static filed that stores its own instance.
   */
  private static DBConnect  m_DBObj;


  /**
   * As this class implements the Singleton Design Pattern to the constructor plays a
   * very important part in the design. It first parses the DataBaseParameters.xml
   * file and gets the DatasourceLookup String in a Hashtable. It then initializes
   * the DataSource to the different DataBases and stores them in a global variable.
   *
   * @exception Exception if it is not able to initialize the DataSource
   * @see CreateURL.java
   */
  private DBConnect() throws Exception{
    try{

      Hashtable dbParameters;

      Context ctx;

      // Generate a URL of the filename.
      CreateURL createURL = new CreateURL();
      URL xmlURL = createURL.getURL("AQConfig/DataBaseParameters.xml");
      URL schemaURL = createURL.getURL("AQConfig/DataBaseParameters.xsd");

      // Parse the XML file and gets the DatasourceLookup String in a Hashtable
      dbParameters = parseXML(xmlURL, schemaURL);

      // Here, the program gets the DataSource Objects and stores them in the
      // global variable.
      Properties env = new Properties();

      env.put (Context.INITIAL_CONTEXT_FACTORY,
                 "com.evermind.server.ApplicationInitialContextFactory");

      // Get the initial Context.
      ctx = new InitialContext(env);

      // Look up the JNDI Connection.
      m_retailDataSource = (DataSource)ctx.lookup(dbParameters.get("Retail").toString());
      m_computerDataSource = (DataSource)ctx.lookup(dbParameters.get("Computer").toString());
      m_printerDataSource = (DataSource)ctx.lookup(dbParameters.get("Printer").toString());
    }
    catch(NamingException p_namingexp){
      p_namingexp.printStackTrace();
      throw new Exception("AQ-1007");
    }
    catch(Exception p_exp){
      throw p_exp;
    }

  }


  /**
   * This method parses the DataBaseParameters.xml and stores the resultant information
   * in a HashTable.
   *
   * @param p_xmlURL Takes in the URL to the XML file
   * @param p_schemaURL Takes in the URL to the Schema file
   * @exception Exception if the specified XML file is not present or there is
   *      some problem while parsing the XML file
   * @return Stores the DataBaseLookup String
   */
  static Hashtable parseXML(URL p_xmlURL, URL p_schemaURL) throws Exception{
    Hashtable dbParameters = new Hashtable();
    try{

      // Define the variables used for parsing
      Node childNode, node;
      NodeList nodeList;
      NamedNodeMap nnm;

      // Get an instance of the parser
      DOMParser parser = new DOMParser();
      XSDBuilder builder = new XSDBuilder();

      XMLSchema schemadoc = (XMLSchema)builder.build(p_schemaURL);
      parser.setXMLSchema(schemadoc);

      // Set various parser options: validation on,
      // warnings shown, error stream set to stderr.
      parser.setErrorStream(System.err);

      // This statement will be used when we write the schema for the XML file
      parser.setValidationMode(XMLParser.SCHEMA_VALIDATION);

      parser.showWarnings(true);

      // Parse the document.
      parser.parse(p_xmlURL);

      // Obtain the document.
      XMLDocument doc = parser.getDocument();

      // Get all the Child Nodes in a NodeList
      nodeList = doc.getFirstChild().getNextSibling().getNextSibling().getChildNodes();

      // Iterate through the nodelist and get the Attribute and value of
      // DataSource used to connect to the DataBase. Store these then in
      // the Hastable.
      for (int j=0; j<nodeList.getLength() ; j++){
        childNode = nodeList.item(j);
        nnm = childNode.getAttributes();
        node = nnm.item(0);

        dbParameters.put(node.getNodeValue(),childNode.getFirstChild().getFirstChild().getNodeValue());
      }
      return dbParameters;
    }
    catch(XMLParseException p_xmlexp){
      p_xmlexp.printStackTrace();
      throw new Exception("AQ-1017");
    }catch(IOException p_ioexp){
      p_ioexp.printStackTrace();
      throw new Exception("AQ-1005");
    }
    catch(SAXException p_saxexp){
      p_saxexp.printStackTrace();
      throw new Exception("AQ-1008");
    }
    catch(Exception p_exp){
      throw p_exp;
    }
  }

  /**
   * Static method which returns the object/instance of this singleton class.
   * This method first checks if an object already exists; if it does then
   * returns the same object, if it doesn't the creates a new object and returns it.
   *
   * @exception Exception if unable to initialize the Class instance
   * @return Class Instance
   */
  public static DBConnect getInstance() throws Exception{
    // Check if the object is created.
    if (m_DBObj == null){
      // If the object is not created, then create it
      m_DBObj = new DBConnect();
    }
    return m_DBObj;

  }


  /**
   * This method is called when the invoking class wants to insert, delete or update
   * a table in the database. If, only a single operation is to be done on the database,
   * then this method is called.
   *
   * @exception Exception if unable to execute the query
   * @param p_query Query to be executed in the database
   * @param p_user Type of user
   * @return returns 0 if the operation fails or else returns the number of
   *     rows effected in the database
   */
  public int executeUpdate(String p_query, String p_user) throws Exception{
      // Creates a String Array of the query and is passed to the executeUpdate
      // method for executing the query in the database
      int i[] = executeUpdate(new String[]{p_query},p_user);

      // Returns the query status (successful or unsuccessful)
      return i[0];
  }


  /**
   * This method is called when the invoking class wants to insert, delete or update
   * a table in the database. If, multiple operations are to be done on the database,
   * then this method can be called. All the queries can be passed in a String
   * Array to the method and all the queries
   * will be executed in the database one
   * by one.
   *
   * @exception Exception if unable to execute a query
   * @param p_queriesArr String Array which holds all the queries to be
   *     executed in the database
   * @param p_user Type of User
   * @return Int Array which stores corresponding values for the queries.
   */
  public int[] executeUpdate(String[] p_queriesArr, String p_user) throws Exception{
    int[] i = null;

    // initialize Connection Object
    Connection conn = null;

    // initialize statement Object
    Statement stmt = null;

    DataSource ds;

    try{
      // Gets the DataSource Object for the particular User
      ds = getDataSource(p_user);

      // Gets the Connection
      conn = (Connection)ds.getConnection();

      int length = p_queriesArr.length;
      stmt = conn.createStatement();

      // Adds all the queries as a batch in the statement. The whole batch will
      // be executed in a single connection to the database.
      for(int j=0;j<length;j++){
        stmt.addBatch(p_queriesArr[j]);
      }

      // Execute the whole batch of queries
      i = stmt.executeBatch();

      return i;
    }catch(SQLException p_sqlexp){
      p_sqlexp.printStackTrace();
      throw new Exception("AQ-1010");
    }
    catch(Exception p_exp){
      p_exp.printStackTrace();
      throw new Exception("AQ-1009");
    }
    finally{
      // Close Statement
      if (stmt != null){
        stmt.close();
      }

      //Close Connection
      if (conn != null){
        conn.close();
      }

    }
  }


  /**
   * This method is called if the calling class wants to execute a select query
   * in the database. Please refer to the class header for more information on
   * Vector returned by this method
   *
   * @param p_query SQL Query to be executed
   * @param p_user Type of User
   * @exception Exception if unable to execute the query
   * @return Vector which holds the data retrieved from the query
   */
  public Vector executeQuery(String p_query, String p_user) throws Exception{

    // Initialize ResultSet
    ResultSet rs = null;

    // Initialize ResultSetMetaData
    ResultSetMetaData rsmd;

    // Initialize Connection
    Connection conn = null;

    // Initialize Statement
    Statement stmt = null;

    // Initialize DataSource
    DataSource ds;

    int columnCount;
    Vector data = new Vector(10,5);

    try{
      // Gets the DataSource for the particular Object
      ds = getDataSource(p_user);

      // Gets the Connection
      conn = (Connection)ds.getConnection();

      // Creates a Statement, executes the query and gets the MetaData of the result
      stmt = conn.createStatement();
      rs = stmt.executeQuery(p_query);
      rsmd = rs.getMetaData();

      columnCount = rsmd.getColumnCount();
      while (rs.next()){
        String row[] = new String[columnCount];
        for (int i=1; i<=columnCount; i++){
          row[i-1] = rs.getString(i);
        }
        data.addElement(row);
      }

    return data;
    }catch(SQLException p_sqlexp){
      p_sqlexp.printStackTrace();
      throw new Exception("AQ-1009");
    }
    catch(Exception p_exp){
      throw p_exp;
    }
    finally{
      // Close Statement
      if (stmt != null){
        stmt.close();
      }

      //Close Connection
      if (conn != null){
        conn.close();
      }
    }
  }


  /**
   * This method returns the DataSource object to be used for the particular User.
   *
   * @param p_user Type of User
   * @return DataSource Object to be used for the user
   */
  DataSource getDataSource(String p_user){
    if (p_user.equals("Computer")){
      return m_computerDataSource;
    }else if (p_user.equals("Printer")){
      return m_printerDataSource;
    }else if (p_user.equals("Customer")){
      return m_retailDataSource;
    }else if (p_user.equals("Retail")){
      return m_retailDataSource;
    }else{
      return null;
    }
  }
}