java黑洞网 首页 博客 问答 站长公众号 pdf 视频 游戏 脚本 实战 其它资源
写博客 注册

程序员最近都爱上了这个网站  程序员们快来瞅瞅吧！  it98k网:it98k.com

本站消息

站长简介/公众号

  出租广告位,需要合作请联系站长


+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

2023-06(1)

seata分布式事务处理技术

发布于2021-06-12 15:04     阅读(941)     评论(0)     点赞(24)     收藏(3)

 

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

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

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

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

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

  1. DROP TABLE IF EXISTS `branch_table`;
  2. CREATE TABLE `branch_table`  (
  3.   `branch_id` bigint(20) NOT NULL,
  4.   `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  5.   `transaction_id` bigint(20) NULL DEFAULT NULL,
  6.   `resource_group_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  7.   `resource_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  8.   `branch_type` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  9.   `status` tinyint(4) NULL DEFAULT NULL,
  10.   `client_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  11.   `application_data` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  12.   `gmt_create` datetime(6) NULL DEFAULT NULL,
  13.   `gmt_modified` datetime(6) NULL DEFAULT NULL,
  14.   PRIMARY KEY (`branch_id`) USING BTREE,
  15.   INDEX `idx_xid`(`xid`) USING BTREE
  16. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  17.  
  18. -- ----------------------------
  19. -- Table structure for global_table
  20. -- ----------------------------
  21. DROP TABLE IF EXISTS `global_table`;
  22. CREATE TABLE `global_table`  (
  23.   `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  24.   `transaction_id` bigint(20) NULL DEFAULT NULL,
  25.   `status` tinyint(4) NOT NULL,
  26.   `application_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  27.   `transaction_service_group` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  28.   `transaction_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  29.   `timeout` int(11) NULL DEFAULT NULL,
  30.   `begin_time` bigint(20) NULL DEFAULT NULL,
  31.   `application_data` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  32.   `gmt_create` datetime(0) NULL DEFAULT NULL,
  33.   `gmt_modified` datetime(0) NULL DEFAULT NULL,
  34.   PRIMARY KEY (`xid`) USING BTREE,
  35.   INDEX `idx_gmt_modified_status`(`gmt_modified`, `status`) USING BTREE,
  36.   INDEX `idx_transaction_id`(`transaction_id`) USING BTREE
  37. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  38.  
  39. -- ----------------------------
  40. -- Table structure for lock_table
  41. -- ----------------------------
  42. DROP TABLE IF EXISTS `lock_table`;
  43. CREATE TABLE `lock_table`  (
  44.   `row_key` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  45.   `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  46.   `transaction_id` bigint(20) NULL DEFAULT NULL,
  47.   `branch_id` bigint(20) NOT NULL,
  48.   `resource_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  49.   `table_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  50.   `pk` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  51.   `gmt_create` datetime(0) NULL DEFAULT NULL,
  52.   `gmt_modified` datetime(0) NULL DEFAULT NULL,
  53.   PRIMARY KEY (`row_key`) USING BTREE,
  54.   INDEX `idx_branch_id`(`branch_id`) USING BTREE
  55. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  56.  
  57. SET FOREIGN_KEY_CHECKS = 1;

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

       file.conf内容如下：

  1. transport {
  2.   type = "TCP"
  3.   server = "NIO"
  4.   heartbeat = true
  5.   enableClientBatchSendRequest = false
  6.   threadFactory {
  7.     bossThreadPrefix = "NettyBoss"
  8.     workerThreadPrefix = "NettyServerNIOWorker"
  9.     serverExecutorThreadPrefix = "NettyServerBizHandler"
  10.     shareBossWorker = false
  11.     clientSelectorThreadPrefix = "NettyClientSelector"
  12.     clientSelectorThreadSize = 1
  13.     clientWorkerThreadPrefix = "NettyClientWorkerThread"
  14.     # netty boss thread size,will not be used for UDT
  15.     bossThreadSize = 1
  16.     #auto default pin or 8
  17.     workerThreadSize = "default"
  18.   }
  19.   shutdown {
  20.     wait = 3
  21.   }
  22.   serialization = "seata"
  23.   compressor = "none"
  24. }
  25.  
  26. store {
  27.   mode = "db"
  28.   publicKey = ""
  29.   file {
  30.     dir = "sessionStore"
  31.     maxBranchSessionSize = 16384
  32.     maxGlobalSessionSize = 512
  33.     fileWriteBufferCacheSize = 16384
  34.     sessionReloadReadSize = 100
  35.     flushDiskMode = async
  36.   }
  37.  
  38.   db {
  39.     DruidDataSource(druid)/BasicDataSource(dbcp)/HikariDataSource(hikari) etc.
  40.     datasource = "druid"
  41.     dbType = "mysql"
  42.     driverClassName = "com.mysql.jdbc.Driver"
  43.     url = "jdbc:mysql://192.168.81.144:3306/seata?rewriteBatchedStatements=true"
  44.     user = "root"
  45.     password = "root"
  46.     minConn = 5
  47.     maxConn = 100
  48.     globalTable = "global_table"
  49.     branchTable = "branch_table"
  50.     lockTable = "lock_table"
  51.     queryLimit = 100
  52.     maxWait = 5000
  53.   }
  54.  
  55. }
  56.  
  57. service {
  58.   vgroupMapping.nacos_consumer_tx = "bp_tx"
  59.   bp_tx.grouplist = "192.168.81.144:8091"
  60.   enableDegrade = false
  61.   #disable seata
  62.   disableGlobalTransaction = false
  63. }

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

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

  1. registry {
  2.   type = "nacos"
  3.   nacos {
  4.     application = "seata-server"
  5.     serverAddr = "192.168.81.144:8848"
  6.     group = "SEATA_GROUP"
  7.     namespace = ""
  8.     cluster = "default"
  9.     username = ""
  10.     password = ""
  11.   }
  12.   file {
  13.     name = "file.conf"
  14.   }
  15. }
  16.  
  17. config {
  18.    type = "file"
  19.    nacos {
  20.     serverAddr = "192.168.81.144:8848"
  21.     namespace = ""
  22.     group = "SEATA_GROUP"
  23.     username = "nacos"
  24.     password = "nacos"
  25.     dataId = "seataServer.properties"
  26.   }
  27.   file {
  28.     name = "file.conf"
  29.   }
  30. }

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

 

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

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

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

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

  1. spring:
  2.   application:
  3.     name: nacos-provider
  4.   cloud:
  5.     alibaba:
  6.       seata:
  7.         tx-service-group: nacos_consumer_tx

        bootstrap.yml配置

  1. appHost: 192.168.81.144
  2. server:
  3.   port: 9002
  4. spring:
  5.   cloud:
  6.     nacos:
  7.       config:
  8.         #118.24.27.34:8848
  9.         server-addr: ${appHost}:8848
  10.         file-extension: yaml
  11.       discovery:
  12.         server-addr: ${appHost}:8848

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

  1. @Configuration
  2. public class SeataDataSourceConfig {
  3.  
  4.     private final  static Logger  LOGGER = LoggerFactory.getLogger(SeataDataSourceConfig.class);
  5.  
  6.     @Bean("druidDataSource")
  7.     public DruidDataSource  druidDataSource(){
  8.         ///LOGGER.info("数据源切换 {} {} {} {} {}",driverClassName,url,username,password,configLocation);
  9.         DruidDataSource dataSource=new DruidDataSource();
  10.         dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
  11.         dataSource.setUrl("jdbc:mysql://192.168.81.144:3306/fee_db?useUnicode=true&characterEncoding=utf-8");
  12.         dataSource.setUsername("root");
  13.         dataSource.setPassword("root");
  14.         return dataSource;
  15.     }
  16.  
  17.  
  18.     @Bean("dataSourceProxy")
  19.     public DataSourceProxy  dataSourceProxy(DruidDataSource  druidDataSource){
  20.         return new DataSourceProxy(druidDataSource);
  21.     }
  22.  
  23.     @Bean("sqlSessionFactoryBean")
  24.     public SqlSessionFactoryBean sqlSessionFactoryBean(DataSourceProxy dataSourceProxy) {
  25.         SqlSessionFactoryBean  sqlSessionFactoryBean=new SqlSessionFactoryBean();
  26.         sqlSessionFactoryBean.setDataSource(dataSourceProxy);
  27.         PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
  28.         ///sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResource("classpath:mybatis/mybatis-config.xml"));
  29.         sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
  30.         return sqlSessionFactoryBean;
  31.     }
  32.  
  33.     @Bean("sqlSessionFactory")
  34.     public SqlSessionFactory  sqlSessionFactory(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
  35.         return sqlSessionFactoryBean.getObject();
  36.     }
  37.  
  38.     @Bean("sqlSessionTemplate")
  39.    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory  sqlSessionFactory){
  40.         return new SqlSessionTemplate(sqlSessionFactory);
  41.    }
  42.  
  43.     /**
  44.      * 开启mapper扫描器
  45.      * @return
  46.      */
  47.     @Bean(name = "mapperScannerConfigurer")
  48.     public MapperScannerConfigurer mapperScannerConfigurer() {
  49.         MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
  50.         mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactoryBean");
  51.         mapperScannerConfigurer.setSqlSessionTemplateBeanName("sqlSessionTemplate");
  52.         mapperScannerConfigurer.setBasePackage("org.cn.fcw");
  53.         return mapperScannerConfigurer;
  54.     }
  55.  
  56.  
  57. }

         (3)业务代码

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

  1. @Service
  2. public class AccountService {
  3.  
  4.     private final  static Logger LOGGER= LoggerFactory.getLogger(AccountService.class);
  5.  
  6.     @Autowired
  7.     private IntegralMapper integralMapper;
  8.  
  9.     @Autowired
  10.     private AccountMapper  accountMapper;
  11.  
  12.  
  13.     @HystrixCommand(commandProperties = {
  14.                     @HystrixProperty(name = "execution.isolation.thread.timeoutInMilliseconds",value = "3000"),
  15.                     @HystrixProperty(name = "circuitBreaker.enabled",value = "true"),
  16.                     @HystrixProperty(name = "circuitBreaker.requestVolumeThreshold",value = "10"),
  17.                     @HystrixProperty(name = "circuitBreaker.sleepWindowInMilliseconds",value = "10000"),
  18.                     @HystrixProperty(name = "circuitBreaker.errorThresholdPercentage",value = "60")
  19.     })
  20.     public BaseResult reduceAccount(int  userId, BigDecimal  price){
  21.         LOGGER.info("reduceAccount-模拟远程调用超时");
  22.         try {
  23.             TimeUnit.SECONDS.sleep(5);
  24.         } catch (InterruptedException e) {
  25.             e.printStackTrace();
  26.         }
  27.         LOGGER.info("reduceAccount--------->线程睡眠结束");
  28.         AccountBean  accountBean = accountMapper.findByUserId(userId);
  29.         IntegralBean integralBean = integralMapper.findByUserId(userId);
  30.         LOGGER.info("扣款前accountBean账户信息####"+accountBean.toString());
  31.         LOGGER.info("扣款前integralBean积分信息###"+integralBean.toString());
  32.         BigDecimal amountTotal = accountBean.getAmount();
  33.         BigDecimal surplus = integralBean.getSurplus();
  34.         BigDecimal  integralRate=new BigDecimal(100),maxDeductRate=new BigDecimal("0.20");
  35.         BigDecimal  maxDeductAmount=maxDeductRate.multiply(price);
  36.         BigDecimal  surplusAmount= surplus.divide(integralRate);
  37.         //积分抵扣金额
  38.         BigDecimal  deductAmount=surplusAmount.doubleValue()>=maxDeductAmount.doubleValue()?maxDeductAmount:surplusAmount;
  39.         //账户扣款金额
  40.         BigDecimal  accountAmount=price.subtract(deductAmount);
  41.         if(amountTotal.doubleValue()<accountAmount.doubleValue()){
  42.             return BaseResult.toSuccess("账户余额不足");
  43.         }
  44.         accountBean.setAmount(amountTotal.subtract(accountAmount));
  45.         integralBean.setSurplus(surplusAmount.subtract(deductAmount).multiply(integralRate));
  46.         LOGGER.info("扣款后accountBean账户信息---"+accountBean.toString());
  47.         LOGGER.info("扣款后integralBean账户信息---"+integralBean.toString());
  48.         integralMapper.updateIntegral(integralBean);
  49.         accountMapper.updateAccount(accountBean);
  50.         return BaseResult.toSuccess("扣款成功");
  51.     }
  52.  
  53. }

        提供远程调用接口

  1. @RestController
  2. public class AccountController {
  3.  
  4.     private final  static Logger LOGGER= LoggerFactory.getLogger(AccountController.class);
  5.  
  6.     @Autowired
  7.     private AccountService accountService;
  8.  
  9.     @PostMapping("/provider/account/pay")
  10.     public BaseResult  pay(Integer userId,double price){
  11.         LOGGER.info("支付收到参数userId-"+userId+",price="+price);
  12.         return accountService.reduceAccount(userId,new BigDecimal(price));
  13.     }
  14. }

       (4)启动服务

资源管理rm向seata注册成功

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

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

  1. #yml文件加载的优先级，nacos的注册服务配置需要方放在bootstrap中
  2. spring:
  3.   cloud:
  4.     alibaba:
  5.       seata:
  6.         tx-service-group: nacos_consumer_tx
  7. mybatis:
  8.   config-location: classpath:mybatis/mybatis-config.xml
  9.   type-aliases-package: org.cn.fcw.bean
  10.   mapper-locations: classpath:mapper/*.xml
  11.  
  12. #feign开启 hystrix的服务降级功能
  13. feign:
  14.   hystrix:
  15.     enabled: true
  16. logging:
  17.   level:
  18.     org.cn.fcw.api.FeignServiceApi: debug
  19.

  bootstrap.yml配置

  1. server:
  2.   port: 9011
  3. appHost: 192.168.81.144
  4. spring:
  5.   application:
  6.     name: nacos_consumer
  7.   cloud:
  8.     nacos:
  9.       discovery:
  10.         server-addr: ${appHost}:8848
  11.       config:
  12.         server-addr: ${appHost}:8848
  13.

   配置seata代理数据源

  1. @Configuration
  2. public class SeataDataSourceConfig {
  3.  
  4.     @Bean("druidDataSource")
  5.     public DruidDataSource  druidDataSource(){
  6.         DruidDataSource dataSource=new DruidDataSource();
  7.         dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
  8.         dataSource.setUrl("jdbc:mysql://192.168.81.144:3306/cateen_db?useUnicode=true&characterEncoding=utf-8");
  9.         dataSource.setUsername("root");
  10.         dataSource.setPassword("root");
  11.         return dataSource;
  12.     }
  13.  
  14.  
  15.     @Bean("dataSourceProxy")
  16.     public DataSourceProxy  dataSourceProxy(DruidDataSource  druidDataSource){
  17.         return new DataSourceProxy(druidDataSource);
  18.     }
  19.  
  20.     @Bean("sqlSessionFactoryBean")
  21.     public SqlSessionFactoryBean sqlSessionFactoryBean(DataSourceProxy dataSourceProxy) {
  22.         SqlSessionFactoryBean  sqlSessionFactoryBean=new SqlSessionFactoryBean();
  23.         sqlSessionFactoryBean.setDataSource(dataSourceProxy);
  24.         PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
  25.         ///sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResource("classpath:mybatis/mybatis-config.xml"));
  26.         sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
  27.         return sqlSessionFactoryBean;
  28.     }
  29.  
  30.     @Bean("sqlSessionFactory")
  31.     public SqlSessionFactory  sqlSessionFactory(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
  32.         return sqlSessionFactoryBean.getObject();
  33.     }
  34.  
  35.     @Bean("sqlSessionTemplate")
  36.    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory  sqlSessionFactory){
  37.         return new SqlSessionTemplate(sqlSessionFactory);
  38.    }
  39.  
  40.     /**
  41.      * 开启mapper扫描器
  42.      * @return
  43.      */
  44.     @Bean(name = "mapperScannerConfigurer")
  45.     public MapperScannerConfigurer mapperScannerConfigurer() {
  46.         MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
  47.         mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactoryBean");
  48.         mapperScannerConfigurer.setSqlSessionTemplateBeanName("sqlSessionTemplate");
  49.         mapperScannerConfigurer.setBasePackage("org.cn.fcw");
  50.         return mapperScannerConfigurer;
  51.     }
  52.  
  53.  
  54. }

(2)编写下单业务

  远程调用支付接口

  1. @Component
  2. @FeignClient(value = "nacos-provider",fallback = FeignServiceApiImpl.class)
  3. public interface FeignServiceApi {
  4.  
  5.     @PostMapping(value = "/provider/account/pay",headers = {"Accept=application/json","Content-Type=application/x-www-form-urlencoded"})
  6.     BaseResult doPay(@RequestParam("userId") int userId,@RequestParam("price") double price);
  7.  
  8.  
  9. }
  1. @Component
  2. public class FeignServiceApiImpl implements FeignServiceApi {
  3.  
  4.     @Override
  5.     public BaseResult doPay(int userId, double price) {
  6.         return BaseResult.toFail("支付服务太累了，跑不动请稍后再试!");
  7.     }
  8. }

 下单服务层

  1. @Service
  2. public class BuyService {
  3.  
  4.     private final static Logger LOGGER = LoggerFactory.getLogger(BuyService.class);
  5.  
  6.     @Autowired
  7.     private BuyMapper buyMapper;
  8.  
  9.     @Autowired
  10.     private FeignServiceApi feignServiceApi;
  11.  
  12.     @GlobalTransactional
  13.     public BaseResult doBuy(int userId) throws Exception {
  14.         List<BuyBean> buyBeanList = buyMapper.findListByUserId(userId, 0);
  15.         LOGGER.info("buyBeanList---" + Arrays.toString(buyBeanList.toArray()));
  16.         if (buyBeanList.isEmpty()) {
  17.             return BaseResult.toFail("暂无可支付订单");
  18.         }
  19.         BuyBean buyBean = buyBeanList.get(0);
  20.         BigDecimal price = buyBean.getPrice();
  21.         ///调用支付
  22.         BaseResult baseResult = feignServiceApi.doPay(userId, price.doubleValue());
  23.         if (baseResult.getCode() == 500) {
  24.             throw new Exception(baseResult.getMsg());
  25.         }
  26.         //修改訂單狀態
  27.         buyBean.setStatus(1);
  28.         int i = buyMapper.updateBuyStatus(buyBean);
  29.         if (i != 1) {
  30.             return BaseResult.toSuccess("支付失败");
  31.         }
  32.         return BaseResult.toSuccess("支付成功");
  33.     }
  34. }

接口层

  1. @RestController
  2. public class BuyController {
  3.  
  4.     private final  static Logger LOGGER = LoggerFactory.getLogger(BuyController.class);
  5.     
  6.     @Autowired
  7.     private BuyService buyService;
  8.     
  9.     @GetMapping("/consumer/buyPay")
  10.     public BaseResult  buyAndPay(Integer  userId){
  11.         LOGGER.info("调用服务消费支付接口userId-"+userId);
  12.         BaseResult baseResult;
  13.         try {
  14.              baseResult = buyService.doBuy(userId);
  15.         } catch (Exception exception) {
  16.             exception.printStackTrace();
  17.             baseResult=BaseResult.toFail(exception.getMessage());
  18.         }
  19.         return baseResult;
  20.     }
  21. }

（3）启动下单服务

    

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

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

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  3.  
  4. -- ----------------------------
  5. -- Table structure for t_account
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `t_account`;
  8. CREATE TABLE `t_account`  (
  9.   `id` int(11) NOT NULL AUTO_INCREMENT,
  10.   `user_id` int(11) NULL DEFAULT NULL,
  11.   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  12.   `age` int(11) NULL DEFAULT NULL,
  13.   `amount` decimal(10, 2) NULL DEFAULT NULL,
  14.   `update_time` datetime(0) NULL DEFAULT NULL,
  15.   PRIMARY KEY (`id`) USING BTREE
  16. ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  17.  
  18. -- ----------------------------
  19. -- Records of t_account
  20. -- ----------------------------
  21. INSERT INTO `t_account` VALUES (1, 100, 'LiuCui', 32, 600.00, '2021-06-07 03:57:16');
  22.  
  23. -- ----------------------------
  24. -- Table structure for t_integral
  25. -- ----------------------------
  26. DROP TABLE IF EXISTS `t_integral`;
  27. CREATE TABLE `t_integral`  (
  28.   `id` int(11) NOT NULL AUTO_INCREMENT,
  29.   `user_id` int(11) NULL DEFAULT NULL,
  30.   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  31.   `surplus` decimal(6, 2) NULL DEFAULT NULL,
  32.   `update_time` datetime(0) NULL DEFAULT NULL,
  33.   PRIMARY KEY (`id`) USING BTREE
  34. ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  35.  
  36. -- ----------------------------
  37. -- Records of t_integral
  38. -- ----------------------------
  39. INSERT INTO `t_integral` VALUES (1, 100, 'LiuCui', 8000.00, '2021-06-07 03:57:16');
  40.  
  41. -- ----------------------------
  42. -- Table structure for undo_log
  43. -- ----------------------------
  44. DROP TABLE IF EXISTS `undo_log`;
  45. CREATE TABLE `undo_log`  (
  46.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  47.   `branch_id` bigint(20) NOT NULL,
  48.   `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  49.   `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  50.   `rollback_info` longblob NOT NULL,
  51.   `log_status` int(11) NOT NULL,
  52.   `log_created` datetime(0) NOT NULL,
  53.   `log_modified` datetime(0) NOT NULL,
  54.   PRIMARY KEY (`id`) USING BTREE,
  55.   UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
  56. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  57.  
  58. SET FOREIGN_KEY_CHECKS = 1;

 AccountBean 、IntergralBean 实体类

  1. @Data
  2. public class IntegralBean {
  3.  
  4.     private Integer id;
  5.  
  6.     private String  name;
  7.  
  8.     private  Integer userId;
  9.  
  10.     private BigDecimal  surplus;
  11.  
  12.     private Date  updateTime;
  13. }
  14.  
  15. @Data
  16. public class AccountBean {
  17.  
  18.     private Integer id;
  19.  
  20.     private Integer userId;
  21.  
  22.     private  String  name;
  23.  
  24.     private BigDecimal amount;
  25.  
  26.     private Date updateTime;
  27. }

 mapper 映射层

  1. /**
  2.  * @author Administrator
  3.  */
  4. @Mapper
  5. public interface IntegralMapper {
  6.  
  7.     /**
  8.      * 根据ID查询
  9.      * @param userId
  10.      * @return
  11.      */
  12.     @Result(id = true,property = "id",column = "id")
  13.     @Result(property = "name",column = "name")
  14.     @Result(property = "userId",column = "user_id")
  15.     @Result(property = "surplus",column = "surplus")
  16.     @Result(property = "updateTime",column = "update_time")
  17.     @Select({" select  *  from  t_integral where user_id=#{userId}"})
  18.     IntegralBean findByUserId(@Param("userId") int userId);
  19.  
  20.  
  21.     /**
  22.      * 扣除積分
  23.      * @param integralBean
  24.      * @return
  25.      */
  26.     @Update({"update t_integral set surplus=#{surplus},update_time=sysdate() where id=#{id}"})
  27.     int updateIntegral(IntegralBean integralBean);
  28. }
  29.  
  30.  
  31. @Mapper
  32. public interface AccountMapper {
  33.  
  34.     /**
  35.      * 查賬戶信息
  36.      * @param userId
  37.      * @return
  38.      */
  39.     @Result(id = true,property = "id",column = "id")
  40.     @Result(property = "userId",column = "user_id")
  41.     @Result(property = "name",column = "name")
  42.     @Result(property = "amount",column = "amount")
  43.     @Result(property = "updateTime",column = "update_time")
  44.     @Select({" select *  from  t_account  where user_id=#{userId}"})
  45.     AccountBean  findByUserId(@Param("userId")int userId);
  46.  
  47.     /**
  48.      * 更新賬戶
  49.      * @param accountBean
  50.      * @return
  51.      */
  52.     @Update({"update t_account set amount=#{amount},update_time=sysdate() where id=#{id}"})
  53.     int  updateAccount(AccountBean accountBean);
  54. }

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

  1. CREATE TABLE `t_buy`  (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `user_id` int(11) NULL DEFAULT NULL COMMENT '用户id',
  4.   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  5.   `goods` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  6.   `price` decimal(10, 2) NULL DEFAULT NULL,
  7.   `status` int(1) NULL DEFAULT NULL COMMENT '0 下单  1 支付',
  8.   `update_time` datetime(0) NULL DEFAULT NULL,
  9.   PRIMARY KEY (`id`) USING BTREE
  10. ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  11.  
  12. -- ----------------------------
  13. -- Records of t_buy
  14. -- ----------------------------
  15. INSERT INTO `t_buy` VALUES (1, 100, 'LiuCui', '香蕉', 20.00, 0, '2021-06-03 23:42:59');
  16. SET FOREIGN_KEY_CHECKS = 1;
  17.  
  18.  
  19. DROP TABLE IF EXISTS `undo_log`;
  20. CREATE TABLE `undo_log`  (
  21.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  22.   `branch_id` bigint(20) NOT NULL,
  23.   `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  24.   `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  25.   `rollback_info` longblob NOT NULL,
  26.   `log_status` int(11) NOT NULL,
  27.   `log_created` datetime(0) NOT NULL,
  28.   `log_modified` datetime(0) NOT NULL,
  29.   PRIMARY KEY (`id`) USING BTREE,
  30.   UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
  31. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  32.  
  33. SET FOREIGN_KEY_CHECKS = 1;

实体类BuyBean

  1. @Data
  2. public class BuyBean {
  3.  
  4.     private Integer id;
  5.  
  6.     private Integer userId;
  7.  
  8.     private String  name;
  9.  
  10.     private BigDecimal  price;
  11.  
  12.     private String  goods;
  13.  
  14.     private Integer  status;
  15.  
  16.     private Date updateTime;
  17. }

  mapper映射BuyMapper

  1. @Mapper
  2. public interface BuyMapper {
  3.  
  4.     /**
  5.      * 查詢用戶購買商品
  6.      * @param userId
  7.      * @return
  8.      */
  9.     @Result(id = true,property = "id",column = "id")
  10.     @Result(property = "userId",column = "user_id")
  11.     @Result(property = "name",column = "name")
  12.     @Result(property = "price",column = "price")
  13.     @Result(property = "goods",column = "goods")
  14.     @Result(property = "status",column = "status")
  15.     @Result(property = "updateTime",column = "update_time")
  16.     @Select({ "select  *  from t_buy  where  user_id=#{userId} and status=#{status} "})
  17.     List<BuyBean> findListByUserId(@Param("userId")Integer userId,@Param("status")int status);
  18.  
  19.  
  20.     /***
  21.      * 修改支付状态
  22.      * @param buyBean
  23.      * @return
  24.      */
  25.     @Update({"update t_buy  set status=#{status} where id=#{id} "})
  26.     int  updateBuyStatus(BuyBean buyBean);
  27.  
  28.     
  29. }

 

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

2022年最受欢迎的8大编程语言

为什么都说程序员找不到女朋友，但是身边程序猿的却没一个单身的

30岁以上你还死磕技术，别说拿高薪，可能你连饭碗都会保不住

程序员被开除，老板：“有你参与的项目全黄了！”

笑话：一个测试工程师走进一家酒吧

趣图：程序员头疼的4种原因

笑话：面试官：请拿出一段体现你水平的代码。我： sudo rm -rf /*面试官：这体现了你哪方面能力？

java精选：SSO单点登录】JWT入门概述&&残留的安全问题

网友说：做开发，不被领导喜欢怎么办？

网友说：我奉劝各位，一定不能在职场透露自己的家庭条件



所属网站分类: 技术文章 > 博客

作者：小泽圈儿郎

链接：http://www.javaheidong.com/blog/article/222573/717a2734e8487881ec07/

来源：java黑洞网

任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任

24 0
收藏该文
已收藏

评论内容：(最多支持255个字符)




程序员的那些事(new) 更多>

2022年最受欢迎的8大编程语言

为什么都说程序员找不到女朋友，但是身边程序猿的却没一个单身的

30岁以上你还死磕技术，别说拿高薪，可能你连饭碗都会保不住

程序员被开除，老板：“有你参与的项目全黄了！”

笑话：一个测试工程师走进一家酒吧

趣图：程序员头疼的4种原因

笑话：面试官：请拿出一段体现你水平的代码。我： sudo rm -rf /*面试官：这体现了你哪方面能力？

java精选：SSO单点登录】JWT入门概述&&残留的安全问题

网友说：做开发，不被领导喜欢怎么办？

网友说：我奉劝各位，一定不能在职场透露自己的家庭条件

电子书(new)

java jdk8 学习笔记

深入剖析Tomcat pdf下载

Java与模式 pdf下载

深入理解Java虚拟机：JVM高级特性与最佳实践(第2版) pdf 下载

数据结构与算法分析：Java语言描述(原书第3版) pdf  下载

Java并发编程实战 中文版 PDF扫描版[9MB]

数据结构与算法分析：Java语言描述(原书第3版) 中文完整pdf扫描版[47MB] 附源码

Hadoop权威指南 pdf下载

重构 改善既有代码的设计 pdf下载

JAVA编程思想 pdf下载

脚本(new)

输入一个数据n，计算斐波那契数列(Fibonacci)的第n个值

求 a+aa+aaa+.......+aaaaaaaaa=?

输出所有的水仙花数，把谓水仙花数是指一个数3位数，其各各位数字立方和等于其本身，

利用for循环打印 9*9 表?

编写程序求 1+3+5+7+……+99 的和值。

给定一个百分制的分数，输出相应的等级

编写程序，判断给定的某个年份是否是闰年

一些有趣的java小程序

菱形的图形生成器，2到100以内的质数-java实用小程序

java代码游戏编写

博客(new)

Android Studio App开发实战项目之广告轮播（附源码 可用于大作业）

【Java 基础】Java SPI 二 之 Java APT原理及APT实战 - 一步步教你写ButterKnife

Android程序设计之音乐播放器实现

原力计划

学生管理系统（Java实现）

书评 | 《深入理解高并发编程：JDK核心技术》

springboot整合sharding-jdbc实现分库分表详解

【熬夜送书 | 第三期】清华社赞助 | 《Java系列丛书》

Spring Security 6 配置方法，废弃 WebSecurityConfigurerAdapter

原力计划

视频教程(new)

由浅入深，全面解析ThreadLocal

超全面讲解Spring Cloud Alibaba技术栈，java视频教程下载

三天全面系统学习ZooKeeper

2020年JavaSE强化系列教程泛型全剖析，java视频教程下载

详讲编码与解码，灵活解决乱码难题，java 视频教程下载

2020年抗疫之作java基础进阶13天

系统学习让你轻松定义java类加载器-java视频教程下载

真小白福音，完全从0带你掌握计算机与编程基础

java零基础入门到精通（2019版）

2020JAVA基础-深入系统的学习数据结构与算法

项目实战(new)

JSP+SSM+Mysql实现的学生成绩管理系统

JSP+Structs+JDBC+mysql实现的诚欣电子商城

JSP+SSH+Mysql+DBCP实现的租车系统

博客系统java项目

这是一个在线考试系统,java项目实战源码下载

简单状态机

Java Web编程课程中开发的项目

银行账户项目样本 用Java完成的一个简单项目

一个使用Java开发在线概述用户管理平台

JAVAWeb项目 JAVA-WEB开发的一些练手项目，这些也适合初学者进行练习

问答(new)

启动一个使用Rapid miner的java项目

如何通过 Apache POI 3.9 从 xls/xlsx 读取时间格式？

如何将自定义菜单栏项放置在地图菜单中的所需位置

我们如何在 Jess 规则中从终端选择默认值

无法在远程系统上使用 Windows 身份验证连接到 MS sql 服务器

Spring - 如何选择依赖bean的属性？

Java - MousePress Hold 和 Release 事件侦听器

将分页与 Struts 2 和 Hibernate 一起使用

Java：运动，需要放慢速度

如何正确调用同一个类中的方法内的方法？

游戏(new)

2人对战游戏，java游戏

java小游戏-数学英雄

java游戏-蛙人

经典骰子游戏,java游戏源码下载

一些java小游戏

java小游戏-抓住这只球

二十一点，java小游戏源码下载

java小游戏集合下载

java五子棋游戏源码下载

java贪吃蛇游戏

其他资源(new)

某公司的雇员分为以下若干类： Employee：这是所有员工总的父类，属性：员工的姓名和生日月份。 方法：getSalary(int month) 根据参数月份来确定工资，如果该月员工过生日，

判断随机整数是否是素数 产生100个0-999之间的随机整数，然后判断这100个随机整数哪些是素数，哪些不是？

完成九宫格程序

. 定义一个int型的一维数组，包含10个元素，分别赋一些随机整数，然后求出所有元素的最大值

歌德巴赫猜想,任何一个大于六的偶数可以拆分成两个质数的和

在屏幕上打印出n行的金字塔图案，如，若n=5,则图案如下

计算圆周率

利用程序输出如下图形:

求 2/1+3/2+5/3+8/5+13/8.....前20项之和？

Nutch入门教程 pdf下载