package ejava.examples.ejb.session.dao;

import java.sql.*;
import java.util.*;
import ejava.examples.ejb.session.Account;
import ejava.examples.ejb.session.BankException;

public class AccountDAO {
   Statement statement_;
   ResultSet rs_;

   public AccountDAO(){}

   public Account find(Connection conn, int accountId)
      throws SQLException {

      Statement s = null;
      ResultSet rs = null;
      try {
         s = conn.createStatement();
         rs = s.executeQuery("select * from Account where id = " + accountId);
         if (rs.next()) {
            return new Account(accountId, rs.getDouble("balance"));
         }
         else {
            return null;
         }
      }
      finally {
         try { if (rs != null) rs.close(); } catch (SQLException ignored) {}
         try { if (s != null) s.close(); } catch (SQLException ignored) {}
      }
   }

   public void update(Connection conn, Account account)
      throws BankException, SQLException {
      Statement s = null;
      try {
         s = conn.createStatement();
         int rows = s.executeUpdate(
            "update account set balance = " + account.getBalance() +
            " where id = " + account.getId());
         if (rows != 1) {
            throw new BankException("account not found");
         }
      }
      finally {
        try { if (s != null) s.close(); } catch (SQLException ignored) {}
      }
   }

   public Collection getAll(
      Connection conn, int maxrows) throws SQLException {

      statement_ = null;
      rs_ = null;
      try {
         Collection accounts = new Vector(maxrows);
         statement_ = conn.createStatement();
         rs_ = statement_.executeQuery("select * from account");
         for(int i=0; rs_.next() && i<maxrows; i++) {
            int id = rs_.getInt("id");
            double balance = rs_.getDouble("balance");
            accounts.add( new Account(id, balance));
         }
         return accounts;
      }
      catch (SQLException ex) {
         try { if (rs_ != null) rs_.close(); } 
	 catch (SQLException ignored) {}
         try { if (statement_ != null) statement_.close(); }
         catch (SQLException ignored) {}
         rs_ = null;
         statement_ = null;
         throw ex;
      }
   }

   public Collection getNext(int maxrows) throws SQLException {
      try {
         Collection accounts = new Vector(maxrows);
         for(int i=0; rs_ != null && rs_.next() && i<maxrows; i++) {
            int id = rs_.getInt("id");
            double balance = rs_.getDouble("balance");
            accounts.add(new Account(id, balance));
         }
         return accounts;
      }
      catch (SQLException ex) {
         try { if (rs_ != null) rs_.close(); } 
	 catch (SQLException ignored) {}
         try { if (statement_ != null) statement_.close(); }
         catch (SQLException ignored) {}
         rs_ = null;
         statement_ = null;
         throw ex;
      }
   }

   public void close() {
      try { if (rs_ != null) rs_.close(); } 
      catch (SQLException ignored) {}
      try { if (statement_ != null) statement_.close(); }
      catch (SQLException ignored) {}
      rs_ = null;
      statement_ = null;
   }
}