2.4 Bases de Dados Estudo de Caso Caso: Caixa Eletrônico • Caixa Eletrônico com acesso à Base de Dados; • Cada cliente possui: Um número de cliente Uma senha Uma conta corrente Uma conta poupança • Os dados serão armazenados em duas tabelas; Tabelas do Caixa Eletrônico Estudo de Caso • A classe Banco precisa se conectar à base para encontrar os cliente existentes; • Criar um método EncontraCliente Se conecta a base de dados Seleciona um cliente apartir de um número SELECT * FROM BankCustomer WHERE Customer_Number = . . . Verifica a senha do cliente Cria um objeto cliente apartir dos dados vindos do banco; Estudo de Caso • A classe ContaBancaria terá seus métodos alterados; • O método getSaldo deve pegar o saldo da base de dados; • Os métodos saque e deposito devem atualizar a base de dados; Case Study: A Bank Database public double getBalance() throws SQLException { Connection conn = SimpleDataSource.getConnection(); try { double balance = 0 PreparedStatement stat = conn.prepareStatement( "SELECT Balance FROM Account WHERE Account_Number = ?"); stat.setInt(1, accountNumber); ResultSet result = stat.executeQuery(); if (result.next()) balance = result.getDouble(1); return balance; } finally { conn.close(); } } Case Study: A Bank Database public void deposit(double amount) throws SQLException { Connection conn = SimpleDataSource.getConnection(); try { PreparedStatement stat = conn.prepareStatement( "UPDATE Account" + " SET Balance = Balance + ?" + " WHERE Account_Number = ?"); stat.setDouble(1, amount); stat.setInt(2, accountNumber); stat.executeUpdate(); } finally { conn.close(); } } File Bank.java 01: 02: 03: 04: 05: 06: 07: 08: 09: 10: 11: 12: 13: 14: 15: 16: import import import import java.sql.Connection; java.sql.ResultSet; java.sql.PreparedStatement; java.sql.SQLException; /** A bank consisting of multiple bank accounts. */ public class Bank { /** Finds a customer with a given number and PIN. @param customerNumber the customer number @param pin the personal identification number @return the matching customer, or null if none found */ Continued File Bank.java 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: public Customer findCustomer(int customerNumber, int pin) throws SQLException { Connection conn = SimpleDataSource.getConnection(); try { Customer c = null; PreparedStatement stat = conn.prepareStatement( "SELECT * FROM BankCustomer WHERE Customer_Number = ?"); stat.setInt(1, customerNumber); ResultSet result = stat.executeQuery(); if (result.next() && pin == result.getInt("PIN")) c = new Customer(customerNumber, result.getInt("Checking_Account_Number"), result.getInt("Savings_Account_Number")); return c; Continued File Bank.java 34: 35: 36: 37: 38: 39: 40: } 41: 42: } finally { conn.close(); } } File BankAccount.java 01: 02: 03: 04: 05: 06: 07: 08: 09: 10: 11: 12: 13: 14: 15: 16: 17: import import import import java.sql.Connection; java.sql.ResultSet; java.sql.PreparedStatement; java.sql.SQLException; /** A bank account has a balance that can be changed by deposits and withdrawals. */ public class BankAccount { /** Constructs a bank account with a given balance. @param anAccountNumber the account number */ public BankAccount(int anAccountNumber) { Continued File BankAccount.java 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: accountNumber = anAccountNumber; } /** Deposits money into a bank account. @param amount the amount to deposit */ public void deposit(double amount) throws SQLException { Connection conn = SimpleDataSource.getConnection(); try { PreparedStatement stat = conn.prepareStatement( "UPDATE Account" + " SET Balance = Balance + ?" + " WHERE Account_Number = ?"); stat.setDouble(1, amount); Continued File BankAccount.java 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: stat.setInt(2, accountNumber); stat.executeUpdate(); } finally { conn.close(); } } /** Withdraws money from a bank account. @param amount the amount to withdraw */ public void withdraw(double amount) throws SQLException { Connection conn = SimpleDataSource.getConnection(); Continued File BankAccount.java 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: try { PreparedStatement stat = conn.prepareStatement( "UPDATE Account" + " SET Balance = Balance - ?" + " WHERE Account_Number = ?"); stat.setDouble(1, amount); stat.setInt(2, accountNumber); stat.executeUpdate(); } finally { conn.close(); } } Continued File BankAccount.java 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: /** Gets the balance of a bank account. @return the account balance */ public double getBalance() throws SQLException { Connection conn = SimpleDataSource.getConnection(); try { double balance = 0; PreparedStatement stat = conn.prepareStatement( "SELECT Balance FROM Account WHERE Account_Number = ?"); stat.setInt(1, accountNumber); ResultSet result = stat.executeQuery(); if (result.next()) balance = result.getDouble(1); Continued File BankAccount.java 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: } 96: return balance; } finally { conn.close(); } } private int accountNumber;