Java-CSV-MySQL GUI应用CSV读取及MySQL导入问题求助

Hey there! Let's work through your Java CSV-to-MySQL GUI app challenges together. You've already got the basics down—selecting a CSV file, reading a single row, and displaying it—so let's fix the multi-row reading first, then tackle the MySQL import step by step.

Fixing Multi-Row CSV Reading #

Right now you're only reading one row, which is probably because you're calling the read method once instead of looping. Instead of manual string splitting (which breaks on commas inside quoted fields), I'd recommend using OpenCSV—it handles all the edge cases of CSV formatting automatically.

First, add the OpenCSV dependency if you're using Maven:

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.6</version>
</dependency>

Then update your reading code to loop through all rows and update your GUI display:

// Assume you have a JTable and DefaultTableModel set up for display
DefaultTableModel tableModel = (DefaultTableModel) yourJTable.getModel();
File selectedFile = yourFileChooser.getSelectedFile();

try (CSVReader reader = new CSVReader(new FileReader(selectedFile))) {
    String[] nextRow;
    List<String[]> allCsvRows = new ArrayList<>();
    
    // Loop through every row in the CSV
    while ((nextRow = reader.readNext()) != null) {
        allCsvRows.add(nextRow);
        // Add the row directly to your table model for real-time display
        tableModel.addRow(nextRow);
    }
    
    // Now allCsvRows contains every row from your CSV, including headers
} catch (IOException e) {
    e.printStackTrace();
    // Show a user-friendly error in the GUI
    JOptionPane.showMessageDialog(null, "Failed to read CSV: " + e.getMessage());
}

This will read every row and update your GUI in one go, eliminating the single-row limit.

Implementing CSV Import to MySQL #

Now let's get that data into your MySQL table. We'll use JDBC for database connections, with prepared statements to avoid SQL injection and boost import performance.

Step 1: Add MySQL JDBC Dependency #

Make sure you have the MySQL connector in your project:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

Step 2: Connect to MySQL & Create Table (Optional) #

If you want to auto-create a table based on your CSV headers, use this code. We'll sanitize column names to avoid invalid characters:

// Use the allCsvRows list from the previous step
String[] csvHeaders = allCsvRows.get(0);
String dbUrl = "jdbc:mysql://localhost:3306/your_database_name";
String dbUser = "your_username";
String dbPassword = "your_password";

try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword)) {
    // Build CREATE TABLE SQL
    StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS csv_import (");
    for (int i = 0; i < csvHeaders.length; i++) {
        // Clean up column names (replace non-alphanumeric chars with underscores)
        String cleanColumnName = csvHeaders[i].replaceAll("[^a-zA-Z0-9_]", "_");
        createTableSql.append(cleanColumnName).append(" VARCHAR(255)");
        if (i != csvHeaders.length - 1) createTableSql.append(", ");
    }
    createTableSql.append(")");

    // Execute table creation
    try (Statement stmt = conn.createStatement()) {
        stmt.execute(createTableSql.toString());
    }
} catch (SQLException e) {
    JOptionPane.showMessageDialog(null, "Failed to create table: " + e.getMessage());
    e.printStackTrace();
}

Step 3: Bulk Insert CSV Data #

Use prepared statements with batch inserts to efficiently load all rows into MySQL:

try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword)) {
    // Build INSERT SQL with placeholders
    StringBuilder insertSql = new StringBuilder("INSERT INTO csv_import (");
    for (int i = 0; i < csvHeaders.length; i++) {
        String cleanColumnName = csvHeaders[i].replaceAll("[^a-zA-Z0-9_]", "_");
        insertSql.append(cleanColumnName);
        if (i != csvHeaders.length - 1) insertSql.append(", ");
    }
    // Add placeholder values for each column
    insertSql.append(") VALUES (").append(String.join(", ", Collections.nCopies(csvHeaders.length, "?"))).append(")");

    try (PreparedStatement pstmt = conn.prepareStatement(insertSql.toString())) {
        // Skip the header row (start from index 1)
        for (int rowIndex = 1; rowIndex < allCsvRows.size(); rowIndex++) {
            String[] rowData = allCsvRows.get(rowIndex);
            // Set values for each placeholder
            for (int colIndex = 0; colIndex < rowData.length; colIndex++) {
                pstmt.setString(colIndex + 1, rowData[colIndex]);
            }
            // Add row to batch
            pstmt.addBatch();

            // Execute batch every 1000 rows to avoid memory issues
            if (rowIndex % 1000 == 0) {
                pstmt.executeBatch();
            }
        }
        // Execute any remaining rows
        pstmt.executeBatch();

        JOptionPane.showMessageDialog(null, "Import successful! Added " + (allCsvRows.size() - 1) + " records.");
    }
} catch (SQLException e) {
    JOptionPane.showMessageDialog(null, "Failed to import data: " + e.getMessage());
    e.printStackTrace();
}

Key Tips to Avoid Headaches #

• Resource Management: Always use try-with-resources for streams, readers, and database connections—it auto-closes them to prevent leaks.
• User Feedback: Replace plain e.printStackTrace() with GUI dialogs so your users know exactly what went wrong.
• Encoding: Ensure your CSV file uses the same encoding as your MySQL database (usually UTF-8) to avoid garbled text.
• Data Types: If your CSV has numbers, dates, or other non-string data, you can extend the code to detect and set appropriate SQL data types instead of using VARCHAR for everything.

内容的提问来源于stack exchange，提问作者Fortune Dludla