// Transaction handling example from 
// Professional Java Server Programming J2EE Edition
// A simple Account object - wraps a row in the Account table.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;
import java.util.*;
import java.io.*;

public class Account {

    private String accountID;
    private String customerName;
    private double amount;

    public String getAccountID() {
          return accountID;
    }

    public String getCustomerName() {
          return customerName;
    }

    public void setCustomerName(String c) {

          customerName = c;
    }

    public double getAmount() {

          return amount;
    }

    public void setAmount( double amt) {
          amount = amt;
    }

    public void create(String actID, String customerName, double amt, Connection con) throws SQLException {

        accountID = actID;
        this.customerName = customerName;
        this.amount = amount;

        PreparedStatement statement = null;

        try {

             statement = con.prepareStatement("Insert into account (accountID," +
                                                "CustomerName, Amount)"  +
                                                "Values ( ?,?,?)");
             statement.setString(1,accountID);
             statement.setString(2,customerName);
             statement.setDouble(3,amt);

             statement.executeUpdate();
        }

        catch(SQLException e) {
             System.out.println("Caught exception");
        }

        finally {
             if(statement != null) {
                 statement.close();
                 System.out.println("Connection closed");
             }
        }
    }

    public void read(String accountID, Connection con) throws SQLException, RecordNotFoundException {

         PreparedStatement statement = null;
 
         try {

             statement = con.prepareStatement("Select customerName, amount FROM Account"
                                              +" where accountID = ?");

             statement.setString(1,accountID);

             ResultSet result = statement.executeQuery();

             if(result.next()) {
                 this.accountID = accountID;
                 this.customerName = result.getString(1);
                 this.amount = result.getDouble(2);
             
             }
             else {
                 throw new RecordNotFoundException();
             }
         }
         finally {
                 if(statement != null) {
                     statement.close();
             }
         }
    }

    public void update(Connection con) throws SQLException {

        PreparedStatement statement = null;

        try {
              statement = con.prepareStatement("Update account set customername = ?," +
                                               "amount = ? where accountID = ?");

              statement.setString(1, customerName);
              statement.setDouble(2, amount);
              statement.setString(3,accountID);

              statement.executeUpdate();
        }
        finally {
              if(statement != null) {
                 statement.close();
              }
        }
    }

         
    public void delete(Connection con) throws SQLException {

       PreparedStatement statement = null;

       try {

           statement = con.prepareStatement("Delete from Account Where AccountID = ?");
           statement.setString(1,accountID);
           int h = statement.executeUpdate();
           System.out.println("Tried to delete " + accountID + " Changed " + h + " records");
       }
       finally {
           if (statement != null) {
                statement.close();
           }
       }
   
   }

   public void delete(String accountID, Connection con) throws SQLException {

       PreparedStatement statement = null;

       try {

           statement = con.prepareStatement("Delete from Account Where AccountID = ?");
           statement.setString(1,accountID);
           statement.executeUpdate();
       }
       finally {
           if (statement != null) {
                statement.close();
           }
       }
   
   }

   public static void main(String args[]) throws SQLException, RecordNotFoundException {


               DriverManager.registerDriver(
                                         new sun.jdbc.odbc.JdbcOdbcDriver());
              
               Connection con = DriverManager.getConnection("jdbc:odbc:AccountDSN");


               // Test code. After running once the database has data.
               // That's why the next execution throws an exception.

               Account personA = new Account();
               personA.create("1","Mike McCarthy",100.0,con);
               Account personB = new Account();
               personB.create("2","Sue Smith",45.00,con);

               ResultSetMetaData rsm = null;

               String answer = "";

               Statement s = con.createStatement();

               ResultSet rs = s.executeQuery("select  * from Account");

               rsm = rs.getMetaData();

               try {

                   while(rs.next()) {   
         
                      for(int col = 1; col <= rsm.getColumnCount(); col++) 
                           answer += rs.getString(col);
                   }
                   con.close();
               }
               catch (SQLException sqle) {
                  System.err.println("Exception caught in main:" + sqle);
               }
            
               System.out.println(answer);
               con.close();

   }
}