spring boot:shardingsphere多数据源,支持未分表的数据源(shardingjdbc 4.1.1)
一，为什么要给shardingsphere配置多数据源？
1，shardingjdbc默认接管了所有的数据源，
如果我们有多个非分表的库时，则最多只能设置一个为默认数据库，
其他的非分表数据库不能访问
2，shardingjdbc对数据库的访问有一些限制：
官方站的说明：
https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/unsupported-items/
说明：刘宏缔的架构森林是一个专注架构的博客，
网站：https://blog.imgtouch.com
本文: https://blog.imgtouch.com/index.php/2023/05/24/springbootshardingsphere-duo-shu-ju-yuan-zhi-chi-wei-fen-biao-de-shu-ju-yuan-shardingjdbc411/
对应的源码可以访问这里获取： https://github.com/liuhongdi/
说明：作者:刘宏缔 邮箱: 371125307@qq.com
二，演示项目的相关信息
1,项目地址:
https://github.com/liuhongdi/shardingmulti
2,项目功能说明：
演示shardingsphere集成了两个分表的库，1个默认库,
非shardingsphere数据源集成了第4个数据库
3,项目结构；如图:
4,数据库结构：
三，配置文件说明
1，pom.xml
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <exclusion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-logging</artifactId> </exclusion> </exclusions> </dependency> <!--sharding jdbc begin--> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>4.1.1</version> </dependency> <!--sharding jdbc end--> <!--mybatis begin--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--mybatis end--> <!--druid begin--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.23</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j2</artifactId> </dependency> <dependency> <groupId>com.lmax</groupId> <artifactId>disruptor</artifactId> <version>3.4.2</version> </dependency> <!--druid end--> <!--mysql begin--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--mysql end--> <!--pagehelper begin--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency> <!--pagehelper end--> <!--thymeleaf begin--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!--thymeleaf end-->
2,application.properties
#error server.error.include-stacktrace=always #error logging.level.org.springframework.web=trace #name spring.application.name = dynamic # goodsdb数据源基本配置 spring.datasource.druid.goodsdb.username = root spring.datasource.druid.goodsdb.password = lhddemo spring.datasource.druid.goodsdb.driver-class-name = com.mysql.cj.jdbc.Driver spring.datasource.druid.goodsdb.url = jdbc:mysql://127.0.0.1:3306/store?serverTimezone=UTC spring.datasource.druid.goodsdb.type = com.alibaba.druid.pool.DruidDataSource spring.datasource.druid.goodsdb.initialSize = 5 spring.datasource.druid.goodsdb.minIdle = 5 spring.datasource.druid.goodsdb.maxActive = 20 spring.datasource.druid.goodsdb.maxWait = 60000 spring.datasource.druid.goodsdb.timeBetweenEvictionRunsMillis = 60000 spring.datasource.druid.goodsdb.minEvictableIdleTimeMillis = 300000 spring.datasource.druid.goodsdb.validationQuery = SELECT 1 FROM DUAL spring.datasource.druid.goodsdb.testWhileIdle = true spring.datasource.druid.goodsdb.testOnBorrow = false spring.datasource.druid.goodsdb.testOnReturn = false spring.datasource.druid.goodsdb.poolPreparedStatements = true # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize = 20 spring.datasource.druid.useGlobalDataSourceStat = true spring.datasource.druid.connectionProperties = druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 #druid sql firewall monitor spring.datasource.druid.filter.wall.enabled=true #druid sql monitor spring.datasource.druid.filter.stat.enabled=true spring.datasource.druid.filter.stat.log-slow-sql=true spring.datasource.druid.filter.stat.slow-sql-millis=10000 spring.datasource.druid.filter.stat.merge-sql=true #druid uri monitor spring.datasource.druid.web-stat-filter.enabled=true spring.datasource.druid.web-stat-filter.url-pattern=/* spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/* #druid session monitor spring.datasource.druid.web-stat-filter.session-stat-enable=true spring.datasource.druid.web-stat-filter.profile-enable=true #druid spring monitor spring.datasource.druid.aop-patterns=com.druid.* #monintor,druid login user config spring.datasource.druid.stat-view-servlet.enabled=true spring.datasource.druid.stat-view-servlet.login-username=root spring.datasource.druid.stat-view-servlet.login-password=root # IP白名单 (没有配置或者为空，则允许所有访问) spring.datasource.druid.stat-view-servlet.allow = 127.0.0.1,192.168.163.1 # IP黑名单 (存在共同时，deny优先于allow) spring.datasource.druid.stat-view-servlet.deny = 192.168.10.1 #mybatis mybatis.mapper-locations=classpath:/mapper/sharding/*Mapper.xml #mybatis.type-aliases-package=com.example.demo.mapper mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl #log logging.config = classpath:log4j2.xml #shardingsphere第一个分表数据源 spring.datasource.druid.saleorder01.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.druid.saleorder01.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.druid.saleorder01.url=jdbc:mysql://127.0.0.1:3306/saleorder01?characterEncoding=utf-8 spring.datasource.druid.saleorder01.username=root spring.datasource.druid.saleorder01.password=lhddemo #shardingsphere第二个分表数据源 spring.datasource.druid.saleorder02.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.druid.saleorder02.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.druid.saleorder02.url=jdbc:mysql://127.0.0.1:3306/saleorder02?characterEncoding=utf-8 spring.datasource.druid.saleorder02.username=root spring.datasource.druid.saleorder02.password=lhddemo #shardingsphere第三个数据源，非分表，作为默认库访问 spring.datasource.druid.orderdb.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.druid.orderdb.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.druid.orderdb.url=jdbc:mysql://127.0.0.1:3306/orderdb?characterEncoding=utf-8 spring.datasource.druid.orderdb.username=root spring.datasource.druid.orderdb.password=lhddemo
3,各数据表的建表语句:
我们创建两个库:saleorder01,saleorder02
然后在各个库内各创建两个数据表:
saleorder01库包括t_order_1,t_order_2
saleorder02库包括t_order_3,t_order_4
建表的sql:
CREATE TABLE `t_order_4` ( `orderId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `goodsName` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name', PRIMARY KEY (`orderId`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='order4'
goodsdb的数据表：
CREATE TABLE `goods` ( `goodsId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `goodsName` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name', `subject` varchar(200) NOT NULL DEFAULT '' COMMENT '标题', `price` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '价格', `stock` int(11) NOT NULL DEFAULT '0' COMMENT 'stock', PRIMARY KEY (`goodsId`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表'
插入数据:
INSERT INTO `goods` (`goodsId`, `goodsName`, `subject`, `price`, `stock`) VALUES (3, '100分电动牙刷', '好用到让你爱上刷牙', '59.00', 96);
orderdb的数据表:
CREATE TABLE `orderinfo` ( `orderId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `orderSn` varchar(100) NOT NULL DEFAULT '' COMMENT '编号', `orderTime` timestamp NOT NULL DEFAULT '1971-01-01 00:00:01' COMMENT '下单时间', `orderStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态:0,未支付，1，已支付，2，已发货，3，已退货,4,已过期', `userId` int(12) NOT NULL DEFAULT '0' COMMENT '用户id', `price` decimal(10,0) NOT NULL DEFAULT '0' COMMENT '价格', `addressId` int(12) NOT NULL DEFAULT '0' COMMENT '地址', PRIMARY KEY (`orderId`), UNIQUE KEY `orderSn` (`orderSn`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表'
插入数据:
INSERT INTO `orderinfo` (`orderId`, `orderSn`, `orderTime`, `orderStatus`, `userId`, `price`, `addressId`) VALUES (77, '20200814171411660', '2020-08-14 09:14:12', 0, 8, '100', 0);
四，java代码说明：
1, DatabasePreciseShardingAlgorithm.java
@Component public class DatabasePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) { Long curValue = shardingValue.getValue(); String curBase = ""; if (curValue > 0 && curValue<=200) { curBase = "saleorder01"; } else { curBase = "saleorder02"; } return curBase; } }
数据库分库算法
2,OrderTablePreciseShardingAlgorithm.java
@Component public class OrderTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) { Long curValue = shardingValue.getValue(); String curTable = ""; if (curValue > 0 && curValue<=100) { curTable = "t_order_1"; } else if (curValue > 100 && curValue<=200) { curTable = "t_order_2"; } else if (curValue > 200 && curValue<=300) { curTable = "t_order_3"; } else { curTable = "t_order_4"; } return curTable; } }
数据库分表算法
3,GoodsdbSourceConfig.java
@Configuration @MapperScan(basePackages = "com.shardingmulti.demo.mapper.goodsdb", sqlSessionTemplateRef = "goodsdbSqlSessionTemplate") public class GoodsdbSourceConfig { @Bean @Primary @ConfigurationProperties("spring.datasource.druid.goodsdb") public DataSource goodsdbDataSource() { return DruidDataSourceBuilder.create().build(); } @Bean @Primary public SqlSessionFactory goodsdbSqlSessionFactory(@Qualifier("goodsdbDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/goodsdb/*.xml")); return bean.getObject(); } @Bean @Primary public DataSourceTransactionManager goodsdbTransactionManager(@Qualifier("goodsdbDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean @Primary public SqlSessionTemplate goodsdbSqlSessionTemplate(@Qualifier("goodsdbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
配置goodsdb数据源，
注意因为要使用mybatis,所以指明了mapper文件所在的包，和xml文件所在的路径
因为这个数据源不是由shardingjdbc所管理，所以要注意两个数据源的mapper程序和xml文件要隔离开
分别放在 mapper/goodsdb 和 mapper/sharding
4,ShardingDataSourceConfig.java
@Configuration @MapperScan(basePackages = "com.shardingmulti.demo.mapper.sharding", sqlSessionFactoryRef = "shardingSqlSessionFactory") public class ShardingDataSourceConfig { //分表算法 @Resource private OrderTablePreciseShardingAlgorithm orderTablePreciseShardingAlgorithm; //分库算法 @Resource private DatabasePreciseShardingAlgorithm databasePreciseShardingAlgorithm; //第一个订单库 @Bean(name = "saleorder01") @ConfigurationProperties(prefix = "spring.datasource.druid.saleorder01") public DataSource saleorder01(){ return DruidDataSourceBuilder.create().build(); } //第二个订单库 @Bean(name = "saleorder02") @ConfigurationProperties(prefix = "spring.datasource.druid.saleorder02") public DataSource saleorder02(){ return DruidDataSourceBuilder.create().build(); } //第三个库，订单统计库，做为默认 @Bean(name = "orderdb") @ConfigurationProperties(prefix = "spring.datasource.druid.orderdb") public DataSource orderdb(){ return DruidDataSourceBuilder.create().build(); } //创建数据源，需要把分库的库都传递进去 @Bean("dataSource") public DataSource dataSource(@Qualifier("saleorder01") DataSource saleorder01, @Qualifier("saleorder02") DataSource saleorder02, @Qualifier("orderdb") DataSource orderdb) throws SQLException { // 配置真实数据源 Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>(); dataSourceMap.put("orderdb", orderdb); dataSourceMap.put("saleorder01", saleorder01); dataSourceMap.put("saleorder02", saleorder02); ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.setDefaultDataSourceName("orderdb"); //如果有多个数据表需要分表，依次添加到这里 shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration()); Properties p = new Properties(); p.setProperty("sql.show", Boolean.TRUE.toString()); // 获取数据源对象 DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,p); return dataSource; } // 创建SessionFactory @Bean(name = "shardingSqlSessionFactory") public SqlSessionFactory shardingSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/sharding/*.xml")); return bean.getObject(); } // 创建事务管理器 @Bean("shardingTransactionManger") public DataSourceTransactionManager shardingTransactionManger(@Qualifier("dataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } // 创建SqlSessionTemplate @Bean(name = "shardingSqlSessionTemplate") public SqlSessionTemplate shardingSqlSessionTemplate(@Qualifier("shardingSqlSessionFactory") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } //订单表的分表规则配置 private TableRuleConfiguration getOrderTableRuleConfiguration() { TableRuleConfiguration result = new TableRuleConfiguration("t_order","saleorder01.t_order_$->{1..2},saleorder02.t_order_$->{3..4}"); result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("orderId",databasePreciseShardingAlgorithm)); result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("orderId",orderTablePreciseShardingAlgorithm)); return result; } //分页 @Bean(name="pageHelper") public PageHelper getPageHelper() { PageHelper pageHelper = new PageHelper(); Properties properties = new Properties(); properties.setProperty("reasonable", "true"); properties.setProperty("supportMethodsArguments", "true"); properties.setProperty("returnPageInfo", "true"); properties.setProperty("params", "count=countSql"); pageHelper.setProperties(properties); return pageHelper; } }
shardingjdbc的数据源，创建时要使用ShardingDataSourceFactory
它负责连接3个库：两个分表库:saleorder01,saleorder02, 一个非分表库:orderdb
注意shardingjdbc所管理的数据源中，只能有一个非分表的库，而且要设置为默认库，
否则不能正常访问
5,HomeController.java
@Controller @RequestMapping("/home") public class HomeController { @Resource private GoodsMapper goodsMapper; @Resource private OrderMapper orderMapper; @Resource private OrderShardingMapper orderShardingMapper; //商品详情 参数:商品id @GetMapping("/goodsinfo") @ResponseBody @DS("goodsdb") public Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) { Goods goods = goodsMapper.selectOneGoods(goodsId); return goods; } //订单统计库，参数：订单id @GetMapping("/orderinfo") @ResponseBody public Order orderInfo(@RequestParam(value="orderid",required = true,defaultValue = "0") Long orderId) { Order order = orderMapper.selectOneOrder(orderId); return order; } //两个分表库中的订单列表 @GetMapping("/orderlist") public String list(Model model, @RequestParam(value="currentPage",required = false,defaultValue = "1") Integer currentPage){ PageHelper.startPage(currentPage, 5); List<OrderSharding> orderList = orderShardingMapper.selectAllOrder(); model.addAttribute("orderlist",orderList); PageInfo<OrderSharding> pageInfo = new PageInfo<>(orderList); model.addAttribute("pageInfo", pageInfo); System.out.println("------------------------size:"+orderList.size()); return "order/list"; } }
实现到各个数据源的访问
6,OrderShardingMapper.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.shardingmulti.demo.mapper.sharding.OrderShardingMapper" > <select id="selectAllOrder" resultType="com.shardingmulti.demo.pojo.OrderSharding"> select * from t_order order by orderId desc </select> </mapper>
7,OrderMapper.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.shardingmulti.demo.mapper.sharding.OrderMapper"> <select id="selectOneOrder" parameterType="long" resultType="com.shardingmulti.demo.pojo.Order"> select * from orderinfo where orderId=#{orderId} </select> </mapper>
8,GoodsMapper.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.shardingmulti.demo.mapper.goodsdb.GoodsMapper"> <select id="selectOneGoods" parameterType="long" resultType="com.shardingmulti.demo.pojo.Goods"> select * from goods where goodsId=#{goodsId} </select> </mapper>
五，测试效果
1,访问goodsdb
http://127.0.0.1:8080/home/goodsinfo?goodsid=3
返回:
2,访问orderdb
http://127.0.0.1:8080/home/orderinfo?orderid=77
返回:
3,访问分表库:
http://127.0.0.1:8080/home/orderlist/
返回:
4,从druid的监控页面查看创建的连接:
http://127.0.0.1:8080/druid
可以看到连接有共4个数据源：
六，查看spring boot版本:
. ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.3.3.RELEASE)
