|
|
SqljDateSample |
|
/*
* @author Elangovan
* @version 1.1
*
* Development Environment : Oracle9i JDeveloper
* Name of the Application : SqljDateSample.sqlj
* Creation/Modification History :
*
* Elangovan 06-Dec-2002 created.
* Savitha 17-Mar-2003 Certified on Linux.
*/ package oracle.otnsamples.sqlj.datetype;
// SQLJ runtime classes.
import sqlj.runtime.ref.DefaultContext;
// Package containing JDBC classes.
import java.sql.Date;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
// Oracle JDBC API.
import oracle.jdbc.pool.OracleDataSource;
// Java Utility Classes.
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
// Java date formatting class.
import java.text.SimpleDateFormat;
// Exception class.
import java.text.ParseException;
// Java IO API.
import java.io.IOException;
//Iterator for accessing otn_deliverydetail records.
#sql iterator otnDeliverydetailIter (Date proposeddate, Date actualdate);
// Iterator to store Order ids.
#sql iterator ordIter (String orderid);
/**
* This sample demonstrates the use of SQLJ calls to access SQL DATE fields.
* The GUI part of this sample is handled separately in SqljDateFrame.java
*/ public class SqljDateSample {
/** Database Connection Context. **/ private DefaultContext ctx;
/** GUI handler for this sample. **/ private static SqljDateFrame gui;
/** Database Connection Object. **/ private Connection conn = null;
/**
* Constructor which in turn calls the constructor of GUI class.
*/ public SqljDateSample() {
gui = new SqljDateFrame(this);
}
/**
* Main entry point for the class. Instantiates the root frame and
* sets up the database connection.
* -- Checks if 'otn_deliverydetail' table is present, else creates it.
* -- Loads existing Orders.
*/ public static void main( String[] args ) {
SqljDateSample sample = new SqljDateSample();
sample.dbConnection(); // Connect to the database.
// If connection exists, check and create the required table.
if ( sample.conn != null) {
sample.checkTables();
// Load existing data to the GUI.
sample.loadDeliveryData();
} 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 Button clicks etc..).
*/ public void dispatchEvent (String eventName) {
// For dispatching the select event.
if( eventName.equals( "INSERT" ) )
insertDeliveryData( );
else if( eventName.equals( "UPDATE" ) )
updateDeliveryDate( );
else if( eventName.equals( "VIEW" ) )
getDeliveryDate( );
else if( eventName.equals( "EXIT" ) )
exitApplication();
}
/**
* Checks if the table ('otn_deliverydetail') is present, else creates it.
* Table Name: otn_deliverydetail
* Column Name Type
* -----------------------------------
* OrderId Number(5)
* ProposedDate Date
* ActualDate Date
*/ private void checkTables() {
try {
int cnt;
// Check from USER_TABLES data dictionary table if the table exists.
#sql { SELECT count(*) INTO :cnt FROM user_tables
WHERE Table_Name = 'OTN_DELIVERYDETAIL'};
// If the cnt is 0, it means that table is not existing.
// The table is created only when cnt returns zero.
if (cnt==0) {
// Table does not exist, create it
#sql { CREATE TABLE otn_deliverydetail ( OrderId NUMBER(5) PRIMARY KEY,
ProposedDate DATE NOT NULL,
ActualDate DATE ) };
gui.putStatus( "otn_deliverydetail Table created." );
}
} catch (SQLException sqlEx) {
gui.putStatus("Could not create table otn_deliverydetail : "+
sqlEx.toString());
}
}
/**
* This methods loads the existing delivery data and displays it to the user.
* The Order Id of all orders are populated into the Order ID JCombobox.
*/ private void loadDeliveryData( ) {
ordIter ordIdIter;
try {
String id = null;
#sql ordIdIter = { SELECT OrderId FROM otn_deliverydetail };
while (ordIdIter.next()) {
id = ordIdIter.orderid();
gui.addToList(id);
}
// Enable select operation on the JComboBox and add the action listener.
gui.enableSelect( );
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
gui.putStatus( " Could not load data : " + sqlEx.toString( ) );
}
}
/**
* This method persists the delivery details to the database using SQLJ
* statement.
*/ private void insertDeliveryData( ) {
// To parse dates from String, Ex: 05-SEP-1980
SimpleDateFormat formatter = new SimpleDateFormat( "dd-MMM-yyyy" );
Date proposedDate = null;
Date actualDate = null;
try {
// Parse the date from user entered String
proposedDate = new Date(formatter.parse(gui.getProposedDate()).getTime());
actualDate = new Date(formatter.parse(gui.getActualDate()).getTime());
} catch( ParseException parseEx ) { }
if( proposedDate == null ) {
gui.putStatus( " Invalid Proposed Delivery Date ");
return;
}
if( actualDate == null )
gui.putStatus(" Invalid Actual Delivery Date, inserting NULL instead.");
try {
int id = gui.getOrderId( );
#sql { INSERT INTO otn_deliverydetail VALUES( :id, :proposedDate,
// :actualDate)} ;
gui.putStatus( "Row Inserted. " );
// Add the inserted Order to JCombobox
gui.addToList( String.valueOf( gui.getOrderId( ) ) );
} catch (Exception sqlEx) {
// If existing order id was given for insert, display error message
if( sqlEx.toString( ).indexOf("ORA-00001") == -1 ) {
gui.putStatus( " Could not insert data " + sqlEx.toString( ) );
} else {
gui.putStatus( " Order Id must be Unique. " );
}
}
}
/**
* This method updates the actual date of delivery for an Order.
*/ private void updateDeliveryDate( ) {
PreparedStatement pstmt = null;
// To parse dates from String
SimpleDateFormat formatter = new SimpleDateFormat( "dd-MMM-yyyy" );
Date actualDate = null;
try {
// Parse the date from user entered String
actualDate = new Date(formatter.parse(gui.getActualDate()).getTime());
} catch( ParseException parseEx ) {
gui.putStatus( " Invalid Actual Delivery Date. ");
return;
}
try {
int id = gui.getOrderId( );
int chk = 0;
#sql { SELECT count(*) INTO :chk FROM otn_deliverydetail
WHERE OrderId = :id };
if( chk == 0 )
gui.putStatus( " Order Id does not exist " );
else {
#sql { UPDATE otn_deliverydetail SET ActualDate = :actualDate
WHERE OrderId = :id };
gui.putStatus( "Row Updated.\nNote: Orderid or"+
" Proposed Delivery Date cannnot be updated." );
}
} catch (Exception sqlEx) {
gui.putStatus( " Could not update data " + sqlEx.toString( ) );
}
}
/**
* This method retrieves the delivery details of the selected order id.
*/ private void getDeliveryDate( ) {
otnDeliverydetailIter deliverydetailIter ;
// To parse dates from String
SimpleDateFormat formatter = new SimpleDateFormat( "dd-MMM-yyyy" );
Date proposedDate = null;
Date actualDate = null;
try {
int id = gui.getOrderId();
#sql deliverydetailIter = { SELECT ProposedDate, ActualDate
FROM otn_deliverydetail
WHERE OrderId = :id };
if( deliverydetailIter.next( ) ) {
// Get the proposed and actual delivery date
proposedDate = deliverydetailIter.proposeddate();
actualDate = deliverydetailIter.actualdate();
gui.setProposedDate( ( proposedDate != null )?
formatter.format( proposedDate ):"" );
gui.setActualDate( ( actualDate != null )?
formatter.format( actualDate ):"" );
} else {
gui.putStatus( " No Orders exists for this Order Id. " );
}
} catch (SQLException sqlEx) {
gui.putStatus( " Could not get data " + sqlEx.toString( ) );
}
}
/**
* 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 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.
conn = ods.getConnection();
// Get a default context using the above connection to execute SQLJ
// statements.
ctx = new DefaultContext(conn);
// Set the above connection context as the default context for this
// application.
DefaultContext.setDefaultContext(ctx);
gui.putStatus("Connected to " + prop.get("SID") +
" Database as " + prop.get("UserName")+".");
} catch(Exception ex){ // Catch exceptions.
gui.putStatus("Error in Connecting to the Database: "+ex.toString());
}
return conn;
}
/**
* Method to close the Sqlj DefaultContext object and exit the application.
*/ private void exitApplication(){
if (ctx != null) try {
ctx.close(); // Close the DefaultContext object.
}catch (SQLException ex) { // Catch SQL Errors.
gui.putStatus("Error while exiting ..\n"+ex.toString());
}
System.exit(0);
}
}
|
|
SqljDateSample |
|