|
|
SqljLongSample |
|
/*
* @author Reghu
* @version 1.1
*
* Development Environment : JDeveloper 2.0
* Name of the Application : SqljLongSample.sqlj
* Creation/Modification History :
*
* rkrishna.in 05-Jan-1999 Created
* Savitha 20-Mar-2003 Certified on Linux.
*/ package oracle.otnsamples.sqlj.longsample;
// Java IO classes.
import java.io.IOException;
// Oracle JDBC APIs.
import oracle.jdbc.pool.OracleDataSource;
// Package containing JDBC classes.
import java.sql.Connection;
import java.sql.SQLException;
// SQLJ runtime classes.
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.AsciiStream;
// Java Utility Classes.
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
/**
* This sample application illustrates accessing of a long column in a
* Oracle table using SQLJ.
*/ public class SqljLongSample{
/** Database Connection Context Object. */ private DefaultContext connContext = null;
/** Connection object. */ private Connection connection = null;
// GUI handler for this sample.
private static SqljLongFrame gui;
// SQLJ iterator declaration that represents the airline records
// fetched from OTN_AIRLINES_LONG.
#sql iterator SelRowIter (String code,String name,String partner);
// SQLJ iterator declaration that represents the Long column
// fetched from OTN_AIRLINES_LONG.
#sql iterator InsuranceInfo( AsciiStream airline_insurance_data );
/**
* Constructor of sqljLongSample class, instantiates GUI.
**/ public SqljLongSample() {
gui = new SqljLongFrame(this); // Instantiate GUI.
gui.setVisible(true);
}
/**
* Main entry point for the class. Instantiates the SqljLongSample class
* and sets up the database connection.
**/ public static void main(String args[]) {
// Instantiate SqljLongSample class.
SqljLongSample ls = new SqljLongSample();
ls.dbConnection(); //Setup the DB connection.
// Check if connection exists, else create it.
if( ls.connection != null ) {
// Check if OTN_AIRLINES_LONG table is present, else create it.
ls.checkTables();
ls.selectRows(); // Fill the JTable with records.
} else gui.putStatus("Error obtaining connection.");
}
/**
* Dispatches the GUI events to the appropriate method, which performs
* 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 SqljLongFrame.java.
**/ public void dispatchEvent (String eventName) {
// Get the row selected from the JTable in the GUI.
int selectedRow = gui.jtable.getSelectedRow();
if(eventName.equals("DISPLAY LONG DATA")) {
// Get the value of code from the row selected in the GUI table.
String code = (String) gui.tmodel.getValueAt(selectedRow,0);
displayLongData(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 the 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 a record to view insurance details");
} catch(Exception ex){ // Catch exceptions.
gui.putStatus("Error in Connecting to the Database: "+ex.toString());
}
return connection;
}
/**
* Checks if the table OTN_AIRLINES_LONG 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 to see if
// the table is existing.
#sql { SELECT count(*) INTO :cnt FROM user_tables
WHERE Table_Name = 'OTN_AIRLINES_LONG'};
// 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.createSchemaTable();
gui.putStatus( "OTN_AIRLINES_LONG Table created.\n" +
" Please click on a record to view insurance details");
}
} catch (SQLException sqlEx) {
gui.putStatus(" Could not create table otn_airline_long : "+
sqlEx.toString());
}
}
/**
* This method select the code, name and partner columns for all records
* in the OTN_AIRLINES_LONG using SQLJ, and populates the JTable.
**/ private void selectRows(){
// Create an instance of the selRowIter iterator to hold the rows
// retrieved by the query below.
SelRowIter selRow = null;
try{
// Embedded SQL: This call select all rows from the OTN_AIRLINES_LONG
// and assigns the retrieved rowset to the selrow iterator.
#sql selRow = { SELECT code,name,partner FROM otn_airlines_long };
// Loop through the iterator.
while(selRow.next()){
// Retrieve column values and add to vector.
String code=selRow.code();
String name=selRow.name();
String partner=selRow.partner();
gui.addToJTable(code, name, partner); // Update GUI.
}
// Close the iterator.
selRow.close();
} catch(Exception ex){ // Catch exceptions.
ex.printStackTrace();
gui.putStatus("Error in selecting rows "+ex.toString());
}
}
/**
* Retrieve the LONG data in the AIRLINE_INSURANCE_DATA column from
* OTN_AIRLINES_LONG table, and display in the TextArea.
**/ private void displayLongData(String code) {
AsciiStream insurancedata; // Hold the LONG data.
StringBuffer dataBuffer = new StringBuffer();
int chunk;
// Create an instance of the InsuranceInfo iterator to hold the rows
// retrieved by the query below.
InsuranceInfo info = null;
try {
// Embedded SQL: this call selects the LONG column and binds the
// retrieved data into the data class variable. The code for the
// selection is bound to code class variable.
#sql info= { SELECT AIRLINE_INSURANCE_DATA
FROM otn_airlines_long WHERE Code =:code};
gui.putStatus("Reading Long Column from the DB, Please wait...");
// Retrieve the stream for the LONG column from the iterator.
while (info.next()){
insurancedata = info.airline_insurance_data();// Get the AsciiStream.
// Read data from the Ascii Stream and save to the String Buffer.
while((chunk = insurancedata.read()) != -1)
dataBuffer.append((char)chunk);
// Display the Long Data.
gui.display.setText(new String(dataBuffer));
gui.putStatus("Long Column is fetched from the DB successfully!");
}
// Close the iterator.
info.close();
} catch(Exception ex){ // Catch exceptions.
gui.putStatus("Error in selecting from the Database " +
ex.toString());
}
}
/**
* Closing the Database Connection Context Object and exit the application.
**/ private void exitApplication() {
try{
gui.putStatus("Closing the connection context....please wait.....");
if(connContext != null)
connContext.close(); // Closing the connContext object.
} catch(SQLException ex){ // Catch SQL Errors.
gui.putStatus(ex.toString());
}
System.exit(0); // Exit the application.
}
}
|
|
SqljLongSample |
|