sql server触发器监听增量同步+springboot rabbitmq框架集成

MeltryLL

已于 2024-08-28 10:26:14 修改

阅读量391

点赞数 14

文章标签：数据库 sql

于 2024-08-22 15:49:42 首次发布

本文链接：https://blog.csdn.net/MeltryLL/article/details/141391698

版权

前言

其实本来考虑使用的debezium之类的成熟框架，监听cdc可以很简单的实现增量同步，但很可惜，业务线上使用的是2008 Standard版本的sql server，根本开启不了cdc，综合考虑自己写触发器实现似乎简单些。

流程如下：sql server数据变更->触发器将数据存储到日志表->生产者定时将日志信息分主题推送mq->消费者处理与自己有关的队列

笔者主张消费者和生产者分离，因为我们这边的业务需求，可能会存在特殊的同步规则，所以“谁消费，谁处理”。

生产者基本无需修改代码，新增监听表也只需配置触发器和yml加一行表名就可以；消费者以下仅演示最基本的同步。

一.sql server操作步骤

1.创建监听日志表

CREATE TABLE [dbo].[monitorLog] (
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [actionType] nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [oldData] text COLLATE Chinese_PRC_CI_AS  NULL,
  [newData] text COLLATE Chinese_PRC_CI_AS  NULL,
  [gmtCreate] datetime DEFAULT (getdate()) NULL,
  [tableName] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  CONSTRAINT [PK__monitorL__3213E83F940AB356] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[monitorLog] SET (LOCK_ESCALATION = TABLE)

2.创建触发器，监听指定表的增删改

CREATE TRIGGER trg_ObjCompanyChangeLog
ON obj_Company  -- 替换为目标表的名称
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- 声明变量
    DECLARE @jsonDataNew NVARCHAR(MAX);
    DECLARE @jsonDataOld NVARCHAR(MAX);
    DECLARE @action NVARCHAR(10);
    DECLARE @tableName NVARCHAR(128) = 'obj_Company'; -- 表名

    -- 处理插入操作
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        SET @action = 'INSERT';
        -- 遍历 inserted 表中的每条记录
        INSERT INTO monitorLog (actionType, oldData, newData, gmtCreate, tableName)
        SELECT 
            @action,
            NULL, -- 对于插入操作，没有旧数据
            (SELECT * FROM inserted i WHERE i.number = inserted.number FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
            GETDATE(),
            @tableName
        FROM inserted;
    END

    -- 处理删除操作
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        SET @action = 'DELETE';
        -- 遍历 deleted 表中的每条记录
        INSERT INTO monitorLog (actionType, oldData, newData, gmtCreate, tableName)
        SELECT 
            @action,
            (SELECT * FROM deleted d WHERE d.number = deleted.number FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
            NULL, -- 对于删除操作，没有新数据
            GETDATE(),
            @tableName
        FROM deleted;
    END

    -- 处理更新操作
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        SET @action = 'UPDATE';
        -- 遍历 inserted 和 deleted 表中的每条记录
        INSERT INTO monitorLog (actionType, oldData, newData, gmtCreate, tableName)
        SELECT 
            @action,
            (SELECT * FROM deleted d WHERE d.number = inserted.number FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
            (SELECT * FROM inserted i WHERE i.number = inserted.number FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
            GETDATE(),
            @tableName
        FROM inserted;
    END
END;
GO

以上监听的表为obj_Company，请依据实际情况修改

二、生产者

1.实体类MonitorLog

@Getter
@Setter
@Accessors(chain = true)
@TableName("monitorLog")
@Component
public class MonitorLog implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    @TableId(value = "id", type = IdType.AUTO)
    private String id;

    /**
     * 操作类型
     */
    @TableField("actionType")
    private String actionType;

    /**
     * 旧数据
     */
    @TableField("oldData")
    private String oldData;

    /**
     * 新数据
     */
    @TableField("newData")
    private String newData;

    /**
     * 创建时间
     */
    @TableField("gmtCreate")
    private LocalDateTime gmtCreate;

    /**
     * 监听的表名
     */
    @TableField("tableName")
    private String tableName;

}

2.依据表名创建消息队列

yml配置在2.4

@Data
@Component
@ConfigurationProperties(prefix = "web-mvc")
public class YmlProperties {
    private List<String> imagePaths;
    private List<String> rabbitMQTopics;
}

rabbitmq配置

@Configuration
public class RabbitExchangeConfig {

    @Autowired
    private ConnectionFactory connectionFactory;

    @Autowired
    private YmlProperties ymlProperties;

    public static final String ConsumptionRecordExchange = "TopicRecordExchange";
    public static final String ConsumptionRecordProcessDeadExchange = "TopicRecordProcessDeadExchange";

    @Bean
    public RabbitAdmin rabbitAdmin() {
        return new RabbitAdmin(connectionFactory);

    }

    @Bean
    TopicExchange consumptionRecordExchange() {
        return new TopicExchange(ConsumptionRecordExchange);
    }

    @Bean
    public DirectExchange consumptionRecordProcessDeadExchange() {
        return new DirectExchange(ConsumptionRecordProcessDeadExchange);
    }

    public void initRabbitMQExchange() {
        List<String> rabbitMQTopics = ymlProperties.getRabbitMQTopics();
        if (!CollectionUtils.isEmpty(rabbitMQTopics)) {
            for (String rabbitMQTopic : rabbitMQTopics) {
                Queue deadQueue = new Queue(StrUtil.toCamelCase(rabbitMQTopic) + "DeadQueue");
                rabbitAdmin().declareQueue(deadQueue);
                rabbitAdmin().declareBinding(BindingBuilder.bind(deadQueue).to(consumptionRecordProcessDeadExchange()).with(rabbitMQTopic));

                Map<String, Object> args = new HashMap<>(2);
                args.put("x-dead-letter-exchange", ConsumptionRecordProcessDeadExchange);
                args.put("x-dead-letter-routing-key", rabbitMQTopic);
                Queue queue = QueueBuilder.durable(StrUtil.toCamelCase(rabbitMQTopic) + "Queue").withArguments(args).build();
                rabbitAdmin().declareQueue(queue);
                rabbitAdmin().declareBinding(BindingBuilder.bind(queue).to(consumptionRecordExchange()).with(rabbitMQTopic));
            }
        }
    }
}

再改一下启动类

@SpringBootApplication
@EnableTransactionManagement
@EnableAsync
@EnableScheduling
public class ChangeEventListenerApplication {

    @Autowired
    RabbitExchangeConfig rabbitExchangeConfig;

    public static void main(String[] args) {
        SpringApplication.run(ChangeEventListenerApplication.class, args);
    }

    @PostConstruct
    public void init() {
        rabbitExchangeConfig.initRabbitMQExchange();
    }

}

3.发送消息

定时任务

@Service
@Slf4j
public class ScheduledTaskService {
    @Autowired
    private MonitorLogServiceImpl monitorLogService;

    @Scheduled(cron = "0 0/1 * * * ?") // Cron表达式，此处每1分钟执行一次
    public void executeTask() {
        List<MonitorLog> allLog = monitorLogService.list();
        log.info("executeTask start, allLog size: " + allLog.size());
        if (CollectionUtils.isEmpty(allLog)){
            return;
        }
        for (MonitorLog log : allLog) {
            monitorLogService.sendMessageToMQ(log);
        }
    }
}

@Service
@Slf4j
public class MonitorLogServiceImpl extends ServiceImpl<MonitorLogMapper, MonitorLog> implements IMonitorLogService {
    @Autowired
    private AmqpTemplate rabbitTemplate;

    /**
     * 发送到消息队列
     * @param message
     */
    public void sendMessageToMQ(MonitorLog message){
        log.info("MQ消息发送：{}", JSONObject.toJSONString(message));
        rabbitTemplate.convertAndSend(RabbitExchangeConfig.ConsumptionRecordExchange, message.getTableName(), JSONObject.toJSONString(message));
        this.removeById(message.getId());
    }
}

4.配置监听表名

修改yml配置文件中的rabbitMQTopics即可

web-mvc:
  imagePaths:
    - "file:D:\\proFile\\test\\"
    - "file:D:\\proFile\\test\\images\\"
    - "classpath:/static/"
  rabbitMQTopics:
    - "kl_exclude_question_from_library"
    - "obj_Company"

三.消费者

依据主题指定接收的队列，命名规则为：驼峰式表名+“Queue”，依据不同的实体类定义不同的消费者和其同步规则

@Slf4j
@Service
@RabbitListener(queues = "objCompanyQueue")
public class ObjCompanyConsumer {
    @Autowired
    private ObjCompanyServiceImpl objCompanyService;

    @RabbitHandler
    @Transactional
    public void receive(String monitorLogString, Channel channel, Message message) {
        MonitorLog monitorLog = JSONObject.parseObject(monitorLogString, MonitorLog.class);
        log.info("Received message: " + monitorLog.toString());
        String actionType = monitorLog.getActionType();
        if (StrUtil.isEmpty(actionType)){
            throw new BusinessException(ExceptionEnum.REJECT_SERVICE,"ActionType is empty");
        }
        if (actionType.equals("INSERT")) {
            String newData = monitorLog.getNewData();
            if (StrUtil.isEmpty(newData)){
                throw new BusinessException(ExceptionEnum.REJECT_SERVICE,"NewData is empty");
            }
            ObjCompany objCompany = JSONObject.parseObject(newData, ObjCompany.class);
            objCompanyService.save(objCompany);
        }
        if (actionType.equals("DELETE")){
            String oldData = monitorLog.getOldData();
            if (StrUtil.isEmpty(oldData)){
                throw new BusinessException(ExceptionEnum.REJECT_SERVICE,"OldData is empty");
            }
            ObjCompany objCompany = JSONObject.parseObject(oldData, ObjCompany.class);
            objCompanyService.removeById(objCompany);
        }
        if (actionType.equals("UPDATE")) {
            String oldData = monitorLog.getOldData();
            String newData = monitorLog.getNewData();
            if (StrUtil.isEmpty(oldData) || StrUtil.isEmpty(newData)){
                throw new BusinessException(ExceptionEnum.REJECT_SERVICE,"OldData or NewData is empty");
            }
            ObjCompany oldObjCompany = JSONObject.parseObject(oldData, ObjCompany.class);
            ObjCompany newObjCompany = JSONObject.parseObject(newData, ObjCompany.class);
            objCompanyService.removeById(oldObjCompany);
            objCompanyService.save(newObjCompany);
        }
    }
}