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