一、初探
ShardingSphere包括了三款产品,两款产品是Sharding-Proxy和Sharding-Sidecar(计划中),而今天要讲的是另一款产品Sharding-JDBC,它是其中的分布式数据库中间件解决方案,基于client层的。它支持分库分表、读写分离、柔性事务、分布式主键、分布式治理能力。架构图如下
二、实战
开战之际,先要准备好后勤工作,构建好数据库主从复制先,具体过程请看https://blog.csdn.net/qq_20475615/article/details/98887912
1、集成过程
- 首先引入依赖,这里使用springboot+mybatis-plus+druid+mysql+sharding-jdbc
<!-- starter -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- Mysql Connector -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!-- MybatisPlus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.1</version>
</dependency>
<!-- sharding-jdbc -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0.M1</version>
</dependency>
- application.yml文件配置
mybatis-plus:
# 放在resource目录 classpath:/mapper/*Mapper.xml
mapper-locations: classpath:/mapper/*.xml
# 实体扫描,多个package用逗号或者分号分隔
typeAliasesPackage: com.example.project.*.*.mapper
global-config:
# 主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
id-type: 2
# 字段策略 0:"忽略判断",1:"非 NULL 判断",2:"非空判断"
field-strategy: 2
# 驼峰下划线转换
db-column-underline: true
# 刷新mapper 调试神器
refresh-mapper: true
# 数据库大写下划线转换
#capital-mode: true
# 逻辑删除配置(下面3个配置)
logic-delete-value: 0
logic-not-delete-value: 1
# SQL 解析缓存,开启后多租户 @SqlParser 注解生效
sql-parser-cache: true
configuration:
map-underscore-to-camel-case: true
cache-enabled: false
sharding:
jdbc:
dataSource:
#如果多个主从即在后面追加即可
names: db-master,db-slave
# 配置主库
db-master: #org.apache.tomcat.jdbc.pool.DataSource
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.1.60:53306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false
username: root
password: 123456
#最大连接数
maxPoolSize: 50
# 配置从库
db-slave:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.1.60:63306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: root
password: 123456
maxPoolSize: 100
config:
# 读写分离配置
masterslave:
#load-balance-algorithm-type: round_robin # 配置多个从库时选择策略,提供轮询与随机,这里选择用轮询//random 随机 //round_robin 轮询
name: dbms
master-data-source-name: db-master
slave-data-source-names: db-slave
props:
sql:
show: true # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!!
- 业务代码
//mybatis-plus 配置类
@Configuration
@MapperScan("com.example.project.*.*.mapper") //这里千万注意只写mapper 所在文件夹,mybatis会进行代理,免得误伤其他文件夹
public class MybatisPlusConfig {
/**
* 分页插件,自动识别数据库类型
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
//简单实体类
@TableName("product")
public class Product {
private Integer id;
private String name;
private String url;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
}
//mapper
public interface ProductsMapper extends BaseMapper<Product> {
}
//简单的服务代码
@Service
public class ProductsServiceImpl{
@Autowired
ProductsMapper productsMapper;
/**
* 测试读
*/
public void getProduct(){
Product product = productsMapper.selectById(1);
System.out.println(product.getName());
}
/**
* 测试写
*/
public void saveProduct(){
Product product = new Product();
product.setId(2);
product.setName("小米");
productsMapper.insert(product);
}
/**
* 测试写后马上读
*/
public void saveAndGetProduct(){
Product product = new Product();
product.setId(10);
product.setName("小米");
productsMapper.insert(product);
Product result = productsMapper.selectById(10);
}
}
//测试类
@SpringBootTest
@RunWith(SpringRunner.class)
public class TestService {
@Autowired
ProductsServiceImpl productsServiceImpl;
@Test
public void testProducts(){
// productsServiceImpl.selectProduct();
productsServiceImpl.getProduct();
// productsServiceImpl.saveProduct();
// productsServiceImpl.saveAndGetProduct();
}
}
- 测试过程,关于docker里数据库日志查看在主从复制已经有讲到 https://blog.csdn.net/qq_20475615/article/details/98887912
①测试读,我们查看从库日志,在 /var/lib/mysql/ 下的log文件,比如我是585b536f66a1.log
②测试写,我们查看主库日志,在 /var/lib/mysql/ 下的log文件,比如我是60a4d54f601b.log
2、拓展
- 上面的集成方法中我们用的sharding-jdbc是<artifactId>sharding-jdbc-spring-boot-starter</artifactId>,下面我们换一种依赖来集成,其他的依赖还是跟上面一样,只要修改sharding-jdbc的
<dependency>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>2.0.3</version>
</dependency>
- 接着增加一个sharding-druid.yml,放到和application.yml平级,内容如下,可以看到配置方式跟上面是不一样的(记住不要把该配置放到application.yml,否则会引起冲突导致启动失败),application.yml里去掉sharding节点那部分,至于mybatis的配置还是跟上面一样
dataSources:
#这里如果用其他连接池,则引入相应的依赖然后修改为对应的类型即可,如!!org.apache.commons.dbcp.BasicDataSource
ds_master: !!com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.1.60:53306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456
ds_slave0: !!com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.1.60:63306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456
masterSlaveRule:
name: ds_ms
masterDataSourceName: ds_master
slaveDataSourceNames: [ds_slave0]
- 增加一个配置类(上面的方式是不需要的)
@Configuration
public class DataSourceConfig {
@Bean
public DataSource dataSource() throws Exception {
return MasterSlaveDataSourceFactory.createDataSource(ResourceUtils.getFile("classpath:sharding-druid.yml"));
}
}
- 代码部分跟上面一样测试即可