Spring Boot JPA调用MySQL LOAD DATA实现CSV批量入库方法咨询

Hey there! As a fellow Spring Boot developer, I get how handy MySQL's LOAD DATA INFILE is for bulk CSV imports—super efficient compared to looping through each row with JPA. Let me break down exactly how to call this command via Spring Boot JPA, plus some key pitfalls to watch out for.

First, a Few Pre-Requisites #

Before diving into code, make sure you have these sorted:

• Enable Local File Loading: If your CSV is on the same machine as your Spring Boot app, you'll need to use LOAD DATA LOCAL INFILE. Add allowLoadLocalInfile=true to your MySQL JDBC URL (more on that later).
• Database Permissions: Your MySQL user must have the FILE privilege to run this command. You can grant it with:

  GRANT FILE ON *.* TO 'your_user'@'localhost';
  

• MySQL Server Config: Some servers disable local file loading by default. Check your my.cnf (Linux) or my.ini (Windows) and set local_infile=ON, then restart MySQL.

Method 1: Use JPA Repository with Native SQL #

The simplest way is to define a native query in your JPA Repository. Here's how:

Step 1: Add the Repository Method

Create a method in your entity repository with @Modifying (since this is a write operation) and nativeQuery=true:

public interface YourEntityRepository extends JpaRepository<YourEntity, Long> {

    @Modifying
    @Query(value = "LOAD DATA LOCAL INFILE :csvPath " +
                   "INTO TABLE your_table_name " +
                   "FIELDS TERMINATED BY ',' " +  // Adjust to your CSV's delimiter
                   "ENCLOSED BY '\"' " +          // Remove if your values aren't wrapped in quotes
                   "LINES TERMINATED BY '\\n' " + // Use '\\r\\n' for Windows line endings
                   "IGNORE 1 ROWS " +             // Skip header row—delete this if no header exists
                   "(column1, column2, column3)", // Match your table's column order exactly
           nativeQuery = true)
    void bulkImportFromCsv(@Param("csvPath") String csvPath);
}

Step 2: Call the Method in a Service

Wrap the call in a transaction (critical for bulk operations) using @Transactional:

@Service
public class ImportService {

    private final YourEntityRepository repository;

    // Constructor injection (preferred over @Autowired)
    public ImportService(YourEntityRepository repository) {
        this.repository = repository;
    }

    @Transactional
    public void importCsvData(String csvFilePath) {
        repository.bulkImportFromCsv(csvFilePath);
    }
}

Step 3: Update Your Database Config

Add the allowLoadLocalInfile=true parameter to your application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/your_db?allowLoadLocalInfile=true&useSSL=false&serverTimezone=UTC
spring.datasource.username=your_db_user
spring.datasource.password=your_db_password

Method 2: Use EntityManager Directly #

If you prefer not to define the query in the repository, you can use EntityManager to execute the native SQL:

@Service
public class ImportService {

    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public void importCsvData(String csvFilePath) {
        String sql = """
            LOAD DATA LOCAL INFILE ? 
            INTO TABLE your_table_name 
            FIELDS TERMINATED BY ',' 
            ENCLOSED BY '\"' 
            LINES TERMINATED BY '\\n' 
            IGNORE 1 ROWS 
            (column1, column2, column3)
        """;

        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, csvFilePath);
        query.executeUpdate();
    }
}

Common Pitfalls to Avoid #

• File Paths: On Windows, use double backslashes (C:\\data\\import.csv) or forward slashes (C:/data/import.csv) to avoid escape character issues.
• Column Mapping: Double-check that the column order in the query matches exactly with your CSV columns—mismatches will lead to incorrect data in your table.
• Character Encoding: If your CSV has special characters (like emojis or non-English text), add CHARACTER SET utf8mb4 to the command:

  LOAD DATA LOCAL INFILE :csvPath CHARACTER SET utf8mb4 INTO TABLE...
  

• Transaction Timeouts: For very large CSV files, adjust your transaction timeout setting to avoid unexpected rollbacks mid-import.

Alternative (If LOAD DATA INFILE Isn't Working) #

If you run into permission or server config roadblocks, Spring Batch is a solid alternative for bulk CSV imports. It handles data validation, transformation, and chunked processing out of the box—though it has a steeper learning curve than using MySQL's native command.

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