|
|
SqljDMLSample |
|
/*
* @author Reghu
* @version 1.1
*
* Development Environment : JDeveloper 2.0
* Name of the Application : SqljDMLSample.sqlj
* Creation/Modification History :
*
* rkrishna.in 29-Dec-1998 Created
* Savitha 18-Mar-2003 Certified on Linux.
*
*/ package oracle.otnsamples.sqlj.dml;
// SQLJ runtime classes.
import sqlj.runtime.ref.DefaultContext;
// Package for JDBC classes.
import java.sql.Connection;
import java.sql.SQLException;
// Java Utility Classes.
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
// Oracle JDBC APIs.
import oracle.jdbc.pool.OracleDataSource;
// Java IO APIs.
import java.io.IOException;
/**
* This application illustrates SQL DML operations like SELECT, INSERT,
* UPDATE and DELETE using SQLJ. The user can insert, select, update and delete
* records from the OTN_AIRLINES table using this sample.
*
* The GUI for this sample is handled in SqljDMLFrame.java
*/ public class SqljDMLSample {
/** Database Connection Object */ private Connection connection = null;
/** Database Connection Context object. */ private DefaultContext connContext = null;
/** GUI handler for this sample. */ private static SqljDMLFrame gui;
/** SQLJ iterator declaration for representing OTN_AIRLINES records. */ #sql iterator SelRowIter(String code, String name, String partner);
/**
* Constructor of sqljDMLSample class, instantiates GUI.
**/ public SqljDMLSample() {
gui = new SqljDMLFrame(this); // Instantiate GUI.
gui.setVisible(true);
}
/**
* Main entry point for the class. Instantiates the DMLSample class
* and sets up the database connection.
**/ public static void main(String args[]) {
SqljDMLSample dml = new SqljDMLSample(); // Instantiate SqljDMLSample class.
dml.dbConnection(); // Connect to the database.
// Check if connection exists, else create it.
if( dml.connection != null )
// Check if OTN_AIRLINES table is present, else create it.
dml.checkTables();
else gui.putStatus("Error obtaining database connection.");
}
/**
* Dispatches the GUI events to appropriate methods, which perform
* the required SQLJ operations. This method is invoked when event occurs
* in the GUI (like table Selection, Button clicks etc.). This method
* is invoked from the setupListeners method of SqljDMLFrame.java
**/ public void dispatchEvent (String eventName) {
// Get the user input values for code, name and partner.
String code = gui.codeTextfield.getText();
String name = gui.nameTextfield.getText();
String partner = gui.partnerTextfield.getText();
// Dispatch different events.
if (eventName.equals("SELECT"))
selectRecords(code, name, partner);
else if (eventName.equals("UPDATE"))
updateRecord(code, name, partner);
else if (eventName.equals("INSERT"))
insertRecords(code, name, partner);
else if (eventName.equals("DELETE"))
deleteRecord(code);
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. It takes in a
* filename as string. The file has connection details.
*/ private static Properties loadParams( String file ) throws IOException {
// Load a ResourceBundle and create 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 SQLJ. Please substitute the
* database connection parameters with appropriate values in file:
* Connection.properties
**/ private Connection dbConnection() {
try {
gui.putStatus("Trying to connect to the Database");
// Load the properties file to get the connection information.
Properties prop = 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"));
// Create a connection object.
connection = ods.getConnection();
// Get a default context using above connection to execute SQLJ statement.
connContext = new DefaultContext(connection);
// Set the above connection context as the default context for this
// application.
DefaultContext.setDefaultContext(connContext);
gui.putStatus("Connected to " + prop.get("SID") +
" Database as " + prop.get("UserName")+". Please click on"+
" SELECT button to view records");
} catch(Exception ex){ // Catch exceptions.
gui.putStatus("Error in Connecting to the Database: "+ex.toString());
}
return connection;
}
/**
* Checks if the table OTN_AIRLINES is present, else creates it.
* Look into PopulateTable.sqlj for more details.
*/ private void checkTables() {
try {
int cnt;
// Check from USER_TABLES data dictionary table if the table is existing.
#sql { SELECT count(*) INTO :cnt FROM user_tables
WHERE Table_Name = 'OTN_AIRLINES'};
// If the cnt is 0, it means that table is not existing.
// The table is created only when cnt returns zero.
if (cnt==0) {
// Call the class to create the table.
PopulateTable pop = new PopulateTable(connection, gui);
pop.createSchemaTables();
gui.putStatus( "otn_airlines Table created." +
" Please click on SELECT"+
" button to view records");
}
} catch (SQLException sqlEx) {
gui.putStatus(" Could not create table otn_airlines : "+
sqlEx.toString());
}
}
/**
* Method to insert a new row into the database using SQLJ (embedded SQL).
* The new row is first inserted into the database, and then reflected in
* the JTable.
**/ private void insertRecords(String code,String name,String partner){
try{
// Embedded SQL: This call inserts a record into the OTN_AIRLINES table.
// The values to be inserted are bound to the variables: code, name
// and partner.
#sql { INSERT INTO otn_airlines VALUES(:code, :name, :partner)};
gui.putStatus("The Record is inserted");
// Update GUI to reflect new record.
gui.addToJTable(code, name, partner);
} catch (SQLException ex) { // Catch SQLExceptions.
gui.putStatus("Error in inserting to the Database: "+ ex.toString());
}
}
/**
* Method to select records from the OTN_AIRLINES table using SQLJ, based on
* the query conditions entered by the user.
**/ private void selectRecords(String code,String name,String partner){
// Clear JTable in GUI.
gui.tmodel.clearTable();
try{
// Use wild card % to query all records if no query condition was entered.
code = "%"+code+"%";
name = "%"+name+"%";
partner = "%"+partner+"%";
// Declare an instance of the SelRowIterator, which will hold
// all the OTN_AIRLINES records returned by the query executed.
SelRowIter selRow = null;
// Embedded SQL: This call selects all records from the OTN_AIRLINES table
// which meet the selection criteria. The selection criteria is bound to
// the variables: code, name and partner. The query results are assigned
// to an iterator: selRow
#sql selRow = { SELECT * FROM otn_airlines
WHERE UPPER(code) LIKE UPPER(:code)
AND UPPER(name) LIKE UPPER(:name)
AND UPPER(partner) LIKE UPPER(:partner)
};
// Populate the iterator and process all rows returned.
while(selRow.next()) {
code = selRow.code();
name = selRow.name();
partner = selRow.partner();
// Insert each record returned into the JTable.
gui.addToJTable(code, name, partner);
}
// Display number of rows retrieved.
gui.putStatus(gui.tmodel.getRowCount() +" Records selected");
// Close the iterator.
selRow.close();
} catch(Exception ex){ // Catch exceptions.
gui.putStatus("Error in selecting rows from database: " +ex.toString());
}
}
/**
* Method to update the selected row with new values using SQLJ.
**/ private void updateRecord(String code,String name,String partner){
try{
// Embedded SQL: This call updates the OTN_AIRLINES record that has been
// selected, to the new values specified in the TextFields. The values to
// be updated are bound to the variables: name,code and partner.
int cnt;
#sql { SELECT count(*) INTO :cnt FROM otn_airlines
WHERE code =:code };
#sql { UPDATE otn_airlines SET name =:name, partner =:partner
WHERE code =:code };
// Update the GUI to reflect the changed record.
if( cnt > 0 ) {
gui.updateJTable( code, name, partner);
}
gui.putStatus( cnt + " Record updated.");
} catch (SQLException ex) {
gui.putStatus("Error in Updating to the Database: "+ ex.toString());
}
}
/**
* Method to delete the selected row in the database and reflect the same in
* JTable.
**/ private void deleteRecord(String code){
try{
int cnt;
#sql { SELECT count(*) INTO :cnt FROM otn_airlines
WHERE code =:code };
// Embedded SQL: This will delete the OTN_AIRLINES record specified.
// The value of the code is bound to the variable: code.
#sql { DELETE FROM otn_airlines WHERE code= :code };
// Delete the row from the JTable.
if( cnt > 0 ) {
gui.deleteFromJTable();
}
gui.putStatus( cnt + " Record deleted." );
} catch(Exception ex){ // Catch exceptions.
gui.putStatus("Error in Deleting from the Database: "+ex.toString());
}
}
/**
* Closes the Sqlj DefaultContext Object and exits the application.
**/ private void exitApplication() {
try{
gui.putStatus("Closing the connection....please wait.....");
if(connContext != null)
connContext.close(); // Closing the connContext object.
} catch(SQLException ex){ // Catch SQLException.
gui.putStatus(ex.toString());
}
System.exit(0); // Exit the application.
}
}
|
|
|