2017년 지방기능경기대회 2주전 공개과제 [제2과제]

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;
 
public class Initialize {
    static Connection conn = null;
    static String sql = "";
    static String db = "jdbc:mysql://127.0.0.1/";
    static Statement stmt = null;
    String option = "?useSSL=false&characterEncoding=utf8&user=root&password=5071";
    
    public Initialize() {
        try{
            System.out.println("connecting...");
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(db+option);
            System.out.println("connect complete!"); 
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void existsDB () {
        if(sql!="") sql="";
        try {
            stmt = conn.createStatement();
            sql = "DROP DATABASE IF EXISTS `sw3_5071`";
            stmt.executeUpdate(sql);
            
            sql = "CREATE DATABASE `sw3_5071`";
            stmt.executeUpdate(sql);
            
            db = "jdbc:mysql://127.0.0.1/sw3_5071";
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void createTable () {
        if(sql!="") sql="";
        try {
            stmt = conn.createStatement();
            
            //TBL_Customer
            sql = "CREATE TABLE `TBL_Customer` ("
                    + "`cID` varchar(6) NOT NULL,"
                    + "`cPW` varchar(4),"
                    + "`cName` varchar(10),"
                    + "`cHP` varchar(13),"
                    + "PRIMARY KEY(cID));";
            stmt.executeUpdate(sql);
            
            //TBL_Bus
            sql = "CREATE TABLE `TBL_Bus` ("
                    + "`bNumber` varchar(4) NOT NULL,"
                    + "`bDeparture` varchar(5),"
                    + "`bArrival` varchar(5),"
                    + "`bTime` time,"
                    + "`bElapse` varchar(10),"
                    + "`bCount` varchar(1),"
                    + "`bPrice` int(6),"
                    + "PRIMARY KEY(bNumber));";
            stmt.executeUpdate(sql);
            
            //TBL_Ticket
            sql = "CREATE TABLE `TBL_Ticket` ("
                    + "`bDate` date,"
                    + "`bNumber` varchar(4),"
                    + "`bNumber2` varchar(5),"
                    + "`bSeat` int(2),"
                    + "`cID` varchar(6),"
                    + "`bPrice` int(6),"
                    + "`bState` varchar(1));";
            stmt.executeUpdate(sql);
            
        }catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void insertDate (String table, int column_cnt) {
        if(sql!="") sql = "";
        try {
            FileInputStream fi = new FileInputStream("C://Users/김벼ㅇ머/Downloads/20171490141449168A/정보기술/정보기술(제2과제)/"+table+".txt");
            InputStreamReader is = new InputStreamReader(fi,"UTF-8");
            BufferedReader br = new BufferedReader(is);
            
            StringTokenizer st = null;
            String readRow = "";
            int columnHead = 1;
            stmt = conn.createStatement();
            
            while ((readRow = br.readLine())!= null) {
                st = new StringTokenizer(readRow,"    ");
                
                String[] arr = new String[column_cnt];
                int i = 0;
                
                while(st.hasMoreTokens()) {
                    arr[i++] = st.nextToken();
                }
                if(columnHead != 1) {
                    if(table == "TBL_BUS") {
                        sql = "INSERT INTO sw3_5071.TBL_Bus VALUES("
                                + "'"+arr[0]+"','"+arr[1]+"','"+arr[2]+"','"+arr[3]+"','"+arr[4]+"','"+arr[5]+"','"+arr[6]+"');";
                    }else if(table == "TBL_CUSTOMER") {
                        sql = "INSERT INTO sw3_5071.TBL_Customer VALUES("
                                + "'"+arr[0]+"','"+arr[1]+"','"+arr[2]+"','"+arr[3]+"');";
                    }else if(table == "TBL_TICKET") {
                        sql = "INSERT INTO sw3_5071.TBL_Ticket VALUES("
                                + "'"+arr[0]+"','"+arr[1]+"','"+arr[2]+"','"+arr[3]+"','"+arr[4]+"','"+arr[5]+"','"+arr[6]+"');";
                    }
                    
                    
                    stmt.executeUpdate(sql);
                }
                
                columnHead++;
            }
            if(br!=null) br.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void createUser() {
        if(sql!="") sql ="";
        try {
            stmt = conn.createStatement();
            sql = "DROP USER IF EXISTS user@127.0.0.1";
            stmt.executeUpdate(sql);
            
            sql = "CREATE USER user@127.0.0.1";
            stmt.executeUpdate(sql);
            
            sql = "GRANT SELECT,INSERT,DELETE,UPDATE ON sw3_5071.* TO user@127.0.0.1";
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void close() {
        try{if(conn!=null) conn.close();}catch(SQLException e) {e.printStackTrace();}
        try{if(stmt!=null) stmt.close();}catch(SQLException e) {e.printStackTrace();}
        System.out.println("Close Complete!");
    }
    
    public static void main(String[] args) {
        new Initialize();
        existsDB();
        new Initialize();
        createTable();
        insertDate("TBL_BUS",7);
        insertDate("TBL_CUSTOMER",4);
        insertDate("TBL_TICKET",7);
        close();
    }
}

sw3_Setting.jar