[8주차] JDBC 실습Ⅰ

객체지향프로그래밍 17장 JDBC 실습

실습문제 5, 6, 7번 mysql jdbc 연결

import java.io.*;
import java.sql.*;
public class jjj {
    public static void main (String[] args) {
        Connection conn;
        Statement stmt = null;
        try {
        Class.forName("com.mysql.jdbc.Driver");        
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/bookdb", "root","");
        System.out.println("DB 연결 완료");
        stmt = conn.createStatement();
        stmt.executeUpdate("insert into book (title, publisher, author)             
 values('" + new String("파워자바".getBytes(), "ISO-8859-1")+"','"
         + new String("인피니티북스".getBytes(),"ISO-8859-1")+"','" 
        + new String("천인국".getBytes(),"ISO-8859-1") +"');");
        printTable(stmt);
        stmt.executeUpdate("update book set author='" 
        + new String("황기태".getBytes(), "ISO-8859-1")+"' 
    where title='"+ new String("명품자바".getBytes(), "ISO-8859-1") +"'");
        printTable(stmt);
        stmt.executeUpdate("delete from book where author='"
        + new String("황기태".getBytes(), "ISO-8859-1") +"'");
        printTable(stmt);
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC 드라이버 로드 에러");
        } catch (SQLException e) {
            System.out.println("SQL 실행 에러");
        } catch (UnsupportedEncodingException e) {
            System.out.println("지원되지 않는 인코딩 타입");
        }
    }
 
    private static void printTable(Statement stmt) 
            throws SQLException, UnsupportedEncodingException {
        ResultSet srs = stmt.executeQuery("select * from book");
        while (srs.next()) {
        System.out.print("\t|\t" + srs.getInt("id"));
        System.out.print("\t|\t" 
        + new String(srs.getString("title").getBytes("ISO-8859-1")));
        System.out.print("\t|\t" 
        + new String(srs.getString("publisher").getBytes("ISO-8859-1")));
        System.out.println("\t|\t" 
        + new String(srs.getString("ing("i").getBytes("ISO-8859-1")));
        }
    }
}

실습문제 8번 데이터 저장/수정/삭제

import java.io.*;
import java.sql.*;
import java.util.Scanner;
public class jjj2 {
    public static void main (String[] args) {
        Connection conn;
        Statement stmt = null;
        int id,id2;
        String title, publisher, author, title2, publisher2, author2;
        try {
        Class.forName("com.mysql.jdbc.Driver");        
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/bookdb", "root","");
        System.out.println("DB 연결 완료");
        stmt = conn.createStatement();
 
            while(true)
            {
            printTable(stmt); // 시작시 처음 데이터베이스 출력
            System.out.println("1. 추가");
            System.out.println("2. 삭제");
            System.out.println("3. 수정");
            System.out.println("4. 끝내기");
            
            System.out.print("메뉴 선택 >>");
            Scanner s = new Scanner(System.in);
            int x = s.nextInt();
            
            if(x == 1){        // 추가
                System.out.print("id>>");
                id = s.nextInt();
                System.out.print("title>>");
                title = s.next();
                System.out.print("publisher>>");
                publisher = s.next();
                System.out.print("author>>");
                author = s.next();
                
        stmt.executeUpdate("insert into book (id, title, publisher, author) 
 values("+id+",'" + new String(title.getBytes(), "ISO-8859-1")+"','"
        + new String(publisher.getBytes(),"ISO-8859-1")+"','" 
        + new String(author.getBytes(),"ISO-8859-1") +"');");
            }
            else if(x == 2){        // 삭제
                System.out.print("삭제할 레코드의 id>>");
                id = s.nextInt();
            stmt.executeUpdate("delete from book where id="+id+";");
            }
            else if(x == 3){        // 수정
                System.out.print("수정할 속성>>");
                String a = s.next();
                if(a.equals("ID")){
            System.out.print("id의 현재 값>>");    id = s.nextInt();
            System.out.print("id의 새로운 값>>");    id2 = s.nextInt();    
    stmt.executeUpdate("update book set id='"+id2+"' where id='"+id+"'");
                }
                else if(a.equals("title"))
                {
            System.out.print("title의 현재 값>>");    title = s.next();
            System.out.print("title의 새로운 값>>");  title2 = s.next();    
    stmt.executeUpdate("update book set title='" 
    + new String(title2.getBytes(), "ISO-8859-1")+"' 
    where title='"+ new String(title.getBytes(), "ISO-8859-1") +"'");                    
                }
                else if(a.equals("publisher"))
                {
    System.out.print("publisher의 현재 값>>");    publisher = s.next();
    System.out.print("publisher의 새로운 값>>");    publisher2 = s.next();    
    stmt.executeUpdate("update book set publisher='" 
    + new String(publisher2.getBytes(), "ISO-8859-1")+"' 
    where publisher='"+ new String(publisher.getBytes(), "ISO-8859-1") +"'");                    
                }
                else if(a.equals("author"))
                {
    System.out.print("author의 현재 값>>");        author = s.next();    
    System.out.print("author의 새로운 값>>");    author2 = s.next();        
    stmt.executeUpdate("update book set author='" 
    + new String(author2.getBytes(), "ISO-8859-1")+"' 
    where author='"+ new String(author.getBytes(), "ISO-8859-1") +"'");    
                }
            }
            else if(x == 4)        // 끝내기
                System.exit(1);                
            }
        
        
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC 드라이버 로드 에러");
        } catch (SQLException e) {
            System.out.println("SQL 실행 에러");
        } catch (UnsupportedEncodingException e) {
            System.out.println("지원되지 않는 인코딩 타입");
        }
    }
 
    private static void printTable(Statement stmt) 
throws SQLException, UnsupportedEncodingException {
        ResultSet srs = stmt.executeQuery("select * from book");
        System.out.println("**************** table Book *****************");
        while (srs.next()) {
            System.out.print("\t|\t" + srs.getInt("id"));
            System.out.print("\t|\t" 
            + new String(srs.getString("title").getBytes("ISO-8859-1")));
            System.out.print("\t|\t" 
            + new String(srs.getString("publisher").getBytes("ISO-8859-1")));
            System.out.println("\t|\t" 
               + new String(srs.getString("author").getBytes("ISO-8859-1")));
        }
        System.out.println("*********************************************");
    }
}