鉴于shardingsphere已经升级,我们现在使用的是RC2的版本
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
有关分表的实现可以参考Springboot2使用shardingsphere分表攻略
现在假设我们在A库中有一个t1表,B库中有一个t2表
t1表字段如下
t2表字段如下
Spring的配置文件如下
spring:
shardingsphere:
datasource:
names: db1,db2
db1:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/cloud_resource?useSSL=FALSE&serverTimezone=GMT%2B8
username: root
password: ****
type: com.alibaba.druid.pool.DruidDataSource
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
db2:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/cloud_resource_base?useSSL=FALSE&serverTimezone=GMT%2B8
username: root
password: ****
type: com.alibaba.druid.pool.DruidDataSource
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
masterslave:
load-balance-algorithm-type: round_robin
name: ms
master-data-source-name: db1
slave-data-source-names: db1,db2
Spring的main方法如下
@EnableFeignClients
@EnableTransactionManagement
@SpringBootApplication(exclude = JtaAutoConfiguration.class)
public class ResourceCenterApplication {
public static void main(String[] args) {
SpringApplication.run(ResourceCenterApplication.class, args);
}
}
通过一系列对两张表的查询,我们可以看到
2020-04-02 15:39:07.018 DEBUG [resource-center,9c42f77c03330c96,9c42f77c03330c96,false] 2205 --- [nio-8004-exec-1] com.cloud.resourcecenter.dao.T1Dao.find : ==> Preparing: select id,name from t1 where id=? 2020-04-02 15:39:07.395 DEBUG [resource-center,9c42f77c03330c96,9c42f77c03330c96,false] 2205 --- [nio-8004-exec-1] com.cloud.resourcecenter.dao.T1Dao.find : ==> Parameters: 1(Integer) 2020-04-02 15:39:07.849 DEBUG [resource-center,9c42f77c03330c96,9c42f77c03330c96,false] 2205 --- [nio-8004-exec-1] com.cloud.resourcecenter.dao.T1Dao.find : <== Total: 1 2020-04-02 15:39:25.317 DEBUG [resource-center,7010cb123ed124e9,7010cb123ed124e9,false] 2205 --- [nio-8004-exec-4] com.cloud.resourcecenter.dao.T2Dao.find : ==> Preparing: select id,age,address from t2 where id=? 2020-04-02 15:39:25.346 DEBUG [resource-center,7010cb123ed124e9,7010cb123ed124e9,false] 2205 --- [nio-8004-exec-4] com.cloud.resourcecenter.dao.T2Dao.find : ==> Parameters: 1(Integer) 2020-04-02 15:39:25.356 DEBUG [resource-center,7010cb123ed124e9,7010cb123ed124e9,false] 2205 --- [nio-8004-exec-4] com.cloud.resourcecenter.dao.T2Dao.find : <== Total: 1
操作起来没有任何问题,不同库的表都可以正常访问。