자바은행

테이블과 트리거 생성

계좌 정보와 입출금 내역 정보를 데이터베이스에 저장하는 은행 예제를 실습해 보자.
SQL*PLUS에서 soctt/tiger로 접속하여 계좌 테이블과 입출금 명세 테이블을 다음 SQL문을 사용해 생성한다.

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)
);

계좌 테이블에서 잔액이 변할 때마다 입출금 명세 테이블에 거래 내역을 쌓는 트리거를 다음을 참조해 생성한다.

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;
/

트리거는 지정된 이벤트가 발생할 때마다 오라클 데이터베이스가 자동으로 호출하는 저장 프로 시저와 같다.
다음은 트리거에 대한 이해를 돕은 예제다.

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;
/

테이블 a에 데이터를 인서트하면, 트리거에 의해 테이블 b에도 같은 데이터가 자동으로 인서트된다.

insert into a values ('홍길동');
insert into a values ('임꺽정');
insert into a values ('장길산');

select * from b;

NAME
----------
홍길동
임꺽정
장길산

자바 빈

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 double amount;
	private double balance;

	public Transaction() {}
	
	public Transaction(String transactionDate,
			String transactionTime,
			String kind,
			double amount,
			double 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(double amount) {
		this.amount = amount;
	}
	public long getBalance() {
		return balance;
	}
	public void setBalance(double balance) {
		this.balance = balance;
	}
	
	@Override
	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append(transactionDate);
		sb.append("|");
		sb.append(transactionTime);
		sb.append("|");
		sb.append(kind);
		sb.append("|");
		sb.append(amount);
		sb.append("|");
		sb.append(balance);
		
		return sb.toString();
	}
        
}

Account.java

package net.java_school.bank;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class Account implements Serializable {
	private String accountNo;
	private String name;
	protected double balance;
	private String kind;
	protected List<Transaction> transactions = new ArrayList<Transaction>();
	
	static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("MM/dd/yyyy");
	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";

	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(double balance) {
		this.balance = balance;
	}

	public String getKind() {
		return kind;
	}
	
	public void setKind(String kind) {
		this.kind = kind;
	}
	
	public List<Transaction> getTransactions() {
		return transactions;
	}
	
	public void setTransactions(List<Transaction> transactions) {
		this.transactions = transactions;
	}
	
	@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

package net.java_school.bank;

import java.io.Serializable;
import java.util.List;

public interface Bank extends Serializable {
	
	//계좌를 생성한다.
	public void addAccount(String accountNo, String name, String kind);
	
	//계좌번호로 계좌를 찾는다.
	public Account getAccount(String accountNo);
	
	//소유자 명으로 계좌를 찾는다.
	public List<Account> findAccountByName(String name);
	
	//모든 계좌를 반환한다.
	public List<Account> getAccounts();
	
	//입금
	public void deposit(String accountNo, double amount);
	
	//출금
	public void withdraw(String accountNo, double amount);
	
	//이체
	public void transfer(String from, String to, double amount);
	
	//입출금 명세
	public List<Transaction> getTransactions(String accountNo); 
  
}

데이터베이스 관련 기능을 다음 인터페이스에 정의한다.

BankDao.java

package net.java_school.bank;

import java.util.List;

public interface BankDao {
	
	//계좌 생성
	public void insertAccount(String accountNo, String name, String kind);
	
	//계좌번호로 계좌 찾기
	public Account selectOneAccount(String accountNo);
	
	//소유자로 계좌 찾기
	public List<Account> selectAccountsByName(String name);
	
	//계좌 목록
	public List<Account> selectAllAccounts();
	
	//입금
	public void deposit(String accountNo, double amount);
	
	//출금
	public void withdraw(String accountNo, double amount);
	
	//입출금 명세
	public List<Transaction> selectAllTransactions(String accountNo);

}

외부 라이브러리 추가

로깅 라이브러리와 오라클 JDBC 드라이버를 클래스 패스에 추가한다.
http://www.slf4j.org/download.html에서 최신 배포본을 내려받는다.
압축을 풀고 slf4j-api-1.7.xx.jar 와 slf4j-simple-1.7.xx.jar 파일과 오라클 JDBC 드라이버를 클래스 패스에 추가한다.
이클립스에서 작업한다면 다음 그림을 참조한다.

BankDao 인터페이스의 구현 클래스를 작성한다.

MyBankDao.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 MyBankDao implements BankDao {
	Logger logger = LoggerFactory.getLogger(MyBankDao.class);
	
	static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
	static final String USER = "scott";
	static final String PASSWORD = "tiger";

	//생성자에서 JDBC 드라이버 로딩
	public MyBankDao() {
		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.getDouble("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.getDouble("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.getDouble("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, double 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, double 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.getDouble("amount"));
				transaction.setBalance(rs.getDouble("balance"));
				all.add(transaction);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, pstmt, con);
		}

		return all;
	}
}

Bank 인터페이스의 구현 클래스를 작성한다.

MyBank.java

package net.java_school.bank;

import java.util.List;

public class MyBank implements Bank {
	
	private BankDao dao = new MyBankDao();

	@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, double amount) {
		dao.deposit(accountNo, amount);
	}

	@Override
	public void withdraw(String accountNo, double amount) {
		dao.withdraw(accountNo, amount);
	}

	@Override
	public void transfer(String from, String to, double 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 MyBank();
	
	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(" ** 메뉴를 선택하세요 ** ");
				System.out.println(" 1 ** 계좌 등록    ");
				System.out.println(" 2 ** 계좌 목록    ");
				System.out.println(" 3 ** 입금    ");
				System.out.println(" 4 ** 출금    ");
				System.out.println(" 5 ** 이체    ");
				System.out.println(" 6 ** 입출금 명세    ");
				System.out.println(" q ** 종료    ");
				System.out.println(" ********************** ");
				System.out.println(">>");
				
				try {
					menu = readCommandLine();
				
					String accountNo = null;
					String name = null;
					String kind = null;
					double amount = 0;
					
					if (menu.equals("1")) {
						//TODO 계좌등록
						System.out.println("계좌 번호를 입력하세요: ");
						accountNo = this.readCommandLine();
						System.out.println("소유자 이름을 입력하세요: ");
						name = this.readCommandLine();
						System.out.println("계좌 종류를 선택하세요. 일반(n), 마이너스(m):  일반(n): : ");
						kind = this.readCommandLine();
						if (kind != null && kind.equals("m")) {
							bank.addAccount(accountNo, name, Account.MINUS);
						} else {
						    bank.addAccount(accountNo, name, Account.NORMAL);
						}
					} else if (menu.equals("2")) {
						//TODO 계좌목록
						List<Account> accounts = bank.getAccounts();
						for (Account account : accounts) {
							System.out.println(account);
						}
					} else if (menu.equals("3")) {
						//TODO 입금
						System.out.println("계좌 번호를 입력하세요: ");
						accountNo = this.readCommandLine();
						System.out.println("입금 액을 입력하세요: ");
						amount = Double.parseDouble(this.readCommandLine());
						bank.deposit(accountNo, amount);
					} else if (menu.equals("4")) {
						//TODO 출금
						System.out.println("계좌 번호를 입력하세요: ");
						accountNo = this.readCommandLine();
						System.out.println("출금 액을 입력하세요: ");
						amount = Double.parseDouble(this.readCommandLine());
						bank.withdraw(accountNo, amount);
					} else if (menu.equals("5")) {
						//TODO 이체
						System.out.println("송금 계좌(From) 번호를 입력하세요: ");
						String from = this.readCommandLine();
						System.out.println("입금 계좌(To) 번호를 입력하세요: ");
						String to = this.readCommandLine();
						System.out.println("이체 금액을 입력하세요: ");
						amount = Double.parseDouble(this.readCommandLine());
						bank.transfer(from, to, amount);
					} else if (menu.equals("6")) {
						//TODO 입출금 명세
						System.out.println("계좌 번호를 입력하세요: ");
						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();
	}
	
}

최종 소스

https://github.com/kimjonghoon/JavaBank