bhaia

package csv_read;
import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;
import java.sql.Statement;

public class Csv {
    /*41 features (columns) */
    static int duration = 0, protocol_type =1 ,service =2,flag =3,src_bytes=4,
    dst_bytes =5,land=6, wrong_fragment=7,urgent=8,hot=9,num_failed_logins=10,logged_in=11, num_compromised =12,root_shell=13,su_attempted=14,num_root=15,
    num_file_creations=16,num_shells=17,num_access_files=18,
    num_outbound_cmds=19,is_host_login=20,is_guest_login=21,
    count=22,srv_count=23,serror_rate=24,srv_serror_rate=25,rerror_rate=26,
    srv_rerror_rate=27,same_srv_rate=28,diff_srv_rate=29,srv_diff_host_rate=30,dst_host_count=31,
    dst_host_srv_count=32,dst_host_same_srv_rate=33,
    dst_host_diff_srv_rate=34,  dst_host_same_src_port_rate=35,
    dst_host_srv_diff_host_rate=36,dst_host_serror_rate=37,
    dst_host_srv_serror_rate=38,dst_host_rerror_rate=39,dst_host_srv_rerror_rate=40;

    private static  Connection openConnection(String url, String user, String password) {
        Properties properties = new Properties();
        properties.put("user", user);
        properties.put("password", password);
        properties.put("characterEncoding", "UTF-8");
        properties.put("useUnicode", "true");
        properties.put("useSSL", "false");
        Connection c;
        try {
            c = DriverManager.getConnection(url, properties);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        System.out.println ("Connection established");
        return c;
    }

  public static void main(String args[]) throws FileNotFoundException {

    Connection sqlCon = openConnection("jdbc:mysql://localhost:3306/monster", "user1", "pass");
    Statement stmt;
    try {
        stmt = sqlCon.createStatement(  ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        System.out.println ("statement created.");
        stmt.executeUpdate(
            "CREATE TABLE IF NOT EXISTS kddTable ("
            + "priKey INT NOT NULL AUTO_INCREMENT, "
            + "Duration INTEGER, "
            + "ProtocolType VARCHAR(64), "
            + "Service VARCHAR(64), "
            + "Flag VARCHAR(64), "
            + "SourceBytes INTEGER, "
            + "DestinationBytes INTEGER, "
            + "Land VARCHAR(64), "
            + "WrongFragment INTEGER, "
            + "Urgent INTEGER, "
            + "Hot INTEGER, "
            /*+ "FailedLogins INTEGER,"
            + "LoggedIn VARCHAR(64),"
            + "NumCompromised INTEGER,"
            + "RootShell INTEGER,"
            + "NumSuAttemps INTEGER,"
            + "NumRoots INTEGER,"
            + "NumFileCreation INTEGER,"
            + "NumShells INTEGER,"
            + "NumAccessFiles INTEGER,"
            + "NumOutBoundCmds INTEGER,"
            + "IsHostLogin VARCHAR(64),"
            + "IsGuestLogin VARCHAR(64),"
            + "Count INTEGER,"*/
            +"PRIMARY KEY (priKey))");
        ResultSet result = stmt.executeQuery("SELECT COUNT(*) FROM kddTable");
        result.next();
        int rowCount = result.getInt(1);
        if ( rowCount  == 0 ){
            System.out.println("populating values");
            Scanner scanner = new Scanner(new File("kdd_10_unlabel.csv"));
            while(scanner.hasNext()){
                String x=scanner.nextLine();
                String[] arr=x.split(",");
                stmt.executeUpdate(
                        "INSERT INTO kddTable (Duration, ProtocolType, Service, Flag"
                        + " ,SourceBytes, DestinationBytes,Land,WrongFragment"
                        + ",Urgent, Hot"
                        + ")"
                        + "values ("+ arr[duration]+",'"+arr[protocol_type]+"','"+arr[service]+"','"
                        +arr[flag]+"',"+arr[src_bytes]+","+arr[dst_bytes]+",'"+arr[land]+"',"+arr[wrong_fragment]+","
                        + arr[urgent]+","+arr[hot]
                        +")",
                        Statement.RETURN_GENERATED_KEYS
                    );
            }
        }
        sqlCon.close();
        System.out.println ("conn closed.");

    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("VendorError: " + e.getErrorCode());
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

  }
}