SEATA整合sharding-jdbc

目录

1.seata-order- sample模块

(1). application.properties

(2). seata.conf

2. seata-product-sample模块

(1). application.properties

(2). seata.conf

3.添加查询功能

4.本文代码及接口描述：

5.其它重要文章：

本文使用的初始源代码来自于：seata-samples/springcloud-seata-sharding-jdbc-mybatis-plus-samples at master · seata/seata-samples · GitHub

但是在运行上面代码过程中，发现老是运行失败，出现各种各样的错误，最后发现是因为配置的原因，初始代码中给的配置信息不全，这里给出完整的配置信息

订单服务、库存服务、Seata服务3个服务都需要注册到nacos中，seata服务的配置信息也要导入到nacos，Seata服务安装步骤：
centos7 安装seata + springboot配置_centos7安装seata_困知勉行1985的博客-CSDN博客

1.seata-order- sample模块

(1). application.properties

#订单微服务服务信息
server.port=8001
spring.application.name=order-server

#订单微服务注册中心
spring.cloud.nacos.discovery.server-addr=127.0.0.1:8848
# 指定nacos命名空间，注意：这里的命名空间name与id是相同的
spring.cloud.nacos.discovery.namespace=e49265e9-1527-41c5-a2aa-e710b6da2b82
spring.cloud.nacos.discovery.group=SEATA_GROUP
spring.cloud.nacos.discovery.username=nacos
spring.cloud.nacos.discovery.password=nacos

#sharding-jdbc数据源配置
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/seata_order_0?serverTimezone=UTC&characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/seata_order_1?serverTimezone=UTC&characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

#sharding-jdbc分片配置
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{0..2}
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{id % 3}

#sharding-jdbc显示最终SQL
spring.shardingsphere.props.sql.show=true

#mybatis-plus配置
mybatis-plus.configuration.map-underscore-to-camel-case=true
#logging.level.io.seata=debug

# SEATA配置
seata.enabled=true
# ${spring.application.name}
seata.application-id=order-server
#大坑
#seata在nacos中存储的配置信息中，有这么一条service.vgroupMapping.default_tx_group=default，下面的属性就取default_tx_group
seata.tx-service-group=default_tx_group
#spring.cloud.alibaba.seata.tx-service-group=default_tx_group
seata.config.type=nacos
#指定nacos地址
seata.config.nacos.server-addr=127.0.0.1:8848
# 指定nacos命名空间，注意：这里的命名空间name与id是相同的
seata.config.nacos.namespace=e49265e9-1527-41c5-a2aa-e710b6da2b82
seata.config.nacos.group=SEATA_GROUP
seata.config.nacos.username=nacos
seata.config.nacos.password=nacos
seata.config.nacos.data-id=seataServer.properties
seata.enable-auto-data-source-proxy=false
spring.main.allow-bean-definition-overriding=true

# SEATA配置
#seata.enabled=true
#seata.application-id=order-server
#seata.tx-service-group=default_tx_group
#seata.config.type=nacos
#seata.config.nacos.server-addr=127.0.0.1:8848
##seata.config.nacos.dataId="seataServer.properties"
#seata.config.nacos.namespace="seata-test"
#seata.config.nacos.group=SEATA_GROUP
#seata.config.nacos.username=nacos
#seata.config.nacos.password=nacos
#
#seata.registry.type=nacos
#seata.registry.nacos.application=seata-server
#seata.registry.nacos.cluster=default
#seata.registry.nacos.server-addr=127.0.0.1:8848
#seata.registry.nacos.group=SEATA_GROUP
#seata.registry.nacos.namespace=e49265e9-1527-41c5-a2aa-e710b6da2b82
#seata.registry.nacos.username=nacos
#seata.registry.nacos.password=nacos
#
#seata.service.vgroup-mapping.default_tx_group=default
#seata.service.enable-degrade=false
#seata.service.disable-global-transaction=false
#
#seata.enable-auto-data-source-proxy=false

(2). seata.conf

client {
    application.id = order-server
    #大坑
    #seata在nacos中存储的配置信息中，有这么一条service.vgroupMapping.default_tx_group=default，下面的属性就取default_tx_group
    transaction.service.group = default_tx_group
}

Note: 奇怪的是这里的application.id可以设置成seata的服务名my-seata-server，也可以设置成应用的服务名order-server(初始代码里的设置)，都可以运行，都可以正常的下订单减库存，这个地方可以研究一下是怎么回事。

2. seata-product-sample模块

(1). application.properties

#产品微服务服务信息
server.port=8002
spring.application.name=product-server

#产品微服务注册中心
spring.cloud.nacos.discovery.server-addr=127.0.0.1:8848
# 指定nacos命名空间，注意：这里的命名空间name与id是相同的
spring.cloud.nacos.discovery.namespace=e49265e9-1527-41c5-a2aa-e710b6da2b82
spring.cloud.nacos.discovery.group=SEATA_GROUP
spring.cloud.nacos.discovery.username=nacos
spring.cloud.nacos.discovery.password=nacos

#数据源配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/seata_product?serverTimezone=UTC&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456

#mybatis-plus配置
mybatis-plus.configuration.map-underscore-to-camel-case=true
logging.level.com.qmp=debug

# seata 分布式事务配置
# 指定seata在nacos的注册服务名
seata.application-id=product-server
#大坑
#seata在nacos中存储的配置信息中，有这么一条service.vgroupMapping.default_tx_group=default，下面的属性就取default_tx_group
seata.tx-service-group=default_tx_group
#spring.cloud.alibaba.seata.tx-service-group=default
seata.config.type=nacos
#指定nacos地址
seata.config.nacos.server-addr=127.0.0.1:8848
seata.config.nacos.group=SEATA_GROUP
# 指定nacos命名空间，注意：这里的命名空间name与id是相同的
seata.config.nacos.namespace=e49265e9-1527-41c5-a2aa-e710b6da2b82
seata.config.nacos.data-id=seataServer.properties
seata.config.nacos.username=nacos
seata.config.nacos.password=nacos
spring.main.allow-bean-definition-overriding=true

## seata 分布式事务配置
#seata.application-id=my-seata-server
##seata.application-id=product-server
#seata.tx-service-group=default_tx_group
#
#seata.config.type=nacos
#seata.config.nacos.server-addr=127.0.0.1:8848
##seata.config.nacos.dataId="seataServer.properties"
#seata.config.nacos.namespace="seata-test"
#seata.config.nacos.group=SEATA_GROUP
#seata.config.nacos.username=nacos
#seata.config.nacos.password=nacos
#
#seata.registry.type=nacos
#seata.registry.nacos.application=product-server
#seata.registry.nacos.server-addr=127.0.0.1:8848
#seata.registry.nacos.group=SEATA_GROUP
#seata.registry.nacos.cluster=default
#seata.registry.nacos.namespace=e49265e9-1527-41c5-a2aa-e710b6da2b82
#seata.registry.nacos.username=nacos
#seata.registry.nacos.password=nacos
#
#seata.service.vgroup-mapping.default_tx_group=default
#seata.service.disable-global-transaction=false
#
#seata.client.rm.report-success-enable=false

(2). seata.conf

client {
    application.id = product-server
    #大坑
    #seata在nacos中存储的配置信息中，有这么一条service.vgroupMapping.default_tx_group=default，下面的属性就取default_tx_group
    transaction.service.group = default_tx_group
}

Note: 奇怪的是这里的application.id可以设置成seata的服务名my-seata-server，也可以设置成应用的服务名product-server(初始代码里的设置)，都可以运行，都可以正常的下订单减库存，这个地方可以研究一下是怎么回事。 

3.添加查询功能

源代码中只有下单和删减库存操作，在这里增加一个查询所有订单的功能。

1. seata-order-sample模块 -> OrderController.java 文件中添加：

    @GetMapping("/seata/listall")
    public List<Order> seataListAll(){
        List<Order> list = orderService.listAll();
        return list;
    }

2. seata-order-sample模块 -> OrderService.java 文件中添加：

    /**
     * 查询所有订单
     * @return
     */
    public List<Order> listAll() {
        //查询所有订单
        QueryWrapper<Order> queryWrapper = new QueryWrapper<Order>();
        return orderMapper.selectList(queryWrapper);
    }

 然后访问该接口 http://localhost:8001/seata/listall

 程序报错：

2022-05-11 15:11:14.551 ERROR 17632 --- [nio-8001-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Error attempting to get column 'create_time' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: getObject with type
; getObject with type; nested exception is java.sql.SQLFeatureNotSupportedException: getObject with type] with root cause

java.sql.SQLFeatureNotSupportedException: getObject with type
    at org.apache.shardingsphere.shardingjdbc.jdbc.unsupported.AbstractUnsupportedOperationResultSet.getObject(AbstractUnsupportedOperationResultSet.java:221) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:38) ~[mybatis-3.5.6.jar:3.5.6]
    at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:28) ~[mybatis-3.5.6.jar:3.5.6]
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85) ~[mybatis-3.5.6.jar:3.5.6]
.............................................................................................................................................
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.39.jar:9.0.39]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_291]

root cause是什么？

Mybatis-plus在处理ResultSet的时候，使用默认的LocalDateTimeTypeHandler无法处理LocalDateTime类型的java数据，需要自己重写LocalDateTimeTypeHandler

package io.seata.order.config;

import com.github.pagehelper.util.StringUtil;
import com.mysql.cj.jdbc.CallableStatement;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class CustomLocalDateTimeTypeHandler extends BaseTypeHandler<LocalDateTime> {

    private final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType)
            throws SQLException {
        if (parameter != null) {
            ps.setString(i, dateTimeFormatter.format(parameter));
        }
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String target = rs.getString(columnName);
        if (StringUtil.isEmpty(target)) {
            return null;
        }
        return LocalDateTime.parse(target, dateTimeFormatter);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String target = rs.getString(columnIndex);
        if (StringUtil.isEmpty(target)) {
            return null;
        }
        return LocalDateTime.parse(target, dateTimeFormatter);
    }

    @Override
    public LocalDateTime getNullableResult(java.sql.CallableStatement callableStatement, int i) throws SQLException {
        String target = callableStatement.getString(i);
        if (StringUtil.isEmpty(target)) {
            return null;
        }
        return LocalDateTime.parse(target, dateTimeFormatter);
    }
}

修改映射类，使用自定义的CustomLocalDateTimeTypeHandler。

1.实体类需要加个注解

@TableName(autoResultMap = true)

2.对应的属性也需要加注解

@TableField(value = "create_time",typeHandler = CustomLocalDateTimeTypeHandler.class)

package io.seata.order.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import io.seata.order.config.CustomLocalDateTimeTypeHandler;
import io.seata.order.config.MyLocalDateTimeTypeHandler;
import lombok.Data;

import java.time.LocalDateTime;
import java.util.Date;

@Data
@TableName(value = "order_info", autoResultMap = true)
public class Order {

    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    private String orderName;

    private Long productId;

    private Integer buyNum;

    @TableField(value = "create_time",typeHandler = CustomLocalDateTimeTypeHandler.class)
    private LocalDateTime createTime;

    @TableField(value = "update_time",typeHandler = CustomLocalDateTimeTypeHandler.class)
    private LocalDateTime updateTime;

}

参考文章： 

MyBatis Plus 自动类型转换之TypeHandler - 周世元ISO8859-1 - 博客园 (cnblogs.com)

4.本文代码及接口描述：

由于对代码做了一些修改，实验了一些功能，所以重新建立了代码仓库，后续会继续研究分库分表情况下的连表查询，分页查询等。

代码仓库中有对微服务调用及分库分表调用的详细描述，请自行参考

https://gitee.com/brucewang1985/seata-sharding-jdbc.git

5.其它重要文章：

重要参考文章1--原理以及代码解析：

从源码看ShardingSphere设计-JDBC篇 - 简书 (jianshu.com)

重要参考文章2 -- Mybatis-plus各种查询方法：

(1条消息) Mybatis-plus各种查询方法_会飞地太阳的博客-CSDN博客_mybatisplus查询

参考文章：关于分库分表

基于ShardingSphere-JDBC的MySQL分库分表_shardingjdbc分页查询_1244497026的博客-CSDN博客