1. 북보
package book;
public class BookVo {
private int booknum;
private String title;
private String writer;
private int stock_b;
public BookVo () {}
public BookVo(int booknum, String title, String writer, int stock_b) {
super();
this.booknum = booknum;
this.title = title;
this.writer = writer;
this.stock_b = stock_b;
}
public BookVo(int booknum, String title, String writer) {
super();
this.booknum = booknum;
this.title = title;
this.writer = writer;
}
public int getBooknum() {
return booknum;
}
public void setBooknum(int booknum) {
this.booknum = booknum;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public int getStock_b() {
return stock_b;
}
public void setStock_b(int stock_b) {
this.stock_b = stock_b;
}
@Override
public String toString() {
return "bookVo (책 번호 = " + booknum + ", 책 제목 = " + title + ", 작가 = " + writer + ", 재고 = " + stock_b + ")";
}
}
2. 북다오
package book;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import conn.DBConnect;
public class BookDao {
private DBConnect dbconn;
public BookDao() {
dbconn = DBConnect.getInstance();
}
public void insert(BookVo vo) {
Connection conn = dbconn.conn();
String sql = "insert into book values (seq_booknum.nextval, ?, ?, 1)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, vo.getTitle());
pstmt.setString(2, vo.getWriter());
int num = pstmt.executeUpdate();
System.out.println(vo.getTitle() + "책이 등록 되었습니다");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void update(BookVo vo) {
Connection conn = dbconn.conn();
String sql = "update book set title = ?, writer = ? where booknum = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, vo.getTitle());
pstmt.setString(2, vo.getWriter());
pstmt.setInt(3, vo.getBooknum());
int num = pstmt.executeUpdate();
System.out.println("책 정보가 수정되었습니다");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void delete(int i) {
Connection conn = dbconn.conn();
String sql = "delete from book where booknum = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, i);
int num = pstmt.executeUpdate();
System.out.println("책" +num+ "권이 삭제 되었습니다");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public ArrayList<BookVo> selectAll() {
ArrayList<BookVo> list = new ArrayList<>();
Connection conn = dbconn.conn();
String sql = "select * from book order by booknum asc";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
list.add(new BookVo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public ArrayList<BookVo> selectByTitle(String title) {
ArrayList<BookVo> list = new ArrayList<>();
Connection conn = dbconn.conn();
String sql = "select * from book where title = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, title);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
list.add(new BookVo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public BookVo selectByBookNum(int bookNum) {
BookVo vo = null;
Connection conn = dbconn.conn();
String sql = "select * from book where bookNum = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bookNum);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
vo = new BookVo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return vo;
}
public ArrayList<BookVo> selectByWriter(String writer) {
ArrayList<BookVo> list = new ArrayList<>();
Connection conn = dbconn.conn();
String sql = "select * from book where writer =? ";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, writer);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
list.add(new BookVo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}
3. 예약 서비스
package book;
import java.util.ArrayList;
import java.util.Scanner;
import member.MemberService;
public class BookService {
private BookDao dao;
public BookService() {
dao = new BookDao();
}
public void insert(Scanner sc) {
System.out.println("< 책 등록 >");
System.out.println("제목 : ");
sc.nextLine();
String title = sc.nextLine();
System.out.println("작가 : ");
String writer = sc.nextLine();
dao.insert(new BookVo(0,title,writer,1));
}
public void getAll () {
System.out.println("< 책 전체 목록 >");
ArrayList<BookVo> list = dao.selectAll();
System.out.println("책번호" + "\t" + "책 제목 " + "\t" + "작가 이름 " + "\t" + "재고수량");
for (BookVo vo : list) {
System.out.println(vo.getBooknum() + "\t" + vo.getTitle() + "\t" + vo.getWriter()+ "\t" + vo.getStock_b() );
}
}
public void edit(Scanner sc) {
System.out.println("< 등록한 책 수정 >");
getAll();
System.out.println("책 번호를 입력하세요");
int booknum = sc.nextInt();
System.out.println("책 제목을 입력하세요");
String title = sc.next();
System.out.println("작가를 입력하세요");
String writer = sc.next();
dao.update(new BookVo(booknum,title,writer));
}
public void delBook (Scanner sc) {
System.out.println("< 책 삭제 >");
getAll();
System.out.println("번호를 입력 해주세요");
int i = sc.nextInt();
dao.delete(i);
}
public void selectbytitle(Scanner sc) {
System.out.println("< 제목으로 책 검색 >");
System.out.println("제목 입력하세요");
sc.nextLine();
String title = sc.nextLine();
ArrayList<BookVo> list = dao.selectByTitle(title);
if (list.size() == 0) {
System.out.println("해당 제목이 없습니다");
} else {
System.out.println(list);
}
}
public void selectbywriter(Scanner sc) {
System.out.println("< 작가이름으로 책 검색 >");
System.out.println("작가를 입력하세요");
sc.nextLine();
String writer = sc.nextLine();
ArrayList<BookVo> list = dao.selectByWriter(writer);
if (list.size() == 0) {
System.out.println("작성자가 없습니다");
} else {
System.out.println(list);
}
}
}
북매니저를 디자인할 때 콘솔창이 최대한 실제 웹페이지처럼 보이길 원해서 깔끔하게 디자인하려고 노력했습니다. 사실 그렇게 어렵지도 않고 딱히 할말은 없지만… 원래 책과 비디오 대여가 대여 시스템의 테이블이었을 때 재고는 무조건 0이나 1로 고정되어 있었습니다. 주식 때문에 커질수록 어떻게 해야 만약에 몰랐으니까… 이렇게 렌탈 패키지에 반납용 Vo를 별도로 만들어서 최대한 지름길을 피하려고 노력했습니다.
'추천 관련글,
하지만 지금은 인벤토리가 커져도 이 방법을 바꾸었으니 소개하다 할 수 있을 것 같아요. 시간날때 해봐야겠네요.