[mini project] 6. 도서관 관리 시스템 - 책 관리 테이블

 

1. BookVo

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. BookDao

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. BookService 

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로 fix 했었다.  왜냐하면 재고가 커질수록 어떻게 해야 할지 몰랐기 때문에...  이렇게 rent 패키지에 반납용 vo 까지 따로 만들어서 최대한 조인을 피해 보려고 했다. 

근데 이제 그 방법을 바꿨으니까 재고가 더 커져도 구상할 수 있을 것 같은 생각이 든다.  시간 나면 해봐야지.