PreparedStatement 실습(주소록 관리)

[JDBC] PreparedStatement 실습(주소록 관리)

[01]

Eclipse setting
    Project type: Java Project
    Project name: mysql_address

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

1. 테이블 구조

DROP TABLE address;

CREATE TABLE address(
    no       INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20) NOT NULL,
    phone    VARCHAR(14) NOT NULL,
    adate    DATETIME    NOT NULL,  -- 날짜
    etc      VARCHAR(40)     NULL -- SOA4, friend, company
);

-- no컬럼은 AUTO_INCREMENT 속성이 선언되어 있음으로 레코드 추가시
-- 컬럼을 명시하지 않아도 자동으로 일련번호가 등록됩니다.
INSERT INTO address(name, phone, adate, etc)
VALUES('가길동', '123-123', now(), 'CBD15');

SELECT no, name, phone, adate, etc FROM address;

 

 

2. 주소록 데이터 한건을 입력받는 클래스를 작성하세요.

>>>>> address/Insert.java

package address;

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 address(name, phone, adate, etc) ");
            sql.append(" VALUES(?, ?, now(), ?)");

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

            //(물음표의 순번, 적용할 값)
            pstmt.setString(1, "김백민");
            pstmt.setString(2, "123-456");
            pstmt.setString(3, "CBD36");
           

            // 레코드 추가후 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. 주소록 데이터 한건을 출력하는 클래스를 작성하세요.

>>>>> address/SelectItem.java

package address;

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 no, name, phone, adate, etc  ");
            sql.append(" FROM address");
            sql.append(" WHERE no =?");

            //쿼리 지정
            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("no  : " + rs.getInt("no"));
                System.out.println("name   : " + rs.getString("name"));
                System.out.println("phone  : " + rs.getString("phone"));
                System.out.println("adate   : " + rs.getString("adate"));
                System.out.println("etc: " + rs.getString("etc"));
                System.out.println("-------------------------------------");
            }
 
        } catch(Exception e) {
            System.out.println("SQLException: " + e.getMessage());
        } finally{
         DBClose.close(con, pstmt, rs);
        }
    }
}

4. 주소록 데이터 목록을 출력하는 클래스를 작성하세요.

>>>>> address/SelectList.java

package address;

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 no, name, phone, adate, etc ");
            sql.append(" FROM address");
            sql.append(" ORDER BY no ASC");

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

            rs = pstmt.executeQuery();

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

5. 주소록 데이터를 수정하는 클래스를 작성하세요.

>>>>> address/Update.java

package address;

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 address");
            sql.append(" SET name='홍길동', phone='369-369',  adate=now(), etc='...' ");
            sql.append("  WHERE no = ?");

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

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

            // 레코드 추가후 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. 주소록 데이터를 삭제하는 클래스를 작성하세요.

>>>>> address/Delete.java

package address;

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 address");
            sql.append(" WHERE no = ?");
           
            pstmt = con.prepareStatement(sql.toString());

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

}