|
|
PLSQLSample |
|
/*
* @author Umesh
* @version 1.2
*
* Development Environment : Oracle 9i JDeveloper
* Name of the Application : PLSQLSample.java
*
* Creation/Modification History :
* ukulkarn.in 26-Dec-1998 Created
* neshastr 22-May-2002 Certified on Oracle9i JDeveloper
* Stephen Raj 20-Jan-2003 Certified on Linux
* Optimised the code
**/ package oracle.otnsamples.jdbc.plsql;
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
import java.util.ArrayList;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.Types;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
/**
* This Sample Application demonstrates accessing PL/SQL Stored Procedure
* using JDBC. The Sample Application connects to Oracle Database instance
* using JDBC-Thin Driver. After connecting to Oracle Database Instance,
* the sample accesses a PL/SQL Stored Procedure namely
* nxt_flight_time_and_nuroutes(Flight_Code IN Varchar2,
* Flight_Time In Out Number, how_many_routes OUT Number).
*
* The PL/SQL Stored Procedure finds out
* a) Next Immediate Departure Time of the Flight after the one time
* entered by the user and returns it in Flight_Time variable
* b) Total Number of Routes on which this flight is flying and returns it in
* how_many_routes variable
*
* Note that if there is no immediate next flight on a particular day, the
* PLSQL stored procedure returns -1 in Flight_Time variable.
*
* The gui for this sample is handled in plsqlFrame.java
*
*/ public class PLSQLSample {
private Connection connection; // Database Connection Object
private PLSQLFrame gui; // gui Handler for this class
/**
* Constructor. Initializes gui.
*/
public PLSQLSample() {
try {
gui = new PLSQLFrame(this); // Initialize the gui
gui.setVisible(true);
gui.setResizable(false);
} catch(Exception e) { // Trap Errors
gui.putStatus("Error while initializing\n " + e.toString());
}
}
/**
* Main entry point for the class. Instantiates the class, sets up the
* database connection and populates the flight codes combo box.
*/ public static void main(String args[]) {
PLSQLSample rootFrame = new PLSQLSample(); // Instantiate application class
rootFrame.dbConnection(); // Set up the db Connection
if (rootFrame.connection!=null)
rootFrame.populateList(); // Populate The Flight Code Combo Box
}
/**
* Dispatches the gui events to the appropriate method, which performs
* the required JDBC operations. This method is invoked when event (like
* Button clicks etc.) occurs in the gui. This method is invoked from the
* setupListeners method of plsqlFrame.java
*/ public void dispatchEvent(String eventName) {
// Dispatch Event
if(eventName.equals("CALL PL/SQL STORED PROCEDURE")) {
// Get the Flight Code as String
String flightCode = gui.getFlightCode();
// Get the Departure Time as int. Note that user will enter Departure
// time in 14:50:00 format. The method getDepartureTime converts it from
// String format(14:50:00) to int (145000) format.
int departureTime = gui.getDepartureTime();
if (departureTime!=-1)
callPlsqlProc(flightCode, departureTime);
else gui.putStatus("Enter time in the correct format (14:50:00)");
} else {
if(eventName.equals("EXIT")) {
exitApplication();
}
}
}
/**
* This method reads a properties file which is passed as
* the parameter to it and load it into a java Properties
* object and returns it.
*/ 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;
}
/**
* Creates a database connection object using DataSource object. Please
* substitute the database connection parameters with appropriate values in
* Connection.properties file
*/ private void dbConnection() {
try {
gui.putStatus("Trying to connect to the Database");
// Load the properties file to get the connection information
Properties prop = this.loadParams("Connection");
// Create a OracleDataSource instance
OracleDataSource ods = new OracleDataSource();
// Sets the driver type
ods.setDriverType("thin");
// Sets the database server name
ods.setServerName((String)prop.get("HostName"));
// Sets the database name
ods.setDatabaseName((String)prop.get("SID"));
// Sets the port number
ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());
// Sets the user name
ods.setUser((String)prop.get("UserName"));
// Sets the password
ods.setPassword((String)prop.get("Password"));
connection=ods.getConnection();
gui.putStatus(" Connected to " + prop.get("SID") +
" Database as " + prop.get("UserName"));
} catch(SQLException ex) { // Trap SQL errors
gui.putStatus("Error in Connecting to the Database "+'\n'+ex.toString());
} catch(IOException ex) { // Trap I/O errors
gui.putStatus("Error in reading the properties file "+'\n'+ex.toString());
}
}
/**
* Method populates the Flight Codes Combo Box holding valid Flight Codes.
*/ private void populateList() {
if (connection!=null) {
Statement stmt = null;
ResultSet rset = null;
try {
//Statement object for executing queries
stmt = connection.createStatement();
ArrayList flightCodes = new ArrayList();
// Query distinct Flight Codes from the Flight_Departures Table
rset = stmt.executeQuery("Select distinct(ID) From Flight_Departures");
// Loop through all the records fetched in the result Set
// and store in the flightcodes array
while(rset.next()) {
flightCodes.add(rset.getString(1));
}
// Initialize the Combo Box which holds flight codes
gui.initializeComboBox(flightCodes);
gui.putStatus("Populated Flight Codes");
} catch(SQLException ex) { // Trap SQL errors
gui.putStatus("Error in populating flight codes\n" + ex.toString());
} finally {
try {
if(stmt != null) {
stmt.close();
}
} catch(SQLException ex) {
}
}
}
}
/**
* This Method accesses PLSQL Stored Procedure namely
* nxt_flight_time_and_nuroutes using JDBC CallableStatement.
* Note that if there is no next immediate flight time for a flight then the
* procedure returns -1 into Flight_time variable.
*/
private void callPlsqlProc(String flightCode, int departureTime) {
gui.putStatus("Calling PL/SQL Stored Procedure...Please wait ");
int numRoutes = 0;
CallableStatement stmt = null;
try {
// Call PLSQL Stored Procedure
// Prepare callable Statement to call PL/SQL Stored Procedure
// Note that to invoke a PL/SQL Stored Function, you can still use a Callable
// Statement which takes following form :
// CallableStatement stmt =
// connection.prepareCall("begin ? = fn(?,?,..); end;");
stmt = connection.prepareCall("begin nxt_flight_time_and_num_routes(?,?,?); end;");
// Binds the parameter types
stmt.setString(1, flightCode); // Bind 1st parameter
stmt.setInt(2, departureTime); // Bind 2nd parameter
stmt.registerOutParameter(2, Types.INTEGER); // 2nd parameter is IN OUT parameter
stmt.registerOutParameter(3, Types.INTEGER); // 3rd parameter is OUT paremeter
// Execute the callable statement
stmt.execute();
departureTime = stmt.getInt(2); // Get Next DepartureTime
numRoutes = stmt.getInt(3); // Get total number of routes
// DisplayResults method converts the next departure time from int to
// String format and displays it on the text fields alongwith number of
// routes
gui.displayResults(numRoutes, departureTime);
} catch(SQLException ex) { // Trap SQL Errors
gui.putStatus("Error while Calling PL/SQL Procedure\n" + ex.toString());
} finally {
try {
if(stmt != null) {
stmt.close(); // close the statement
}
} catch(SQLException ex) {
}
}
}
/**
* Method to close the database connection and exit the application
*/ private void exitApplication() {
try {
if(connection != null) {
connection.close(); // Close the connection
}
System.exit(0);
} catch(SQLException ex) { // Trap SQL Errors
gui.putStatus("Error While Closing Connection .." + ex.toString());
System.exit(0);
}
}
}
|
|
|