/*
* @author Elangovan
* @version 1.0
*
* Development Environment : Oracle9i JDeveloper
* Name of the Application : ConnectionWrapperSample.java
* Creation/Modification History :
*
* Elangovan 04-Jan-2002 Created
* Venky 06-Feb-2003 Certified on Linux
*
*/
package oracle.otnsamples.oracle9ijdbc.connectionwrapper;
// Oracle JDBC extensions
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// JDBC classes
import java.sql.Statement;
import java.util.Enumeration;
// Standard java utility classes
import java.util.Hashtable;
import java.util.Properties;
import java.util.ResourceBundle;
/**
*
* This sample illustrates the use of OracleConnectionWrapper
* (oracle.jdbc.OracleConnectionWrapper) and customizing the wrapper to suit
* the user requirements.
*
* Consider a scenario where the number of Statements created by an
* user has to be limited to 10. Instead of implementing this business logic in
* all the methods that make a JDBC call, the connection instance can be
* wrapped and the number of statements created can be tracked in the
* wrapper class.
*
* This sample application demonstrates the use of OracleConnectionWrapper,
* using an Order-Entry Application. The user can perform a maximum of 10
* operations (query or update the status of an order). The number of operations
* performed is displayed to the user. When the user reaches the limit, any further
* operations are disabled and the user has to quit the application.
*
* The custom wrapping of the OracleConnection is implemented in
* CustomConnWrapper class (CustomConnWrapper.java).
*/
public class ConnectionWrapperSample {
// Holds the custom wrapper class
private OracleConnection connWrapper = null;
// User interface for this sample
ConnectionWrapperFrame gui = null;
/**
* Constructor. Initializes the GUI
*/
public ConnectionWrapperSample() {
gui = new ConnectionWrapperFrame(this);
}
/**
* Main entry point to the application class. Connects to the Database and
* displays the current TimeStamp of the database.
*/
public static void main(String[] args) {
// Instantiate the class
ConnectionWrapperSample sample = new ConnectionWrapperSample();
// Connect to Database
sample.dbConnect();
// If connected, display list of Order Id's
if (sample.connWrapper != null) {
sample.displayOrderId();
}
}
/**
* Dispatches events generated in the GUI to the appropriate methods for
* handling. Each time an event is dispatched, the number of statements
* created is checked .
*/
public void dispatchEvent(String eventName) {
if (eventName.equals("SHOW ORDER STATUS")) {
// Display order status
showOrderStatus();
} else if (eventName.equals("UPDATE ORDER STATUS")) {
// Update order status
updateOrderStatus();
} else if (eventName.equals("EXIT")) {
// Perform clean up and close the application
exitApplication();
}
// Check whether the number of statements created is within the limit(10).
// Also displays the count to the user.
checkStatementCount();
}
/**
* Creates a database connection object using DataSource object. Please
* substitute the database connection parameters with appropriate values in
* Connection.properties file
*/
public void dbConnect() {
try {
gui.putStatus("Trying to connect to the Database");
// Load the properties file to get the database connection information
Properties prop = this.loadParams("Connection");
// Create a OracleDataSource instance
OracleDataSource ods = new OracleDataSource();
// Set the driver type
ods.setDriverType("thin");
// Set the database server name
ods.setServerName((String) prop.get("HostName"));
// Set the database name
ods.setDatabaseName((String) prop.get("SID"));
// Set the port number
ods.setPortNumber(new Integer((String) prop.get("Port")).intValue());
// Set the user name
ods.setUser((String) prop.get("UserName"));
// Set the password
ods.setPassword((String) prop.get("Password"));
// Retrieve a connection from the Datasource and wrap the connection.
// The connection to be wrapped is passed to the constructor of the
// the custom wrapper class, which wraps the connection and returns an instance
// of the custom wrapper.
this.connWrapper = new CustomConnWrapper((OracleConnection) ods.getConnection());
gui.putStatus(" Connected to " + prop.get("SID") + " Database as " +
prop.get("UserName"));
} catch (SQLException ex) { // Handle SQL errors
gui.putStatus("Error in Connecting to the Database " + '\n' +
ex.toString());
}catch (IOException ex) { // Handle IO errors
gui.putStatus("Error in reading the properties file " + '\n' +
ex.toString());
}
}
/**
* This method reads a properties file, which is passed as
* the parameter to it, and loads it into a java Properties
* object and returns the object.
*/
private static Properties loadParams(String file) throws IOException {
// Loads a ResourceBundle and creates Properties from it
Properties prop = new Properties();
ResourceBundle bundle = ResourceBundle.getBundle(file);
Enumeration enum = bundle.getKeys();
String key = null;
while (enum.hasMoreElements()) {
key = (String) enum.nextElement();
prop.put(key, bundle.getObject(key));
}
return prop;
}
/**
* This method retrieves the list of orders from the orders table and displays
* it to the user.
*/
public void displayOrderId() {
Statement stmt = null;
ResultSet rset = null;
try {
gui.putStatus(" Retrieving list of available orders ");
// The custom wrapper class maintains the count of number of Statements created
// using this connection. Each time a Statement is created (irrespective
// of the API i.e. createStatement() or prepareStatement() or ...)
// the count is incremented by one.
// Hence, irrespective of the method from which the Statement is
// created, the statement count is maintained.
// Create a Statement object
stmt = this.connWrapper.createStatement();
// Retrieve the list of Order Id's
rset = stmt.executeQuery(" SELECT Order_Id FROM ORDERS ");
// Populate the Order Id Combobox with the list
while (rset.next())
gui.addOrderId(rset.getString(1));
gui.putStatus(" Successfully retrieved Orders list ");
} catch (SQLException sqlEx) { // Trap SQL Errors
gui.putStatus(" Error while retrieving Order Id : " +
sqlEx.toString());
} finally {
try {
// Close the ResultSet and Statement object
rset.close();
stmt.close();
} catch (Exception ex) {
gui.putStatus(" Error : " + ex.toString());
}
}
// Show the order status for the first order id
showOrderStatus();
// Check statement count and display the count to user
checkStatementCount();
// Enable the Buttons and Combobox
gui.enableUpdate();
}
/**
* This method displays the Order status for the selected Order Id.
*/
public void showOrderStatus() {
PreparedStatement pstmt = null;
ResultSet rset = null;
try {
// Get the selected Order Id
int orderId = gui.getSelectedOrderId();
// Get the Order Status for the selected Order Id
pstmt = this.connWrapper.prepareStatement(" SELECT Order_Status " +
" FROM ORDERS " +
" WHERE Order_Id = ? ");
// Set the Order Id
pstmt.setInt(1, orderId);
// Retrieve the Order Status and display
rset = pstmt.executeQuery();
rset.next();
gui.displayOrderStatus(rset.getInt(1));
} catch (SQLException sqlEx) { // Trap SQL Errors
gui.putStatus(" Error while retrieving Order Status : " +
sqlEx.toString());
} finally {
try {
// Close the ResultSet and PreparedStatement object
rset.close();
pstmt.close();
} catch (Exception ex) {
gui.putStatus(" Error : " + ex.toString());
}
}
}
/**
* This methopd updates the Order Status of the selected Order Id.
*/
public void updateOrderStatus() {
PreparedStatement pstmt = null;
try {
// Get the selected Order Id
int orderId = gui.getSelectedOrderId();
// Order status
int orderStatus = gui.getOrderStatus();
// Update Order with the new Status
pstmt = this.connWrapper.prepareStatement(" UPDATE Orders " +
" SET Order_Status = ? " +
" WHERE Order_Id = ? ");
// Set the Status
pstmt.setInt(1, orderStatus);
// Set the Order Id
pstmt.setInt(2, orderId);
// Execute the update
pstmt.executeUpdate();
gui.putStatus(" Order status updated successfully ");
} catch (Exception sqlEx) { // Trap SQL Errors
gui.putStatus(" Error while updating of Order Status : " +
sqlEx.toString());
} finally {
try {
// Close the PreparedStatement object
pstmt.close();
} catch (Exception ex) {
gui.putStatus(" Error : " + ex.toString());
}
}
}
/**
* This method checks the number of Statements created. This detail is
* retrieved from the custom wrapper class and displayed to the user.
*/
public void checkStatementCount() {
// Get the number of Statements created
int stmtCount = ((CustomConnWrapper) this.connWrapper).getStatementCount();
// Display the count to the user
gui.displayCount(stmtCount);
// Check if the number of statements created is less than 10.
if (stmtCount == 10) {
// Prevent the user from performing any more operations
gui.disableUpdate();
gui.putStatus(" 10 operations have been performed, exit application ");
}
}
/**
* This method performs the clean up action and closes the application
*/
void exitApplication() {
// If connected to database
if (this.connWrapper != null) {
try {
// Close the connection
this.connWrapper.close();
this.connWrapper = null;
} catch (SQLException sqlEx) { // Trap errors
gui.putStatus(" Error while Closing the connection: " +
sqlEx.toString());
}
}
// Close the application
System.exit(0);
}
}
Bookmark