java无法将百万条记录从csv文件插入MySql数据库

Question

所以我想每100行读一次，然后打印出来，每100行应该有一次，我不知道在哪里插入代码。有一百万条记录的CSV文件没有被插入数据库，因为只有几千条被插入

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;
}

Answer 1

如果要将CSV文件中的记录以100个为一批插入数据库表，则需要一个计数器。在下面的代码中，我使用了一个变量count。每当它达到100行时，代码就会插入这100行并重置count变量

注意：代码后面有更多解释

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();
        }
    }
}

在方法dbConnection()中，我删除了Class.forName()，因为它是no longer needed。我还更改了异常处理。如果该方法无法获得数据库连接，那么继续下去就没有多大意义，因为您将无法向数据库中插入任何内容，这就是程序的全部意义。因此，在方法dbConnection()中捕获SQLException并打印堆栈跟踪意味着当您尝试创建PreparedStatement时，您将得到一个NullPointerExcetion，因为con将为空

在方法main中，我在创建lineReader时使用try-with-resources

我看不出上课的原因。您可以直接从CSV记录设置PreparedStatement参数

由于Java 7中存在multi-catch，因此当每个catch块仅打印堆栈跟踪时，不需要为每个异常单独的catch块