· 10 years ago · Jun 10, 2016, 06:21 PM
1package csv_read;
2import java.io.File;
3import java.io.FileNotFoundException;
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.util.Properties;
9import java.util.Scanner;
10import java.sql.Statement;
11
12public class Csv {
13 /*41 features (columns) */
14 static int duration = 0, protocol_type =1 ,service =2,flag =3,src_bytes=4,
15 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,
16 num_file_creations=16,num_shells=17,num_access_files=18,
17 num_outbound_cmds=19,is_host_login=20,is_guest_login=21,
18 count=22,srv_count=23,serror_rate=24,srv_serror_rate=25,rerror_rate=26,
19 srv_rerror_rate=27,same_srv_rate=28,diff_srv_rate=29,srv_diff_host_rate=30,dst_host_count=31,
20 dst_host_srv_count=32,dst_host_same_srv_rate=33,
21 dst_host_diff_srv_rate=34, dst_host_same_src_port_rate=35,
22 dst_host_srv_diff_host_rate=36,dst_host_serror_rate=37,
23 dst_host_srv_serror_rate=38,dst_host_rerror_rate=39,dst_host_srv_rerror_rate=40;
24
25 private static Connection openConnection(String url, String user, String password) {
26 Properties properties = new Properties();
27 properties.put("user", user);
28 properties.put("password", password);
29 properties.put("characterEncoding", "UTF-8");
30 properties.put("useUnicode", "true");
31 properties.put("useSSL", "false");
32 Connection c;
33 try {
34 c = DriverManager.getConnection(url, properties);
35 } catch (Exception e) {
36 e.printStackTrace();
37 return null;
38 }
39 System.out.println ("Connection established");
40 return c;
41 }
42
43 public static void main(String args[]) throws FileNotFoundException {
44
45 Connection sqlCon = openConnection("jdbc:mysql://localhost:3306/monster", "user1", "pass");
46 Statement stmt;
47 try {
48 stmt = sqlCon.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
49 ResultSet.CONCUR_READ_ONLY);
50 System.out.println ("statement created.");
51 stmt.executeUpdate(
52 "CREATE TABLE IF NOT EXISTS kddTable ("
53 + "priKey INT NOT NULL AUTO_INCREMENT, "
54 + "Duration INTEGER, "
55 + "ProtocolType VARCHAR(64), "
56 + "Service VARCHAR(64), "
57 + "Flag VARCHAR(64), "
58 + "SourceBytes INTEGER, "
59 + "DestinationBytes INTEGER, "
60 + "Land VARCHAR(64), "
61 + "WrongFragment INTEGER, "
62 + "Urgent INTEGER, "
63 + "Hot INTEGER, "
64 /*+ "FailedLogins INTEGER,"
65 + "LoggedIn VARCHAR(64),"
66 + "NumCompromised INTEGER,"
67 + "RootShell INTEGER,"
68 + "NumSuAttemps INTEGER,"
69 + "NumRoots INTEGER,"
70 + "NumFileCreation INTEGER,"
71 + "NumShells INTEGER,"
72 + "NumAccessFiles INTEGER,"
73 + "NumOutBoundCmds INTEGER,"
74 + "IsHostLogin VARCHAR(64),"
75 + "IsGuestLogin VARCHAR(64),"
76 + "Count INTEGER,"*/
77 +"PRIMARY KEY (priKey))");
78 ResultSet result = stmt.executeQuery("SELECT COUNT(*) FROM kddTable");
79 result.next();
80 int rowCount = result.getInt(1);
81 if ( rowCount == 0 ){
82 System.out.println("populating values");
83 Scanner scanner = new Scanner(new File("kdd_10_unlabel.csv"));
84 while(scanner.hasNext()){
85 String x=scanner.nextLine();
86 String[] arr=x.split(",");
87 stmt.executeUpdate(
88 "INSERT INTO kddTable (Duration, ProtocolType, Service, Flag"
89 + " ,SourceBytes, DestinationBytes,Land,WrongFragment"
90 + ",Urgent, Hot"
91 + ")"
92 + "values ("+ arr[duration]+",'"+arr[protocol_type]+"','"+arr[service]+"','"
93 +arr[flag]+"',"+arr[src_bytes]+","+arr[dst_bytes]+",'"+arr[land]+"',"+arr[wrong_fragment]+","
94 + arr[urgent]+","+arr[hot]
95 +")",
96 Statement.RETURN_GENERATED_KEYS
97 );
98 }
99 }
100 sqlCon.close();
101 System.out.println ("conn closed.");
102
103 } catch (SQLException e) {
104 System.out.println("SQLException: " + e.getMessage());
105 System.out.println("SQLState: " + e.getSQLState());
106 System.out.println("VendorError: " + e.getErrorCode());
107 // TODO Auto-generated catch block
108 e.printStackTrace();
109 }
110
111 }
112}