本站消息

站长简介/公众号


站长简介:逗比程序员,理工宅男,前每日优鲜python全栈开发工程师,利用周末时间开发出本站,欢迎关注我的微信公众号:幽默盒子,一个专注于搞笑,分享快乐的公众号

  价值13000svip视频教程,java大神匠心打造,零基础java开发工程师视频教程全套,基础+进阶+项目实战,包含课件和源码

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


+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

2021-05(18)

2021-06(40)

2021-07(5)

seata分布式事务处理技术

发布于2021-06-12 15:04     阅读(920)     评论(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. -- Table structure for global_table
  19. -- ----------------------------
  20. DROP TABLE IF EXISTS `global_table`;
  21. CREATE TABLE `global_table` (
  22. `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  23. `transaction_id` bigint(20) NULL DEFAULT NULL,
  24. `status` tinyint(4) NOT NULL,
  25. `application_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  26. `transaction_service_group` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  27. `transaction_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  28. `timeout` int(11) NULL DEFAULT NULL,
  29. `begin_time` bigint(20) NULL DEFAULT NULL,
  30. `application_data` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  31. `gmt_create` datetime(0) NULL DEFAULT NULL,
  32. `gmt_modified` datetime(0) NULL DEFAULT NULL,
  33. PRIMARY KEY (`xid`) USING BTREE,
  34. INDEX `idx_gmt_modified_status`(`gmt_modified`, `status`) USING BTREE,
  35. INDEX `idx_transaction_id`(`transaction_id`) USING BTREE
  36. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  37. -- ----------------------------
  38. -- Table structure for lock_table
  39. -- ----------------------------
  40. DROP TABLE IF EXISTS `lock_table`;
  41. CREATE TABLE `lock_table` (
  42. `row_key` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  43. `xid` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  44. `transaction_id` bigint(20) NULL DEFAULT NULL,
  45. `branch_id` bigint(20) NOT NULL,
  46. `resource_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  47. `table_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  48. `pk` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  49. `gmt_create` datetime(0) NULL DEFAULT NULL,
  50. `gmt_modified` datetime(0) NULL DEFAULT NULL,
  51. PRIMARY KEY (`row_key`) USING BTREE,
  52. INDEX `idx_branch_id`(`branch_id`) USING BTREE
  53. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  54. 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. store {
  26. mode = "db"
  27. publicKey = ""
  28. file {
  29. dir = "sessionStore"
  30. maxBranchSessionSize = 16384
  31. maxGlobalSessionSize = 512
  32. fileWriteBufferCacheSize = 16384
  33. sessionReloadReadSize = 100
  34. flushDiskMode = async
  35. }
  36. db {
  37. DruidDataSource(druid)/BasicDataSource(dbcp)/HikariDataSource(hikari) etc.
  38. datasource = "druid"
  39. dbType = "mysql"
  40. driverClassName = "com.mysql.jdbc.Driver"
  41. url = "jdbc:mysql://192.168.81.144:3306/seata?rewriteBatchedStatements=true"
  42. user = "root"
  43. password = "root"
  44. minConn = 5
  45. maxConn = 100
  46. globalTable = "global_table"
  47. branchTable = "branch_table"
  48. lockTable = "lock_table"
  49. queryLimit = 100
  50. maxWait = 5000
  51. }
  52. }
  53. service {
  54. vgroupMapping.nacos_consumer_tx = "bp_tx"
  55. bp_tx.grouplist = "192.168.81.144:8091"
  56. enableDegrade = false
  57. #disable seata
  58. disableGlobalTransaction = false
  59. }

      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. config {
  17. type = "file"
  18. nacos {
  19. serverAddr = "192.168.81.144:8848"
  20. namespace = ""
  21. group = "SEATA_GROUP"
  22. username = "nacos"
  23. password = "nacos"
  24. dataId = "seataServer.properties"
  25. }
  26. file {
  27. name = "file.conf"
  28. }
  29. }

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

         (3)业务代码

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

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

        提供远程调用接口

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

       (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. #feign开启 hystrix的服务降级功能
  12. feign:
  13. hystrix:
  14. enabled: true
  15. logging:
  16. level:
  17. org.cn.fcw.api.FeignServiceApi: debug

  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

   配置seata代理数据源

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

(2)编写下单业务

  远程调用支付接口

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

 下单服务层

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

接口层

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

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

 AccountBean 、IntergralBean 实体类

  1. @Data
  2. public class IntegralBean {
  3. private Integer id;
  4. private String name;
  5. private Integer userId;
  6. private BigDecimal surplus;
  7. private Date updateTime;
  8. }
  9. @Data
  10. public class AccountBean {
  11. private Integer id;
  12. private Integer userId;
  13. private String name;
  14. private BigDecimal amount;
  15. private Date updateTime;
  16. }

 mapper 映射层

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

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. -- Records of t_buy
  13. -- ----------------------------
  14. INSERT INTO `t_buy` VALUES (1, 100, 'LiuCui', '香蕉', 20.00, 0, '2021-06-03 23:42:59');
  15. SET FOREIGN_KEY_CHECKS = 1;
  16. DROP TABLE IF EXISTS `undo_log`;
  17. CREATE TABLE `undo_log` (
  18. `id` bigint(20) NOT NULL AUTO_INCREMENT,
  19. `branch_id` bigint(20) NOT NULL,
  20. `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  21. `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  22. `rollback_info` longblob NOT NULL,
  23. `log_status` int(11) NOT NULL,
  24. `log_created` datetime(0) NOT NULL,
  25. `log_modified` datetime(0) NOT NULL,
  26. PRIMARY KEY (`id`) USING BTREE,
  27. UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
  28. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  29. SET FOREIGN_KEY_CHECKS = 1;

实体类BuyBean

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

  mapper映射BuyMapper

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

 

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



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

作者:小泽圈儿郎

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

来源:java黑洞网

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

24 0
收藏该文
已收藏

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