java - 如何将字段插入 mysql 数据库，但如果重复则跳过一两列？

Question

我正在从文件中逐行读取，然后将详细信息插入 mysql 数据库中。但问题是有些行不包含我称为 natted ip 和 port 的字段。我循环遍历该文件然后插入到数据库中。然而问题是，因为并非每一行都包含 natted ip 和端口，所以我需要数据库在插入到它们不出现的表中时跳过这两列，但插入其余的并且不重复。我怎样才能确保这一点？

    public static void readData() throws ClassNotFoundException, SQLException{

        File fileName = new File(FILE);

        try(Scanner input = new Scanner(fileName) ){

            String firstLine, secondLine, thirdLine;
            firstLine = input.nextLine();


            secondLine = input.nextLine();
            String[] secondString = secondLine.split(" ");

            for(String string: secondString){

                timestamp = secondString[0]+" "+secondString[1];  //to be inserted
                timezone =  secondString[2]; //to be inserted
            }


            thirdLine = input.nextLine();
            String[] thirdString = thirdLine.split(":");

            for(String string: thirdString){

                session = thirdString[1].trim();  //to be inserted
            }



            while(input.hasNextLine()){

                String line;
                line = input.nextLine();

                String[] cdr_string = line.split(" ");

                for(String string:cdr_string){
                    type = cdr_string[1].trim(); //to be inserted
                    internalIP_and_port = cdr_string[8];
                    destinationIP_and_port = cdr_string[10].trim();    
                }

                if(internalIP_and_port.contains("[")){
                    String[] splitIPs = internalIP_and_port.split(Pattern.quote("["));

                    for(String string:splitIPs){
                        internalIP_and_port = splitIPs[0].trim();
                        nattedIP_and_port = splitIPs[1].trim();
                        nattedIP_and_port = nattedIP_and_port.substring(0, nattedIP_and_port.length() -1);
                    }

                    String[] splitIP_and_port = nattedIP_and_port.split(":");

                    for(String string:splitIP_and_port){
                        natted_ip = splitIP_and_port[0].trim();  //to be inserted
                        natted_port = splitIP_and_port[1].trim();  //to be inserted
                    }
//                    System.out.println(natted_ip);
                }

                String[] split_internal_IP_and_port = internalIP_and_port.split(":");

                for(String string : split_internal_IP_and_port){
                    internal_ip = split_internal_IP_and_port[0].trim(); //to be inserted
                    internal_port = split_internal_IP_and_port[1].trim();  //to be inserted
                }

                String[] split_destination_IP_and_port = destinationIP_and_port.split(":");

                for(String string : split_destination_IP_and_port){
                    destination_ip = split_destination_IP_and_port[0].trim(); //to be inserted
                    destination_port = split_destination_IP_and_port[1].trim(); //to be inserted
                }

                saveData();

            }

        }catch(IOException ex){
            System.out.println(ex);
        }

        File log_done = new File(FILE_DONE);
        fileName.renameTo(log_done);
        fileName.delete();
    }

    private static void saveData() throws ClassNotFoundException, SQLException{

        Connection con = connect();

        PreparedStatement stmt = con.prepareStatement("INSERT INTO decoder(type, internal_ip, internal_port, natted_ip, natted_port, destination_ip, destination_port, session, timestamp, timezone, filename) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        stmt.setString(1, type);
        stmt.setString(2, internal_ip);
        stmt.setString(3, internal_port);
        stmt.setString(4, natted_ip);
        stmt.setString(5, natted_port);
        stmt.setString(6, destination_ip);
        stmt.setString(7, destination_port);
        stmt.setString(8, session);
        stmt.setString(9, timestamp);
        stmt.setString(10, timezone);
        stmt.setString(11, FILENAME);

        stmt.executeUpdate();

    }
    public static Connection connect() throws ClassNotFoundException, SQLException{

        Class.forName("com.mysql.jdbc.Driver");

        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }```

I'm reading from this file:
 udp  VPN: public --> public  41.72.118.178:56620 --> 103.10.116.19:51130
 https  VPN: public --> public  10.65.19.224:46765[165.56.53.149:15674] --> 31.13.75.36:443
 tcp  VPN: public --> public  35.212.240.3:34533 --> 41.72.96.58:20792
 dns  VPN: public --> public  10.65.179.189:12718 --> 165.56.45.2:53
 https  VPN: public --> public  10.65.145.37:34490[165.56.53.161:38013] --> 185.60.219.9:443
 https  VPN: public --> public  10.65.14.63:43956[165.56.53.178:27552] --> 216.58.223.106:443
 https  VPN: public --> public  10.66.32.44:37573[165.56.53.141:29821] --> 185.60.219.33:443
 udp  VPN: public --> public  172.21.242.243:10188[41.72.123.88:55777] --> 119.3.74.66:10327
 http  VPN: public --> public  172.21.218.82:65167[41.72.121.132:46765] --> 5.45.58.214:80
 https  VPN: public --> public  10.66.118.81:52792[165.56.53.185:46319] --> 104.82.200.59:443
 udp  VPN: public --> public  76.64.76.78:13114 --> 41.72.108.238:34835

I want it to show like this:
+----+----------+----------------+---------------+---------------+------------
| id | type     | internal_ip    | internal_port | natted_ip     | natted_port  
+----+----------+----------------+---------------+---------------+------------
|  1 | udp      | 41.72.118.178  | 56620         | NULL          | NULL        |  
|  2 | https    | 10.65.19.224   | 46765         | 165.56.53.149 | 15674       |  
|  3 | tcp      | 35.212.240.3   | 34533         | NULL      | NULL        |  
|  4 | dns      | 10.65.179.189  | 12718         | NULL      | NULL        |  
|  5 | https    | 10.65.145.37   | 34490         | 165.56.53.161 | 38013       |  
|  6 | https    | 10.65.14.63    | 43956         | 165.56.53.178 | 27552       |  
|  7 | https    | 10.66.32.44    | 37573         | 165.56.53.141 | 29821       |  
|  8 | udp      | 172.21.242.243 | 10188         | 41.72.123.88  | 55777       |  
|  9 | http     | 172.21.218.82  | 65167         | 41.72.121.132 | 46765       |  
| 10 | https    | 10.66.118.81   | 52792         | 165.56.53.185 | NULL        |  
| 11 | udp      | 76.64.76.78    | 13114         | NULL      | NULL        |  
| 12 | https    | 10.66.81.19    | 34736         | 165.56.53.81  | 29021       |  
| 13 | tcp      | 10.66.18.62    | 55976         | 165.56.53.233 | 62585 


And not like this:

+----+----------+----------------+---------------+---------------+------------
| id | type     | internal_ip    | internal_port | natted_ip     | natted_port  
+----+----------+----------------+---------------+---------------+------------
|  1 | udp      | 41.72.118.178  | 56620         | NULL          | NULL        |  
|  2 | https    | 10.65.19.224   | 46765         | 165.56.53.149 | 15674       |  
|  3 | tcp      | 35.212.240.3   | 34533         | 165.56.53.149 | 15674       |  
|  4 | dns      | 10.65.179.189  | 12718         | 165.56.53.149 | 15674       |  
|  5 | https    | 10.65.145.37   | 34490         | 165.56.53.161 | 38013       |  
|  6 | https    | 10.65.14.63    | 43956         | 165.56.53.178 | 27552       |  
|  7 | https    | 10.66.32.44    | 37573         | 165.56.53.141 | 29821       |  
|  8 | udp      | 172.21.242.243 | 10188         | 41.72.123.88  | 55777

Answer 1

您应该在外部初始化 natted_ip 和 natted_port，如下代码所示。因为这个变量保存了以前记录的数据，并且您不会清除它们。对于所有可以为 null 的变量都可能发生这种情况。

natted_ip = null;
natted_port = null;
for(String string:splitIP_and_port){
       natted_ip = splitIP_and_port[0].trim();  
       natted_port = splitIP_and_port[1].trim();  
}