|
|
DateSample |
|
/*
* @author Elangovan
* @version 1.2
*
* Development Environment : Oracle9i JDeveloper
* Name of the Application : DateSample.java
* Creation/Modification History :
*
* Elangovan 06-Dec-2002 Created
* Savitha 10-Jan-2003 Certified on Linux
* Included checkTables() method
*/ package oracle.otnsamples.jdbc.datetype;
// Package containing JDBC classes
import java.sql.Date;
import java.sql.Types;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
// Oracle JDBC APIs
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 classes
import java.text.ParseException;
// java IO APIs
import java.io.IOException;
/**
* This sample demonstrates the use of JDBC calls to access DATE fields.
*
* The GUI part of this sample is handled separately in DateFrame.java
*/ public class DateSample {
private Connection conn = null; // database connection object
private DateFrame gui = null; // For Handling the GUI related operations
/**
* Constructor which calls the constructor of GUI class.
*/ public DateSample() {
gui =new DateFrame(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 ) {
DateSample sample = new DateSample();
// Connect to database
sample.dbConnection( );
if( sample.conn != null ) {
// Check if 'otn_deliverydetail' table is present, else create it
sample.checkTables( );
// Load existing data
sample.loadDeliveryData( );
}
}
/**
* Dispatches the GUI events to the appropriate method, which performs
* the required JDBC operations. This method is invoked when event occurs
* in the GUI (like Button clicks).
*
* @param eventName The name of the event generated by GUI
*/ 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() {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement();
rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
" WHERE Table_Name = 'OTN_DELIVERYDETAIL' ");
if (!rset.next()) {
// Table does not exist, create it
stmt.executeUpdate(" CREATE TABLE otn_deliverydetail ( " +
" OrderId NUMBER(5) PRIMARY KEY, "+
" ProposedDate DATE NOT NULL, "+
" ActualDate DATE ) ");
gui.appendStatus( " otn_deliverydetail Table created. " );
}
} catch (SQLException sqlEx) {
gui.putStatus(" Could not create table otn_deliverydetail : "+sqlEx.toString());
} finally {
try {
if( rset != null ) rset.close( );
if( stmt != null ) stmt.close( );
} catch(SQLException ex) { }
}
}
/**
* This method 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( ) {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement( );
rset = stmt.executeQuery( "SELECT OrderId " +
" FROM otn_deliverydetail" );
while( rset.next( ) ) {
gui.addToList( rset.getString( 1 ) );
}
// Enable select operation on the JComboBox and add the action listener.
gui.enableSelect( );
} catch (SQLException sqlEx) {
gui.putStatus( " Could not load data : " + sqlEx.toString( ) );
} finally {
try {
if( rset != null ) rset.close( );
if( stmt != null ) stmt.close( );
} catch(SQLException ex) { }
}
}
/**
* This method persists the delivery details to the database.
*/ private void insertDeliveryData( ) {
PreparedStatement pstmt = null;
// 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 {
pstmt = conn.prepareStatement( " INSERT INTO otn_deliverydetail " +
" VALUES( ?,?,? ) " );
pstmt.setInt( 1, gui.getOrderId( ) );
// Set the proposed delivery date
pstmt.setDate( 2, proposedDate );
// If actual delivery date is blank or invalid, insert NULL instead.
if( actualDate == null )
// Insert null for actual date
pstmt.setNull( 3, Types.DATE);
else // Set the actual delivery date
pstmt.setDate( 3, actualDate );
// Execute the statement
int rows = pstmt.executeUpdate( );
gui.putStatus( rows +" row(s) 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 " );
}
} finally {
try {
if( pstmt != null ) pstmt.close( );
} catch(SQLException ex) { }
}
}
/**
* 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 {
pstmt = conn.prepareStatement( " UPDATE otn_deliverydetail " +
" SET ActualDate = ? " +
" WHERE OrderId = ? " );
// Set the actual date
pstmt.setDate( 1, actualDate );
pstmt.setInt( 2, gui.getOrderId( ) );
int rows = pstmt.executeUpdate( );
gui.putStatus( rows +" row(s) Updated. " );
if( rows == 0 )
gui.putStatus( " Order Id does not exist " );
} catch (Exception sqlEx) {
gui.putStatus( " Could not update data " + sqlEx.toString( ) );
} finally {
try {
if( pstmt != null ) pstmt.close( );
} catch(SQLException ex) { }
}
}
/**
* This method retrieves the delivery details of the selected order id.
*/ private void getDeliveryDate( ) {
PreparedStatement pstmt = null;
ResultSet rset = null;
// To parse dates from String
SimpleDateFormat formatter = new SimpleDateFormat( "dd-MMM-yyyy" );
Date proposedDate = null;
Date actualDate = null;
try {
pstmt = conn.prepareStatement( "SELECT ProposedDate, ActualDate " +
"FROM otn_deliverydetail " +
"WHERE OrderId = ?" );
pstmt.setInt( 1, gui.getOrderId( ) );
rset = pstmt.executeQuery( );
if( rset.next( ) ) {
// Get the proposed and actual delivery date
proposedDate = rset.getDate( 1 );
actualDate = rset.getDate( 2 );
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( ) );
} finally {
try {
if( rset != null ) rset.close( );
if( pstmt != null ) pstmt.close( );
} catch(SQLException ex) { }
}
}
/**
* 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.
* @param file The name of the file having connection details
*/ 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 = DateSample.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"));
// Create a connection object
conn = 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 IO errors
gui.putStatus(
"Error in reading the properties file "+'\n'+ex.toString());
}
}
/**
* Closes the connection and exits from the program when exit button is
* clicked.
*/ private void exitApplication(){
if (conn != null) {
try {
conn.close(); // Close the connection
} catch( Exception ex ) {
gui.putStatus(" Error in Closing the connection: "+ex.toString());
}
}
System.exit(0);
}
}
|
|
DateSample |
|