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;