1

I have the following sqlTable:

    +-------------------+---------+------+-----+---------+----------------+
    | Field             | Type    | Null | Key | Default | Extra          |
    +-------------------+---------+------+-----+---------+----------------+
    | id                | int(11) | NO   | PRI | NULL    | auto_increment |
    | shape_id          | int(11) | YES  |     | NULL    |                |
    | shape_pt_lat      | double  | YES  |     | NULL    |                |
    | shape_pt_lon      | double  | YES  |     | NULL    |                |
    | shape_pt_sequence | double  | YES  |     | NULL    |                |
    +-------------------+---------+------+-----+---------+----------------+

I need to insert about 5kk rows from a CSV file the procces I do is as following: I parse every row and split it by delimiter "," and insert the data into an object, Every 500 objects I make a batch insert;

I take a time sample every insert and its about 500 objs every 12-15 secs

the parsing method:

    ArrayList<Shapes> shapes = new ArrayList<>();
    int counter = 500;
    try {
        String line;
        BufferedReader in = getBufferedReader();
        line = in.readLine();
        while ((line = in.readLine()) != null) {
            String[] columns = line.split(",");
            Shapes shape = new Shapes();
            for (int i = 0; i < columns.length; i++) {
                switch (i) {
                    case 0:
                        shape.setShape_id(columns[0]);
                        break;
                    case 1:
                        shape.setShape_pt_lat(columns[1]);
                        break;
                    case 2:
                        shape.setShape_pt_lon(columns[2]);
                        break;
                    case 3:
                        shape.setShape_pt_sequence(columns[3]);
                        break;
                }
            }

            shapes.add(shape);
            counter--;

            if(counter == 0) {
                SqlHelper.getSqlHelper().addBatchShapes(shapes);
                counter = 500;
                shapes.clear();
                long t = System.currentTimeMillis();
                System.out.println("Shape update: +500 " + new Time(t) );
            }
        }
        if(!shapes.isEmpty())
            SqlHelper.getSqlHelper().addBatchShapes(shapes);
        SqlHelper.getSqlHelper().closeBatch();
    } catch (IOException e) {
        e.printStackTrace();
    }

the insert method:

    String sql = "INSERT INTO shapes (" + Shapes.DB_COLUMN_SHAPE_ID + ", " + Shapes.DB_COLUMN_SHAPE_PT_LON + ", " + Shapes.DB_COLUMN_SHAPE_PT_LAT + ", " + Shapes.DB_COLUMN_SHAPE_PT_SEQUENCE + ") VALUES (?,?,?,?)";

    try {
        if (connection == null) {
            connection = getConnection();
            batchstatement = connection.prepareStatement(sql);
        }
        for (Shapes shape : shapes) {
            batchstatement.setInt(1, Integer.parseInt(shape.getShape_id()));
            batchstatement.setDouble(2, Double.parseDouble(shape.getShape_pt_lat()));
            batchstatement.setDouble(3, Double.parseDouble(shape.getShape_pt_lon()));
            batchstatement.setDouble(4, Double.parseDouble(shape.getShape_pt_sequence()));
            batchstatement.addBatch();
        }
        batchstatement.executeBatch();
        batchstatement.clearParameters();
        batchstatement.clearBatch();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Is there a faster method to do this ?

P.S the tables includes Hebrew and I get utf8 error when trying to use INFILE

Bogdan Kobylynskyi
  • 1,150
  • 1
  • 12
  • 34

0 Answers0