[9주차] JDBC 실습 Ⅱ

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

실습문제 17-5번 DB 연결 상태 화면과 3개의 레코드 삽입 후 화면

import java.io.*;
import java.sql.*;
 
public class AddRecord {
    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(); 
            int id = 0;
 
    stmt.executeUpdate("insert into book (title, publisher, author) values('" 
    + getMySQLString("엄마를 부탁해") + "','" + getMySQLString("창비") + "','" 
    + getMySQLString("신경숙") + "')");
    stmt.executeUpdate("insert into book (title, publisher, author) values('" 
    + getMySQLString("명품 자바") + "','" + getMySQLString("생능") + "','" 
    + getMySQLString("천인국") + "')"); 
    stmt.executeUpdate("insert into book (title, publisher, author) values('" 
    + getMySQLString("파워 자바") + "','" + getMySQLString("인피니티북스") + "','" 
    + getMySQLString("천인국") + "')");  
 
            printTable(stmt,"select * from book");  // 테이블 출력
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC 드라이버 로드 에러");
        } catch (SQLException e) {
            System.out.println("SQL 실행 에러");
        }
    }
    private static String getMySQLString(String str) {
        String r=null;
        try {
        r = new String(str.getBytes(), "ISO-8859-1");    // SQL형으로 변형
        } catch (UnsupportedEncodingException e) {
            System.out.println("지원되지 않는 인코딩 타입");
        }
        return r;
    }
    private static String getJavaString(String str) {
        String r=null;
        try {
        r = new String(str.getBytes("ISO-8859-1"));    // java형으로 변형
        } catch (UnsupportedEncodingException e) {
            System.out.println("지원되지 않는 인코딩 타입");
        }
        return r;
    }
 
private static void printTable( Statement stmt, String query )
   {
      try
      {
         ResultSet resultSet = stmt.executeQuery( query );
 
         ResultSetMetaData metaData = resultSet.getMetaData();
         int numberOfColumns = metaData.getColumnCount();
 
         for ( int i = 1; i <= numberOfColumns; i++ )
            System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
         System.out.println();
         
         while ( resultSet.next() ) 
         {
            for ( int i = 1; i <= numberOfColumns; i++ )
               System.out.printf( "%-8s\t", getJavaString( resultSet.getString( i ) ));     // Java형문자열로 변환해서 출력
            System.out.println();
         }              
      }
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } 
   } 
}

DB 연결 상태 확인, 3개의 레코드 추가

실습문제 17-8번 도서관리 프로그램 실습

import java.io.*;
// import java.sql.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;
 
public class DBManager {
      
static final String JDBC_DRIVER ="com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost:3306/bookdb";
static final String USERNAME = "root";
static final String PASSWORD = "";
   
static Connection connection; 
static Statement statement; 
static Scanner scanner; 
    
public DBManager()
{
    try {
        Class.forName( JDBC_DRIVER ); 
        connection = DriverManager.getConnection(
                DATABASE_URL, USERNAME, PASSWORD); 
                     
        statement = connection.createStatement();
           }  
            
    catch (ClassNotFoundException e) {
        System.err.println( "JDBC 드라이버 로드 에러" );
        System.exit( 1 ); 
    } 
    catch (SQLException e) {
        System.err.println( "DB 연결 에러" );
        System.exit( 1 ); 
    } 
 
    scanner = new Scanner( System.in );
 
          int choice = getChoice();
 
          while ( choice != 4 )
          {
             switch( choice )
             {
             case 1 :
                 addBook();
                 break;
             case 2 :
                 deleteBook();
                 break;
             case 3 :
                 updateBook();
                 break;
            default:
                   System.out.printf( "잘못 입력하셨습니다.:  %d\n", choice );
             } 
 
             choice = getChoice();
          } 
} 
 
 
private int getChoice()
{        
      System.out.println( "번호를 선택하세요:" );
      System.out.println( "1 새로운 book 추가" );
      System.out.println( "2 book 삭제" );
      System.out.println( "3 book 수정" );
      System.out.println( "4 끝내기" );
      return scanner.nextInt();
 }
 
private static void addBook() {
try {
    System.out.print("책 이름>>");
    String title = scanner.next(); 
    System.out.print("출판사>>");
    String publisher = scanner.next();
    System.out.print("작가>>");
    String author = scanner.next();
    
    statement.executeUpdate("insert into book (title, publisher, author) values(“
    +"'" + getMySQLString(title) + "','" + getMySQLString(publisher) + "','" 
    + getMySQLString(author) + "')"); // 레코드 추가
    
    printTable("select * from book");  // 테이블 출력
    } catch (SQLException e) {
        System.out.println("레코드 추가 에러");
    }
}
private static void deleteBook() {
try {
    System.out.print("id>>");
    int id =  Integer.parseInt(scanner.next());
        // or  try int id = scanner.nextInt();  
        
    // 한글 변환 문제 없음
    statement.executeUpdate("delete from book where id="+id); // 레코드 삭제
    printTable("select * from book");  // 테이블 출력
    } catch (SQLException e) {
        System.out.println("레코드 삭제 에러");
    }
}
private static void updateBook() {
try {
    System.out.print("수정할 속성 이름>>");
    String colName =  scanner.next();
    System.out.print("수정할 속성의 현재값 >>");
    String oldValue =  scanner.next();
    System.out.print("수정할 속성의 새로운 값 >>");
    String newValue =  scanner.next();
    if (colName.equals("id")) 
        statement.executeUpdate("update book set " + colName + "=“
          + newValue + " where " + colName + "="+ oldValue + ""); //데이터 수정
    else
        statement.executeUpdate("update book set " + colName + "='“
        +getMySQLString(newValue) + "' where " + colName + "='“
        + getMySQLString(oldValue) +"'"); //데이터 수정
    printTable("select * from book");  // 테이블 출력
    } catch (SQLException e) {
        System.out.println("레코드 수정 에러");
    }    
}
 
private static String getMySQLString(String str) {
    String r=null;
    try {
         r = new String(str.getBytes(), "ISO-8859-1");    // SQL형으로 변형
    } catch (UnsupportedEncodingException e) {
        System.out.println("지원되지 않는 인코딩 타입");
    }
    return r;
}
 
private static String getJavaString(String str) {
    String r=null;
    try {
        r = new String(str.getBytes("ISO-8859-1"));    // java형으로 변형
    } catch (UnsupportedEncodingException e) {
        System.out.println("지원되지 않는 인코딩 타입");
    }
    return r;
}
 
private static void printTable( String query )
  {
     try
     {
        ResultSet resultSet = statement.executeQuery( query );
        
        ResultSetMetaData metaData = resultSet.getMetaData();
        int numberOfColumns = metaData.getColumnCount();
 
        for ( int i = 1; i <= numberOfColumns; i++ )
           System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
        System.out.println();
        
        while ( resultSet.next() ) 
        {
           for ( int i = 1; i <= numberOfColumns; i++ )
              System.out.printf( "%-8s\t", getJavaString( resultSet.getString( i ) ));
           System.out.println();
        } 
     }
     catch ( SQLException sqlException ) 
     {
        sqlException.printStackTrace();
     } 
  } 
 
 public static void main( String args[] ) 
   {
      new DBManager();
   } 
}