ruoyi-vue分离版3.8.3整合shardingsphere分库分表5.2.1

        本文介绍基于yaml配置文件的全配置分库分表引擎整合，无需java代码编写配置config，下载源代码目的是为了要里面的yaml配置文件，并不需要安装编译shardingsphere。

一、官网下载apache-shardingsphere-5.2.1-src.zip，里面的test项目下面有很多yaml配置文件可以参考。

二、更改ruoyi-admin下面的application-druid.yml并创建application-sharding.yml，在application-druid.yml最下面增加：

spring:
    datasource:

                。。。。。。（原有配置省略）

    shardingsphere:
        configLocation: application-sharding.yml

三、这是我的application-sharding.yml配置文件，请大家参考：

# 分库分表配置
dataSources:
  ds_0:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: root
    maxTotal: 100
  ds_1:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: root
    maxTotal: 100

rules:
  - !TRANSACTION
    defaultType: LOCAL
  - !SHARDING
    tables:
      tmp_order:
        actualDataNodes: ds_${0..1}.tmp_order_${0..1}
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: tmp_order_inline
        keyGenerateStrategy:
          column: order_id
          keyGeneratorName: snowflake
      tmp_order_item:
        actualDataNodes: ds_${0..1}.tmp_order_item_${0..1}
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: tmp_order_item_inline
        keyGenerateStrategy:
          column: order_item_id
          keyGeneratorName: snowflake
      sys_order_tmp:
        actualDataNodes: ds_${0..1}.sys_order_tmp_${0..1}
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: sys_order_tmp_inline
        keyGenerateStrategy:
          column: order_id
          keyGeneratorName: snowflake
    bindingTables:
      - tmp_order,tmp_order_item
    broadcastTables:
      - t_address
    defaultDatabaseStrategy:
      standard:
        shardingColumn: user_id
        shardingAlgorithmName: database_inline
    defaultTableStrategy:
      none:

    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${user_id % 2}
      database_id_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${id % 2}
      tmp_order_inline:
        type: INLINE
        props:
          algorithm-expression: tmp_order_${order_id % 2}
      tmp_order_item_inline:
        type: INLINE
        props:
          algorithm-expression: tmp_order_item_${order_id % 2}
      sys_order_tmp_inline:
        type: INLINE
        props:
          algorithm-expression: sys_order_tmp_${order_id % 2}

    keyGenerators:
      snowflake:
        type: SNOWFLAKE

props:
  sql-show: true

四、相关表结构

CREATE TABLE `tmp_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `tmp_order_item` (
  `order_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

五、修改两个pom.xml文件，增加对shardingsphere的支持

1、RuoYi-Vue-master\pom.xml

增加：

<sharding.version>5.2.1</sharding.version>
<hikari-cp.version>3.4.2</hikari-cp.version>
<snakeyaml.version>1.33</snakeyaml.version>

<!-- 分库分表引擎 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>${sharding.version}</version>
</dependency>

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>${hikari-cp.version}</version>
</dependency>

<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>${snakeyaml.version}</version>
</dependency>

2、ruoyi-framework\pom.xml

增加：

<!-- 分库分表引擎 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

六、ruoyi-vue相关代码修改

1、增加ruoyi-framework\src\main\java\com\ruoyi\framework\config\properties\ShardingProperties.java

@Configuration
public class ShardingProperties {
    @Value("${spring.shardingsphere.configLocation}")
    private String configLocation;

    public String getConfigLocation() {
        return configLocation;
    }
}

2、修改：ruoyi-common\src\main\java\com\ruoyi\common\DataSourceType.java

增加：SHARDING

public enum DataSourceType
{
    /**
     * 主库
     */
    MASTER,

    /**
     * 从库
     */
    SLAVE,

    /**
     * 分库分表
     */
    SHARDING
}

3、修改ruoyi-framework\src\main\java\com\ruoyi\framework\DruidConfig.java

增加：

@Bean
public DataSource shardingDataSource(ShardingProperties shardingProperties) throws Exception
{
    ClassPathResource classPathResource = new ClassPathResource(shardingProperties.getConfigLocation());
    InputStream inputStream = classPathResource.getInputStream();
    File tmpFile = File.createTempFile(shardingProperties.getConfigLocation(), ".tmp");
    Files.copy(inputStream, tmpFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
    DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(tmpFile);
    return dataSource;
}

修改：

@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource(DataSource masterDataSource)
{
    Map<Object, Object> targetDataSources = new HashMap<>();
    targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
    setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
    setDataSource(targetDataSources, DataSourceType.SHARDING.name(), "shardingDataSource");
    return new DynamicDataSource(masterDataSource, targetDataSources);
}

七、下面对若依代码生成的主子表逻辑代码进行修改：

1、TmpOrderMapper.java新增

/**
 * 单条新增明细
 *
 * @param tmpOrderItem 明细对象
 * @return 结果
 */
public int insertTmpOrderItem(TmpOrderItem tmpOrderItem);

2、TmpOrderServiceImpl修改

@Service
@DataSource(DataSourceType.SHARDING)
public class TmpOrderServiceImpl implements ITmpOrderService
{

/**
     * 新增明细信息
     *
     * @param tmpOrder 订单对象
     */
    public void insertTmpOrderItem(TmpOrder tmpOrder)
    {
        List<TmpOrderItem> tmpOrderItemList = tmpOrder.getTmpOrderItemList();
        Long orderId = tmpOrder.getOrderId();
        if (StringUtils.isNotNull(tmpOrderItemList))
        {
            List<TmpOrderItem> list = new ArrayList<TmpOrderItem>();
            for (TmpOrderItem tmpOrderItem : tmpOrderItemList)
            {
                tmpOrderItem.setOrderId(orderId);
                tmpOrderMapper.insertTmpOrderItem(tmpOrderItem);
//                list.add(tmpOrderItem);
            }
//            if (list.size() > 0)
//            {
//                tmpOrderMapper.batchTmpOrderItem(list);
//            }
        }
    }
}

3、TmpOrderMapper.xml修改

<update id="updateTmpOrder" parameterType="TmpOrder">
    update tmp_order
    <trim prefix="SET" suffixOverrides=",">
        <if test="status != null">status = #{status},</if>
    </trim>
    where order_id = #{orderId}
</update>

4、TmpOrderMapper.xml新增

<insert id="insertTmpOrderItem" parameterType="TmpOrderItem" useGeneratedKeys="true" keyProperty="orderItemId">
    insert into tmp_order_item
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="orderId != null">order_id,</if>
        <if test="userId != null">user_id,</if>
        <if test="status != null">status,</if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="orderId != null">#{orderId},</if>
        <if test="userId != null">#{userId},</if>
        <if test="status != null">#{status},</if>
    </trim>
</insert>

        经过以上几个步骤，我们就把ruoyi-vue与shardingsphere整合完毕。主子表的代码生成需要更改批量入库为单条入库，否则不支持；任何表的分片主键都不能出现在update语句的set之中。参考TmpOrderMapper.java和TmpOrderMapper.xml。对比来看，shardingsphere没有期望的那么好，与mycat差不多，大量SQL语句有限制，对业务代码入侵很严重，慎用。