0 前言

0.1 Sharding-Sphere

Sharding-Sphere是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈执虹，它由Sharding-JDBC榛了、Sharding-Proxy和Sharding-Sidecar這3款相互獨(dú)立的產(chǎn)品組成停巷。他們均提供標(biāo)準(zhǔn)化的數(shù)據(jù)分片硅卢、讀寫分離仁期、柔性事務(wù)和數(shù)據(jù)治理功能，可適用于如Java同構(gòu)懂盐、異構(gòu)語言褥赊、容器、云原生等各種多樣化的應(yīng)用場景莉恼。
Sharding-Sphere定位為關(guān)系型數(shù)據(jù)庫中間件拌喉，旨在充分合理地在分布式的場景下利用關(guān)系型數(shù)據(jù)庫的計(jì)算和存儲(chǔ)能力翼岁，而并非實(shí)現(xiàn)一個(gè)全新的關(guān)系型數(shù)據(jù)庫。

0.2 Sharding-JDBC

我們這邊主要用的是里面的Sharding-JDBC：

定位為輕量級(jí)Java框架司光，在Java的JDBC層提供的額外服務(wù)琅坡。 它使用客戶端直連數(shù)據(jù)庫，以jar包形式提供服務(wù)残家，無需額外部署和依賴榆俺，可理解為增強(qiáng)版的JDBC驅(qū)動(dòng)，完全兼容JDBC和各種ORM框架坞淮。

• 適用于任何基于Java的ORM框架茴晋，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
• 基于任何第三方的數(shù)據(jù)庫連接池回窘，如：DBCP, C3P0, BoneCP, Druid, HikariCP等诺擅。
• 支持任意實(shí)現(xiàn)JDBC規(guī)范的數(shù)據(jù)庫。目前支持MySQL啡直，Oracle烁涌，SQLServer和PostgreSQL。

系統(tǒng)架構(gòu)圖

1. 配置
   1.1 springboot+mybatis+druid
   略
   1.2 sharding-spheres
   pom.xml引入：

<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0.M2</version>
</dependency>

1.3 分片設(shè)計(jì)

邏輯表結(jié)構(gòu)：

CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)

按照order_id進(jìn)行范圍分片（order_id < 100的在shard1酒觅，其他的在shard2）撮执；
再按user_id進(jìn)行hash分片（user_id % 2 分表落在t_order_0和t_order_1上）

數(shù)據(jù)庫結(jié)構(gòu)如下圖：

數(shù)據(jù)庫結(jié)構(gòu)

application.properties

server.port=9001

mybatis.mapper-locations=classpath:mapper/*.xml

sharding.jdbc.datasource.names=ds0,ds1

sharding.jdbc.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/shard1?useSSL=false
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=password

sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/shard2?useSSL=false
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=password

sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..1}
sharding.jdbc.config.sharding.tables.t_order.database-strategy.standard.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.joey.springboot.shardingdemo.conf.OrderShardingAlgorithm
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{user_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id

sharding.jdbc.config.sharding.props.sql.show=true

具體的配置參數(shù)含義見官方文檔
http://shardingsphere.io/document/current/cn/manual/sharding-jdbc/configuration/config-spring-boot/

其中OrderShardingAlgorithm配置類為：

package com.joey.springboot.shardingdemo.conf;

import io.shardingsphere.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

public class OrderShardingAlgorithm implements PreciseShardingAlgorithm {

    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
        if(shardingValue.getValue().compareTo(100) > 0) {
            return "ds1";
        }
        return "ds0";
    }
}

2. 測試代碼

controller：

package com.joey.springboot.shardingdemo.controller;

import com.joey.springboot.shardingdemo.dao.Order;
import com.joey.springboot.shardingdemo.service.OrderService;
import io.shardingsphere.core.jdbc.core.connection.ShardingConnection;
import io.shardingsphere.core.jdbc.core.transaction.TransactionLoader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.sql.SQLException;

@RestController
public class OrderController {

    @Autowired
    OrderService orderService;

    @Autowired
    DataSource dataSource;

    @RequestMapping("/order/{id}")
    public Order getOrderById(@PathVariable int id) {
        System.out.println(orderService.getOrderById(id));
        return orderService.getOrderById(id);
    }

    @RequestMapping(value="/order", method= RequestMethod.POST)
    @ResponseBody
    @Transactional(rollbackFor = Exception.class)
    public Order addOrder(@RequestBody Order order) {
        orderService.addOrder(order);
        Order order1 = new Order();
        order1.setOrderId(order.getOrderId()+100);
        order1.setUserId(order.getUserId()+1);
        orderService.addOrder(order1);
        return orderService.getOrderById(order.getOrderId());
    }
}

service：

package com.joey.springboot.shardingdemo.service;

import com.joey.springboot.shardingdemo.dao.Order;
import com.joey.springboot.shardingdemo.dao.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class OrderService {

    @Autowired
    private OrderMapper orderMapper;

    public Order getOrderById(int id) {
        return orderMapper.selectByPrimaryKey(id);
    }


    public int addOrder(Order order) {
        return orderMapper.insert(order);
    }
}

model：

package com.joey.springboot.shardingdemo.dao;

public class Order {
    private Integer orderId;

    private Integer userId;

    public Integer getOrderId() {
        return orderId;
    }

    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }
}

mapper：

package com.joey.springboot.shardingdemo.dao.mapper;

import com.joey.springboot.shardingdemo.dao.Order;

public interface OrderMapper {
    int deleteByPrimaryKey(Integer orderId);

    int insert(Order record);

    int insertSelective(Order record);

    Order selectByPrimaryKey(Integer orderId);

    int updateByPrimaryKeySelective(Order record);

    int updateByPrimaryKey(Order record);
}

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.joey.springboot.shardingdemo.dao.mapper.OrderMapper">
  <resultMap id="BaseResultMap" type="com.joey.springboot.shardingdemo.dao.Order">
    <id column="order_id" jdbcType="INTEGER" property="orderId" />
    <result column="user_id" jdbcType="INTEGER" property="userId" />
  </resultMap>
  <sql id="Base_Column_List">
    order_id, user_id
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_order
    where order_id = #{orderId,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_order
    where order_id = #{orderId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.joey.springboot.shardingdemo.dao.Order">
    insert into t_order (order_id, user_id)
    values (#{orderId,jdbcType=INTEGER}, #{userId,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.joey.springboot.shardingdemo.dao.Order">
    insert into t_order
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="orderId != null">
        order_id,
      </if>
      <if test="userId != null">
        user_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="orderId != null">
        #{orderId,jdbcType=INTEGER},
      </if>
      <if test="userId != null">
        #{userId,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.joey.springboot.shardingdemo.dao.Order">
    update t_order
    <set>
      <if test="userId != null">
        user_id = #{userId,jdbcType=INTEGER},
      </if>
    </set>
    where order_id = #{orderId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.joey.springboot.shardingdemo.dao.Order">
    update t_order
    set user_id = #{userId,jdbcType=INTEGER}
    where order_id = #{orderId,jdbcType=INTEGER}
  </update>
</mapper>

這樣可以方便地使用@Transactional注解實(shí)現(xiàn)弱XA

• 完全支持非跨庫事務(wù)，例如：僅分表舷丹，或分庫但是路由的結(jié)果在單庫中抒钱。
• 完全支持因邏輯異常導(dǎo)致的跨庫事務(wù)。例如：同一事務(wù)中颜凯，跨兩個(gè)庫更新谋币。更新完畢后，拋出空指針症概，則兩個(gè)庫的內(nèi)容都能回滾蕾额。
• 不支持因網(wǎng)絡(luò)、硬件異常導(dǎo)致的跨庫事務(wù)穴豫。例如：同一事務(wù)中凡简，跨兩個(gè)庫更新，更新完畢后精肃、未提交之前，第一個(gè)庫宕機(jī)帜乞，則只有第二個(gè)庫數(shù)據(jù)提交司抱。

3.問題

3.1問題描述

版本號(hào)
springboot 1.5.10.RELEASE
sharding-jdbc 3.0.0.M2

用spring自帶的@Transactional 報(bào)錯(cuò)：

Could not dispatch event: io.shardingsphere.core.transaction.listener.TransactionListener@6e43ddd6 to public void io.shardingsphere.core.transaction.listener.TransactionListener.listen(io.shardingsphere.core.transaction.event.TransactionEvent) throws java.sql.SQLException

3.2問題原因

觸發(fā)事務(wù)的時(shí)候會(huì)調(diào)用TransactionListener.listen中的：

TransactionManager transactionManager = TransactionContextHolder.get().getTransactionManager();

從ThreadLocal中獲得的TransactionContext中的transactionManager為空
springboot啟動(dòng)的時(shí)候，加載了TransactionLoader黎烈，里面的doXaTransactionConfiguration是set了ThreadLocal中的transactionManager习柠，但是set只對當(dāng)前線程的ThreadLocal變量有效匀谣，所以導(dǎo)致在有新線程起來的時(shí)候再初始化TransactionContext時(shí)，transactionManager就為空资溃。

3.3 問題解決

修改源碼中的TransactionContext類

    private TransactionManager transactionManager = new WeakXaTransactionManager();