How to import a large csv into a mysql with java using prepared statements and batch

Question

here I have a block of code that will insert into a table called Earnings and will use prepared statements for 4 integers for each row of the csv and add them to a batch and execute the batch when all lines are read. Why does this start inserting at line 2009 of the csv file, in other words it seems unstable and will only import a part of the csv to the mysql table and how could I make the batch execution more efficient with 6530 lines of csv? Also how do I handle a prepared statement that doesnt exist, say that there is a possility of there being an int at parameter index 4 of the preparedstatement or could be empty.

statement.executeUpdate("CREATE TABLE Earnings("
                    + "tournament INT references Tournaments( tournament_id ), "
                    + "player INT references Players( player_id ), "
                    + "prize_money INT, "
                    + "position INT, "
                    + "PRIMARY KEY(tournament, player))");
Statement statement = conn.createStatement();
try{
                String insertTableSQL2 = "INSERT INTO Earnings"
                        + "(tournament, player, prize_money, position) VALUES"
                        + "(?,?,?,?)";
                PreparedStatement preparedStatement2 = conn.prepareStatement(insertTableSQL2);
                BufferedReader reader2 = new BufferedReader(new FileReader("earnings.csv"));
                String line = null; //line read from csv
                Scanner scanner = null; //scanned line
                while ((line = reader2.readLine()) != null) {

                    scanner = new Scanner(line);
                    scanner.useDelimiter(",");

                        while (scanner.hasNext()) {
                            String str = scanner.next();
                            preparedStatement2.setInt(1, Integer.parseInt(str));
                            String str2 = scanner.next();
                            preparedStatement2.setInt(2, Integer.parseInt(str2));
                            String str3 = scanner.next();
                            preparedStatement2.setInt(3, Integer.parseInt(str3));
                            String str4 = scanner.next();
                            preparedStatement2.setInt(4, Integer.parseInt(str4));
                            preparedStatement2.addBatch();
                        }


                }
                preparedStatement2.executeBatch();
                preparedStatement2.close();
                scanner.close();
                reader2.close(); //closing CSV reader
            }
            catch(Exception e){
                e.printStackTrace();
            }
            System.out.println("earnings.csv imported");

csv is formatted like:

19367,26,677,1
19367,112,271,2
19372,51,134,1
19382,213,150,1
19382,243,50,2
18446,34,2976,1
18446,111,1082,2
19452,28,135,1

score 1 · Accepted Answer · answered Mar 19 '19 at 04:32

1

Use LOAD DATA INFILE syntax. That will be more faster also no need to write all of these code lines if you use that.

Check here

answered Mar 19 '19 at 04:32

snfrox

124
7

I have tried before and it said The MySQL server is running with the --secure-file-priv option so it cannot execute this statement – ripboissss Mar 19 '19 at 04:37
Try to resolve that problem then you will get more efficient way of doing the same thing. Check this answer https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql – snfrox Mar 19 '19 at 06:30

score 0 · Answer 2 · answered Mar 19 '19 at 05:34

You can use Library com.opencsv.CSVReader as CSV reader/writer. And write simple code that read line by line CSV file and construct the SQL query, then just execute the query.

public void loadCSVfile() {
        try (CSVReader reader = new CSVReader(new FileReader("earnings.csv"), ',');
             Connection connection = DBConnection.getConnection();) {
            String insertQuery = "Insert into Earnings (tournament, player, prize_money, position) values (null,?,?,?)";
            PreparedStatement pstmt = connection.prepareStatement(insertQuery);
            String[] rowData = null;
            int i = 0;
            while ((rowData = reader.readNext()) != null) {
                for (String data : rowData) {
                    pstmt.setString(i++, data);
                    pstmt.addBatch();
                    pstmt.executeBatch();
                }
                i = 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

Or load with MySQL command if it possible:

public void loadCSV()
    {
        try (Connection connection = DBConnection.getConnection()) {
            String sqlQuery = "LOAD DATA LOCAL INFILE '" + "earnings.csv" + "' INTO TABLE Earnings FIELDS TERMINATED BY ','" + " LINES TERMINATED BY '\n' (tournament, player, prize_money, position) ";
            Statement stmt = connection.createStatement();
            stmt.execute(sqlQuery);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

How to import a large csv into a mysql with java using prepared statements and batch

2 Answers2