Java Bank
Creating tables and triggers
In this section we will modify the Java bank example to store the account and transaction history in the database.
Connect to soctt account with SQL*PLUS and create the following table.
create table bankaccount (
accountno varchar2(50),
owner varchar2(20) not null,
balance number,
kind varchar2(10),
constraint PK_BANKACCOUNT primary key(accountno),
constraint CK_BANKACCOUNT_NORMAL
CHECK (balance >= CASE WHEN kind='NORMAL' THEN 0 END),
constraint CK_BANKACCOUNT_KIND CHECK (kind in ('NORMAL', 'MINUS'))
);
create table transaction (
transactiondate timestamp,
kind varchar2(10),
amount number,
balance number,
accountno varchar2(50),
constraint FK_TRANSACTION FOREIGN KEY(accountno)
REFERENCES bankaccount(accountno)
);
The transaction table will use the trigger to insert the data. Here is an example of a trigger that is a hint:
create table a (name varchar2(10));
create table b (name varchar2(10));
CREATE OR REPLACE TRIGGER test_tri
AFTER
INSERT OR UPDATE ON a
FOR EACH ROW
BEGIN
insert into b values (:new.name);
END;
/
insert into a values ('Alison');
insert into a values ('Bill');
insert into a values ('Carol');
select * from b;
Create a trigger that accumulates data in the transaction history table whenever your account balance changes.
create or replace trigger bank_trigger after insert or update of balance on bankaccount for each row begin if :new.balance > :old.balance then insert into transaction values ( systimestamp, 'DEPOSIT', :new.balance - :old.balance, :new.balance, :old.accountno ); end if; if :new.balance < :old.balance then insert into transaction values ( systimestamp, 'WITHDRAW', :old.balance - :new.balance, :new.balance, :old.accountno ); end if; end; /
Modifying JavaBeans
Transaction history class is the same as before.
Transaction.java
package net.java_school.bank;
import java.io.Serializable;
public class Transaction implements Serializable {
private String transactionDate;
private String transactionTime;
private String kind;
private long amount;
private long balance;
public Transaction() {}
public Transaction(String transactionDate,
String transactionTime,
String kind,
long amount,
long balance) {
this.transactionDate = transactionDate;
this.transactionTime = transactionTime;
this.kind = kind;
this.amount = amount;
this.balance = balance;
}
public String getTransactionDate() {
return transactionDate;
}
public void setTransactionDate(String transactionDate) {
this.transactionDate = transactionDate;
}
public String getTransactionTime() {
return transactionTime;
}
public void setTransactionTime(String transactionTime) {
this.transactionTime = transactionTime;
}
public String getKind() {
return kind;
}
public void setKind(String kind) {
this.kind = kind;
}
public long getAmount() {
return amount;
}
public void setAmount(long amount) {
this.amount = amount;
}
public long getBalance() {
return balance;
}
public void setBalance(long balance) {
this.balance = balance;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(this.transactionDate);
sb.append("|");
sb.append(this.transactionTime);
sb.append("|");
sb.append(this.kind);
sb.append("|");
sb.append(this.amount);
sb.append("|");
sb.append(this.balance);
return sb.toString();
}
}
Change the abstract class Account to a normal class. Remove NormalAccount and MinusAccount.
Account.java
package net.java_school.bank;
import java.io.Serializable;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class Account implements Serializable {
private String accountNo;
private String name;
protected long balance;
private String kind;
protected List<Transaction> transactions = new ArrayList<Transaction>();
static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd");
static final SimpleDateFormat TIME_FORMAT = new SimpleDateFormat("HH:mm:ss");
static final String DEPOSIT = "DEPOSIT";
static final String WITHDRAW = "WITHDRAW";
static final String NORMAL = "NORMAL";
static final String MINUS = "MINUS";
static final NumberFormat NUMBER_FORMAT = NumberFormat.getInstance();
public Account() {}
public Account(String accountNo, String name, String kind) {
this.accountNo = accountNo;
this.name = name;
this.kind = kind;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAccountNo() {
return accountNo;
}
public void setAccountNo(String accountNo) {
this.accountNo = accountNo;
}
public long getBalance() {
return balance;
}
public void setBalance(long balance) {
this.balance = balance;
}
public List<Transaction> getTransactions() {
return transactions;
}
public void setTransactions(List<Transaction> transactions) {
this.transactions = transactions;
}
public String getKind() {
return kind;
}
public void setKind(String kind) {
this.kind = kind;
}
@Override
public String toString() {
StringBuffer sb = new StringBuffer();
sb.append(accountNo);
sb.append("|");
sb.append(name);
sb.append("|");
sb.append(balance);
sb.append("|");
sb.append(kind);
return sb.toString();
}
}
Bank.java shows all the functions that the JavaBank system can do.
Bank.java
package net.java_school.bank;
import java.io.Serializable;
import java.util.List;
public interface Bank extends Serializable {
//Creating an account.
public void addAccount(String accountNo, String name, String kind);
/*//Creating an account
public void addAccount(String accountNo, String name, long balance, String kind);
*/
//Finding an account by account number
public Account getAccount(String accountNo);
//Finding an account by owner name
public List<Account> findAccountByName(String name);
//All accounts
public List<Account> getAccounts();
//Deposit
public void deposit(String accountNo, long amount);
//Withdraw
public void withdraw(String accountNo, long amount);
//Transfer
public void transfer(String from, String to, long amount);
//All transaction history
public List<Transaction> getTransactions(String accountNo);
}
BankDao.java show all database related functions.
BankDao.java
package net.java_school.bank;
import java.util.List;
public interface BankDao {
//Creating an account
public void insertAccount(String accountNo, String name, String kind);
//Finding an account by account number
public Account selectOneAccount(String accountNo);
//Finding an account by owner name
public List<Account> selectAccountsByName(String name);
//All accounts
public List<Account> selectAllAccounts();
//Deposit
public void deposit(String accountNo, long amount);
//Withdraw
public void withdraw(String accountNo, long amount);
//All transaction history
public List<Transaction> selectAllTransactions(String accountNo);
}
Adding libraries
Add the logging library and the Oracle JDBC driver to your classpath.
Download the latest distribution from http://www.slf4j.org/download.html.
Uncompress and add the slf4j-api-1.7.xx.jar and slf4j-simple-1.7.xx.jar files and the Oracle JDBC driver to the classpath.
If you are working in Eclipse, see the following figure.
Create an implementation class for BankDao.
ShinhanBankDao.java
package net.java_school.bank;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ShinhanBankDao implements BankDao {
Logger logger = LoggerFactory.getLogger(ShinhanBankDao.class);
static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
static final String USER = "scott";
static final String PASSWORD = "tiger";
public ShinhanBankDao() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
private void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void insertAccount(String accountNo, String name, String kind) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = "INSERT INTO bankaccount VALUES (?, ?, 0, ?)";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, accountNo);
pstmt.setString(2, name);
pstmt.setString(3, kind);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, pstmt, con);
}
}
@Override
public Account selectOneAccount(String accountNo) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Account account = null;
String sql = "SELECT accountNo,owner,balance,kind " +
"FROM bankaccount " +
"WHERE accountNo = ?";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, accountNo);
rs = pstmt.executeQuery();
if (rs.next()) {
account = new Account();
account.setAccountNo(rs.getString("accountNo"));
account.setName(rs.getString("owner"));
account.setBalance(rs.getLong("balance"));
account.setKind(rs.getString("kind"));
return account;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, pstmt, con);
}
return null;
}
@Override
public List<Account> selectAccountsByName(String name) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Account> matched = new ArrayList<Account>();
Account account = null;
String sql = "SELECT accountNo,owner,balance,kind " +
"FROM bankaccount " +
"WHERE owner = ? " +
"ORDER By accountNo DESC";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
while (rs.next()) {
account = new Account();
account.setAccountNo(rs.getString("accountNo"));
account.setName(rs.getString("owner"));
account.setBalance(rs.getLong("balance"));
account.setKind(rs.getString("kind"));
matched.add(account);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, pstmt, con);
}
return matched;
}
@Override
public List<Account> selectAllAccounts() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Account> all = new ArrayList<Account>();
Account account = null;
String sql = "SELECT accountNo,owner,balance,kind " +
"FROM bankaccount " +
"ORDER By accountNo DESC";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
account = new Account();
account.setAccountNo(rs.getString("accountNo"));
account.setName(rs.getString("owner"));
account.setBalance(rs.getLong("balance"));
account.setKind(rs.getString("kind"));
all.add(account);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, pstmt, con);
}
return all;
}
@Override
public void deposit(String accountNo, long amount) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = "UPDATE bankaccount " +
"SET balance = balance + ? " +
"WHERE accountNo = ?";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setLong(1, amount);
pstmt.setString(2, accountNo);
pstmt.executeUpdate();
logger.debug("AccountNo:{} Amount:{} DEPOSIT/WITHDRAW:{}",
accountNo, amount, Account.DEPOSIT);
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, pstmt, con);
}
}
@Override
public void withdraw(String accountNo, long amount) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = "UPDATE bankaccount " +
"SET balance = balance - ? " +
"WHERE accountNo = ?";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setLong(1, amount);
pstmt.setString(2, accountNo);
pstmt.executeUpdate();
logger.debug("AccountNo:{} Amount:{} DEPOSIT/WITHDRAW:{}",
accountNo, amount, Account.DEPOSIT);
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(null, pstmt, con);
}
}
@Override
public List<Transaction> selectAllTransactions(String accountNo) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Transaction> all = new ArrayList<Transaction>();
Transaction transaction = null;
String sql = "SELECT transactionDate,kind,amount,balance " +
"FROM transaction " +
"WHERE accountNo = ? " +
"ORDER By transactionDate ASC";
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, accountNo);
rs = pstmt.executeQuery();
while (rs.next()) {
transaction = new Transaction();
String date = Account.DATE_FORMAT.format(rs.getTimestamp("transactionDate"));
String time = Account.TIME_FORMAT.format(rs.getTimestamp("transactionDate"));
transaction.setTransactionDate(date);
transaction.setTransactionTime(time);
transaction.setKind(rs.getString("kind"));
transaction.setAmount(rs.getLong("amount"));
transaction.setBalance(rs.getLong("balance"));
all.add(transaction);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, pstmt, con);
}
return all;
}
}
Create an implementation class for Bank.
ShinhanBank.java
package net.java_school.bank;
import java.util.List;
public class ShinhanBank implements Bank {
private BankDao dao = new ShinhanBankDao();
@Override
public void addAccount(String accountNo, String name, String kind) {
dao.insertAccount(accountNo, name, kind);
}
@Override
public Account getAccount(String accountNo) {
return dao.selectOneAccount(accountNo);
}
@Override
public List<Account> findAccountByName(String name) {
return dao.selectAccountsByName(name);
}
@Override
public List<Account> getAccounts() {
return dao.selectAllAccounts();
}
@Override
public void deposit(String accountNo, long amount) {
dao.deposit(accountNo, amount);
}
@Override
public void withdraw(String accountNo, long amount) {
dao.withdraw(accountNo, amount);
}
@Override
public void transfer(String from, String to, long amount) {
dao.withdraw(from, amount);
dao.deposit(to, amount);
}
@Override
public List<Transaction> getTransactions(String accountNo) {
return dao.selectAllTransactions(accountNo);
}
}
BankUi.java
package net.java_school.bank;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List;
public class BankUi {
private Bank bank = new ShinhanBank();
private String readCommandLine() throws IOException {
InputStreamReader isr = new InputStreamReader(System.in);
BufferedReader br = new BufferedReader(isr);
String input = br.readLine();
return input;
}
public void startWork() {
String menu = null;
do {
System.out.println(" ** Please select menu ** ");
System.out.println(" 1 ** New Account registration ");
System.out.println(" 2 ** All Accounts ");
System.out.println(" 3 ** Deposit ");
System.out.println(" 4 ** Withdraw ");
System.out.println(" 5 ** Transfer ");
System.out.println(" 6 ** Transaction history ");
System.out.println(" q ** Quit ");
System.out.println(" ********************** ");
System.out.println(">>");
try {
menu = readCommandLine();
String accountNo = null;
String name = null;
String kind = null;
long amount = 0;
if (menu.equals("1")) {
//TODO New Account registration
System.out.println("Enter the account number of the account you want to create: ");
accountNo = this.readCommandLine();
System.out.println("Enter the owner name of the account you want to create: ");
name = this.readCommandLine();
System.out.println("Please select an account kind. noraml (n), minus (m): normal (n) : : ");
kind = this.readCommandLine();
if (kind.equals("") || kind.equals("n") || kind.equals("m")) {
if (kind.equals("")) {
kind = Account.NORMAL;
} else if (kind.equals("n")) {
kind = Account.NORMAL;
} else {
kind = Account.MINUS;
}
bank.addAccount(accountNo, name, kind);
}
} else if (menu.equals("2")) {
//TODO All accounts
List<Account> accounts = bank.getAccounts();
for (Account account : accounts) {
System.out.println(account);
}
} else if (menu.equals("3")) {
//TODO Deposit
System.out.println("Please enter your account number: ");
accountNo = this.readCommandLine();
System.out.println("Please enter deposit amount: ");
amount = Integer.parseInt(this.readCommandLine());
bank.deposit(accountNo, amount);
} else if (menu.equals("4")) {
//TODO Withdraw
System.out.println("Please enter your account number: ");
accountNo = this.readCommandLine();
System.out.println("Please enter withdraw amount: ");
amount = Integer.parseInt(this.readCommandLine());
bank.withdraw(accountNo, amount);
} else if (menu.equals("5")) {
//TODO Transfer
System.out.println("Please enter your withdrawal account number: ");
String from = this.readCommandLine();
System.out.println("Please enter your deposit account number: ");
String to = this.readCommandLine();
System.out.println("Enter transfer amount: ");
amount = Integer.parseInt(this.readCommandLine());
bank.transfer(from, to, amount);
} else if (menu.equals("6")) {
//TODO Transaction history
System.out.println("Please enter your account number: ");
accountNo = this.readCommandLine();
List<Transaction> transactions = bank.getTransactions(accountNo);
for (Transaction transaction : transactions) {
System.out.println(transaction);
}
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println();
} while (!menu.equals("q"));
}
public static void main(String[] args) throws Exception {
BankUi ui = new BankUi();
ui.startWork();
}
}