// COBOL Programmers Swing With Java - copyright 2005 Doke, Hardgrave, & Johnson
// Chapter 11 - OO Development Issues
// Program to demonstrate 3 tier design
// Provide access to Checking Accounts Stored in Access Database
// CheckingAccountDA.java 1 JAN 05

import java.sql.*;
public class CheckingAccountDA
{
    static String url = "jdbc:odbc:db1";  // The dB name is "db1"
    static Connection aConnection;
    static Statement aStatement;
    static ResultSet rs;
    static  CheckingAccountPD anAccount;

    public static void initialize() // initialize method
    {
	try
	{   // load the jdbc - odbc bridge driver for Windows-95
	    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	    // create connection instance
	    aConnection = DriverManager.getConnection(url,"Admin","JavaIsFun");
	    // create statement object instance
	    aStatement = aConnection.createStatement();
	} // end try
	catch (Exception e)
	    {System.out.println("Exception caught "+ e);}
    } // end initialize

    public static void terminate() // terminate method
    {
    try
    {	rs.close();		// close everything
	aStatement.close();
	aConnection.close();
    } // end try
	catch (Exception e)
	    {System.out.println("Exception caught "+ e);}
    }  // end terminate

    public static CheckingAccountPD getAccount(int acctNo) throws NoAccountFoundException
    {
    try
    {
	String sqlQuery = "SELECT AccountNo, Balance, MinBalance FROM CheckingAccount WHERE AccountNo =" + acctNo;
     	rs = aStatement.executeQuery(sqlQuery);	 // execute the SQL query statement
	boolean more = rs.next();  // set the cursor
	if (more)
	{  // if we got the account extract the data & display
	    int acctNO = rs.getInt(1);
	    float balance = rs.getFloat(2);
	    float minBalance = rs.getFloat(3);
	    anAccount = new CheckingAccountPD(acctNO, balance);
        }
        else
	{ // no account found - create & throw the exception
	    NoAccountFoundException e = new NoAccountFoundException("Not Found");
	    throw e;
	}
     } // end try
     catch (SQLException e)
     { while (e != null) // we can have multiple exceptions here - print all
	{ System.out.println("SQLException caught "+ e);
	  e = e.getNextException();
        } // end while loop
      } // end catch
       return anAccount;
    } // end getAccount

    public static void updateAccount(int acctNo, float balance)
    {
    try
    {
	String sqlUpdate = "UPDATE CheckingAccount SET Balance = "+ balance +" WHERE AccountNo = " + acctNo;
	aStatement.executeUpdate(sqlUpdate);
    } // end try
    catch (SQLException e)
    {  while (e != null) // we can have multiple exceptions here - print all
        { System.out.println("SQLException caught "+ e);
	  e = e.getNextException();
        } // end while loop
    } // end catch
    } // end update
} // end CheckingAccountDA.java