Bulk-import staff from a CSV

Java · Spring Boot · intermediate · modification

HR currently types new hires into the admin console one by one. This adds a bulk import: `StaffImportController` accepts the CSV upload, `StaffCsvParser` reads the header, finds the `email`, `full_name` and `department` columns by name and validates every row up front (field count, blanks, duplicate emails within the file), and `StaffImportService` creates the new staff members in one transaction, skipping emails that already exist with a single `IN` query added to `StaffMemberRepository`. Any problem with the file comes back as a 400 problem detail that HR can act on, and nothing is saved in that case.

HR admin backend on Spring Boot 3.3 (Java 21), Spring Data JPA (Hibernate 6) on PostgreSQL, Spring Security. `/api/admin/**` already requires `ROLE_HR_ADMIN` (existing `SecurityConfig`, unchanged), and the multipart limits in `application.yml` (5 MB) cover the largest file. Existing and unchanged: the `StaffMember` entity (`id`, `email` with a unique index and always stored lowercased, `fullName`, `department`). Imports are rare and run by one HR admin at a time; a unique-index violation from a concurrent write is mapped to 409 by the existing global exception handler.

Requirements

• `POST /api/admin/staff/import` takes a multipart upload (part `file`) of a staff CSV and creates a staff member for every data row whose email is not on file yet. It answers 200 with `{"created": n, "skipped": m}`, where `skipped` counts rows whose email already belongs to a staff member. A file with a header row and no data rows imports nothing (200, both counts 0).
• The file is UTF-8 text whose first line is a header row. Columns are matched by name (case-insensitive, surrounding spaces ignored), so they may come in any order and extra columns are ignored; `email`, `full_name` and `department` are required. The template HR downloads lists exactly those three, in that order.
• HR fills the template in Excel and saves it as "CSV UTF-8 (Comma delimited)", which writes a byte-order mark (BOM, U+FEFF) at the very start of the file. A file with a BOM must import exactly like the same file without one.
• The whole import is rejected with 400 and a problem detail that names the problem (with the line number where there is one), and nothing is saved, when: the file is empty, a required column is missing, a data row has a different number of fields than the header, a required value is blank, or an email appears twice in the file. Blank lines are skipped, LF and CRLF line endings are both accepted, and emails are compared and stored lowercased.
• Input contract: the values come from the HR system's export, so no field contains a comma, a double quote or a line break (quoting is not supported), header names are unique, emails are valid ASCII addresses, every value is at most 200 characters, and a file has at most 2,000 data rows.

Files touched

• src/main/java/com/acme/hr/staff/StaffMemberRepository.java
• src/main/java/com/acme/hr/staff/StaffRow.java
• src/main/java/com/acme/hr/staff/ImportResult.java
• src/main/java/com/acme/hr/staff/StaffCsvException.java
• src/main/java/com/acme/hr/staff/StaffCsvParser.java
• src/main/java/com/acme/hr/staff/StaffImportService.java
• src/main/java/com/acme/hr/staff/StaffImportController.java

--- src/main/java/com/acme/hr/staff/StaffMemberRepository.java
 
 import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.query.Param;
 
+import java.util.Collection;
+import java.util.List;
 import java.util.Optional;
 
 
     Optional<StaffMember> findByEmail(String email);
+
+    @Query("select s.email from StaffMember s where s.email in :emails")
+    List<String> findExistingEmails(@Param("emails") Collection<String> emails);
 }
 
--- src/main/java/com/acme/hr/staff/StaffRow.java
+package com.acme.hr.staff;
+
+public record StaffRow(String email, String fullName, String department) {
+}
+
--- src/main/java/com/acme/hr/staff/ImportResult.java
+package com.acme.hr.staff;
+
+public record ImportResult(int created, int skipped) {
+}
+

Review this PR

Java practice