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 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 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 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;
}
}
}
