Spring BootアプリケーションでExcelファイルからMySQLへデータを一括インポートする機能を実装する方法について説明します。Apache POIとHutoolライブラリを活用し、効率的なデータ読み込みと挿入処理を実現します。

依存関係の設定（pom.xml）

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.3.0</version>
    </dependency>
    <dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>5.8.11</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.9</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

データソース設定（application.properties）

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.example.model

エンティティクラス

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Employee {
    private Long id;
    private String name;
    private String company;
    private String gender;
    private String partner;
    private String address;
}

DAOインターフェースとMapper

@Mapper
public interface EmployeeDao {
    void batchInsert(@Param("employees") List<Employee> employees);
}

対応するMapper XML:

<mapper namespace="com.example.dao.EmployeeDao">
    <insert id="batchInsert">
        INSERT INTO employee_data (name, company, gender, partner, address)
        VALUES
        <foreach collection="employees" item="emp" separator=",">
            (#{emp.name}, #{emp.company}, #{emp.gender}, #{emp.partner}, #{emp.address})
        </foreach>
    </insert>
</mapper>

サービスクラスでのインポート実装

@Service
@Slf4j
public class ExcelImportService {

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Autowired
    private EmployeeDao employeeDao;

    public void importFromExcel(String filePath) {
        // Excel読み込み
        ExcelReader reader = ExcelUtil.getReader(filePath);
        List<Employee> employees = reader.readAll(Employee.class);

        // バッチ処理でDB登録
        try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            EmployeeDao dao = session.getMapper(EmployeeDao.class);
            dao.batchInsert(employees);
            session.commit();
        }
    }

    // 複数スレッドによる並列処理例
    public void parallelImport(String filePath) throws InterruptedException {
        ExcelReader reader = ExcelUtil.getReader(filePath);
        List<Employee> allEmployees = reader.readAll(Employee.class);

        int batchSize = 2000;
        List<List<Employee>> batches = Lists.partition(allEmployees, batchSize);
        ExecutorService executor = Executors.newFixedThreadPool(4);
        CountDownLatch latch = new CountDownLatch(batches.size());

        for (List<Employee> batch : batches) {
            executor.submit(() -> {
                try {
                    employeeDao.batchInsert(batch);
                } finally {
                    latch.countDown();
                }
            });
        }

        latch.await();
        executor.shutdown();
    }
}

テストコード

@SpringBootTest
@MapperScan("com.example.dao")
class ExcelImportTest {

    @Autowired
    private ExcelImportService importService;

    @Test
    void testBatchImport() {
        importService.importFromExcel("C:\\temp\\employees.xlsx");
    }

    @Test
    void testParallelImport() throws InterruptedException {
        importService.parallelImport("C:\\temp\\employees.xlsx");
    }
}

データベーステーブル定義

CREATE TABLE employee_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    company VARCHAR(255),
    gender VARCHAR(10),
    partner VARCHAR(255),
    address VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;