[지방] 공개과제 문제1 완벽소스

package initialize;
 
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
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 Statement stmt = null;
    
    String driver = "com.mysql.jdbc.Driver";
    static String db = "jdbc:mysql://127.0.0.1/";
    String option = "?useSSL=false&characterEncoding=utf8&user=root&password=5071";
    
    static String sql = "";
    
    public Initialize() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(db+option); 
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        
    }
    
    public static void existDB () {
        if(sql!="") {sql="";}
        try {
            System.out.println("DB 중복 검사 중...");
            stmt = conn.createStatement();
            sql = "DROP DATABASE IF EXISTS company_5071;";
            stmt.executeUpdate(sql);
            System.out.println("company_5071 DB 생성 중...");
            sql = "CREATE DATABASE `company_5071`;";
            stmt.executeUpdate(sql);
            db = "jdbc:mysql://127.0.0.1/company_5071";
            System.out.println("DB 생성 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void createTable () {
        if(sql!="") {sql="";}
        try {
            System.out.println("테이블 생성 중...");
            stmt = conn.createStatement();
            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`));";
            stmt.executeUpdate(sql);
            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`));";
            stmt.executeUpdate(sql);
            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);";
            stmt.executeUpdate(sql);
            System.out.println("테이블 생성 완료!");
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void insertData (String tableName, int columnCount) {
        if(sql!="") {sql="";}
        try {
            FileInputStream fi = new FileInputStream("C://Users/김벼ㅇ머/Desktop/"+tableName+".txt");
            InputStreamReader is = new InputStreamReader(fi, "UTF-8");
            BufferedReader br = new BufferedReader(is);
            StringTokenizer st;
            
            String readRow = "";
            int columnHead = 1;
            
            stmt = conn.createStatement();
            
            while ((readRow=br.readLine())!=null) {
                st = new StringTokenizer(readRow,"    ");
                int i = 0;
                String[] arr = new String[columnCount];
                
                while(st.hasMoreTokens()) {
                    arr[i++] = st.nextToken();
                }
                
                if(columnHead!=1) {
                    if(tableName == "admin") {
                        sql = "INSERT INTO company_5071.admin VALUES ("
                                + "'"+arr[0]+"','"+arr[1]+"','"+arr[2]+"','"+arr[3]+"','"+arr[4]+"');";
                    } else if(tableName == "customer") {
                        sql = "INSERT INTO company_5071.customer VALUES ("
                                + "'"+arr[0]+"','"+arr[1]+"','"+arr[2]+"','"+arr[3]+"','"+arr[4]+"','"+arr[5]+"');";
                    } else if(tableName == "contract") {
                        sql = "INSERT INTO company_5071.contract VALUES ("
                                + "'"+arr[0]+"','"+arr[1]+"','"+arr[2]+"','"+arr[3]+"','"+arr[4]+"','"+arr[5]+"');";
                    }
                    stmt.executeUpdate(sql);
                }
                columnHead++;
            }
            
            
            if(br!=null) br.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void createUser () {
        if(sql!="") {sql="";}
        try { 
            System.out.println("아이디 생성 중..");
            stmt = conn.createStatement();
            sql = "DROP USER IF EXISTS user@127.0.0.1";
            stmt.executeUpdate(sql);
            sql = "CREATE USER user@127.0.0.1 IDENTIFIED BY '1234';";
            stmt.executeUpdate(sql);
            sql = "GRANT SELECT,INSERT,DELECT,UPDATE ON company_5071.* TO user@127.0.0.1";
            System.out.println("아이디 생성 완료!..");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void Close() {
        System.out.println("객체 종료 중...");
        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("객체 종료 성공!");
    }
    
    public static void main(String[] args) {
        new Initialize();
        existDB();
        new Initialize();
        createTable();
        insertData("admin",5);
        insertData("customer",6);
        insertData("contract",6);
        createUser();
        Close();
    }
}