국비학원 20일차) 오라클로 계좌 관리 시스템 만들기(사용자 정의 예외)

 

<조건> 깁니다...

**계좌관리 시스템**
고객은 통장(계좌)을 개설할 수 있다.
개설시 필요한 정보는 계좌번호, 초기 납입금, 계좌주명이 필요하다.
계좌번호로 계좌를 조회하면 계좌주명, 잔액 정보가 나온다.
고객은 입금할 수 있고, 출금할 수 있다.
고객은 다수의 계좌를 개설할 수 있다.
고객명으로 계좌의 전체 총액 및 평균액을 조회할 수 있다.
 
- 업무 정의 

- db 설계 : table (column 정의)

- class의 list 
-업무정의


- db 설계 : table (column 정의)
db  이름 : BankVO
accountNumber varchar2(50) primary key,
accountName varchar2(40) not null,
balance(50) number not null;


- class list
-업무정의


- db 설계 : table (column 정의)
db  이름 : BankVO
accountNumber varchar2(50) primary key,
accountName varchar2(40) not null,
balance(50) number not null;


- class list
---- TestAccountDao 
---- AccountDao (비즈니스 로직)     
insertAccount    
depositMoney                
withDrawMoney                 
findAccount                 
avgAccount


---- InvalidBalanceException 
---- NotFoundAccountException 
---- NotFoundNameException
---- InsufficientMoneyException
---- DuplicateAccountException 


---- AccountVO
String accountNumber
String accountName
int balance


--세부 Exception        
insertAccount : 객체
-InsufficientMoneyException : 초기 입금액이 0원 이하 일때
-NotFoundName : 고객명이 없을 시에
-DuplicateAccountException : 이미 동일한 계좌가 생성되어 있습니다.
System.out.println("계좌생성완료");


depositMoney : String, int
-InsufficientMoneyException : 0원 이하 입금시
-NotFoundAccountException : 계좌가 없을 시
System.out.println(int+" 가 입금 되었습니다.");
메세지 리턴값 : int 


withDrawMoney : String, int
-InsufficientMoneyException : 0원 이하 출금시
-InvalidBalanceException 잔액부족할 시에      
-NotFoundAccountException : 계좌가 없을 시
System.out.println(int+" 가 출금 되었습니다.");
메세지 리턴값 : int 
      
findAccount : String
-NotFoundAccountException : 계좌가 없을 시
반환값 : 객체타입


sumAccount : String
-NotFoundName : 고객이 없을 시에
리턴값 : int 


avgAccount : String
-NotFoundName : 고객이 없을 시에
리턴값 : int 

 

 

 

 

 

<클래스들...> DAO, SERVICE, TEST 주요 코드만...

 

 

 

 

<DAO>

 

package service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.SQLSyntaxErrorException;

import exception.DuplicateAccountException;
import exception.NotFoundAccountException;
import exception.NotFoundNameException;
import vo.AccountVO;

public class AccountDao implements Dao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	@Override
	public void con() {

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver"); // 드라이버 로드
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); // connection객체생성
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public void discon() {
		try {
			ps.close();
			conn.close();
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public void insertAccount(AccountVO vo) {
		con();
		int n = 0;
		String sql = "INSERT INTO BANKVO"
				+ " VALUES(?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setNString(1, vo.getAccountNumber());
			ps.setNString(2, vo.getAccountName());
			ps.setInt(3, vo.getBalance());
			ps.executeUpdate();

		} catch (SQLIntegrityConstraintViolationException e) {
			throw new DuplicateAccountException("계좌번호 중복");
		}catch (SQLException e) {
			e.printStackTrace();
		}
		discon();
	}




	@Override
	public AccountVO findAccount(String accNum){
		con();
		AccountVO av = null;
		String sql = "SELECT * FROM BANKVO"
				+ "	WHERE ACCOUNTNUMBER=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setNString(1, accNum);
			rs = ps.executeQuery();

			if(rs.next()) {
				av = new AccountVO(rs.getString(1),rs.getString(2),rs.getInt(3));
			} else {
				throw new NotFoundAccountException("계좌주 없음");
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		discon();
		return av;
	}



	@Override
	public int depositMoney(String accNum, int money){

		int m = -1;
		AccountVO av = findAccount(accNum);
		con();
		m=av.getBalance();
		m+=money;
		String sql = "update bankvo set balance=? where accountnumber=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, m);
			ps.setString(2, accNum);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		discon();
		return money;
	}



	@Override
	public int withDrawMoney(String accNum, int money) {
		int m = -1;
		AccountVO av = findAccount(accNum);
		con();

		try {
			m=av.getBalance();
			m-=money;
			String sql = "update bankvo set balance=? where accountnumber=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, m);
			ps.setString(2, accNum);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw new NotFoundAccountException("계좌 없음");
		}
		discon();
		return money;
	}

	@Override
	public int sumAccount(String name)  {
		con();
		int i = -1;
		String sql = "SELECT BALANCE FROM BANKVO"
				+ " WHERE ACCOUNTNAME = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.getUpdateCount();
			rs = ps.executeQuery();
			if(rs.next()) {
				i=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public int avgAccount(String name) {
		con();
		int i = -1;
		String sql = "SELECT TRUNC(AVG(BALANCE)) FROM BANKVO"
				+ "where accountname=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			if(rs.next()) {
				i=rs.getInt(1);
			}
		}  catch (SQLSyntaxErrorException ee) {
			throw new NotFoundNameException("계좌주 없음");
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
   }
 }

 

 

 

 

<Service>

 

package service;

import vo.AccountVO;

public class ServiceImpl implements Service{

    private Dao dao; 	
	
	public ServiceImpl() {
		dao = new AccountDao();
	}

	@Override
	public void insertAccount(AccountVO vo) {
	
	  dao.insertAccount(vo);
	
	}

	@Override
	public AccountVO findAccount(String accNum) {
		return dao.findAccount(accNum);
	}

	@Override
	public int depositMoney(String accNum, int money) {
		return dao.depositMoney(accNum, money);
	}

	@Override
	public int withDrawMoney(String accNum, int money) {
		return dao.withDrawMoney(accNum, money);
	}

	@Override
	public int sumAccount(String name) {
		return dao.sumAccount(name);
	}

	@Override
	public int avgAccount(String name) {
		return dao.avgAccount(name);
	}
}

 

 

 

 

<Test>

 

package test;

import java.util.Scanner;

import exception.InsufficientMoneyException;
import exception.NotFoundAccountException;
import exception.NotFoundNameException;
import service.Service;
import service.ServiceImpl;
import vo.AccountVO;

public class Test {

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		Scanner sc = new Scanner(System.in);
		String accountNumber=null;
		String accountName=null;
		int balance=0;

		boolean flag=true; 
		int i=0; 
		Service service = new ServiceImpl(); 
		while(flag){
			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("7.종료");
			i=sc.nextInt();
			switch(i){ 
			case 1: 
				try {
					System.out.println("계좌번호");
					accountNumber=sc.next();
					System.out.println("계좌주:");
					accountName=sc.next();
					System.out.println("입금");
					balance=sc.nextInt();

					if(balance<0) {
						throw new InsufficientMoneyException("입금액이 구림");
					} else if(accountName.equals(null)) {
						throw new NotFoundNameException("계좌주가 없음");
					} else {
						service.insertAccount(new AccountVO(accountNumber,accountName,balance));
					}
				} catch (Exception e1) {
					e1.printStackTrace();
				}
				break; 
			case 2:
				try {
					System.out.println("검색할 계좌번호");
					accountNumber=sc.next();
					AccountVO av = service.findAccount(accountNumber);
					System.out.println(av);
				} catch (Exception e1) {
					e1.printStackTrace();
				}
				break; 

			case 3: 
				try {
					System.out.println("계좌번호");
					accountNumber = sc.next();
					System.out.println("입금액");
					balance = sc.nextInt();
					if(balance<0) {
						throw new InsufficientMoneyException("입금액이 없음");
					} else {
						balance = service.depositMoney(accountNumber, balance);
						System.out.println("입금되었습니다 "+balance);
					}
				} catch (Exception e1) {
					e1.printStackTrace();
				}
				break; 

			case 4: //출금	
				try {
					System.out.println("계좌번호");
					accountNumber = sc.next();
					System.out.println("출금액");
					balance = sc.nextInt();
					if(balance<=0) {
						throw new InsufficientMoneyException("0원 이하 출금 불가");
					} else {
						service.withDrawMoney(accountNumber, balance);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}


				break;

			case 5:	
				try {
					System.out.println("계좌주 이름");
					accountName = sc.next();
					int money = service.sumAccount(accountName);
					if(money==-1) {
						throw new NotFoundNameException("고객이 없음");
					} else {
						System.out.println(money);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				break;

			case 6: 
				try {
					System.out.println("계좌주 이름");
					accountName = sc.next();
					int a = service.avgAccount(accountName);
					System.out.println("평균"+a);
				} catch (Exception e) {
					e.printStackTrace();
				}
				break;

			case 7:
				flag=false; 

			}

		}

	}

}

 

 

결과

 

대충 이런 식...

 

 

마지막 자습 시간 과제로 내주신 거

조건을 하나하나 세세하게 주셔서

맞추는거 힘드르뜨....

 

 

 

요즘 스프링 강의 사가지고 강의 듣고 실습하고 있는데

넘넘 재밋다양~

자습시간 두시간 정도 주는데 강의 들으면서 실습하면 개꿀임..

자바할땐 진짜 맨날맨날 주글거같앴는데

JDBC는 자바보단 할만해서 다른 공부 할 시간이 있당

조으다