seata分布式事务处理技术

最新推荐文章于 2023-05-09 19:48:56 发布

fengchengwu2012

最新推荐文章于 2023-05-09 19:48:56 发布

阅读量1w

点赞数

分类专栏： Java 文章标签：分布式 seata 分布式事务微服务

本文链接：https://blog.csdn.net/fengchengwu2012/article/details/117675099

版权

Java 专栏收录该内容

63 篇文章 1 订阅

订阅专栏

微服务开发中，多数据源，跨进程调用的事务问题是老生常谈的问题，以下介绍springcloudalibba的中间件seata,实现分布式事务处理的方案：

seata分布式事务的过程：

TM 请求 TC，开始一个新的全局事务，TC 会为这个全局事务生成一个 XID。
XID 通过微服务的调用链传递到其他微服务。
RM 把本地事务作为这个XID的分支事务注册到TC。
TM 请求 TC 对这个 XID 进行提交或回滚。
TC 指挥这个 XID 下面的所有分支事务进行提交、回滚

一、构建seata服务，前提是注册中心、配置中心已经搭建好，本文使用nacos作为注册中心进行演示：

1、启动已经构建好的nacos服务，服务地址http://192.168.81.144:8848/nacos

2、安装seata服务，主要有以下步骤：

（1）、使用mysql创建一个名为seata的数据库，库建好后连上数据库创建三张表，建表语句如下，这三张表是协调事务用的

DROP TABLE IF EXISTS `branch_table`;
CREATE TABLE `branch_table`  (
  `branch_id` bigint(20) NOT NULL,
  `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `transaction_id` bigint(20) NULL DEFAULT NULL,
  `resource_group_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `resource_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `branch_type` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` tinyint(4) NULL DEFAULT NULL,
  `client_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `application_data` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gmt_create` datetime(6) NULL DEFAULT NULL,
  `gmt_modified` datetime(6) NULL DEFAULT NULL,
  PRIMARY KEY (`branch_id`) USING BTREE,
  INDEX `idx_xid`(`xid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for global_table
-- ----------------------------
DROP TABLE IF EXISTS `global_table`;
CREATE TABLE `global_table`  (
  `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `transaction_id` bigint(20) NULL DEFAULT NULL,
  `status` tinyint(4) NOT NULL,
  `application_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `transaction_service_group` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `transaction_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `timeout` int(11) NULL DEFAULT NULL,
  `begin_time` bigint(20) NULL DEFAULT NULL,
  `application_data` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gmt_create` datetime(0) NULL DEFAULT NULL,
  `gmt_modified` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`xid`) USING BTREE,
  INDEX `idx_gmt_modified_status`(`gmt_modified`, `status`) USING BTREE,
  INDEX `idx_transaction_id`(`transaction_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for lock_table
-- ----------------------------
DROP TABLE IF EXISTS `lock_table`;
CREATE TABLE `lock_table`  (
  `row_key` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `transaction_id` bigint(20) NULL DEFAULT NULL,
  `branch_id` bigint(20) NOT NULL,
  `resource_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `table_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pk` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gmt_create` datetime(0) NULL DEFAULT NULL,
  `gmt_modified` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`row_key`) USING BTREE,
  INDEX `idx_branch_id`(`branch_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

（2）修改seata配置文件file.conf、registry.conf

file.conf内容如下：

transport {
  type = "TCP"
  server = "NIO"
  heartbeat = true
  enableClientBatchSendRequest = false
  threadFactory {
    bossThreadPrefix = "NettyBoss"
    workerThreadPrefix = "NettyServerNIOWorker"
    serverExecutorThreadPrefix = "NettyServerBizHandler"
    shareBossWorker = false
    clientSelectorThreadPrefix = "NettyClientSelector"
    clientSelectorThreadSize = 1
    clientWorkerThreadPrefix = "NettyClientWorkerThread"
    # netty boss thread size,will not be used for UDT
    bossThreadSize = 1
    #auto default pin or 8
    workerThreadSize = "default"
  }
  shutdown {
    wait = 3
  }
  serialization = "seata"
  compressor = "none"
}

store {
  mode = "db"
  publicKey = ""
  file {
    dir = "sessionStore"
    maxBranchSessionSize = 16384
    maxGlobalSessionSize = 512
    fileWriteBufferCacheSize = 16384
    sessionReloadReadSize = 100
    flushDiskMode = async
  }

  db {
    DruidDataSource(druid)/BasicDataSource(dbcp)/HikariDataSource(hikari) etc.
    datasource = "druid"
    dbType = "mysql"
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://192.168.81.144:3306/seata?rewriteBatchedStatements=true"
    user = "root"
    password = "root"
    minConn = 5
    maxConn = 100
    globalTable = "global_table"
    branchTable = "branch_table"
    lockTable = "lock_table"
    queryLimit = 100
    maxWait = 5000
  }

}

service {
  vgroupMapping.nacos_consumer_tx = "bp_tx"
  bp_tx.grouplist = "192.168.81.144:8091"
  enableDegrade = false
  #disable seata
  disableGlobalTransaction = false
}

file.conf中的service模块中配置事务组，我在这里定义为nacos_consumer_tx,这个子串后面有用要注意。

将seata服务注册到nacos服务，修改registry.conf配置如下：

registry {
  type = "nacos"
  nacos {
    application = "seata-server"
    serverAddr = "192.168.81.144:8848"
    group = "SEATA_GROUP"
    namespace = ""
    cluster = "default"
    username = ""
    password = ""
  }
  file {
    name = "file.conf"
  }
}

config {
   type = "file"
   nacos {
    serverAddr = "192.168.81.144:8848"
    namespace = ""
    group = "SEATA_GROUP"
    username = "nacos"
    password = "nacos"
    dataId = "seataServer.properties"
  }
  file {
    name = "file.conf"
  }
}

（3）配置文件修改好后，启动seata服务

二、服务搭建好，开始写代码：

业务场景：模拟电商下单，扣减商户余额、积分

1、搭建支付的微服务nacos-consumer-two

(1) 在配置文件application.yml中配置事务组，名字要和seata配置文件中service模块中的vgroupMapping的后缀nacos_consumer_tx一致

spring:
  application:
    name: nacos-provider
  cloud:
    alibaba:
      seata:
        tx-service-group: nacos_consumer_tx

bootstrap.yml配置

appHost: 192.168.81.144
server:
  port: 9002
spring:
  cloud:
    nacos:
      config:
        #118.24.27.34:8848
        server-addr: ${appHost}:8848
        file-extension: yaml
      discovery:
        server-addr: ${appHost}:8848

( 2)配置seata代理加载数据源

@Configuration
public class SeataDataSourceConfig {

    private final  static Logger  LOGGER = LoggerFactory.getLogger(SeataDataSourceConfig.class);

    @Bean("druidDataSource")
    public DruidDataSource  druidDataSource(){
        ///LOGGER.info("数据源切换 {} {} {} {} {}",driverClassName,url,username,password,configLocation);
        DruidDataSource dataSource=new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://192.168.81.144:3306/fee_db?useUnicode=true&characterEncoding=utf-8");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }


    @Bean("dataSourceProxy")
    public DataSourceProxy  dataSourceProxy(DruidDataSource  druidDataSource){
        return new DataSourceProxy(druidDataSource);
    }

    @Bean("sqlSessionFactoryBean")
    public SqlSessionFactoryBean sqlSessionFactoryBean(DataSourceProxy dataSourceProxy) {
        SqlSessionFactoryBean  sqlSessionFactoryBean=new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSourceProxy);
        PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        ///sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResource("classpath:mybatis/mybatis-config.xml"));
        sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
        return sqlSessionFactoryBean;
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory  sqlSessionFactory(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("sqlSessionTemplate")
   public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory  sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
   }

    /**
     * 开启mapper扫描器
     * @return
     */
    @Bean(name = "mapperScannerConfigurer")
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactoryBean");
        mapperScannerConfigurer.setSqlSessionTemplateBeanName("sqlSessionTemplate");
        mapperScannerConfigurer.setBasePackage("org.cn.fcw");
        return mapperScannerConfigurer;
    }


}

(3)业务代码

模拟支付业务，使用@GlobalTransactional注解开启全局事务

@Service
public class AccountService {

    private final  static Logger LOGGER= LoggerFactory.getLogger(AccountService.class);

    @Autowired
    private IntegralMapper integralMapper;

    @Autowired
    private AccountMapper  accountMapper;


    @HystrixCommand(commandProperties = {
                    @HystrixProperty(name = "execution.isolation.thread.timeoutInMilliseconds",value = "3000"),
                    @HystrixProperty(name = "circuitBreaker.enabled",value = "true"),
                    @HystrixProperty(name = "circuitBreaker.requestVolumeThreshold",value = "10"),
                    @HystrixProperty(name = "circuitBreaker.sleepWindowInMilliseconds",value = "10000"),
                    @HystrixProperty(name = "circuitBreaker.errorThresholdPercentage",value = "60")
    })
    public BaseResult reduceAccount(int  userId, BigDecimal  price){
        LOGGER.info("reduceAccount-模拟远程调用超时");
        try {
            TimeUnit.SECONDS.sleep(5);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        LOGGER.info("reduceAccount--------->线程睡眠结束");
        AccountBean  accountBean = accountMapper.findByUserId(userId);
        IntegralBean integralBean = integralMapper.findByUserId(userId);
        LOGGER.info("扣款前accountBean账户信息####"+accountBean.toString());
        LOGGER.info("扣款前integralBean积分信息###"+integralBean.toString());
        BigDecimal amountTotal = accountBean.getAmount();
        BigDecimal surplus = integralBean.getSurplus();
        BigDecimal  integralRate=new BigDecimal(100),maxDeductRate=new BigDecimal("0.20");
        BigDecimal  maxDeductAmount=maxDeductRate.multiply(price);
        BigDecimal  surplusAmount= surplus.divide(integralRate);
        //积分抵扣金额
        BigDecimal  deductAmount=surplusAmount.doubleValue()>=maxDeductAmount.doubleValue()?maxDeductAmount:surplusAmount;
        //账户扣款金额
        BigDecimal  accountAmount=price.subtract(deductAmount);
        if(amountTotal.doubleValue()<accountAmount.doubleValue()){
            return BaseResult.toSuccess("账户余额不足");
        }
        accountBean.setAmount(amountTotal.subtract(accountAmount));
        integralBean.setSurplus(surplusAmount.subtract(deductAmount).multiply(integralRate));
        LOGGER.info("扣款后accountBean账户信息---"+accountBean.toString());
        LOGGER.info("扣款后integralBean账户信息---"+integralBean.toString());
        integralMapper.updateIntegral(integralBean);
        accountMapper.updateAccount(accountBean);
        return BaseResult.toSuccess("扣款成功");
    }

}

提供远程调用接口

@RestController
public class AccountController {

    private final  static Logger LOGGER= LoggerFactory.getLogger(AccountController.class);

    @Autowired
    private AccountService accountService;

    @PostMapping("/provider/account/pay")
    public BaseResult  pay(Integer userId,double price){
        LOGGER.info("支付收到参数userId-"+userId+",price="+price);
        return accountService.reduceAccount(userId,new BigDecimal(price));
    }
}

(4)启动服务

资源管理rm向seata注册成功

2、创建下单服务nacos-consumer-two

(1) 在配置文件application.yml中配置事务组,与支付服务相同

#yml文件加载的优先级，nacos的注册服务配置需要方放在bootstrap中
spring:
  cloud:
    alibaba:
      seata:
        tx-service-group: nacos_consumer_tx
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml
  type-aliases-package: org.cn.fcw.bean
  mapper-locations: classpath:mapper/*.xml

#feign开启 hystrix的服务降级功能
feign:
  hystrix:
    enabled: true
logging:
  level:
    org.cn.fcw.api.FeignServiceApi: debug

bootstrap.yml配置

server:
  port: 9011
appHost: 192.168.81.144
spring:
  application:
    name: nacos_consumer
  cloud:
    nacos:
      discovery:
        server-addr: ${appHost}:8848
      config:
        server-addr: ${appHost}:8848

配置seata代理数据源

@Configuration
public class SeataDataSourceConfig {

    @Bean("druidDataSource")
    public DruidDataSource  druidDataSource(){
        DruidDataSource dataSource=new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://192.168.81.144:3306/cateen_db?useUnicode=true&characterEncoding=utf-8");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }


    @Bean("dataSourceProxy")
    public DataSourceProxy  dataSourceProxy(DruidDataSource  druidDataSource){
        return new DataSourceProxy(druidDataSource);
    }

    @Bean("sqlSessionFactoryBean")
    public SqlSessionFactoryBean sqlSessionFactoryBean(DataSourceProxy dataSourceProxy) {
        SqlSessionFactoryBean  sqlSessionFactoryBean=new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSourceProxy);
        PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        ///sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResource("classpath:mybatis/mybatis-config.xml"));
        sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
        return sqlSessionFactoryBean;
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory  sqlSessionFactory(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("sqlSessionTemplate")
   public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory  sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
   }

    /**
     * 开启mapper扫描器
     * @return
     */
    @Bean(name = "mapperScannerConfigurer")
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactoryBean");
        mapperScannerConfigurer.setSqlSessionTemplateBeanName("sqlSessionTemplate");
        mapperScannerConfigurer.setBasePackage("org.cn.fcw");
        return mapperScannerConfigurer;
    }


}

(2)编写下单业务

远程调用支付接口

@Component
@FeignClient(value = "nacos-provider",fallback = FeignServiceApiImpl.class)
public interface FeignServiceApi {

    @PostMapping(value = "/provider/account/pay",headers = {"Accept=application/json","Content-Type=application/x-www-form-urlencoded"})
    BaseResult doPay(@RequestParam("userId") int userId,@RequestParam("price") double price);


}

@Component
public class FeignServiceApiImpl implements FeignServiceApi {

    @Override
    public BaseResult doPay(int userId, double price) {
        return BaseResult.toFail("支付服务太累了，跑不动请稍后再试!");
    }
}

下单服务层

@Service
public class BuyService {

    private final static Logger LOGGER = LoggerFactory.getLogger(BuyService.class);

    @Autowired
    private BuyMapper buyMapper;

    @Autowired
    private FeignServiceApi feignServiceApi;

    @GlobalTransactional
    public BaseResult doBuy(int userId) throws Exception {
        List<BuyBean> buyBeanList = buyMapper.findListByUserId(userId, 0);
        LOGGER.info("buyBeanList---" + Arrays.toString(buyBeanList.toArray()));
        if (buyBeanList.isEmpty()) {
            return BaseResult.toFail("暂无可支付订单");
        }
        BuyBean buyBean = buyBeanList.get(0);
        BigDecimal price = buyBean.getPrice();
        ///调用支付
        BaseResult baseResult = feignServiceApi.doPay(userId, price.doubleValue());
        if (baseResult.getCode() == 500) {
            throw new Exception(baseResult.getMsg());
        }
        //修改訂單狀態
        buyBean.setStatus(1);
        int i = buyMapper.updateBuyStatus(buyBean);
        if (i != 1) {
            return BaseResult.toSuccess("支付失败");
        }
        return BaseResult.toSuccess("支付成功");
    }
}

接口层

@RestController
public class BuyController {

    private final  static Logger LOGGER = LoggerFactory.getLogger(BuyController.class);
    
    @Autowired
    private BuyService buyService;
    
    @GetMapping("/consumer/buyPay")
    public BaseResult  buyAndPay(Integer  userId){
        LOGGER.info("调用服务消费支付接口userId-"+userId);
        BaseResult baseResult;
        try {
             baseResult = buyService.doBuy(userId);
        } catch (Exception exception) {
            exception.printStackTrace();
            baseResult=BaseResult.toFail(exception.getMessage());
        }
        return baseResult;
    }
}

（3）启动下单服务

三、表结构、实体类、mapper文件

1、nacos-provider-two使用fee_db，有t_account、t_intergal、undo_log（回滚记录表）有三个表,

undo_log记录每个事务参与者sql的逆向操作，一旦参与事务shi，就会执行这些反向sql，实现事务的回滚。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_account
-- ----------------------------
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_account
-- ----------------------------
INSERT INTO `t_account` VALUES (1, 100, 'LiuCui', 32, 600.00, '2021-06-07 03:57:16');

-- ----------------------------
-- Table structure for t_integral
-- ----------------------------
DROP TABLE IF EXISTS `t_integral`;
CREATE TABLE `t_integral`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `surplus` decimal(6, 2) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_integral
-- ----------------------------
INSERT INTO `t_integral` VALUES (1, 100, 'LiuCui', 8000.00, '2021-06-07 03:57:16');

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_id` bigint(20) NOT NULL,
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `rollback_info` longblob NOT NULL,
  `log_status` int(11) NOT NULL,
  `log_created` datetime(0) NOT NULL,
  `log_modified` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

AccountBean 、IntergralBean 实体类

@Data
public class IntegralBean {

    private Integer id;

    private String  name;

    private  Integer userId;

    private BigDecimal  surplus;

    private Date  updateTime;
}

@Data
public class AccountBean {

    private Integer id;

    private Integer userId;

    private  String  name;

    private BigDecimal amount;

    private Date updateTime;
}

mapper 映射层

/**
 * @author Administrator
 */
@Mapper
public interface IntegralMapper {

    /**
     * 根据ID查询
     * @param userId
     * @return
     */
    @Result(id = true,property = "id",column = "id")
    @Result(property = "name",column = "name")
    @Result(property = "userId",column = "user_id")
    @Result(property = "surplus",column = "surplus")
    @Result(property = "updateTime",column = "update_time")
    @Select({" select  *  from  t_integral where user_id=#{userId}"})
    IntegralBean findByUserId(@Param("userId") int userId);


    /**
     * 扣除積分
     * @param integralBean
     * @return
     */
    @Update({"update t_integral set surplus=#{surplus},update_time=sysdate() where id=#{id}"})
    int updateIntegral(IntegralBean integralBean);
}


@Mapper
public interface AccountMapper {

    /**
     * 查賬戶信息
     * @param userId
     * @return
     */
    @Result(id = true,property = "id",column = "id")
    @Result(property = "userId",column = "user_id")
    @Result(property = "name",column = "name")
    @Result(property = "amount",column = "amount")
    @Result(property = "updateTime",column = "update_time")
    @Select({" select *  from  t_account  where user_id=#{userId}"})
    AccountBean  findByUserId(@Param("userId")int userId);

    /**
     * 更新賬戶
     * @param accountBean
     * @return
     */
    @Update({"update t_account set amount=#{amount},update_time=sysdate() where id=#{id}"})
    int  updateAccount(AccountBean accountBean);
}

2、nacos-consumer-two服务连接cateen_db，表t_buy、undo_log结构如下：

CREATE TABLE `t_buy`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL COMMENT '用户id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `goods` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` decimal(10, 2) NULL DEFAULT NULL,
  `status` int(1) NULL DEFAULT NULL COMMENT '0 下单  1 支付',
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_buy
-- ----------------------------
INSERT INTO `t_buy` VALUES (1, 100, 'LiuCui', '香蕉', 20.00, 0, '2021-06-03 23:42:59');
SET FOREIGN_KEY_CHECKS = 1;


DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_id` bigint(20) NOT NULL,
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `rollback_info` longblob NOT NULL,
  `log_status` int(11) NOT NULL,
  `log_created` datetime(0) NOT NULL,
  `log_modified` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

实体类BuyBean

@Data
public class BuyBean {

    private Integer id;

    private Integer userId;

    private String  name;

    private BigDecimal  price;

    private String  goods;

    private Integer  status;

    private Date updateTime;
}

mapper映射BuyMapper

@Mapper
public interface BuyMapper {

    /**
     * 查詢用戶購買商品
     * @param userId
     * @return
     */
    @Result(id = true,property = "id",column = "id")
    @Result(property = "userId",column = "user_id")
    @Result(property = "name",column = "name")
    @Result(property = "price",column = "price")
    @Result(property = "goods",column = "goods")
    @Result(property = "status",column = "status")
    @Result(property = "updateTime",column = "update_time")
    @Select({ "select  *  from t_buy  where  user_id=#{userId} and status=#{status} "})
    List<BuyBean> findListByUserId(@Param("userId")Integer userId,@Param("status")int status);


    /***
     * 修改支付状态
     * @param buyBean
     * @return
     */
    @Update({"update t_buy  set status=#{status} where id=#{id} "})
    int  updateBuyStatus(BuyBean buyBean);

    
}