웹개발 수업/JDBC
[Day +47 / JBDC]PreparedStatement 도서 관리 프로그램 만들기
Chole Woo
2021. 8. 27. 22:02
210827 금
PreparedStatement
: Connection객체의 preparedStatement() 메소드를 사용하여 객체 생성
-SQL문장이 미리 컴파일 되고 실행 시간동안 인수 값을 위한 공간을 확보한다는 점에서 Statement와 다름
-각각의 인수에 대해 위치홀더(?)를 사용하여 SQL문장을 정의할 수 있게 함
try{ String query = “INSERT INTO MEMBER VALUES(?, ?)”;
pstmt = conn.preparedStatement(query); pstmt.setString(1, id);
pstmt.setString(2, password); } catch(SQLException e){ e.printStackTrace(); }
*장점
1) 미리 설정을 해놔서 자리를 만들어 놓기 때문에 실제 실행시 빠르다
2) 간결하다
3) sql injection도 피할 수 있다
1> Controller
package book.controller;
import java.util.List;
import book.model.service.BookService;
import book.model.vo.Book;
import book.view.BookView;
/*
* Controller : 1. 요청 받은 데이터를 검증하고 서비스 클래스의 메소드에 인자로 정보를 전달
* 2. 수행 결과를 해당하는 뷰 페이지에 연결하는 역할을 수행
* */
public class BookController {
BookService bs = new BookService();
BookView bv = new BookView();
// 1. 도서 신규 등록용 메소드
public void insertBook(Book b) {
int result = bs.insertBook(b);
if(result > 0) {
bv.displayResult("insert");
} else {
bv.displayError("insert");
}
}
// 6. 도서 전체 목록 조회용 메소드
public void selectBookList() {
List<Book> bookList = bs.selectBookList();
bv.displayBookList(bookList);
}
// 2. 도서 수정용 메소드
public void updateBook(int bookId, Book b) {
// 수정할 내용이 담긴 book 객체에 bookId 함께 담아서 Service로 넘김
b.setBookId(bookId);
int result = bs.updateBook(b);
if(result > 0) {
bv.displayResult("update");
} else {
bv.displayError("update");
}
}
// 3. 도서 삭제용 메소드
public void deleteBook(int bookId) {
int result = bs.deleteBook(bookId);
if(result > 0) {
bv.displayResult("delete");
} else {
bv.displayError("delete");
}
}
// 4. 도서 번호로 도서 조회용 메소드
public void searchBookId(int bookId) {
Book b = bs.searchBookId(bookId);
bv.displayBook(b);
}
// 5. 도서명 검색
public void searchBookTitle(String bookTitle) {
// bookTitle 키워드 포함 여부로 해당하는 검색 된 도서 리스트 리턴
List<Book> bookList = bs.searchBookTitle(bookTitle);
bv.displayBookList(bookList);
}
}2> BookDao
package book.model.dao;
import static common.JDBCTemplate.close;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import book.model.vo.Book;
/* Dao (Data Access Object) : 실제 Data에 접근해서 동작할 코드 작성
* C : create (insert)
* R : read (select)
* U : update
* D : delete
* */
public class BookDao {
// 1. 도서 1권 insert
public int insertBook(Connection conn, Book b) {
PreparedStatement pstmt = null;
int result = 0;
String sql = "insert into book "
+ "values (seq.nextval, ?, ?, ?, to_date(?, 'rrrr/mm/dd'), ?)";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, b.getTitle());
pstmt.setString(2, b.getAuthor());
pstmt.setString(3, b.getPublisher());
pstmt.setString(4, b.getPublisherDate());
pstmt.setInt(5, b.getPrice());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
}
return result;
}
// 6. 도서 전체 목록 조회
public List<Book> selectBookList(Connection conn) {
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "select * from book";
List<Book> bookList = new ArrayList<>();
try {
pstmt = conn.prepareStatement(sql);
rset = pstmt.executeQuery();
while(rset.next()) {
Book b = new Book(rset.getInt("book_id"),
rset.getString("title"),
rset.getString("author"),
rset.getString("publisher"),
rset.getString("publisher_date"),
rset.getInt("price"));
bookList.add(b);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rset);
close(pstmt);
}
return bookList;
}
// 2. 도서 정보 수정
public int updateBook(Connection conn, Book b) {
PreparedStatement pstmt = null;
int result = 0;
String sql = "update book set title = ?, author = ?, publisher = ?, "
+ "publisher_date = to_date(?, 'rrrr/mm/dd'), price = ? "
+ "where book_id = ?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, b.getTitle());
pstmt.setString(2, b.getAuthor());
pstmt.setString(3, b.getPublisher());
pstmt.setString(4, b.getPublisherDate());
pstmt.setInt(5, b.getPrice());
pstmt.setInt(6, b.getBookId());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
}
return result;
}
// 3. 도서 삭제용 메소드
public int deleteBook(Connection conn, int bookId) {
PreparedStatement pstmt = null;
int result = 0;
String sql = "delete from book where book_id = ?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bookId);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
}
return result;
}
// 4. 도서 번호로 도서 조회용 메소드
public Book searchBookId(Connection conn, int bookId) {
String sql = "select * from book where book_id = ?";
PreparedStatement pstmt = null;
ResultSet rset = null;
Book b = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bookId);
rset = pstmt.executeQuery();
if (rset.next()) {
b = new Book(rset.getInt("book_id"),
rset.getString("title"),
rset.getString("author"),
rset.getString("publisher"),
rset.getString("publisher_date"),
rset.getInt("price"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rset);
close(pstmt);
}
return b;
}
// 5. 도서명 조회
public List<Book> searchBookTitle(Connection conn, String bookTitle) {
PreparedStatement pstmt = null;
ResultSet rset = null;
List<Book> bookList = new ArrayList<>();
// like 연산자 사용 시 ? 위치홀더 인식을 위해 '%'와 분리하여 작성하여야 함
String sql = "select * from book where title like '%' || ? || '%'";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, bookTitle);
rset = pstmt.executeQuery();
while(rset.next()) {
Book b = new Book(rset.getInt(1),
rset.getString(2),
rset.getString(3),
rset.getString(4),
rset.getString(5),
rset.getInt(6));
bookList.add(b);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rset);
close(pstmt);
}
return bookList;
}
}3> BookService
package book.model.service;
import static common.JDBCTemplate.*;
import java.sql.Connection;
import java.util.List;
import book.model.dao.BookDao;
import book.model.vo.Book;
/* Service : 1. 컨트롤러로부터 매개변수로 정보를 전달 받는다
* 2. Connection 객체를 생성한다
* 3. 생성한 Connection 객체와 전달 받은 매개변수를 DAO 클래스의 메소드로 전달한다
* 4. 수행 결과에 따른 트랜잭션을 관리한다
* */
public class BookService {
BookDao bd = new BookDao();
// 1. 도서 신규 등록용 메소드
public int insertBook(Book b) {
Connection conn = getConnection();
int result = bd.insertBook(conn, b);
if(result > 0) {
commit(conn);
} else {
rollback(conn);
}
close(conn);
return result;
}
// 6. 도서 전체 목록 조회용 메소드
public List<Book> selectBookList() {
Connection conn = getConnection();
List<Book> bookList = bd.selectBookList(conn);
close(conn);
return bookList;
}
// 2. 도서 정보 수정용 메소드
public int updateBook(Book b) {
Connection conn = getConnection();
int result = bd.updateBook(conn, b);
if(result > 0) {
commit(conn);
} else {
rollback(conn);
}
close(conn);
return result;
}
// 3. 도서 삭제용 메소드
public int deleteBook(int bookId) {
Connection conn = getConnection();
int result = bd.deleteBook(conn, bookId);
if(result > 0){
commit(conn);
}else{
rollback(conn);
}
close(conn);
return result;
}
// 4. 도서 번호로 도서 조회용 메소드
public Book searchBookId(int bookId) {
Connection conn = getConnection();
Book b = bd.searchBookId(conn, bookId);
close(conn);
return b;
}
// 5. 도서명 검색
public List<Book> searchBookTitle(String bookTitle) {
Connection conn = getConnection();
List<Book> bookList = bd.searchBookTitle(conn, bookTitle);
close(conn);
return bookList;
}
}4> Book
package book.model.vo;
public class Book {
private int bookId;
private String title;
private String author;
private String publisher;
private String publisherDate;
private int price;
/*
BOOK_ID NUMBER
TITLE VARCHAR2(50 BYTE)
AUTHOR VARCHAR2(20 BYTE)
PUBLISHER VARCHAR2(20 BYTE)
PUBLISHER_DATE DATE
PRICE NUMBER
*/
public Book() {}
// 매개변수 생성자 (bookId 제외 - DB의 Sequence 객체로 발생 시킬 예정)
public Book(String title, String author, String publisher, String publisherDate, int price) {
super();
this.title = title;
this.author = author;
this.publisher = publisher;
this.publisherDate = publisherDate;
this.price = price;
}
// 매개변수 생성자 (모든 필드)
public Book(int bookId, String title, String author, String publisher, String publisherDate, int price) {
super();
this.bookId = bookId;
this.title = title;
this.author = author;
this.publisher = publisher;
this.publisherDate = publisherDate;
this.price = price;
}
public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getPublisher() {
return publisher;
}
public void setPublisher(String publisher) {
this.publisher = publisher;
}
public String getPublisherDate() {
return publisherDate;
}
public void setPublisherDate(String publisherDate) {
this.publisherDate = publisherDate;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
@Override
public String toString() {
return "Book [bookId=" + bookId + ", title=" + title + ", author=" + author + ", publisher=" + publisher
+ ", publisherDate=" + publisherDate + ", price=" + price + "]";
}
}5-1> BookMenu
package book.view;
import java.util.Scanner;
import book.controller.BookController;
import book.model.vo.Book;
public class BookMenu {
Scanner sc = new Scanner(System.in);
BookController bc = new BookController();
public void displayMenu() {
while(true) {
System.out.println("*** 도서 관리 프로그램 ***");
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("9. 끝내기");
System.out.print("번호 선택 : ");
int no = Integer.parseInt(sc.nextLine());
switch(no) {
case 1 :
bc.insertBook(inputBook());
break;
case 2 :
bc.updateBook(inputBookId(), inputBook());
break;
case 3 :
bc.deleteBook(inputBookId());
break;
case 4 :
// 한 권의 책 조회이므로 메소드 리턴 타입은 Book으로 처리
bc.searchBookId(inputBookId());
break;
case 5 :
bc.searchBookTitle(inputBookTitle());
break;
case 6 :
bc.selectBookList();
break;
case 9 :
System.out.println("프로그램을 종료합니다.");
return;
default :
System.out.println("번호를 잘못 누르셨습니다.");
}
}
}
// 도서 정보 입력용 메소드
public Book inputBook() {
System.out.print("도서명 : ");
String title = sc.nextLine();
System.out.print("저자명 : ");
String author = sc.nextLine();
System.out.print("출판사 : ");
String publisher = sc.nextLine();
System.out.print("출판일(19990101 형식으로 입력) : ");
String publisherDate = sc.nextLine();
System.out.print("가격 : ");
int price = Integer.parseInt(sc.nextLine());
return new Book(title, author, publisher, publisherDate, price);
}
// 도서 아이디 입력용 메소드
public int inputBookId() {
System.out.print("도서 번호 입력 : ");
return Integer.parseInt(sc.nextLine());
}
// 도서 제목 입력용 메소드
public String inputBookTitle() {
System.out.print("검색할 도서명 입력 :");
return sc.nextLine();
}
}5-2> BookView
package book.view;
import java.util.List;
import book.model.vo.Book;
public class BookView {
// 결과 출력용 메소드
public void displayResult(String msg) {
switch(msg) {
case "insert" :
System.out.println("도서 정보 입력이 완료 되었습니다.");
break;
case "update" :
System.out.println("도서 정보 수정이 완료 되었습니다.");
break;
case "delete" :
System.out.println("도서 정보 삭제가 완료 되었습니다.");
break;
}
}
// 에러 출력용 메소드
public void displayError(String msg) {
switch(msg) {
case "insert" :
System.out.println("도서 정보 입력에 실패하였습니다.");
break;
case "update" :
System.out.println("도서 정보 수정에 실패하였습니다.");
break;
case "delete" :
System.out.println("도서 정보 삭제에 실패하였습니다.");
break;
}
}
// 도서 목록 출력용 메소드
public void displayBookList(List<Book> bookList) {
if(bookList.isEmpty()) {
System.out.println("조회 결과가 없습니다.");
return;
}
System.out.println("*** 도서 목록 출력 ***");
for(Book b : bookList) {
System.out.println(b);
}
}
// 도서 출력용 메소드
public void displayBook(Book b) {
if(b == null) {
System.out.println("조회 결과가 없습니다.");
return;
}
System.out.println("*** 도서 출력 ***");
System.out.println(b);
}
}6> JDBCTemplate
package common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
// JDBC 업무 처리에 있어 공통 된 부분을 static 메소드로 정의하여
// 공유하여 사용할 수 있도록 함
public class JDBCTemplate {
// 1. Connection 객체 생성 메소드
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe",
"student", "student");
// 자동 커밋 처리 되지 않도록 설정
// (어플리케이션 내에서 판별하여 commit/rollback 수행하기 위해)
conn.setAutoCommit(false);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// 2. 트랜잭션 관리 메소드 (commit/rollback)
public static void commit(Connection conn) {
try {
if(conn != null && !conn.isClosed()) {
conn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollback(Connection conn) {
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 3. 사용한 객체 close 메소드
public static void close(Connection conn) {
try {
if(conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// PreparedStatement는 Statement의 후손이므로 부모 타입인 Statement만
// 작성하면 같이 사용 가능 (다형성)
public static void close(Statement stmt) {
try {
if(stmt != null && !stmt.isClosed()) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rset) {
try {
if(rset != null && !rset.isClosed()) {
rset.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}7> Run
package book.run;
import book.view.BookMenu;
public class Run {
public static void main(String[] args) {
BookMenu bm = new BookMenu();
bm.displayMenu();
}
}