1. 环境形容
本次测试基于 JeecgBoot 2.4.6,测试代码在 Jeecg-boot-module-system 中编写。
2. 引入坐标
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
3. 配置 yml 文件
datasource:
druid:
stat-view-servlet:
enabled: true
loginUsername: admin
loginPassword: 123456
allow:
web-stat-filter:
enabled: true
dynamic:
druid: # 全局 druid 参数,绝大部分值和默认保持一致。(现已反对的参数如下, 不分明含意不要乱设置)
# 连接池的配置信息
# 初始化大小,最小,最大
initial-size: 5
min-idle: 5
maxActive: 20
# 配置获取连贯期待超时的工夫
maxWait: 60000
# 配置距离多久才进行一次检测,检测须要敞开的闲暇连贯,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连贯在池中最小生存的工夫,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 关上 PSCache,并且指定每个连贯上 PSCache 的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦挡的 filters,去掉后监控界面 sql 无奈统计,'wall' 用于防火墙
filters: stat,wall,slf4j
# 通过 connectProperties 属性来关上 mergeSql 性能;慢 SQL 记录
connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
primary: master # 设置默认的数据源或者数据源组, 默认值即为 master
strict: false # 严格匹配数据源, 默认 false. true 未匹配到指定数据源时抛异样,false 应用默认数据源
datasource:
master:
url: jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
# 多数据源配置
#multi-datasource1:
#url: jdbc:mysql://localhost:3306/jeecg-boot2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
#username: root
#password: root
#driver-class-name: com.mysql.cj.jdbc.Driver
# 指定默认数据源名称
shardingsphere:
props:
sql:
show: true
dataSource:
names: ds0
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
sharding:
tables:
sys_log:
logicTable: sys_log
actualDataNodes: ds0.sys_log$->{1..2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: sys_log$->{id % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: id
worker:
id: 1
留神:在 jeecgboot 原有数据源的根底上,减少了 primary 节点,用来设置默认的数据源。
4. 建设数据表
在 jeecgboot 默认的数据库中,将 sys_log 表复制两份,别离命名为 sys_log1 和 sys_log2
5. 增加配置类
在 config 目录下,增加配置类 DataSourceConfiguration 和 DataSourceHealthConfig
其中 DataSourceConfiguration.java 配置类代码如下:
package org.jeecg.config;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {
/**
* 分表数据源名称
*/
public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
/**
* 动静数据源配置项
*/
@Autowired
private DynamicDataSourceProperties dynamicDataSourceProperties;
@Lazy
@Resource
DataSource shardingDataSource;
/**
* 将 shardingDataSource 放到了多数据源(dataSourceMap)中
* 留神有个版本的 bug,3.1.1 版本 不会进入 loadDataSources 办法,这样就始终造成数据源注册失败
*/
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 治理的数据源也交给动静数据源治理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
return dataSourceMap;
}
};
}
/**
* 将动静数据源设置为首选的
* 当 spring 存在多个数据源时, 主动注入的是首选的对象
* 设置为次要的数据源之后,就能够反对 shardingjdbc 原生的配置形式了
*
* @return
*/
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());
dataSource.setStrict(dynamicDataSourceProperties.getStrict());
dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
dataSource.setProvider(dynamicDataSourceProvider);
dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
dataSource.setSeata(dynamicDataSourceProperties.getSeata());
return dataSource;
}
}
DataSourceHealthConfig.java 配置类代码如下:
package org.jeecg.config;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.actuate.autoconfigure.jdbc.DataSourceHealthContributorAutoConfiguration;
import org.springframework.boot.actuate.health.AbstractHealthIndicator;
import org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;
import java.util.Map;
@Configuration
public class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {@Value("${spring.datasource.dbcp2.validation-query:select 1}")
private String defaultQuery;
public DataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {super(dataSources, metadataProviders);
}
@Override
protected AbstractHealthIndicator createIndicator(DataSource source) {DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
if (!StringUtils.hasText(indicator.getQuery())) {indicator.setQuery(defaultQuery);
}
return indicator;
}
}
6. 接口编写
Mapper
package org.jeecg.modules.shardingjdbc.mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* Created by sunh.
*/
@Mapper
@Repository
public interface SysLogShardingMapper {
/**
* 插入日志
* @param type
* @param content
* @param operateType
* @return
*/
@Insert("insert into sys_log(log_type,log_content,operate_type)values(#{type},#{content},#{operateType})")
int insertLog(@Param("type") int type, @Param("content") String content, @Param("operateType") int operateType);
}
Service
package org.jeecg.modules.shardingjdbc.service;
public interface SysLogShardingService {int insertLog( int type, String content, int operateType);
}
ServiceImpl
package org.jeecg.modules.shardingjdbc.service.Impl;
import com.baomidou.dynamic.datasource.annotation.DS;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.config.DataSourceConfiguration;
import org.jeecg.modules.shardingjdbc.mapper.SysLogShardingDao;
import org.jeecg.modules.shardingjdbc.service.SysLogShardingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Slf4j
@Service
@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)
public class SysLogShardingServiceImpl implements SysLogShardingService {
@Autowired
private SysLogShardingDao sysLogShardingDao;
@Override
public int insertLog(int type, String content, int operateType) {int affectedRows = sysLogShardingDao.insertLog( type,content, operateType);
return affectedRows;
}
}
7. 测试用例
package org.jeecg.modules.shardingjdbc.controller;
import org.jeecg.common.api.vo.Result;
import org.jeecg.modules.shardingjdbc.mapper.SysLogShardingDao;
import org.jeecg.modules.shardingjdbc.service.SysLogShardingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* 测试 sharding-jdbc
*/
@RestController
@RequestMapping("/sys_log")
public class SysLogShardingController {
@Autowired
private SysLogShardingService sysLogShardingService;
@GetMapping("/test1")
public Result<?> TestMongoDb(){for(int i=1;i<20;i++){sysLogShardingService.insertLog( i,"jeecgboot",i);
}
return Result.OK("存入胜利");
}
}