本文介绍基于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语句有限制,对业务代码入侵很严重,慎用。