0

I read lines from an ASCII file based on Number of lines in a file in Java

After I read the lines a created ArryLists from data. I inserted these ArrayLists to MySQL by the following way:

public static void post(ArrayList<String> date1, 
            ArrayList<Integer> cloud_layer_1,
            ArrayList<Integer> cloud_layer_2,
            ArrayList<Integer> cloud_layer_3,
            ArrayList<Integer> cloud_layer_4,
            ArrayList<Integer> cloud_layer_5,
            ArrayList<Integer> LayerC_1, 
            ArrayList<Integer> LayerC_2,
            ArrayList<Integer> LayerC_3,
            ArrayList<Integer> LayerC_4,
            ArrayList<Integer> LayerC_5, 
            ArrayList<Integer> ncb,
            ArrayList<Character> sc,
            ArrayList<Integer> hlbc,
            ArrayList<Integer> hvv,
            ArrayList<Integer> hslbc,
            ArrayList<Integer> hhbc,
            ArrayList<String> iaws,
            ArrayList<Integer> scL,
            ArrayList<Integer> reL,
            ArrayList<Integer> profL,
            ArrayList<Integer> leL,
            ArrayList<String> tempL,
            ArrayList<Integer> wtL,
            ArrayList<Integer> tiltL,
            ArrayList<Integer> bclL,
            ArrayList<String> parL,
            ArrayList<Integer> sbacL,
            //List<Double> hx) throws Exception{
            ArrayList<String> twabss) throws Exception{
        try{
            Connection con = getConnection();
            int ii = date1.size();
            String sql = "INSERT INTO ceilo6(time, "
                    + "cloud_1, "
                    + "cloud_2,"
                    + "cloud_3,"
                    + "cloud_4,"
                    + "cloud_5,"
                    + "layer_1,"
                    + "layer_2,"
                    + "layer_3,"
                    + "layer_4,"
                    + "layer_5,"
                    + "ncb,"
                    + "sc,"
                    + "hlbc,"
                    + "hvv,"
                    + "hslbc,"
                    + "hhbc,"
                    + "iaws,"
                    + "scL,"
                    + "reL,"
                    + "profL,"
                    + "leL,"
                    + "tempL,"
                    + "wtL,"
                    + "tiltL,"
                    + "bclL,"
                    + "parL,"
                    + "sbacL,"
                    + "twabss)"

                   + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try(PreparedStatement posted = con.prepareStatement(sql)){
            for(int k =0; k < ii; k++){
            posted.setString(1, date1.get(k));
            posted.setInt(2, cloud_layer_1.get(k));
            posted.setInt(3, cloud_layer_2.get(k));
            posted.setInt(4, cloud_layer_3.get(k));
            posted.setInt(5, cloud_layer_4.get(k));
            posted.setInt(6, cloud_layer_5.get(k));
            posted.setInt(7, LayerC_1.get(k));
            posted.setInt(8, LayerC_2.get(k));
            posted.setInt(9, LayerC_3.get(k));
            posted.setInt(10, LayerC_4.get(k));
            posted.setInt(11, LayerC_5.get(k));
            posted.setInt(12, ncb.get(k));
            posted.setString(13,String.valueOf(sc.get(k)));
            posted.setInt(14, hlbc.get(k));
            posted.setInt(15, hvv.get(k));
            posted.setInt(16, hslbc.get(k));
            posted.setInt(17, hhbc.get(k));
            posted.setString(18, iaws.get(k));
            posted.setInt(19, scL.get(k));
            posted.setInt(20, reL.get(k));
            posted.setInt(21, profL.get(k));
            posted.setInt(22, leL.get(k));
            posted.setString(23, tempL.get(k));
            posted.setInt(24, wtL.get(k));
            posted.setInt(25, tiltL.get(k));
            posted.setInt(26, bclL.get(k));
            posted.setString(27, parL.get(k));
            posted.setInt(28, sbacL.get(k));
            posted.setString(29, twabss.get(k));
            posted.addBatch();       
            posted.executeBatch();

            }
            }catch(Exception e){System.out.println(e);} 
        }catch(Exception e){System.out.println(e);}
        finally {System.out.println("Insert Completed.");}


    }

It inserts data during 4 minutes even though I used Branch. I read that LOAD DATA IFILE would speed up the process but I cannot use it because the original ASCII file contains header and I cannot modify the original file. Can someone suggest me a way to speed up the inserting process?

Beata
  • 335
  • 2
  • 10
  • 21
  • 1
    An interesting approach to have 29 parameters. Most people would've used a single parameter of `List`. – Kayaman May 29 '18 at 07:32
  • 1
    If you add a `con.setAutoCommit(false);` before your loop, and a `con.commit()` after your loop, you would likely get a huge speedup (at least if you are using InnoDB tables) – nos May 29 '18 at 07:33
  • 1
    surely you want to only do `posted.executeBatch();` **after** your loop – Scary Wombat May 29 '18 at 07:35
  • LOAD DATA FILE, you could ignore the Header, if you can't modify the file. `LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;` Ignore 1 LInes skip first header line. IF you have multiple lines header you could change it. – Red Boy May 29 '18 at 07:36
  • 1
    @ScaryWombat or every N rows, but yeah, that's the underlying issue. Batching without really batching. – Kayaman May 29 '18 at 07:37
  • Dear ScaryWombat and nos many thanks for your help! It works. I can insert the data during 20 seconds using with your suggestion. – Beata May 29 '18 at 08:49

0 Answers0