SpringBoot整合Sharding-JDBC实现读写分离
Sharding-JDBC实现读写分离,记得先要实现数据库的主从结构先。
1、Sharding-JDBC 简介
Sharding-JDBC 是的分布式数据库中间件解决方案。Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar(计划
中)是 3 款相互独立的产品,共同组成了 ShardingSphere。Sharding-JDBC 定位于轻量级的 Java 框架,它使
用客户端直连数据库,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。
-
适用于任何基于 Java 的 ORM 框架,如:JPA,Hibernate,Mybatis,Spring JDBC Template 或直接使用
JDBC。
-
基于任何第三方的数据库连接池,如:DBCP,C3P0,BoneCP,Druid,HikariCP 等。
-
支持任意实现 JDBC 规范的数据库。目前支持 MySQL,Oracle,SQLServer 和 PostgreSQL。
架构图如下:
支持以下的特效:
-
分库分表
-
读写分离
-
柔性事务
-
分布式主键
-
分布式治理能力
2、工程准备
搭建读写分离的数据库,这里选择的数据库的版本为Mysql5.7,详细信息如下:
数据库类型 | 数据库 | ip |
---|---|---|
主 | cool | 10.0.0.3 |
从 | cool | 10.0.0.13 |
从 | cool | 10.0.0.17 |
在主库里面执行以下的数据库初始化脚本:
USE `cool`;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (`id` int(12) NOT NULL AUTO_INCREMENT,`username` varchar(12) NOT NULL,`password` varchar(30) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `idx-username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
主从数据库已经搭建好了,所以执行完上面的脚本后,2 个从库应该也有 user 表。
3、案例讲解
3.1 pom依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.0.3.RELEASE</version><relativePath/></parent><groupId>com.forezp</groupId><artifactId>sharding-jdbc-master-slave</artifactId><version>0.0.1-SNAPSHOT</version><name>sharding-jdbc-master-slave</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.10</version></dependency><dependency><groupId>io.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>3.1.0.M1</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>
3.2 配置文件
在 spring boot 工程配置文件 application.yml
做以下的配置:
sharding:jdbc:dataSource:names: db-test0,db-test1,db-test2# 配置主库db-test0:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://{master-host}:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername: rootpassword: root# 最大连接数maxPoolSize: 20# 配置第一个从库db-test1:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://{slave1-host}:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMTusername: rootpassword: rootmaxPoolSize: 20# 配置第二个从库db-test2:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://{slave2-host}:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMTusername: rootpassword: rootmaxPoolSize: 20# 配置读写分离config:masterslave:# 配置从库选择策略,提供轮询与随机(random随机、round_robin轮询),这里选择用轮询load-balance-algorithm-type: round_robinname: db1s2master-data-source-name: db-test0slave-data-source-names: db-test1,db-test2# 开启SQL显示,默认值false,注意仅配置读写分离时不会打印日志props:sql:show: true
server:port: 8085
mybatis:config-location: classpath:META-INF/mybatis-config.xml
sharding.jdbc.dataSource.names
:配置数据库的名称,就是多个数据源的名称。
sharding.jdbc.dataSource
:配置多个数据源,需要配置数据库名称,和上面配置的对应。以及数据的配置,
包括连接池的类型、连接器、数据库地址、 数据库账户密码信息等。
sharding.jdbc.config.masterslave.load-balance-algorithm-type
:查询时的负载均衡算法,目前有 2
种算法,round_robin
(轮询)和 random
(随机)。
sharding.jdbc.config.masterslave.master-data-source-name
:主数据源名称。
sharding.jdbc.config.masterslave.slave-data-source-names
:从数据源名称,多个用逗号隔开。
3.3 业务类
User
实体类:
package com.example.shardingjdbcmasterslave.entity;import java.io.Serializable;public class User implements Serializable {private static final long serialVersionUID = -1205226416664488559L;private Integer id;private String username;private String password;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}
}
UserRepository
类:
package com.example.shardingjdbcmasterslave.repository;import com.example.shardingjdbcmasterslave.entity.User;
import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper
public interface UserRepository {Long addUser(User user);List<User> list();}
UserService
类:
package com.example.shardingjdbcmasterslave.service;import com.example.shardingjdbcmasterslave.entity.User;import java.util.List;public interface UserService {Long addUser(User user);List<User> list();
}
UserServiceImpl
类:
package com.example.shardingjdbcmasterslave.service.impl;import com.example.shardingjdbcmasterslave.entity.User;
import com.example.shardingjdbcmasterslave.repository.UserRepository;
import com.example.shardingjdbcmasterslave.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class UserServiceImpl implements UserService {@AutowiredUserRepository userRepository;@Overridepublic Long addUser(User user) {// 强制路由主库// HintManager.getInstance().setMasterRouteOnly();return userRepository.addUser(user);}@Overridepublic List<User> list() {return userRepository.list();}
}
mybatis-config.xml
配置文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><typeAliases><package name="com.example.shardingjdbcmasterslave.entity"/></typeAliases><mappers><mapper resource="META-INF/mappers/User.xml"/></mappers>
</configuration>
User.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.example.shardingjdbcmasterslave.repository.UserRepository"><resultMap id="baseResultMap" type="com.example.shardingjdbcmasterslave.entity.User"><result column="id" property="id" jdbcType="INTEGER" /><result column="username" property="username" jdbcType="VARCHAR" /><result column="password" property="password" jdbcType="VARCHAR" /></resultMap><insert id="addUser">INSERT INTO user (id, username, password)VALUES (#{id,jdbcType=INTEGER},#{username,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR})</insert><select id="list" resultMap="baseResultMap">SELECT u.* FROM user u</select></mapper>
3.4 控制类
package com.example.shardingjdbcmasterslave.web;import com.example.shardingjdbcmasterslave.entity.User;
import com.example.shardingjdbcmasterslave.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;@RestController
public class UserController {@Autowiredprivate UserService userService;@GetMapping("/users")public Object list() {return userService.list();}@GetMapping("/add")public Object add(@RequestParam Integer id, @RequestParam String username, @RequestParam String password) {User user = new User();user.setId(id);user.setUsername(username);user.setPassword(password);return userService.addUser(user);}
}
3.5 启动类
package com.example.shardingjdbcmasterslave;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication
public class ShardingJdbcMasterSlaveApplication {public static void main(String[] args) {SpringApplication.run(ShardingJdbcMasterSlaveApplication.class, args);}}
3.6 主要工作流程
3.7 测试
查看数据库的 CRUD 日志,日志目录在 /var/lib/mysql
目录下。
调用 2 个接口,可以在主库对应主机的日志目录下查看插入数据的日志:
Query select @@session.transaction_read_only
Query INSERT INTO user (id, username, password)VALUES (134,'forezp134','1233edwd')
从库对应主机的日志目录下查看查询数据的日志:
Query SELECT u.* FROM user u
这就说明,Sharding-JDBC 实现了数据库的读写分离。
4、参考资料
https://github.com/apache/incubator-shardingsphere-example/releases/tag/3.1.0.M1
https://shardingsphere.apache.org/document/current/cn/overview/
https://github.com/apache/incubator-shardingsphere