某顶级投行数据审计方案之一：三表策略工厂模板联动处理方案-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_37064763/article/details/146139294

背景和痛点

在投行交易系统中，对于交易订单的数据状态追踪和操作留痕是核心审计需求。通过主表、Stage表、History表的三表联动设计实现分布式系统中的业务流程状态管理。

交易订单表结构设计

主表

CREATE TABLE trade_main (
id BIGINT PRIMARY KEY COMMENT '主键',
trade_no VARCHAR(32) NOT NULL COMMENT '交易编号',
status VARCHAR(20) NOT NULL COMMENT '当前状态',
INDEX idx_status (status)
);

Stage表

CREATE TABLE trade_stage (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    main_id BIGINT NOT NULL COMMENT '关联主表ID',
    trade_no VARCHAR(32) NOT NULL COMMENT '交易编号',
    status VARCHAR(20) NOT NULL COMMENT '阶段状态',
    is_current TINYINT(1) DEFAULT 0 COMMENT '是否当前有效',
    is_latest TINYINT(1) DEFAULT 0 COMMENT '是否最新记录',
    INDEX idx_main_current (main_id, is_current),
    INDEX idx_main_latest (main_id, is_latest)
);

History表

CREATE TABLE trade_history (
   id BIGINT AUTO_INCREMENT PRIMARY KEY,
   main_id BIGINT NOT NULL COMMENT '关联主表ID',
   trade_no VARCHAR(32) NOT NULL COMMENT '交易编号',
   STATUS VARCHAR(20) NOT NULL COMMENT '阶段状态',
   start_time DATETIME NOT NULL COMMENT '阶段开始时间',
   end_time DATETIME COMMENT '阶段结束时间',
   INDEX idx_main_time (main_id, start_time)
);

双标记位：is_current快速定位有效阶段，is_latest定位最新操作
时间区间：History表记录状态生效时段，支持时间切片查询
索引优化：对main_id+状态标记建立组合索引

交易订单流程的三个阶段数据

假设订单 main_id=1001 经历以下阶段：
开始订单 → 订单进行中 → 完成订单，对应生成三条 stage 记录。

阶段1：开始交易

-- --------------- 主表状态 ---------------
id  | order_no      | amount | status
----|---------------|--------|---------
1001| ORD202401001  | 500.00 | INITIAL

-- --------------- Stage表记录 ---------------
id  | main_id | status  | is_current | is_latest 
----|---------|---------|------------|-----------
101 | 1001    | INITIAL | true       | true      

-- --------------- History表 ---------------
（无记录）

阶段1：交易进行中

-- --------------- 主表更新 ---------------
UPDATE order_main SET 
    status = 'PROCESSING' 
WHERE id = 1001;

-- --------------- Stage表更新 ---------------
-- 新增记录
id  | main_id | status      | is_current | is_latest | created_time
----|---------|-------------|------------|-----------|-------------------
102 | 1001    | PROCESSING  | true       | true      | 2025-01-01 11:00

-- 原记录更新
UPDATE order_stage SET 
    is_current = false,
    is_latest = false 
WHERE id = 101;

-- --------------- History表 ---------------
（仍无记录）

阶段3：完成交易


-- --------------- 主表更新 ---------------
UPDATE order_main SET 
    status = 'COMPLETED' 
WHERE id = 1001;

-- --------------- Stage表更新 ---------------
-- 新增记录
id  | main_id | status      | is_current | is_latest | created_time
----|---------|-------------|------------|-----------|-------------------
103 | 1001    | COMPLETED   | false      | true      | 2025-01-01 12:00

-- 原记录更新
UPDATE order_stage SET 
    is_current = false,
    is_latest = false 
WHERE id = 102;

-- --------------- History表新增 ---------------
INSERT INTO order_history (id, main_id, start_time, end_time, status)
VALUES 
(201, 1001, '2025-01-01 10:00', '2025-01-01 11:00', 'INITIAL'),
(202, 1001, '2025-01-01 11:00', '2025-01-01 12:00', 'PROCESSING'),
(203, 1001, '2025-01-01 12:00', NULL, 'COMPLETED');

最终数据

-- --------------- 主表最终状态 ---------------
id  | order_no      | amount | status
----|---------------|--------|---------
1001| ORD202401001  | 500.00 | COMPLETED

-- --------------- Stage表最终状态 ---------------
id  | main_id | status      | is_current | is_latest
----|---------|-------------|------------|-----------
101 | 1001    | INITIAL     | false      | false  
102 | 1001    | PROCESSING  | false      | false    
103 | 1001    | COMPLETED   | false      | true      

-- --------------- History表数据 ---------------
id  | main_id | start_time          | end_time            | status
----|---------|---------------------|---------------------|---------
201 | 1001    | 2025-01-01 10:00   | 2025-01-01 11:00   | INITIAL
202 | 1001    | 2025-01-01 11:00   | 2025-01-01 12:00   | PROCESSING
203 | 1001    | 2025-01-01 12:00   | NULL                | COMPLETED

策略工厂模板核心流程

1. 策略工厂模板

@Slf4j
public class ProcessorFactory {
    private static Map<String, AbstractBizProcessorHandler> processorMap = new ConcurrentHashMap<>();
    public static AbstractBizProcessorHandler getInvokeHandler(String parameter){
        return processorMap.get(parameter);
    }

    public static void register(String parameter, AbstractBizProcessorHandler handler){
        if (parameter==null||handler==null){
            return;
        }
        log.info("------------- register processor into factory: parameter->{}, handler->{}", parameter,handler);
        processorMap.put(parameter, handler);
    }
}

@Slf4j
public abstract class AbstractBizProcessorHandler<MAIN extends BaseMainEntity> implements InitializingBean {
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public void process(MAIN mainData){
        if (mainData.getId()==null){
            //1.initial
            log.info("*********** start: [initial] process");
            mainData =saveMain(mainData);
            saveNewStage(mainData);
        }else if (!checkCompletion(mainData)){
            //2.processing
            log.info("*********** start: [processing] process");
            mainData =saveMain(mainData);
            disableOldStage(mainData);
            saveNewStage(mainData);
        }else{
            // 3. completed
            log.info("*********** start: [completed] process");
            mainData =saveMain(mainData);
            saveHistory(mainData);
            disableOldStage(mainData);
            saveCompletedStage(mainData);
        }
    }

    protected abstract MAIN saveMain(MAIN mainData);
    protected abstract void saveNewStage(MAIN mainData);
    protected abstract boolean checkCompletion(MAIN mainData);
    protected abstract void saveHistory(MAIN mainData);
    protected abstract void disableOldStage(MAIN mainData);
    protected abstract void saveCompletedStage(MAIN mainData);

}

2. 核心交易实现类

@Slf4j
@Component
public class TradeProcessorHandler extends AbstractBizProcessorHandler<TradeMain> {
    @Resource
    private TradeMainMapper tradeMainMapper;
    @Resource
    private TradeStageMapper tradeStageMapper;
    @Resource
    private TradeHistoryMapper tradeHistoryMapper;
    private static final String PARAMETER = "trade";


    @Override
    public void afterPropertiesSet() throws Exception {
        ProcessorFactory.register(PARAMETER, this);
    }

    @Override
    protected TradeMain saveMain(TradeMain mainData) {
        mainData.setCreateTime(LocalDateTime.now());
        tradeMainMapper.save(mainData);
        log.info("---------1. save main data, id->{}, status->{}", mainData.getId(), mainData.getStatus());
        return mainData;
    }

    @Override
    protected void saveNewStage(TradeMain mainData) {
        TradeStage tradeStage = new TradeStage();
        BeanUtils.copyProperties(mainData, tradeStage);
        tradeStage.setId(null);
        tradeStage.setMainId(mainData.getId());
        tradeStage.setIsCurrent(true);
        tradeStage.setIsLatest(true);
        tradeStageMapper.save(tradeStage);
        log.info("---------2. save new stage data, id->{}, mainId->{}", tradeStage.getId(), tradeStage.getMainId());
    }

    @Override
    protected boolean checkCompletion(TradeMain mainData) {
        boolean isCompletion = "closed".equalsIgnoreCase(mainData.getStatus());
        log.info("---------3. check main is completion, id->{}, isCompletion->{}", mainData.getId(), isCompletion);
        return isCompletion;
    }

    @Override
    protected void saveHistory(TradeMain mainData) {
        TradeHistory tradeHistory = new TradeHistory();
        BeanUtils.copyProperties(mainData,tradeHistory);
        tradeHistory.setId(null);
        tradeHistory.setMainId(mainData.getId());
        tradeHistory.setStartTime(mainData.getCreateTime());
        tradeHistory.setEndTime(LocalDateTime.now());
        tradeHistoryMapper.save(tradeHistory);
        log.info("---------4. save new history data, id->{}, mainId->{}", tradeHistory.getId(), tradeHistory.getMainId());
    }

    @Override
    protected void disableOldStage(TradeMain mainData) {
        Example example = new Example(TradeStage.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("main_id", mainData.getId());
        for (TradeStage tradeStage : tradeStageMapper.selectByExample(example)) {
            tradeStage.setIsLatest(false);
            tradeStage.setIsCurrent(false);
            tradeStageMapper.updateByPrimaryKey(tradeStage);
        }
        log.info("---------5. disable old stage data, mainId->{}",mainData.getId());
    }

    @Override
    protected void saveCompletedStage(TradeMain mainData) {
        TradeStage tradeStage = new TradeStage();
        BeanUtils.copyProperties(mainData, tradeStage);
        tradeStage.setId(null);
        tradeStage.setMainId(mainData.getId());
        tradeStage.setIsCurrent(true);
        tradeStage.setIsLatest(false);
        tradeStageMapper.save(tradeStage);
        log.info("---------6. save completed stage data, id->{}, mainId->{}", tradeStage.getId(), tradeStage.getMainId());
    }
}