CSV文件迁移至数据库代码优化：如何过滤空单元格并指定初始表名

Hey there! Let's tackle optimizing your CSV-to-PostgreSQL code while adding the two features you mentioned: filtering rows with empty cells and supporting a configurable table name. Here's a detailed breakdown of the improvements and a revised code example:

• Eliminate SQL Injection Risk
  Your current code builds SQL queries by concatenating CSV input directly—this is a critical security vulnerability. Replace string concatenation with PreparedStatement; it handles parameter escaping automatically and is far safer.

• Automatic Resource Management
  Manually closing BufferedReader, Connection, and Statement is error-prone (you might miss closing them if an exception occurs). Use Java's try-with-resources syntax to auto-close these resources once they're no longer needed.

• Batch Insert for Performance
  Executing an INSERT statement for every single row is slow, especially with large CSV files. Use batch inserts (addBatch() + executeBatch()) to group multiple inserts into one database call, drastically improving throughput.

• Fix Hardcoded Values
  Paths, database credentials, and table names are hardcoded right now. Make these configurable (via command-line args, method parameters, or a config file) so your code is reusable across different files/tables.

• Improve Error Handling
  Just printing stack traces doesn't give you context about which row failed. Log the problematic line number or CSV content when an error occurs, so you can debug easily. Also, avoid catching generic Exception—catch specific exceptions like SQLException or IOException for cleaner code.

• Handle CSV Encoding Properly
  FileReader uses your system's default encoding, which might not match your CSV's encoding (e.g., UTF-8). Use InputStreamReader with an explicit encoding to avoid garbled text.

• Filter Empty Cells
  After splitting each CSV line, check if any required fields (like Nr_dzialki or Powierzchnia) are empty or whitespace-only. Skip the row if they are.

• Configurable Table Name
  Pass the table name as a command-line argument or method parameter instead of hardcoding "Dzialki". This makes your code work with any table structure that matches your CSV columns.

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class CsvToDatabase {

    // Make database configs configurable (could also use a properties file)
    private static final String DB_URL = "jdbc:postgresql://johnny.heliohost.org:5432/hostdb_allotments";
    private static final String DB_USER = "hostdb_administrator";
    private static final String DB_PASSWORD = ""; // Fill in your actual password

    public static void main(String[] args) {
        // Validate command-line arguments
        if (args.length != 2) {
            System.out.println("Usage: java CsvToDatabase <csv-file-path> <target-table-name>");
            return;
        }

        String csvPath = args[0];
        String targetTableName = args[1];
        int lineNumber = 0;

        // Use try-with-resources to auto-manage all closable resources
        try (InputStreamReader isr = new InputStreamReader(new FileInputStream(csvPath), "UTF-8");
             BufferedReader br = new BufferedReader(isr);
             Connection connect = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             // Prepare reusable INSERT statement with placeholders
             PreparedStatement pstmt = connect.prepareStatement(
                     String.format("INSERT INTO %s (Nr_dzialki, Powierzchnia) VALUES (?, ?)", targetTableName)
             )) {

            String line;
            while ((line = br.readLine()) != null) {
                lineNumber++;
                String[] arr = line.split(",");

                // Skip row if required fields are empty/whitespace or column count is insufficient
                if (arr.length < 2 || arr[0].trim().isEmpty() || arr[1].trim().isEmpty()) {
                    System.out.printf("Skipping line %d: Missing or empty required fields%n", lineNumber);
                    continue;
                }

                // Set parameter values for the prepared statement
                pstmt.setString(1, arr[0].trim());
                pstmt.setString(2, arr[1].trim());
                pstmt.addBatch(); // Add row to batch

                // Execute batch every 100 rows (adjust based on your database's capacity)
                if (lineNumber % 100 == 0) {
                    pstmt.executeBatch();
                    System.out.printf("Successfully inserted %d rows%n", lineNumber);
                }
            }

            // Execute any remaining rows in the batch
            pstmt.executeBatch();
            System.out.printf("Import complete! Total rows inserted into %s: %d%n", targetTableName, lineNumber);

        } catch (Exception e) {
            System.err.printf("Error processing line %d: %s%n", lineNumber, e.getMessage());
            e.printStackTrace();
        }
    }
}

• Robust CSV Parsing: The split(",") method fails if your CSV has commas inside quoted values (e.g., "Smith, Jane"). For production use, use a dedicated CSV parser like Apache Commons CSV or OpenCSV to handle edge cases.
• Transaction Management: Add connect.setAutoCommit(false) before batch inserts, then connect.commit() after the final batch. If any insert fails, you can roll back the entire transaction with connect.rollback() to maintain data integrity.
• Password Security: Never hardcode passwords in your code. Use environment variables, a secure vault, or encrypted properties files instead.

内容的提问来源于stack exchange，提问作者Adam Przedlacki