외우기

#1 bookshop.sql

CREATE TABLE bookshop(

isbn varchar2(15) primary key,

subject varchar2(30) not null,

author varchar2(10),

price number(6));

insert into bookshop(isbn, subject, author, price) values('88-010239-A3', '자바무작정 따라하기', '김따라', 12000);

insert into bookshop(isbn, subject, author, price) values('90-123456-B1', 'JSP달인되기', '박달인', 25000);

insert into bookshop(isbn, subject, author, price) values('87-345432-C1', '오라클 3일완성', '이완성', 27000);

commit

select * from bookshop;

#2

package book.entity;

public class BookShopVo {

private String isbn;

private String subject;

private String author;

private int price;

public BookShopVo() {

super();

}

public String getIsbn() {

return isbn;

}

public void setIsbn(String isbn) {

this.isbn = isbn;

}

public String getSubject() {

return subject;

}

public void setSubject(String subject) {

this.subject = subject;

}

public String getAuthor() {

return author;

}

public void setAuthor(String author) {

this.author = author;

}

public int getPrice() {

return price;

}

public void setPrice(int price) {

this.price = price;

}

}

#3 BookDao Interface

package book.dao;

public interface BookDao {

String sql_all = "SELECT * FROM BOOKSHOP";

String sql_insert = "INSERT INTO BOOKSHOP(ISBN, SUBJECT, AUTHOR, PRICE) VALUES(?,?,?,?)";

String sql_count = "SELECT COUNT(*) FROM BOOKSHOP";

}

#4 BookShopDAO

package book.dao;

//CRUD + Commit + Rollback + biz 값을 받아 리턴!

import java.sql.*;

import java.util.*;

import book.entity.*;

import static common.JDBCTemplate.*;

// Biz <====== DAO =====> BIZ 

public class BookShopDAO implements BookDao {

private Connection con;

public BookShopDAO(Connection con) {

// biz 호출

this.con = con;

}

// Insert

// String sql_insert = "INSERT INTO BOOKSHOP(ISBN, SUBJECT, AUTHOR, PRICE)

// VALUES(?,?,?,?)";

public int appendBook(BookShopVo book_info) {

PreparedStatement pstm = null;

int res = 0;

try {

pstm = con.prepareStatement(sql_insert);

pstm.setString(1, book_info.getIsbn());

pstm.setString(2, book_info.getSubject());

pstm.setString(3, book_info.getAuthor());

pstm.setInt(4, book_info.getPrice());

res = pstm.executeUpdate();

if (res > 0) {

Commit(con);

}

} catch (SQLException e) {

e.printStackTrace();

Rollback(con);

} finally {

Close(pstm);

}

return res;

}

// SELECT(ALL)

// String sql_all = "SELECT * FROM BOOKSHOP";

public ArrayList<BookShopVo> listBook() {

ArrayList<BookShopVo> all = new ArrayList<>();

BookShopVo vo = null;

Statement stmt = null;

ResultSet rs = null;

try {

stmt = con.createStatement();

rs = stmt.executeQuery(sql_all);

while (rs.next()) {

// vo = new BookShopVo(rs.getString(1), rs.getString(2), rs.getString(3),

// rs.getInt(4));

vo = new BookShopVo();

vo.setIsbn(rs.getString(1));

vo.setSubject(rs.getString(2));

vo.setAuthor(rs.getString(3));

vo.setPrice(rs.getInt(4));

all.add(vo);

}

} catch (SQLException s) {

s.printStackTrace();

} finally {

Close(rs);

Close(stmt);

}

return all;

}

// Count

public int countBook() {

Statement stmt = null;

ResultSet rs = null;

int cnt = 0;

try {

stmt = con.createStatement();

rs = stmt.executeQuery(sql_count);

if (rs.next()) {

cnt = rs.getInt(1); // 컬럼명 count(*)을 쓸 수 없으므로 1로 처리한다.

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

Close(rs);

Close(stmt);

}

return cnt;

}

}

#5 BookShopBiz

package book.biz;

import java.sql.*;

import java.util.*;

import book.dao.*;

import book.entity.*;

import static common.JDBCTemplate.*;

// Controller <========= Biz ==========> DAO

public class BookShopBiz {

public ArrayList<BookShopVo> listBook() {

Connection conn = getConnection();

BookShopDAO dao = new BookShopDAO(conn);

ArrayList<BookShopVo> all = dao.listBook();

Close(conn);

return all;

}

public int appendBook(BookShopVo book_info) {

Connection conn = getConnection();

BookShopDAO dao = new BookShopDAO(conn);

int n = dao.appendBook(book_info);

Close(conn);

return n;

}

public int countBook() {

Connection conn = getConnection();

BookShopDAO dao = new BookShopDAO(conn);

int n = dao.countBook();

Close(conn);

return n;

}

}

#6 View(Controller)

package book.view;

import book.entity.*;

import java.util.*;

import book.biz.*;

public class SalesBookShop {

public SalesBookShop() {

super();

}

Scanner sc = new Scanner(System.in);

public int menu()// * 화면설계 _VIEW */

{

StringBuffer sb = new StringBuffer();

sb.append("1. 전체");

sb.append("2. 추가");

sb.append("3. 레코드수");

sb.append("4. 종료");

sb.append("선택(1-4) :");

System.out.println(sb.toString());

int n = sc.nextInt();

return n;

}

public void process(int n) {

String isbn = null;

String subject = null;

String author = null;

int price = 0;

BookShopBiz biz = new BookShopBiz();

BookShopVo vo = new BookShopVo();

switch (n) {

case 1:

System.out.println("전체 출력");

ArrayList<BookShopVo> all = biz.listBook();

/*

* for (BookShopVo r : all) { System.out.printf("%10s %10s %10s %10d \n ",

* r.getIsbn(), r.getSubject(), r.getAuthor(), r.getPrice());

* 

*/

Iterator<BookShopVo> iter = all.iterator();

while (iter.hasNext()) {

BookShopVo vo1 = (BookShopVo) iter.next();

System.out.print("ISBN : " + vo.getIsbn() + "\t");

System.out.print("Subject : " + vo.getSubject() + "\t");

System.out.print("Author : " + vo.getAuthor() + "\t");

System.out.print("Price : " + vo.getPrice() + "\t");

}

break;

case 2: /* 추가 */

System.out.print("ISBN : ");

isbn = sc.next();

vo.setIsbn(isbn);

System.out.print("SUBJECT : ");

subject = sc.next();

vo.setSubject(subject);

System.out.print("AUTHOR : ");

author = sc.next();

vo.setAuthor(author);

System.out.print("PRICE : ");

price = sc.nextInt();

vo.setPrice(price);

int k = biz.appendBook(vo);

if (k > 0) {

System.out.println("추가되었습니다.");

} else {

System.out.println("추가 실패하였습니다");

}

break;

case 3: /* 레코드 수 */

System.out.println("레코드 수 : " + biz.countBook());

break;

case 4:

System.out.println("** 프로그램을 종료 합니다 **");

System.exit(0);

}

}

public static void main(String[] args) {

SalesBookShop sbs = new SalesBookShop();

while(true) {

int n = sbs.menu(); // 화면 설계 호출

sbs.process(n); // 입력받은 값으로 메뉴를 선택

}

}

}