package databaseLab;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.*;
import java.util.Properties;



public class ImportInDb {
    public static void main(String[] arg) throws ClassNotFoundException{
        Class.forName("com.mysql.jdbc.Driver");
        try {
            Properties p=new Properties();
            p.setProperty("user","root");
            p.setProperty("password","");
            p.setProperty("useUnicode","true");
            p.setProperty("characterEncoding","cp1251");

            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaselab", p);
            PreparedStatement stmt = con.prepareStatement("INSERT INTO databaselab.src (strABCDEF, strFrom, strTo, Amount, Operator, Region) VALUES (?,?,?,?,?,?)");
            delete(stmt, "src");
            File file = new File("Kody_DEF-9kh.txt");
            try {
                BufferedReader bufferedReader = new BufferedReader(new InputStreamReader (new FileInputStream(file),Charset.forName("utf8")));
                String[] str;
                String line;
                int count = 0;
                try {
                    while((line = bufferedReader.readLine())!=null){
                        str = line.split(";");
                        //if(isInt(str[3])){
                        if(str.length>1){
                          stmt.setString(1, str[0].trim());
                          stmt.setString(2, str[1].trim());
                          stmt.setString(3, str[2].trim());
                          stmt.setInt(4, Integer.parseInt(str[3].trim()));
                          stmt.setString(5, str[4].trim());
                          stmt.setString(6, str[5].trim());
                          stmt.addBatch();
                          count++;
                          if(count>200){
                              stmt.executeBatch();
                              stmt.clearBatch();
                              count = 0;
                              System.out.println("Я сделяль");
                          }

                        }
                        stmt.executeBatch();
                        stmt.clearBatch();

                       // }


                    }

                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            ResultSet res = stmt.executeQuery("SELECT DISTINCT Region from src;");
            stmt = con.prepareStatement("insert into regions (strName) VALUES (?);");
            while(res.next()){
                stmt.setString(1, res.getString(1));
                stmt.addBatch();
            }
            stmt.executeBatch();
            stmt.clearBatch();
            res = stmt.executeQuery("SELECT DISTINCT Operator from src;");
            stmt = con.prepareStatement("insert into operators (strName) VALUES (?);");
            while(res.next()){
                stmt.setString(1, res.getString(1));
                stmt.addBatch();
            }
            stmt.executeBatch();
            stmt.clearBatch();
            res.close();
            stmt.close();
            con.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


    }
    public static void delete(Statement stmt, String strName){
        try {
           stmt.executeUpdate("delete from "+strName+";");
           stmt.executeUpdate("ALTER TABLE src AUTO_INCREMENT=0;");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static Boolean isInt(String str){
        try{
            Integer.parseInt(str);
            return true;
        }
        catch(Exception e){
            return false;
        }
    }
}