springboot：shardingsphere多数据源,⽀持未分表的数据源
（shard。。。
⼀，为什么要给shardingsphere配置多数据源？
1，shardingjdbc默认接管了所有的数据源，
如果我们有多个⾮分表的库时，则最多只能设置⼀个为默认数据库，
其他的⾮分表数据库不能访问
2，shardingjdbc对数据库的访问有⼀些限制：
官⽅站的说明：
/document/legacy/4.x/document/cn/manual/sharding-jdbc/unsupported-items/
说明：作者：刘宏缔邮箱: 371125307@qq
⼆，演⽰项⽬的相关信息
1，项⽬地址:
github/liuhongdi/shardingmulti
2，项⽬功能说明：
演⽰shardingsphere集成了两个分表的库，1个默认库,
⾮shardingsphere数据源集成了第4个数据库
3，项⽬结构；如图:
4，数据库结构：
三，配置⽂件说明
1，l
<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&batis.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
#error
springframework.web=trace
#name
spring.application.name = dynamic
# goodsdb数据源基本配置
spring.dsdb.username = root
spring.dsdb.password = lhddemo
spring.dsdb.driver-class-name = sql.cj.jdbc.Driver
spring.dsdb.url = jdbc:mysql://127.0.0.1:3306/store?serverTimezone=UTC spring.pe = com.alibaba.druid.pool.DruidDataSource
spring.dsdb.initialSize = 5
spring.dsdb.minIdle = 5
spring.dsdb.maxActive = 20
spring.dsdb.maxWait = 60000
spring.dsdb.timeBetweenEvictionRunsMillis = 60000
spring.dsdb.minEvictableIdleTimeMillis = 300000
spring.dsdb.validationQuery = SELECT 1 FROM DUAL
spring.stWhileIdle = true
spring.stOnBorrow = false
spring.stOnReturn = false
spring.dsdb.poolPreparedStatements = true
# 配置监控统计拦截的filters，去掉后监控界⾯sql⽆法统计，'wall'⽤于防⽕墙
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize = 20
spring.datasource.druid.useGlobalDataSourceStat = true
spring.tionProperties = Sql=true;druid.stat.slowSqlMillis=500 #druid sql firewall monitor
spring.datasource.druid.abled=true
#druid sql monitor
spring.datasource.druid.abled=true
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=10000
spring.datasource.druid.-sql=true
#druid uri monitor
spring.datasource.abled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.lusions=*.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.abled=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/*l
#ample.demo.mapper
#log
#shardingsphere第⼀个分表数据源
spring.datasource.pe=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.saleorder01.sql.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.pe=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.saleorder02.sql.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.pe=com.alibaba.druid.pool.DruidDataSource
spring.derdb.sql.cj.jdbc.Driver
spring.derdb.url=jdbc:mysql://127.0.0.1:3306/orderdb?characterEncoding=utf-8
spring.derdb.username=root
spring.derdb.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=0DEFAULT 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=0DEFAULT 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=0DEFAULT 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 = Value();
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 = Value();
String curTable = "";
if (curValue > 0 && curValue<=100) {
log4j2 xml配置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.dsdb", sqlSessionTemplateRef = "goodsdbSqlSessionTemplate") public class GoodsdbSourceConfig {
@Bean
@Primary
@ConfigurationProperties("spring.dsdb")
public DataSource goodsdbDataSource() {
ate().build();
}
@Bean
@Primary
public SqlSessionFactory goodsdbSqlSessionFactory(@Qualifier("goodsdbDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论