实现三类历史数据原子导入与共同修订批次

yeg

2026-09-06 561ff691ae9b58dc534ebe625d63471d040d3160

实现三类历史数据原子导入与共同修订批次

复用测试行锁和单页校验，三表全量通过后在一个事务内写入，重复跳过、冲突整批拒绝。新增修订类型约束迁移，列表仅查询摘要以支持甲方全量工作簿。覆盖隔离数据库事务和真实 MySQL 样例验证。

 battery-business/src/main/java/com/battery/business/service/ITestConsoleDataService.java

@@ -15,6 +15,6 @@



    /** 在一个事务中写入一次行为、覆盖状态并执行请求的物理删除。 */
    /** 在测试行锁和事务内执行一次修订；ALL 导入共用修订编号作为三 Sheet 批次号。 */
    Long submitAction(TTestDataRevisionAction action, List<DataRevisionItem> overrides);
}

 battery-business/src/main/java/com/battery/business/service/impl/TestConsoleDataServiceImpl.java

@@ -33,7 +33,7 @@
    @Override public List<TTestDataRevisionAction> selectActions(TTestDataRevisionAction query)
    {
        if (query == null) query = new TTestDataRevisionAction();
        if (query.getDataType() != null && !Set.of("REALTIME", "STEP", "CYCLE").contains(query.getDataType())) throw new ServiceException("数据类型无效");
        if (query.getDataType() != null && !Set.of("REALTIME", "STEP", "CYCLE", "ALL").contains(query.getDataType())) throw new ServiceException("数据类型无效");
        if (query.getOperationType() != null && !Set.of("EDIT", "IMPORT", "DELETE").contains(query.getOperationType())) throw new ServiceException("操作类型无效");
        return mapper.selectDataRevisionActions(query);
    }
@@ -50,8 +50,9 @@
    public Long submitAction(TTestDataRevisionAction action, List<DataRevisionItem> items)
    {
        if (action == null || action.getTestId() == null) throw new ServiceException("测试ID不能为空");
        if (action.getDataType() == null || !Set.of("REALTIME", "STEP", "CYCLE").contains(action.getDataType())) throw new ServiceException("数据类型无效");
        if (action.getDataType() == null || !Set.of("REALTIME", "STEP", "CYCLE", "ALL").contains(action.getDataType())) throw new ServiceException("数据类型无效");
        if (action.getOperationType() == null || !Set.of("EDIT", "IMPORT", "DELETE").contains(action.getOperationType())) throw new ServiceException("操作类型无效");
        if ("ALL".equals(action.getDataType()) && !"IMPORT".equals(action.getOperationType())) throw new ServiceException("三 Sheet 仅支持导入");
        if (action.getReason() == null || action.getReason().isBlank()) throw new ServiceException("数据操作原因不能为空");
        if (action.getScopeSummary() == null || action.getScopeSummary().isBlank()) throw new ServiceException("数据操作范围不能为空");
        TTest test = mapper.selectTestByIdForUpdate(action.getTestId());
@@ -64,45 +65,22 @@
        if ("IMPORT".equals(action.getOperationType()))
        {
            if (!"COMPLETED".equals(test.getTestStatus())) throw new ServiceException("测试完成后才可以导入数据");
            List<Map> rows;
            try { rows = JSON.parseArray(action.getChangesSummary(), Map.class); }
            catch (RuntimeException ex) { throw new ServiceException("导入数据格式无效"); }
            if (rows == null || rows.isEmpty()) throw new ServiceException("导入数据不能为空");
            Map<String, Map<String, Object>> identities = new LinkedHashMap<>();
            Map<String, Map<String, Object>> bindings = new LinkedHashMap<>();
            for (Map<String, Object> existing : mapper.selectImportDataRecords(action.getTestId(), action.getDataType()))
            {
                identities.put(importIdentity(existing), existing);
            }
            boolean workbook = "ALL".equals(action.getDataType());
            Map<String, List<Map<String, Object>>> sheets = importSheets(action, workbook);
            List<String> errors = new ArrayList<>();
            List<String> addedIdentities = new ArrayList<>();
            Map<String, Map<String, Object>> bindings = new LinkedHashMap<>();
            Set<String> expectedBatteries = null;
            action.setImportedCount(0); action.setSkippedCount(0);
            for (int index = 0; index < rows.size(); index++)
            for (var sheet : sheets.entrySet())
            {
                Map<String, Object> row = rows.get(index);
                Object sourceRow = row == null ? index + 2 : row.getOrDefault("sourceRow", index + 2);
                try
                {
                    if (row == null) throw new ServiceException("列「记录编号」：数据行不能为空");
                    String batteryNo = importText(row, "batteryNo", "电池号", 100);
                    Map<String, Object> binding = bindings.computeIfAbsent(batteryNo, key -> mapper.selectImportBinding(action.getTestId(), key));
                    Map<String, Object> values = importedRecord(action, row, binding);
                    String identity = importIdentity(values);
                    Map<String, Object> previous = identities.get(identity);
                    if (previous != null)
                    {
                        if (!sameImportContent(values, previous)) throw new ServiceException("列「记录编号」：相同来源身份和编号存在不同内容，冲突拒绝");
                        action.setSkippedCount(action.getSkippedCount() + 1);
                        continue;
                    }
                    identities.put(identity, values);
                    addedIdentities.add(identity);
                    mutations.add(() -> mapper.insertImportedDataRecord(action.getDataType(), values));
                    changes.add(new LinkedHashMap<>(row));
                }
                catch (ServiceException ex) { errors.add("第" + sourceRow + "行，" + ex.getMessage()); }
                Set<String> batteries = prepareImport(action, sheet.getKey(), sheet.getValue(), workbook, bindings,
                        errors, addedIdentities, changes, mutations);
                if (workbook && expectedBatteries != null && !expectedBatteries.equals(batteries))
                    errors.add(sheetLabel(sheet.getKey()) + "工作表，列「电池号」：三个 Sheet 的电池号集合不一致");
                expectedBatteries = batteries;
            }
            // 先完成整页校验，再写入修订与数据；测试行锁同时串行化重复提交和整体删除。
            // 三类全量校验后才执行写入，共享测试行锁、事务和唯一修订编号；失败不留下部分数据或修订。
            if (!errors.isEmpty()) throw new ServiceException(String.join("；", errors));
            action.setImportedCount(changes.size());
            action.setIdentitySummary(JSON.toJSONString(addedIdentities));
@@ -153,6 +131,75 @@
        mutations.forEach(Runnable::run);
        return action.getActionId();
    }

    private Map<String, List<Map<String, Object>>> importSheets(TTestDataRevisionAction action, boolean workbook)
    {
        Map<String, List<Map<String, Object>>> sheets = new LinkedHashMap<>();
        try
        {
            Object parsed = JSON.parse(action.getChangesSummary());
            if (workbook && (!(parsed instanceof Map<?, ?> map) || !map.keySet().equals(Set.of("REALTIME", "STEP", "CYCLE"))))
                throw new ServiceException("三 Sheet 必须包含实时、工步、循环工作表");
            for (String type : workbook ? List.of("REALTIME", "STEP", "CYCLE") : List.of(action.getDataType()))
            {
                Object value = workbook ? ((Map<?, ?>) parsed).get(type) : parsed;
                if (!(value instanceof List<?> rows) || rows.isEmpty()) throw new ServiceException(sheetLabel(type) + "工作表：导入数据不能为空");
                List<Map<String, Object>> records = new ArrayList<>();
                for (Object row : rows)
                {
                    if (row != null && !(row instanceof Map)) throw new ServiceException(sheetLabel(type) + "工作表：数据行格式无效");
                    records.add((Map<String, Object>) row);
                }
                sheets.put(type, records);
            }
        }
        catch (ServiceException ex) { throw ex; }
        catch (RuntimeException ex) { throw new ServiceException("导入数据格式无效"); }
        return sheets;
    }

    private Set<String> prepareImport(TTestDataRevisionAction action, String type, List<Map<String, Object>> rows,
            boolean workbook, Map<String, Map<String, Object>> bindings, List<String> errors,
            List<String> addedIdentities, List<Map<String, Object>> changes, List<Runnable> mutations)
    {
        Map<String, Map<String, Object>> identities = new LinkedHashMap<>();
        for (Map<String, Object> existing : mapper.selectImportDataRecords(action.getTestId(), type))
            identities.put(importIdentity(existing), existing);
        Set<String> batteries = new HashSet<>();
        for (int index = 0; index < rows.size(); index++)
        {
            Map<String, Object> row = rows.get(index);
            Object sourceRow = row == null ? index + 2 : row.getOrDefault("sourceRow", index + 2);
            try
            {
                if (row == null) throw new ServiceException("列「记录编号」：数据行不能为空");
                String batteryNo = importText(row, "batteryNo", "电池号", 100);
                batteries.add(batteryNo);
                Map<String, Object> binding = bindings.computeIfAbsent(batteryNo, key -> mapper.selectImportBinding(action.getTestId(), key));
                Map<String, Object> values = importedRecord(action, type, row, binding);
                String identity = importIdentity(values);
                Map<String, Object> previous = identities.get(identity);
                if (previous != null)
                {
                    if (!sameImportContent(values, previous)) throw new ServiceException("列「记录编号」：相同来源身份和编号存在不同内容，冲突拒绝");
                    action.setSkippedCount(action.getSkippedCount() + 1);
                    continue;
                }
                identities.put(identity, values);
                String revisionIdentity = workbook ? type + ":" + identity : identity;
                addedIdentities.add(revisionIdentity);
                mutations.add(() -> mapper.insertImportedDataRecord(type, values));
                Map<String, Object> change = new LinkedHashMap<>(row);
                if (workbook) { change.put("dataType", type); change.put("identity", revisionIdentity); }
                changes.add(change);
            }
            catch (ServiceException ex) { errors.add((workbook ? sheetLabel(type) + "工作表，" : "") + "第" + sourceRow + "行，" + ex.getMessage()); }
        }
        return batteries;
    }

    private String sheetLabel(String type)
    { return switch (type) { case "REALTIME" -> "实时"; case "STEP" -> "工步"; default -> "循环"; }; }

    private long recordId(String type, String identity)
    {
@@ -207,7 +254,7 @@
        values.put("extension_measurements_json", JSON.toJSONString(extension));
    }

    private Map<String, Object> importedRecord(TTestDataRevisionAction action, Map<String, Object> row, Map<String, Object> binding)
    private Map<String, Object> importedRecord(TTestDataRevisionAction action, String type, Map<String, Object> row, Map<String, Object> binding)
    {
        if (binding == null || List.of("test_battery_id", "binding_id", "adapter_code", "devtype", "devid", "subdevid", "chlid")
                .stream().anyMatch(key -> binding.get(key) == null))
@@ -233,20 +280,20 @@
        values.put("server_received_at_utc", action.getCreatedAtUtc());
        values.put("cycle_no", importInteger(row, "cycleNo", "循环序号", Integer.MAX_VALUE));
        Map<String, Object> metrics = new LinkedHashMap<>();
        columns(action.getDataType()).keySet().forEach(key -> metrics.put(key, importMetric(row, key)));
        values.putAll(editableValues(action.getDataType(), metrics));
        if ("STEP".equals(action.getDataType()))
        columns(type).keySet().forEach(key -> metrics.put(key, importMetric(row, key)));
        values.putAll(editableValues(type, metrics));
        if ("STEP".equals(type))
        {
            Map<String, Object> extension = new TreeMap<>();
            STEP_EXTENSION_FIELDS.forEach(key -> extension.put(key, importMetric(row, key)));
            mergeStepExtension(action.getDataType(), null, extension, values);
            mergeStepExtension(type, null, extension, values);
        }
        if (!"CYCLE".equals(action.getDataType()))
        if (!"CYCLE".equals(type))
        {
            values.put("step_no", importInteger(row, "stepNo", "工步序号", Integer.MAX_VALUE));
            values.put("step_name", importText(row, "stepName", "工步名称", 191));
        }
        if ("REALTIME".equals(action.getDataType()))
        if ("REALTIME".equals(type))
        {
            values.put("cabinet_sequence_id", importInteger(row, "sequenceId", "记录序号", 9007199254740991L));
            for (String field : List.of("devtype", "devid", "subdevid", "chlid")) values.put("cabinet_" + field, binding.get(field));

 battery-business/src/main/resources/mapper/battery/TTestMapper.xml

@@ -192,16 +192,15 @@
        <result property="createdAtUtc" column="created_at_utc"/>
    </resultMap>
    <select id="selectDataRevisionActions" parameterType="com.battery.business.domain.TTestDataRevisionAction" resultMap="TTestDataRevisionActionResultMap">
        <!-- 列表只排序摘要字段；大批次完整 JSON 留给详情查询，避免 MySQL 排序内存耗尽。 -->
        select a.action_id, a.test_id, t.test_name, t.reception_status,
               a.data_type, a.operation_type, a.scope_summary,
               cast(a.identity_summary as char) identity_summary,
               cast(a.changes_summary as char) changes_summary, a.reason,
               cast(a.before_content_json as char) before_content_json, a.operator_id, a.operator_name, a.created_at_utc
               a.data_type, a.operation_type, a.scope_summary, a.reason,
               a.operator_id, a.operator_name, a.created_at_utc
        from t_test_data_revision_action a join t_test t on t.test_id = a.test_id
        <where>
            <if test="testId != null">and a.test_id = #{testId}</if>
            <if test="testName != null and testName != ''">and t.test_name like concat('%', #{testName}, '%')</if>
            <if test="dataType != null and dataType != ''">and a.data_type = #{dataType}</if>
            <if test="dataType != null and dataType != ''">and (a.data_type = #{dataType} or a.data_type = 'ALL')</if>
            <if test="operationType != null and operationType != ''">and a.operation_type = #{operationType}</if>
            <if test="beginCreatedAt != null">and a.created_at_utc &gt;= #{beginCreatedAt}</if>
            <if test="endCreatedAt != null">and a.created_at_utc &lt; #{endCreatedAt}</if>

 ruoyi-admin/src/main/resources/db/migration-final/V9__workbook_import_revision.sql

New file
@@ -0,0 +1,5 @@
-- 三 Sheet 共用一次修订，ALL 仅表示跨三类数据的导入动作，详见 ADR-0051。
ALTER TABLE t_test_data_revision_action DROP CONSTRAINT chk_data_revision_type;
ALTER TABLE t_test_data_revision_action
    ADD CONSTRAINT chk_data_revision_type
        CHECK (data_type IN ('REALTIME', 'STEP', 'CYCLE') OR (data_type = 'ALL' AND operation_type = 'IMPORT'));

 ruoyi-admin/src/test/java/com/battery/business/web/ConsoleSingleSheetImportDatabaseTest.java

@@ -31,12 +31,13 @@
    private TTestController controller;
    private TTestMapper mapper;
    private ITestConsoleDataService service;
    private JdbcTemplate jdbc;

    @BeforeEach void setUp() throws Exception
    {
        var source = new UnpooledDataSource("org.h2.Driver", "jdbc:h2:mem:" + UUID.randomUUID() + ";MODE=MySQL;DATABASE_TO_LOWER=TRUE", "sa", "");
        keepAlive = source.getConnection();
        var jdbc = new JdbcTemplate(source);
        jdbc = new JdbcTemplate(source);
        jdbc.execute("create table sys_user(user_id bigint, nick_name varchar(64), del_flag varchar(1))");
        jdbc.execute("create table t_test_type(test_type_id bigint, test_type_name varchar(64), del_flag int)");
        jdbc.execute("create table t_test(test_id bigint primary key, test_name varchar(100), creator_id bigint, principal_id bigint, test_type_id bigint, test_purpose varchar(100), test_status varchar(20), reception_status varchar(20), monitor_timeout_minutes int, started_at_utc timestamp, reception_boundary_at_utc timestamp, ended_at_utc timestamp, del_flag int default 0, create_by varchar(64), create_time timestamp, update_by varchar(64), update_time timestamp, remark varchar(100), logic_step_mappings_json varchar(100), console_detail_json varchar(100))");
@@ -50,6 +51,9 @@
        jdbc.execute("create table t_detection_cabinet(cabinet_id bigint, adapter_code varchar(64))");
        jdbc.execute("insert into t_detection_cabinet values(1,'adapter-a')");
        jdbc.execute("create table t_test_data_revision_action(action_id bigint auto_increment primary key, test_id bigint, data_type varchar(20), operation_type varchar(20), scope_summary varchar(200), identity_summary json, before_content_json json, changes_summary json, reason varchar(200), operator_id bigint, operator_name varchar(64), created_at_utc timestamp)");
        jdbc.execute("alter table t_test_data_revision_action add constraint chk_data_revision_type check (data_type in ('REALTIME','STEP','CYCLE'))");
        org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(keepAlive,
                new ClassPathResource("db/migration-final/V9__workbook_import_revision.sql"));
        String common = "test_id bigint not null, test_battery_id bigint not null, binding_id bigint not null, adapter_code varchar(64) not null, source_identity varchar(256) not null, source_record_id varchar(128), server_received_at_utc timestamp(3), cycle_no int, content_sha256 binary(32) not null, extension_measurements_json varchar(2000)";
        String metrics = "step_no int, step_name varchar(191), step_type varchar(64), voltage_v decimal(30,12), current_a decimal(30,12), capacity_ah decimal(30,12), energy_wh decimal(30,12) check (energy_wh <> 999)";
        jdbc.execute("create table t_test_realtime_record(record_id bigint auto_increment primary key," + common + "," + metrics + ", cabinet_devtype int, cabinet_devid int, cabinet_subdevid int, cabinet_chlid int, cabinet_sequence_id bigint, cabinet_device_time_utc timestamp(3), cabinet_device_time_raw varchar(64), cabinet_adapter_timezone_id varchar(64), capture_slot_utc timestamp(3) not null, temperature decimal(30,12), positive_potential decimal(30,12), negative_potential decimal(30,12), collector_id bigint, collector_channel_code varchar(100), collector_device_time_utc timestamp(3), unique(test_id,test_battery_id,capture_slot_utc))");
@@ -102,6 +106,102 @@
        assertTrue(service.selectActions(new TTestDataRevisionAction()).isEmpty());
    }

    @Test void workbookImportsThreeSheetsWithOneRevisionAndSkipsRetry()
    {
        var sheets = Map.of("REALTIME", List.of(row(), row()), "STEP", List.of(row()), "CYCLE", List.of(row()));
        var result = submitWorkbook(sheets);
        assertEquals(3, result.get("importedCount"));
        assertEquals(1, result.get("skippedCount"));
        assertNotNull(result.get("actionId"));
        for (String type : List.of("REALTIME", "STEP", "CYCLE"))
        {
            assertEquals(1, mapper.selectImportDataRecords(1L, type).size());
            var query = new TTestDataRevisionAction(); query.setDataType(type);
            assertEquals(1, service.selectActions(query).size());
        }
        var actions = service.selectActions(new TTestDataRevisionAction());
        assertEquals(1, actions.size());
        assertEquals("ALL", actions.get(0).getDataType());
        assertEquals("三页验收", actions.get(0).getReason());
        var retry = submitWorkbook(sheets);
        assertEquals(0, retry.get("importedCount"));
        assertEquals(4, retry.get("skippedCount"));
        assertNull(retry.get("actionId"));
        assertEquals(1, service.selectActions(new TTestDataRevisionAction()).size());
    }

    private com.ruoyi.common.core.domain.AjaxResult submitWorkbook(Map<String, List<Map<String, Object>>> sheets)
    {
        return controller.dataAction(1L, new TTestController.ConsoleDataActionRequest("ALL", "IMPORT", "三 Sheet 导入", "[]",
                JSON.toJSONString(sheets), "三页验收", List.of()));
    }

    @Test void workbookLateDatabaseFailureRollsBackAllSheetsAndRevision()
    {
        var cycle = row(); cycle.put("dischargeEnergy", 999);
        jdbc.execute("alter table t_test_cycle_record add check (discharge_energy_wh <> 999)");
        assertThrows(RuntimeException.class, () -> submitWorkbook(Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(cycle))));
        assertWorkbookEmpty();
    }

    @Test void workbookMissingEmptyInvalidOrConflictingSheetNeverWritesAnySheet()
    {
        var sheets = new LinkedHashMap<String, List<Map<String, Object>>>();
        sheets.put("REALTIME", List.of(row())); sheets.put("STEP", List.of(row()));
        assertThrows(RuntimeException.class, () -> submitWorkbook(sheets));
        assertWorkbookEmpty();
        sheets.put("CYCLE", List.of());
        assertThrows(RuntimeException.class, () -> submitWorkbook(sheets));
        assertWorkbookEmpty();
        var invalid = row(); invalid.put("cycleNo", 0); invalid.put("sourceRow", 23);
        sheets.put("CYCLE", List.of(invalid));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("循环工作表，第23行"));
        assertWorkbookEmpty();
        var conflict = row(); conflict.put("dischargeCapacity", 88);
        sheets.put("CYCLE", List.of(row(), conflict));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("冲突拒绝"));
        assertWorkbookEmpty();
    }

    @Test void workbookRejectsDifferentBatterySetsEvenWhenBothBatteriesBelongToTest()
    {
        jdbc.execute("insert into t_test_battery values(6,1,'BAT-2',0)");
        jdbc.execute("insert into t_test_channel_binding values(7,6,11)");
        var other = row(); other.put("batteryNo", "BAT-2");
        var sheets = Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(other));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("电池号集合不一致"));
        assertWorkbookEmpty();
        other.put("batteryNo", "OTHER");
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("不属于当前测试"));
        assertWorkbookEmpty();
    }

    @Test void workbookConflictWithExistingCyclePreservesFactsAndRejectsOtherSheets()
    {
        submit("CYCLE", List.of(row()));
        var conflict = row(); conflict.put("dischargeCapacity", 88);
        assertThrows(RuntimeException.class, () -> submitWorkbook(Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(conflict))));
        assertTrue(mapper.selectImportDataRecords(1L, "REALTIME").isEmpty());
        assertTrue(mapper.selectImportDataRecords(1L, "STEP").isEmpty());
        assertEquals(1, mapper.selectImportDataRecords(1L, "CYCLE").size());
        assertEquals(1, service.selectActions(new TTestDataRevisionAction()).size());
    }

    @Test void workbookCannotImportBeforeTestCompletionOrWithoutReason()
    {
        var sheets = Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(row()));
        assertThrows(RuntimeException.class, () -> controller.dataAction(1L, new TTestController.ConsoleDataActionRequest("ALL", "IMPORT", "三 Sheet 导入", "[]", JSON.toJSONString(sheets), " ", List.of())));
        jdbc.update("update t_test set test_status='RUNNING', reception_status='RECEIVING' where test_id=1");
        assertThrows(RuntimeException.class, () -> submitWorkbook(sheets));
        assertWorkbookEmpty();
    }

    private void assertWorkbookEmpty()
    {
        for (String type : List.of("REALTIME", "STEP", "CYCLE")) assertTrue(mapper.selectImportDataRecords(1L, type).isEmpty());
        assertTrue(service.selectActions(new TTestDataRevisionAction()).isEmpty());
    }

    @Test void differentRecordsInTheSameSecondPreserveDeviceTime()
    {
        var next = row(); next.put("recordNo", "18"); next.put("sequenceId", 18); next.put("stepNo", 2);

 ruoyi-admin/src/test/java/com/battery/business/web/ConsoleWorkbookImportMysqlTest.java

New file
@@ -0,0 +1,118 @@
package com.battery.business.web;

import static org.junit.jupiter.api.Assertions.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.DriverManager;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import com.alibaba.fastjson2.JSON;
import com.battery.business.domain.TTestDataRevisionAction;
import com.battery.business.mapper.TTestMapper;
import com.battery.business.service.ITestConsoleDataService;
import com.battery.business.service.impl.TestConsoleDataServiceImpl;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledIfEnvironmentVariable;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.aop.framework.ProxyFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.test.util.ReflectionTestUtils;
import org.springframework.transaction.annotation.AnnotationTransactionAttributeSource;
import org.springframework.transaction.interceptor.TransactionInterceptor;

/** 仅在脚本提供的随机隔离库内验证真实 MySQL 迁移、事务和甲方全量样例。 */
@EnabledIfEnvironmentVariable(named = "WORKBOOK_TEST_SCHEMA", matches = "ticket04_import_[a-f0-9]{32}")
class ConsoleWorkbookImportMysqlTest
{
    @Test void fullWorkbookMigrationTransactionRevisionAndWarningIsolation() throws Exception
    {
        String schema = System.getenv("WORKBOOK_TEST_SCHEMA");
        String user = System.getenv("WORKBOOK_TEST_USER"), password = System.getenv("WORKBOOK_TEST_PASSWORD");
        String base = "jdbc:mysql://127.0.0.1:6134/";
        String options = "?useSSL=false&allowPublicKeyRetrieval=true&connectionTimeZone=UTC";
        try (var admin = DriverManager.getConnection(base + options, user, password); var sql = admin.createStatement())
        {
            sql.execute("create database " + schema + " character set utf8mb4 collate utf8mb4_0900_ai_ci");
            try
            {
                String url = base + schema + options;
                var configuration = Flyway.configure().dataSource(url, user, password).locations("classpath:db/migration-final").cleanDisabled(true);
                configuration.target("8").load().migrate();
                var flyway = configuration.target("9").load();
                assertEquals(1, flyway.migrate().migrationsExecuted);
                assertEquals(0, flyway.migrate().migrationsExecuted);
                var source = new UnpooledDataSource("com.mysql.cj.jdbc.Driver", url, user, password);
                var jdbc = new JdbcTemplate(source);
                jdbc.execute("insert into t_test(test_id,test_name,creator_id,principal_id,test_type_id,test_purpose,test_status,reception_status) values(1,'三 Sheet 验收',1,1,1,'隔离测试','COMPLETED','ENDED')");
                jdbc.execute("insert into t_detection_cabinet(cabinet_id,adapter_code,cabinet_code,cabinet_name,vendor_device_type,vendor_device_id) values(1,'adapter-a','CAB1','检测柜',27,1)");
                jdbc.execute("insert into t_detection_channel(channel_id,adapter_code,cabinet_id,devtype,devid,subdevid,chlid) values(11,'adapter-a',1,27,1,1,2)");
                jdbc.execute("insert into t_test_battery(test_battery_id,test_id,group_id,battery_no,collector_id) values(4,1,1,'BAT-1',1)");
                jdbc.execute("insert into t_test_channel_binding(binding_id,test_id,test_battery_id,channel_id) values(5,1,4,11)");
                jdbc.execute("insert into t_test_warning_threshold(test_id,metric_code,max_value,unit,severity) values(1,'TEMPERATURE',1,'C','WARNING')");
                var factory = new SqlSessionFactoryBean(); factory.setDataSource(source);
                factory.setTypeAliasesPackage("com.battery.business.domain");
                factory.setMapperLocations(new ClassPathResource("mapper/battery/TTestMapper.xml"));
                var mapper = new SqlSessionTemplate(factory.getObject()).getMapper(TTestMapper.class);
                var proxy = new ProxyFactory(new TestConsoleDataServiceImpl(mapper));
                proxy.addAdvice(new TransactionInterceptor(new DataSourceTransactionManager(source), new AnnotationTransactionAttributeSource()));
                var service = (ITestConsoleDataService) proxy.getProxy();
                var controller = new TTestController() {
                    @Override public Long getUserId() { return 1L; }
                    @Override public String getUsername() { return "验收操作员"; }
                };
                ReflectionTestUtils.setField(controller, "consoleDataService", service);
                var sample = JSON.parseObject(Files.readString(Path.of(System.getenv("TICKET03_SAMPLE_JSON"))));
                Map<String, List<Map<String, Object>>> sheets = new LinkedHashMap<>();
                for (String type : List.of("REALTIME", "STEP", "CYCLE")) sheets.put(type, (List) sample.getJSONArray(type).toJavaList(Map.class));
                // 在最后一类写入制造数据库错误，确认前两类和修订一起回滚。
                jdbc.execute("alter table t_test_cycle_record add constraint workbook_probe_failure check (cycle_no <> 2)");
                assertThrows(RuntimeException.class, () -> submit(controller, sheets));
                assertTrue(mapper.selectPersistedFinalRealtimeRecords(1L).isEmpty());
                assertTrue(mapper.selectPersistedFinalStepRecords(1L).isEmpty());
                assertTrue(mapper.selectPersistedFinalCycleRecords(1L).isEmpty());
                assertTrue(service.selectActions(new TTestDataRevisionAction()).isEmpty());
                jdbc.execute("alter table t_test_cycle_record drop constraint workbook_probe_failure");
                var imported = submit(controller, sheets);
                assertEquals(7767, imported.get("importedCount"));
                assertEquals(7605, mapper.selectPersistedFinalRealtimeRecords(1L).size());
                assertEquals(129, mapper.selectPersistedFinalStepRecords(1L).size());
                assertEquals(33, mapper.selectPersistedFinalCycleRecords(1L).size());
                var actions = service.selectActions(new TTestDataRevisionAction());
                assertEquals(1, actions.size());
                var revision = service.selectActionById(actions.get(0).getActionId());
                assertEquals(imported.get("actionId"), revision.getActionId());
                assertEquals("三 Sheet 共用原因", revision.getReason());
                var changes = JSON.parseArray(revision.getChangesSummary());
                assertEquals(7767, changes.size());
                assertEquals("REALTIME", changes.getJSONObject(0).getString("dataType"));
                assertEquals("STEP", changes.getJSONObject(7605).getString("dataType"));
                assertEquals("CYCLE", changes.getJSONObject(7734).getString("dataType"));
                for (String type : sheets.keySet()) {
                    var query = new TTestDataRevisionAction(); query.setTestId(1L); query.setDataType(type);
                    assertEquals(1, service.selectActions(query).size());
                }
                var retry = submit(controller, sheets);
                assertEquals(0, retry.get("importedCount")); assertEquals(7767, retry.get("skippedCount")); assertNull(retry.get("actionId"));
                sheets.get("CYCLE").get(32).put("dischargeCapacity", 99);
                assertThrows(RuntimeException.class, () -> submit(controller, sheets));
                assertEquals(1, service.selectActions(new TTestDataRevisionAction()).size());
                assertTrue(mapper.selectWarningExceptions(1L, null).isEmpty());
                assertEquals("COMPLETED", mapper.selectTestById(1L).getTestStatus());
                assertEquals("ENDED", mapper.selectTestById(1L).getReceptionStatus());
            }
            finally { sql.execute("drop database " + schema); }
        }
    }

    private com.ruoyi.common.core.domain.AjaxResult submit(TTestController controller, Map<String, List<Map<String, Object>>> sheets)
    {
        return controller.dataAction(1L, new TTestController.ConsoleDataActionRequest("ALL", "IMPORT", "三 Sheet 导入", "[]",
                JSON.toJSONString(sheets), "三 Sheet 共用原因", List.of()));
    }
}

 scripts/test-workbook-import.ps1

New file
@@ -0,0 +1,29 @@
param([switch] $FullSuite)
$ErrorActionPreference = 'Stop'
$workbookSuffix = [guid]::NewGuid().ToString('N')
$workbookSchema = 'ticket04_import_' + $workbookSuffix
$workbookUser = 'workbook_' + $workbookSuffix.Substring(0, 16)
$workbookPassword = [guid]::NewGuid().ToString('N')

function Invoke-WorkbookSql([string] $sql) {
    $sql | wsl --exec docker exec -i infrastructure-mysql-1 sh -lc 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mysql -uroot --batch'
    if ($LASTEXITCODE -ne 0) { throw '隔离库管理命令失败' }
}

Push-Location (Join-Path $PSScriptRoot '..')
try {
    # 临时账号仅拥有本次随机库的权限，不启动服务、不修改共享开发库。
    Invoke-WorkbookSql "CREATE USER '$workbookUser'@'%' IDENTIFIED BY '$workbookPassword'; GRANT ALL PRIVILEGES ON ${workbookSchema}.* TO '$workbookUser'@'%';"
    $env:WORKBOOK_TEST_SCHEMA = $workbookSchema
    $env:WORKBOOK_TEST_USER = $workbookUser
    $env:WORKBOOK_TEST_PASSWORD = $workbookPassword
    $env:TICKET03_SAMPLE_JSON = (Resolve-Path '../.scratch/simulator-and-data-import-v1/ticket03-sample.json').Path
    if ($FullSuite) { mvn test }
    else { mvn -pl ruoyi-admin -am test '-Dtest=ConsoleWorkbookImportMysqlTest' '-Dsurefire.failIfNoSpecifiedTests=false' }
    if ($LASTEXITCODE -ne 0) { throw '三 Sheet 导入验证失败' }
} finally {
    Remove-Item Env:WORKBOOK_TEST_SCHEMA,Env:WORKBOOK_TEST_USER,Env:WORKBOOK_TEST_PASSWORD,Env:TICKET03_SAMPLE_JSON -ErrorAction SilentlyContinue
    if ($workbookSchema -notmatch '^ticket04_import_[a-f0-9]{32}$' -or $workbookUser -notmatch '^workbook_[a-f0-9]{16}$') { throw '隔离资源名称校验失败' }
    Invoke-WorkbookSql "DROP DATABASE IF EXISTS $workbookSchema; DROP USER IF EXISTS '$workbookUser'@'%';"
    Pop-Location
}