[지방] 지방기능경기대회 문제 1

package jdbc;
 
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;
 
public class JDBC {
    Connection conn;
    Statement stmt;
    ResultSet rs;
    
    String Driver = "com.mysql.jdbc.Driver";
    String dbUrl = "jdbc:mysql://127.0.0.1/";
    String dbName = "";
    String loginOption = "?characterEncoding=utf8&useSSL=false&user=root&password=5071";
    
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
    //DB Connecting
    public void dbconnect() {
        try {
            System.out.println("DB Connecting....");
            Class.forName(Driver);
            conn = DriverManager.getConnection(dbUrl+dbName+loginOption);
            System.out.println("DB "+dbName+" Connected!\n");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    //SELECT FROM
    public void selectQuery(String select, String from, int columns) throws SQLException {
        String selectSql = "SELECT "+select+" FROM "+from;
        dbconnect();
        
        stmt = conn.createStatement();
        rs = stmt.executeQuery(selectSql);
        
        System.out.println("↓ "+selectSql+" ↓\n");
        
        while(rs.next()) {
            for(int i = 1; i<=columns; i++) {
                System.out.print(rs.getString(i));
                if(i != columns) {
                    System.out.print("\t");
                }
            }
            System.out.println();
        }
        
        System.out.println("\nSELECT COMPLETE!\n");
    }
    
    //SELECT FROM WHERE
    public void selectQuery(String select, String from, int columns, String where) throws SQLException {
        String selectSql = "SELECT "+select+" FROM "+from+" WHERE "+where;
        dbconnect();
        
        stmt = conn.createStatement();
        rs = stmt.executeQuery(selectSql);
        
        System.out.println("↓ "+selectSql+" ↓\n");
        
        while(rs.next()) {
            for(int i = 1; i<=columns; i++) {
                System.out.print(rs.getString(i));
                if(i != columns) {
                    System.out.print("\t");
                }
            }
            System.out.println();
        }
        
        System.out.println("\nSELECT COMPLETE!\n");
    }
    
    //파일 읽어서 INSERT하는 메서드
    public void file_read(String table_name, int column_cnt) throws Exception {
          
          
          try {
           FileInputStream input = new FileInputStream("C://" + table_name + ".txt");
           InputStreamReader reader = new InputStreamReader(input, "UTF-8");
           BufferedReader in = new BufferedReader(reader);
           // 한글 깨짐 현상 해결
           StringTokenizer st = null;
 
 
           String b = "";
           int line = 1;
 
           while ((b = in.readLine()) != null) {
 
            // Stringtokenizer를 이용해 탭으로 토큰으로 분리
            st = new StringTokenizer(b, "    ");
            String str[] = new String[column_cnt];
            int i = 0;
            
            
            // 각 분리된 토큰을 배열에 넣음
            while (st.hasMoreTokens()) {
             str[i++] = st.nextToken();
            }
            
            if (line != 1) {
 
             // DB에 insert
             String squery = "";
             
             if (table_name.equals("admin"))
              squery = "insert into company_5071.admin values('" + str[0] + "','" + str[1] + "','" + str[2] + "','" + str[3]
                + "','" + str[4] + "')";
             else if (table_name.equals("customer"))
              squery = "insert into company_5071.customer values('" + str[0] + "','" + str[1] + "','" + str[2] + "','" + str[3]
                + "','" + str[4] + "','" + str[5] + "')";
             else
              squery = "insert into company_5071.contract values('" + str[0] + "','" + str[1] + "','" + str[2] + "','" + str[3]
                + "','" + str[4] + "','" + str[5] + "')";
 
             System.out.println(squery);
             
             executeUpdate(squery);
            }
            
            line++;
 
           }
           
           if(in!=null) try{in.close();} catch(Exception e) {}
           
          } catch (Exception e) {
           e.printStackTrace();
          }
 
         }
    
    //sql 쿼리 삽입 메서드
    public void executeUpdate (String sql) throws SQLException {
        stmt.executeUpdate(sql);
    }
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public static void main(String[] args) {
        System.out.println("=============================");
        System.out.println("JDBC START");
        System.out.println("=============================");
        
        JDBC jdbc = new JDBC();
        jdbc.dbconnect();
        
        try {
            jdbc.stmt = jdbc.conn.createStatement();
            
            //기존에 만들어진 DB 및 테이블 삭제
            System.out.println("DROPING EXISTS DATABASES....");
            String sql = "DROP DATABASE IF EXISTS `company_5071`;";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("DROP EXISTS DATABASES COMPLETE!!\n");
            
            //DB `company_선수번호` 가 생성되게 하시오. (UTF8 형식)
            System.out.println("CREATING DATABASE.....");
            sql = "CREATE DATABASE `company_5071` DEFAULT CHARACTER SET UTF8;";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("CREATE DATABASE COMPLETE!\n");
            
            jdbc.dbName = "company_5071";
            if(jdbc.conn.isClosed()!=true) try{jdbc.conn.close();} catch(SQLException e) {}
            jdbc.dbconnect();
            jdbc.stmt = jdbc.conn.createStatement();
            
            //admin 테이블 생성
            System.out.println("`admin` TABLE CREATING....");
            sql = "CREATE TABLE `admin` ("
                    + "`name` varchar(20) NOT NULL,"
                    + "`passwd` varchar(20) NOT NULL,"
                    + "`position` varchar(20),"
                    + "`jumin` char(14),"
                    + "`inputDate` date,"
                    + "PRIMARY KEY(`name`,`passwd`));";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("`admin` TABLE CREATE COMPLETE!\n");
            
            //customer 테이블 생성
            System.out.println("`customer` TABLE CREATING....");
            sql = "CREATE TABLE `customer` ("
                    + "`code` char(7) NOT NULL,"
                    + "`name` varchar(20) NOT NULL,"
                    + "`birth` date,"
                    + "`tel` varchar(20),"
                    + "`address` varchar(100),"
                    + "`company` varchar(20),"
                    + "PRIMARY KEY(`code`,`name`));";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("`customer` TABLE CREATE COMPLETE!\n");
            
            //contract 테이블 생성
            System.out.println("`contract` TABLE CREATING....");
            sql = "CREATE TABLE `contract` ("
                    + "`customerCode` char(7) NOT NULL,"
                    + "`contractName` varchar(20) NOT NULL,"
                    + "`regPrice` int,"
                    + "`regDate` date NOT NULL,"
                    + "`monthPrice` int,"
                    + "`adminName` varchar(20) NOT NULL);";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("`contract` TABLE CREATE COMPLETE!\n");
            
            //제공되는 택스트 파일이 각 테이블로 입력될 수 있도록 하시오.
            jdbc.file_read("admin",5);
            jdbc.selectQuery("*","admin",5);
            
            //DB 사용자 아이디를 'user', 패스워드를 '1234'로 생성하시오.
            
            //중복 사용자 제거
            System.out.println("DROPING EXISTS USER ....");
            sql = "DROP USER IF EXISTS user@localhost";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("EXISTS USER DROP COMPLETE!!\n");
            
            //생성
            System.out.println("CREATING USER....");
            sql = "CREATE USER user@localhost IDENTIFIED BY '1234';";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("CREATE USER COMPLETE!!\n");
            
            //user 사용자 권한 부여&적용
            System.out.println("GRANTING TO USER...");
            sql = "GRANT SELECT,INSERT,DELETE,UPDATE ON company_5071.* TO user@localhost;";
            jdbc.stmt.executeUpdate(sql);
            sql = "FLUSH PRIVILEGES;";
            jdbc.stmt.executeUpdate(sql);
            System.out.println("GRANT AND FLUSH COMPLETE!!");
            
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(jdbc.conn != null) try {jdbc.conn.close();} catch(SQLException e) {}
            if(jdbc.stmt != null) try {jdbc.stmt.close();} catch(SQLException e) {}
            if(jdbc.rs != null) try {jdbc.rs.close();} catch(SQLException e) {}
        }
        
    }
}