Excel导入校验：把字段、文件重复和数据库冲突分开

上一篇 Excel 导入的评论里，有读者提到：用监听器配合校验注解，字段多了也方便维护。这个建议很好。姓名必填、邮箱格式、长度限制，确实没必要全挤在读取循环里。

但邮箱格式正确，离“可以写库”还差两步：文件里有没有重复，数据库里有没有冲突。

我把联系人导入的小例子拆了一下。读取仍用 POI，字段规则改成 Bean Validation。这里想讲清楚的是校验放在哪一层，换成监听器读取，同样需要作这些判断。

同一张表，错误来自三个地方

假设数据库已经有 used@example.com。收到的表是这样的：

Excel行号	姓名	邮箱	要检查什么
2	空	bad	姓名必填、邮箱格式
3	张三	ONE@example.com	格式正常
4	空	空	本例约定跳过空白行
5	李四	one@example.com	与第3行重复
6	王五	used@example.com	与库内记录重复

第2行看它自己就能判断。第5行必须记得第3行，第6行必须知道数据库的状态。

如果只给 DTO 加上 @NotBlank 和 @Email，后两种情况并不会自动消失。可以写自定义约束访问数据库，但这篇选择把跨行、查库规则放在导入服务里，避免一次字段校验顺手变成一次 SQL。

注解负责字段，行号留在外面

核心对象分成两层：Contact 放字段约束，LocatedContact 保留原始行号。邮箱在对象创建时去除首尾空格并转成小写，再进入文件内去重和写库。

邮箱大小写不敏感，是这个联系人示例的业务约定，不能当作所有系统都适用的标准。

字段校验后，把属性名映射回中文列名，最终都返回同一种结构：

record RowError(int row, String column, String message) {}

POI 的行下标从0开始，所以反馈给用户的行号用 i + 1。第4行虽然跳过了，第5行还是第5行，不能按有效记录数重新编号。

另外，@Email 不表示“必填”，也不验证邮箱能否收信。本例叠加了 @NotBlank 和长度约束。格式判断的具体语义由校验实现决定，可对照 Jakarta Validation 3.0 的 Email 说明。

文件重复和库内重复，分别处理

文件内用集合记住标准化后的邮箱。再次出现时，错误挂在后出现的那一行；如果产品要求两行都标红，可以把集合换成“邮箱→首次行号”的 Map。

查库则收集格式合法的邮箱，一次执行 WHERE email IN (:emails)，再把已有邮箱映射回各行。即使某一行姓名不合格，它的邮箱仍可能同时报告库内重复，便于用户一次修改完。

这次示例得到4条错误，行号是：

ERROR_ROWS=[2, 2, 5, 6]

随后直接返回错误，数据库只剩原来的1条记录。没有“边校验边插入”。

这里限定最多1000个数据行；更大文件要另考虑分批预查、参数数量和错误报告大小，不能无限扩张一个 IN。

预查通过，也要保留唯一约束

再安排一组明确的执行顺序：

1. 本次文件有 new@example.com、race@example.com，预查都不存在。
2. 另一次请求先提交 race@example.com。
3. 本批开始写入：第一行成功执行，第二行碰到唯一约束。

整批写入包在同一个 TransactionTemplate 中，异常离开回调后回滚。最终是：

RACE_FINAL: new=0, total=1

数据库里保留的是另一次请求已经提交的记录，本批第一行没有留下来。

这组实验是在预查和写入之间安排一次已提交写入，验证这个竞争窗口；没有用并发吞吐量包装它。

预查负责给出容易修改的行级提示，唯一约束负责最终拒绝冲突。竞争发生时，本例返回整批级409，不解析数据库异常字符串来猜用户的 Excel 行号。422表示预校验发现了具体错误，409表示写入时发生冲突，这是本文接口选择的错误协议。

自己跑一遍核心规则

环境为 JDK 21、Spring Boot 3.5.5 管理的依赖、Hibernate Validator 8.0.3.Final、Spring Framework 6.2.10、H2 2.3.232。下面给完整的规则实验，直接传入带原始行号的解析结果，省去上传和 Excel 文件构造；三项测试分别检查混合错误、竞争窗口、正常写入。

创建下面3个文件，在 pom.xml 所在目录执行：

mvn test

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>3.5.5</version><relativePath/>
  </parent>
  <groupId>demo</groupId><artifactId>excel-import-lab</artifactId><version>1.0</version>
  <properties><java.version>21</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties>
  <dependencies>
    <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId></dependency>
    <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency>
    <dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><scope>runtime</scope></dependency>
    <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency>
  </dependencies>
</project>

src/main/java/demo/ImportRules.java

package demo;

import java.util.*;
import javax.sql.DataSource;
import jakarta.validation.Validator;
import jakarta.validation.constraints.*;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;

@Service
public class ImportRules {
    public record Contact(
        @NotBlank(message="姓名必填") @Size(max=50, message="姓名不能超过50个字符") String name,
        @NotBlank(message="邮箱必填") @Email(message="邮箱格式不正确")
        @Size(max=254, message="邮箱不能超过254个字符") String email) {
        public Contact {
            name = name == null ? null : name.trim();
            email = email == null ? null : email.trim().toLowerCase(Locale.ROOT);
        }
    }
    public record LocatedContact(int row, Contact contact) {}
    public record RowError(int row, String column, String message) {}
    private final Validator validator;
    private final JdbcTemplate jdbc;
    private final NamedParameterJdbcTemplate named;
    private final TransactionTemplate tx;

    public ImportRules(Validator validator, JdbcTemplate jdbc, DataSource ds) {
        this.validator = validator;
        this.jdbc = jdbc;
        this.named = new NamedParameterJdbcTemplate(jdbc);
        this.tx = new TransactionTemplate(new DataSourceTransactionManager(ds));
    }

    public List<RowError> precheck(List<LocatedContact> rows) {
        var errors = new ArrayList<RowError>();
        var emails = new LinkedHashSet<String>();
        var usable = new ArrayList<LocatedContact>();
        for (var row : rows) {
            var violations = validator.validate(row.contact());
            violations.forEach(v -> errors.add(new RowError(row.row(),
                v.getPropertyPath().toString().equals("name") ? "姓名" : "邮箱",
                v.getMessage())));
            boolean validEmail = violations.stream().noneMatch(v ->
                v.getPropertyPath().toString().equals("email"));
            if (validEmail) {
                if (!emails.add(row.contact().email())) {
                    errors.add(new RowError(row.row(), "邮箱", "邮箱在本文件中重复"));
                }
                usable.add(row);
            }
        }
        // 最多1000行；一次批量预查，不在每行里查库。
        if (!emails.isEmpty()) {
            var existing = new HashSet<>(named.queryForList(
                "SELECT email FROM contacts WHERE email IN (:emails)",
                Map.of("emails", emails), String.class));
            usable.stream().filter(r -> existing.contains(r.contact().email()))
                .forEach(r -> errors.add(new RowError(r.row(), "邮箱", "邮箱已存在")));
        }
        errors.sort(Comparator.comparingInt(RowError::row)
            .thenComparing(RowError::column).thenComparing(RowError::message));
        return errors;
    }

    public void writeAtomically(List<LocatedContact> rows) {
        tx.executeWithoutResult(status -> {
            for (var row : rows) {
                jdbc.update("INSERT INTO contacts(name,email) VALUES (?,?)",
                    row.contact().name(), row.contact().email());
            }
        });
    }
}

src/test/java/demo/RulesTest.java

package demo;

import java.util.*;
import jakarta.validation.*;
import org.junit.jupiter.api.*;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import static org.junit.jupiter.api.Assertions.*;

class RulesTest {
    ValidatorFactory factory;
    JdbcTemplate jdbc;
    ImportRules rules;
    @BeforeEach void setup() {
        var ds = new DriverManagerDataSource("jdbc:h2:mem:" + UUID.randomUUID() + ";DB_CLOSE_DELAY=-1", "sa", "");
        jdbc = new JdbcTemplate(ds);
        jdbc.execute("CREATE TABLE contacts(name VARCHAR(50) NOT NULL,email VARCHAR(254) NOT NULL UNIQUE)");
        factory = Validation.buildDefaultValidatorFactory();
        rules = new ImportRules(factory.getValidator(), jdbc, ds);
    }
    @AfterEach void close() { factory.close(); jdbc.execute("SHUTDOWN"); }
    ImportRules.LocatedContact row(int number, String name, String email) {
        return new ImportRules.LocatedContact(number, new ImportRules.Contact(name, email));
    }
    @Test void allLayers() {
        jdbc.update("INSERT INTO contacts VALUES ('旧用户','used@example.com')");
        // 模拟解析结果：第4行是空白，保留下来的行仍沿用原始行号。
        var rows = List.of(row(2,"","bad"), row(3,"张三","ONE@example.com"),
            row(5,"李四","one@example.com"), row(6,"王五","used@example.com"));
        var errors = rules.precheck(rows);
        assertEquals(List.of(2,2,5,6), errors.stream().map(ImportRules.RowError::row).toList());
        assertEquals(1, jdbc.queryForObject("SELECT COUNT(*) FROM contacts", Integer.class));
        System.out.println("ERROR_ROWS=" + errors.stream().map(ImportRules.RowError::row).toList());
    }
    @Test void precheckCannotReplaceConstraint() {
        var rows = List.of(row(2,"新用户","new@example.com"), row(3,"竞争用户","race@example.com"));
        assertTrue(rules.precheck(rows).isEmpty());
        jdbc.update("INSERT INTO contacts VALUES ('另一个请求','race@example.com')");
        assertThrows(DataIntegrityViolationException.class, () -> rules.writeAtomically(rows));
        assertEquals(0, jdbc.queryForObject("SELECT COUNT(*) FROM contacts WHERE email='new@example.com'", Integer.class));
        assertEquals("另一个请求", jdbc.queryForObject("SELECT name FROM contacts", String.class));
        System.out.println("RACE_FINAL: new=0, total=1");
    }
    @Test void goodRowsAreNormalizedAndWritten() {
        var rows = List.of(row(2," 张三 ","ONE@example.com"));
        assertTrue(rules.precheck(rows).isEmpty()); rules.writeAtomically(rows);
        assertEquals("one@example.com", jdbc.queryForObject("SELECT email FROM contacts", String.class));
    }
}

接回上传接口时，解析器先构造带行号的列表；precheck 有错误就返回，没有错误再调用 writeAtomically，并在事务调用外转换冲突响应。H2 验证的是这组规则和回滚行为，接到实际数据库后还要用相同用例检查字段类型、唯一索引和事务配置。

你们的导入发现一个邮箱已存在时，是整批退回，还是跳过这一行？尤其想听听后来改过一次规则的情况：当时是什么业务需求推动改的？