Not able to insert million records from csv file to MySql database

Question

So I wanted to read per say 100 lines and print it and it should be happening for every 100 lines and I don't know where to insert that code. The CSV file with one million records isn't getting inserted into the DB as only few thousand are getting inserted.

String csvFilePath = "C:\\Student1.csv";
try {
    BufferedReader lineReader = new BufferedReader(new FileReader("C:\\File12\\Student1.csv"));
    CSVParser records = CSVParser.parse(lineReader, CSVFormat.EXCEL.withFirstRecordAsHeader().withIgnoreHeaderCase().withTrim());
    System.out.println(records.size);
    ArrayList<TestSql> students = new ArrayList<TestSql>();
    for (CSVRecord record : records) {
        TestSql testsql = new TestSql();
        testsql.setDate(record.get(0));
        testsql.setName(record.get(1));
        testsql.setGender(record.get(2));

        students.add(testsql);
    }
    PreparedStatement statement = null;
    Connection con = dbconnection();
    String sql = "INSERT INTO test12(DOB, NAME, GENDER) VALUES (?, ?, ?)";
    statement = con.prepareStatement(sql);
    for (TestSql record : students) {
        statement.setString(1, record.getDate());
        statement.setString(2, record.getName());
        statement.setString(3, record.getGender());
        statement.addBatch();
    }
    statement.executeBatch();
    con.commit();
    con.close();

} catch (SQLException ex) {
    ex.printStackTrace();
} catch (FileNotFoundException ex) {
    ex.printStackTrace();
} catch (IOException ex) {
    ex.printStackTrace();
}

public static Connection dbconnection() {
    Connection connection = null;
    try {
        System.out.println( "Hello World!" );
        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/newschema1", "root", "12345");
        System.out.println("connection sucessfull");
        connection.setAutoCommit(false);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

No like that works but million reecords is too much to read. So i wanted to read like 100 or 1000 records from the csv file and print it — user10025328, Feb 19 '21 at 13:13
Why don't you simply use mysql's built-in load data statement for importing csv files? — Shadow, Feb 19 '21 at 14:07
(Jdbc) connection (timeout) can be an issue (on big loads). No stacktrace? — xerx593, Feb 19 '21 at 14:23

Abra · Answer 1 · 2021-02-19T14:21:15.980

If you want to insert records from the CSV file into the database table in batches of 100, then you need a counter. In the below code I use a variable count. Whenever it reaches 100, the code inserts those 100 rows and resets the count variable.

Note: More explanations after the code.

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

public class CsvParse {
    private static final int  LIMIT = 100;

    public static Connection dbConnection() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/newschema1",
                                                            "root",
                                                            "12345");
        connection.setAutoCommit(false);
        return connection;
    }

    public static void main(String[] args) {
        try (BufferedReader lineReader = new BufferedReader(new FileReader("C:\\File12\\Student1.csv"))) {
            CSVParser records = CSVParser.parse(lineReader,
                                                CSVFormat.EXCEL.withFirstRecordAsHeader().withIgnoreHeaderCase().withTrim());
            String sql = "INSERT INTO test12(DOB, NAME, GENDER) VALUES (?, ?, ?)";
            Connection con = dbConnection();
            PreparedStatement statement = con.prepareStatement(sql); 
            int count = 0;
            for (CSVRecord record : records) {
                count++;
                if (count > LIMIT) {
                    count = 1;
                    statement.executeBatch();
                    con.commit();
                    statement.clearBatch();
                }
                statement.setString(1, record.get(0));
                statement.setString(2, record.get(1));
                statement.setString(3, record.get(2));
                statement.addBatch();
            }
            // Insert last batch that may be less than LIMIT.
            statement.executeBatch();
            con.commit();
            con.close();
            records.close();
        }
        catch (IOException | SQLException e) {
            e.printStackTrace();
        }
    }
}

In method dbConnection(), I removed Class.forName() since it is no longer needed. I also changed the exception handling. If the method fails to obtain a database connection then there's not much point in continuing since you won't be able to insert anything into the database and that's the whole point of the program. So catching the SQLException in method dbConnection() and printing the stack trace means that when you try to create a PreparedStatement, you will get a NullPointerExcetion since con will be null.

In method main I use try-with-resources when creating lineReader.

I don't see the reason for class TestSql. You can simply set the PreparedStatement parameters directly from the CSV record.

Since Java 7 there is multi-catch so no need for a separate catch block for each exception when each catch block simply prints the stack trace.

Not able to insert million records from csv file to MySql database

1 Answers1