PreparedStatement 실습(도서관리)

PreparedStatement 실습(도서관리)

[01] 도서관리

Eclipse setting
    Project type: Java Project
            name: mysql_book
          ibrary: mysql-connector-java-5.1.18-bin.jar

- 실행
J:
CD J:\CBD36\ws_java\mysql_book\bin
java -classpath %CLASSPATH%;J:\CBD36\lib\java\mysql-connector-java-5.1.18-bin.jar book.Insert

- MySQL Client
  H:
  CD H:\soa9\mysql51\bin
  mysql -u javauser -p1234 javadb

1. 테이블 구조
   - 번호, 사이트 이름, URL 주소, 가입 아이디, 기타 설명

DROP TABLE book;

CREATE TABLE book(
    bookno   INT          NOT NULL PRIMARY KEY AUTO_INCREMENT, -- 도서 번호
    title    VARCHAR(100) NOT NULL, -- 책 이름
    author   VARCHAR(50)  NOT NULL, -- 저자
    price    INT          NOT NULL, -- 가격
    bookdate DATETIME     NOT NULL, -- 구입날짜
    etc      VARCHAR(200)      NULL  -- 기타
);

-- no컬럼은 AUTO_INCREMENT 속성이 선언되어 있음으로 레코드 추가시
-- 컬럼을 명시하지 않아도 자동으로 일련번호가 등록됩니다.
INSERT INTO book(title, author, price, bookdate, etc)
VALUES('쉬운 자바', '개발자', 20000, now(), '필수 서적');

INSERT INTO book(title, author, price, bookdate)
VALUES('쉬운 JSP', '개발자', 22000, now());

SELECT bookno, title, author, price, bookdate
FROM book
ORDER BY bookno ASC;

+--------+-----------+--------+-------+---------------------+
| bookno | title     | author | price | bookdate            |
+--------+-----------+--------+-------+---------------------+
|      1 | 쉬운 자바 | 개발자 | 20000 | 2010-11-08 14:45:16 |
|      2 | 쉬운 JSP  | 개발자 | 22000 | 2010-11-08 14:45:16 |
+--------+-----------+--------+-------+---------------------+

 

2. 도서관리 데이터 한건을 입력받는 클래스를 작성하세요.

>>>>> book/Insert.java
- 도서 번호, 구입 날짜는 입력할 필요가 없음, 자동 생성됨.
- SQL: INSERT INTO book(title, author, price, bookdate, etc)
       VALUES(?, ?, ?, now(), ?)

package book;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Insert {
    public static void main(String args[]) {
        Connection con=null;
        PreparedStatement  pstmt=null;
        //ResultSet rs=null;

       
        try {
         con = DBConnect.getConnection();
         
            StringBuffer sql = new StringBuffer();
            sql.append(" INSERT INTO book(title, author, price, bookdate, etc) ");
            sql.append(" VALUES(?, ?, ?, now(), ?)");

            //쿼리 지정
            pstmt = con.prepareStatement(sql.toString());

            //(물음표의 순번, 적용할 값)
            pstmt.setString(1, "자바의 정석");
            pstmt.setString(2, "남궁성");
            pstmt.setInt(3, 30000);
            pstmt.setString(4, "10회독 목표");
           

            // 레코드 추가후 1을 ret에 저장
            int cnt = pstmt.executeUpdate();

            System.out.println("레코드 " + cnt + "개가 추가 되었습니다.");
           
        } catch(Exception e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally{
         DBClose.close(con, pstmt);
        }
    }

}

 

3. 도서관리 데이터 한건을 출력하는 클래스를 작성하세요.

>>>>> book/SelectItem.java
- SQL: SELECT bookno, title, author, price, bookdate
       FROM book
       WHERE bookno =?;

 

package book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SelectItem {
    public static void main(String args[]) {
        Connection con=null;
        PreparedStatement  pstmt=null;
        ResultSet rs=null;

              
        try {

            //커넥션 연결
            con = DBConnect.getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT bookno, title, author, price, bookdate  ");
            sql.append(" FROM book");
            sql.append(" WHERE bookno =?");

            //쿼리 지정
            pstmt = con.prepareStatement(sql.toString());

            //(물음표의 순번, 적용할 값)
            //문자열 지정 pstmt.setString(....)
            //첫번째 ?표에 값 1을 지정
            pstmt.setInt(1, 1);

            rs = pstmt.executeQuery();

            // 하나의 레코드 출력
            // if (rs.next() == true){
               
            // 여러개의 레코드 출력   
            while(rs.next()){
             System.out.println("bookno  : " + rs.getInt("bookno"));
                System.out.println("title   : " + rs.getString("title"));
                System.out.println("author  : " + rs.getString("author"));
                System.out.println("price   : " + rs.getString("price"));
                System.out.println("bookdate: " + rs.getString("bookdate"));
                System.out.println("-------------------------------------");
            }
 
        } catch(Exception e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally{
         DBClose.close(con, pstmt, rs);
        }
    }
}

4. 도서관리 데이터 목록을 출력하는 클래스를 작성하세요.

>>>>> book/SelectList.java
- SQL: SELECT bookno, title, author, price, bookdate
       FROM book
       ORDER BY bookno ASC;

 

package book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SelectList {
    public static void main(String args[]) {
        Connection con=null;
        PreparedStatement  pstmt=null;
        ResultSet rs=null;

       
        try {
            //커넥션 연결
            con = DBConnect.getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT bookno, title, author, price, bookdate ");
            sql.append(" FROM book");
            sql.append(" ORDER BY bookno ASC");

            //쿼리 지정
            pstmt = con.prepareStatement(sql.toString());

            rs = pstmt.executeQuery();

            // 하나의 레코드 출력
            // if (rs.next() == true){
               
            // 여러개의 레코드 출력   
            while(rs.next()){
             System.out.println("bookno  : " + rs.getInt("bookno"));
                System.out.println("title   : " + rs.getString("title"));
                System.out.println("author  : " + rs.getString("author"));
                System.out.println("price   : " + rs.getString("price"));
                System.out.println("bookdate: " + rs.getString("bookdate"));
                System.out.println("-------------------------------------");
            }
 
        } catch(Exception e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally{
         DBClose.close(con, pstmt, rs);
        }
    }
}

5. 도서관리 데이터를 수정하는 클래스를 작성하세요.
    - SQL
      UPDATE book
      SET title='JAVA', author='author', price=25000, bookdate=now(), etc='...'
      WHERE bookno = 3;

>>>>> book/Update.java

package book;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Update {
    public static void main(String args[]) {
        Connection con=null;
        PreparedStatement  pstmt=null;
        //ResultSet rs=null;

       
        try {
            con = DBConnect.getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append(" UPDATE book");
            sql.append(" SET title='JAVA', author='author', price=25000, bookdate=now(), etc='...' ");
            sql.append("  WHERE bookno = ?");

            //쿼리 지정
            pstmt = con.prepareStatement(sql.toString());

            //(물음표의 순번, 적용할 값)
            pstmt.setInt(1, 3);

            // 레코드 추가후 1을 ret에 저장
            int cnt = pstmt.executeUpdate();

            System.out.println("레코드 " + cnt + "개가 수정 되었습니다.");
           
        } catch(Exception e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally{
         DBClose.close(con, pstmt);
        }
    }

}

6. 도서관리 데이터를 삭제하는 클래스를 작성하세요.
    - SQL
      DELETE FROM book
      WHERE bookno = 3;

>>>>> book/Delete.java

package book;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Delete {
    public static void main(String args[]) {
        Connection con = null;
        PreparedStatement  pstmt = null;

        try {

            con = DBConnect.getConnection();
           
            StringBuffer sql = new StringBuffer();
            sql.append(" DELETE FROM book");
            sql.append(" WHERE bookno = ?");
           
            pstmt = con.prepareStatement(sql.toString());

            pstmt.setInt(1, 3);
           
            int cnt = pstmt.executeUpdate();
           
            System.out.println("레코드 " + cnt + "개가 삭제 되었습니다.");
           
        } catch(Exception e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally{
         DBClose.close(con, pstmt);
            
        }
    }

}