Introduction
기존에 작성한 글 JDBC 기초편에 이어 이번엔 상품관리 & 재고 관리 프로그램을 만들어 보았다. 이는 2개의 DB 테이블, Trigger, Sequence를 사용하여 별도의 테이블간 재고 수량 기록 및 입출고 내역 관리가 이루어진다. 이번 글에서는 기초편에서 다루지 않았던 부분들 및 Oracle DB (특히 Trigger) 중심으로 기술할 예정이다.
Requirements
<<요구사항>>
상품재고관리프로그램을 작성하세요.
다음과 같은 데이터를 담을수 있도록 처리하세요.
--------------------------------------------------------------
product_id p_name price description stock
--------------------------------------------------------------
nb_ss7 삼성노트북 1570000 시리즈 7 55
nb_macbook_air 맥북에어 1200000 애플 울트라북 0
pc_ibm ibmPC 750000 windows 8 10
--------------------------------------------------------------
- 상품정보를 삭제하면, 해당 입출고 데이터도 삭제되도록 처리하세요.
- 입고된 수량보다 많은 수량을 출고하려하면, 에러메세지를 보이고, 실행중지하세요.
- 상품입출고테이블에 데이터가 삽입될때마다, 자동으로 재고테이블의 수량이 변경될 수 있도록 트리거를 작성하세요.
상품테이블 PRODUCT_STOCK
PRODUCT_ID VARCHAR2(30) PRIMARY KEY,
PRODUCT_NAME VARCHAR2(30) NOT NULL,
PRICE NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50),
STOCK NUMBER DEFAULT 0
상품입출고 테이블 PRODUCT_IO
IO_NO NUMBER PRIMARY KEY => sequence
PRODUCT_ID VARCHAR2(30) references PRODUCT_STOCK Table PRODUCT_ID
IODATE DATE DEFAULT SYSDATE
AMOUNT NUMBER
STATUS CHAR(1) CHECK (STATUS IN ('I', 'O'))
(메뉴 구성 : 메인메뉴)
===== 상품재고관리프로그램 =====
1.전체상품조회
2.상품아이디검색
3.상품명검색
4.상품추가
5.상품정보변경
6.상품삭제
7.상품입/출고 메뉴
9.프로그램종료
(메뉴 구성 : 수정메뉴)
===== 상품정보변경메뉴 =====
1.상품명변경
2.가격변경
3.설명변경
9.메인메뉴로 돌아가기
(메뉴 구성 : 입출고메뉴)
===== 상품입출고메뉴=====
1.전체입출고내역조회
2.상품입고
3.상품출고
9.메인메뉴로 돌아가기
Requirements Analysis: DB
- 테이블과 Trigger: 상품 재고 테이블과 상품 입출고 테이블, 두 가지 테이블을 생성한다. 이는 Trigger로 연결되어 상품 재고 테이블에서 새로운 상품과 그의 수량을 추가하면 입출고 테이블에 자동 입고 등록이 되고, 입출고 테이블에서 기존 상품(재고 테이블에 존재하는)을 입/출고하게되면 입출고 테이블에는 내역 반영, 상품 재고 테이블에는 재고 수량이 합산 또는 감산된다.
- 입고된 수량보다 많은 수량을 출고할 수 없게 하려면 1. DB 자체적으로 check Constraint를 선언하여 (amount > 0)으로 데이터 무결성을 지키는 방법이 있고, 자바 내부에서 처리하는 방법도 있다. 만약을 대비하여 check Constraint 또한 선언하기로 한다.
- 재고 테이블과 입출고 테이블은 Foreign Key(product_id) 상속 관계로 설정되어 있고, 데이터 무결성의 원칙을 지키기 위해 on delete cascade(부모 테이블의 정보를 삭제시 자식 테이블의 정보 또한 삭제) 설정이 요구된다.
Requirements Analysis: Java
- 부수메뉴인 입출고 메뉴의 원활한 호출 및 값 대입을 위하여 vo는 각 테이블에 맞게 별도로 생성한다.
- 입출고 메뉴의 조회 및 입/출고 처리를 위해 해당 메뉴에서는 재고 테이블이 아닌 입출고 테이블을 호출하고 처리한다. 이 때, 입출고 구분은 대입될 재고 수량의 양/음수 구분이 아닌 status 값 대입을 통해 Oracle DB의 Conditional Trigger가 자동 반영하도록 한다.
- 입출고 테이블의 5개 Column 중, DB선에서 채번된 sequence 값을 자동 대입하는 io_no, trigger 발동 시점의 일자가 default 값으로 대입되는 io_Date, Query문에서 자동 대입 또는 파라미터 전달등을 통해 대입될 status 값은 사용자의 개입이 불필요하다. 오직 product_id와 amount만이 사용자로부터 입력 받은 값이 반영된다. 따라서 이 두 가지 항목에 대한 파라미터 생성자를 선언하면 보다 쉽고 빠른 객체 생성 및 반환이 가능하다.
Oracle DB: Table, Sequence, Trigger
<<Table: product_stock>>
create table product_stock0( product_id varchar2(30), product_name varchar2(30) not null, price number(10) not null, description varchar2(50), stock number default 0, constraint PK_PRODUCT_ID primary key(product_id) on delete cascade ); --ADD FOREIGN KEY CONSTRAINT alter table product_stock add constraints FK_PRODUCT_ID foreign key(product_id) references product_stock(product_id);<<Table: product_io>>
create table product_io( io_no number, product_id varchar2(30), iodate date default sysdate, amount number, status char(1), constraints CK_IO_STATUS check(status in ('I', 'O')), constraints FK_PRODUCT_ID foreign key(product_id) references product_stock00(product_id) on delete set null ); --ADD CHECK CONSTRAINT: AMOUNT CANNOT BE NEGATIVE alter table product_io add constraints CK_AMOUNT_NOT_NEGATIVE_ check (amount >= 0);<<Create Sequence>>
create sequence seq_product;<<Trigger(1): add Stock to I/O Table when new Product added>>
create or replace trigger trg_product1 after insert on product_stock for each row BEGIN insert into product_io values (seq_product.nextval, :new.product_id, default, :new.stock, 'I'); END; /<<Trigger(2): add Stock to Stock Table from I/O Table>>
create or replace trigger trg_product03 after insert on product_io for each row BEGIN if :new.status = 'I' then update product_stock set stock = stock + :new.amount where product_id = :new.product_id; elsif :new.status = 'O' then update product_stock set stock = stock - :new.amount where product_id = :new.product_id; end if; END; /<<Don't forget to commit!>>
commit;
요구사항 분석에 맞게 생성한 2개의 테이블, 2개의 Trigger, 그리고 Sequence이다.
이 중 Trigger의 역할이 특히 중요한데, 첫 번째 Trigger는 재고 테이블에 새로운 상품이 등록되면 이를 입출고 테이블에 등록 및 입고 처리한다. 재고 테이블의 Column 중 Stock, 즉 재고 항목이 있기 때문에, 이 역시 일종의 입고에 해당되기 때문이다.
또한 두 번째 Trigger는 입출고 테이블에 입/출고를 진행할 때에 status Column의 값에 따라 입/출 여부를 판별하여 수량을 반영한다. IF-Condition과 PSEUDO를 활용하여 새로 대입된 수량(amount)를 기존의 (PSEUDO가 붙지 않은 Column명 = 해당 Column의 기존 값) 값에 더하거나 빼는 식이다. 입출고 테이블의 입/출고는 java의 입/출 메뉴에서 진행될 것이고, Trigger를 통해 재고 테이블의 총 수량에 자동 반영될 것이다.
Properties: PreparedStatement
<<product-query.properties>>
############################## ###STOCK MANAGEMENT QUERIES### ############################## selectAll = select * from product_stock0 getID = select product_id from product_stock0 where product_id = ? selectID = select * from product_stock0 where product_id like ? selectName = select * from product_stock0 where product_name like ? insert = insert into product_stock0 values (?,?,?,?,?) delete = delete from product_stock0 where product_id = ? update1 = update product_stock0 set update3 = = ? where product_id = ? selectAllio = select * from product_io0 addStock = insert into product_io0 values (seq_product.nextval,?,default, ?, 'I') subtractStock = insert into product_io0 values (seq_product.nextval,?,default, ?, 'O') getAmount = select stock from product_stock0 where product_id = ?
DAO 클래스에 대입될 Query문, 즉 preparedstatement pstmt = conn.prepareStatement(String sql);에 대입될 미완성 쿼리문을 .properties 파일에 저장해 두었다. 이 뿐만 아니라 프로그램을 구동시키기 위한 필수 정보 중 하나인 문자열 driver, url, user, password 등을 properties로 작성하여 불러오면 보안성 및 관리의 편의성 향상을 꾀할 수 있다. 이는 key-value의 형식을 취하고 있기 때문에 .getProperty(String key); 와 같이 불러올 수 있다.
또한 위의 properties 파일에서 key update1, update3은 매개인자로 대상 String을 받아와 이와 결합하기 위함이다. 단 conn.prepareStatement()의 인자로 전달할 문자열은 "결합이 완료된" 쿼리문이어야 한다. 즉 String sql = update1 + 인자로 받은 대상 문자열 + update3의 형식이어야 한다는 것. 또한 properties 파일을 작성할 때엔 쿼리문을 반드시 한 줄로 작성하여야 한다.
Java: Negative Stock Exception Handling(1) Get Existing Stock
<<Comparison: get existing Stock>>
--FROM DAO: GET EXISTING STOCK public int getAmount(Connection conn, String productID) { PreparedStatement pstmt = null; ResultSet rset = null; String sql = prop.getProperty("getAmount"); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, productID); rset = pstmt.executeQuery(); while (rset.next()) { amount = rset.getInt("stock"); } } catch (Exception e) { e.printStackTrace(); } finally { closeR(rset); closeS(pstmt); } return amount; }--QUERY FROM PROPERTIES getAmount = select stock from product_stock0 where product_id = ?
요구 사항 중, 재고 테이블의 총 수량보다 많은 수량을 출고하려고 할 때 에러를 발생시키고, 프로그램을 종료시키라는 항목이 있었다. 이를 처리하기 위해서는 다음과 같은 방법이 존재한다:
- Oracle DB에서 Check Constraint (amount >= 0) 선언
- SQLIntergrityConstraintViolationException의 예외처리
- 고의로 null을 발생시킨 후 NullPointerException 예외처리
- 총 수량을 int 값으로 받아온 후 사용자가 입력한 출고 수량과 대조 및 조건절 처리
0번, DB에서 check Constraint를 거는 것은 선택이 아닌 필수이다.
(DB측에서 다시 한 번 검증하여 에러를 발생시켜 데이터 무결성을 지킬 수 있기 때문에)
또한 2번, SQLIntergrityConstraintViolationException 항목이 꽤나 재미있는 방법이다. 이름은 참 길지만 정말 직관적인 네이밍 센스다. 이를 직역하면 SQL 무결성(Intergrity) 제약조건(Constraint) 위반(Violation) 예외(Exception)로, DB에서 테이블에 사전 설정한 제약 조건이 위반되어 데이터의 무결성을 해치게 되는 상황에 발생하는 예외이며 이는 SQLException의 후손이다.
입출고 관리의 대상 테이블인 입출고 테이블에서 사전 선언된 Constraint는 총 2가지로, primary Key가 부여된 io_no와 check를 부여한 amount가 있었다. 이 중, io_no는 sequence를 통해 채번된 중복 없는 고유번호가 Trigger를 통해 자동으로 대입되고 있으므로 예외 발생 가능성이 존재하는(즉 사용자로부터 입력값을 받는) Column은 오직 amount 뿐이다. 따라서 SQL무결성 제약조건 위반 Exception을 예외처리하면 해결될 것 같지만, 이는 오직 표면적인 방법일 뿐이다. Trigger를 통해 재고 테이블과 입출고 테이블이 연동되어 있으므로 전자 테이블의 제약조건이 위반되는 경우가 발생하면 이 역시 같은 예외를 발생시키기 때문이다. 다시 말해, "총 수량보다 많은 수량을 출고하려고 할 때" 이 한 가지 경우에만 한정하기 위해서는 별도의 조건절이 필요하기에 다루기 다소 까다로우며 효율이 좋지 않다.
코드를 작성하며 위에 작성한 방법 모두를 시도해 보았고, 여타 예외 상황이 발생할 가능성이 없는 3번+4번 방식으로 최종 진행하였다. 별도의 메소드를 통해 현 재고의 총 수량을 받아온 후 이를 int로 리턴받았으며, 이 반환값과 사용자가 입력한 출고 수량을 대조하여 출고 결과가 음수가 아닐 때만 출고를 진행하였다. 또한 출고 결과가 음수일 경우 출고를 진행하지 않고, null을 리턴하게 한다. 이후 이 null로 인해 발생하는 NullPointerException을 예외처리하여 별도의 메세지를 출력한 후 프로그램을 의도된 비정상 종료(System.exit(0);) 하도록 했다.
Java: Negative Stock Exception Handling(2) Compare with input-Stock, Print Error Message, Exit the Program
<<Compare Existing Stock with input-Stock>>
--FROM VIEW: DISTINGUISH STOCK IN/TAKE OFF private StockIO addStock(int i) { try { System.out.println("Which Product would you wanna stock/take Off? \nPlease fill up the form"); System.out.println("\"Exact\" Product ID? > "); String productID = sc.nextLine(); System.out.println("Amount? > "); int amount = Integer.parseInt(sc.nextLine()); existingStock = pc.getAmount(productID); if (i == 0) { if (amount <= existingStock) { stockIO = new StockIO(productID, amount); } else return null; } else if (i == 1) { stockIO = new StockIO(productID, amount); } } catch (Exception e) { printInvalid("Form"); e.printStackTrace(); } return stockIO; }<<Print Error Message>>
public int addStock(Connection conn, StockIO stockIO, int i) { int result = 0; PreparedStatement pstmt = null; String sql = null; if(i == 1) sql = prop.getProperty("addStock"); else sql = prop.getProperty("subtractStock"); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, stockIO.getProductID()); pstmt.setInt(2, stockIO.getAmount()); result = pstmt.executeUpdate(); } catch(NullPointerException npe) { System.out.println ("Input Amount less than Existing Amount of Stock. \nPlease check Again."); System.exit(0); }catch (Exception e) { e.printStackTrace(); } finally { closeS(pstmt); } return result; }
예외처리 방식을 설정하고 코드를 작성하는 것까지는 좋았는데, 에러 메세지를 발생시킬 위치를 찾지 못해 고생을 조금 했다. 차근차근 생각해보면 쉽다. JDBC 기초편에서 작성한 바와 같이 현 JDBC 코드는:
- run -> view -> controller -> service -> DAO (처리 요청, request)
- DAO (처리값 도출) -> service -> controller -> view (처리값 반환, response)
-의 프로세스로 이루어 진다. 또한 코드의 처리 흐름을 살펴보았을 때, view에 별도 메소드를 생성하여 대조 결과 총 수량보다 출고 수량이 많으면 고의적으로 null을 리턴하고 있으므로 DAO에서 NullPointerException이 발생하게 된다. 왜냐? view에서 리턴받은 null이 service와 controller를 그대로 타고가서 DAO에서 처리하려 할 때 딱 걸리게 되니까. 따라서 DAO에서의 딱 한 번의 예외처리로 의도한 바 구현이 가능하다.
Archive: JDBC Stock Management System - zip
아카이브 겸 코드 전문의 압축 파일을 이 곳에 백업한다.
링크: https://www.dropbox.com/s/4tzmz1r6kkad4vl/park_stock_management_final.zip?dl=0
