当前位置 : 主页 > 编程语言 > java >

Sharding JDBC读写分离实现原理及实例

来源:互联网 收集:自由互联 发布时间:2021-04-03
一、核心功能和不支持项 核心功能 提供一主多从的读写分离配置,可独立使用,也可配合分库分表使用。 独立使用读写分离支持SQL透传。 同一线程且同一数据库连接内,如有写入操作

一、核心功能和不支持项

核心功能

  • 提供一主多从的读写分离配置,可独立使用,也可配合分库分表使用。
  • 独立使用读写分离支持SQL透传。
  • 同一线程且同一数据库连接内,如有写入操作,以后的读操作均从主库读取,用于保证数据一致性。
  • 基于Hint的强制主库路由。

不支持项

  • 主库和从库的数据同步(所以需要另外实现主从同步,如使用Mysql的binlog实现)。
  • 主库和从库的数据同步延迟导致的数据不一致。
  • 主库双写或多写。
  • 跨主库和从库之间的事务的数据不一致。主从模型中,事务中读写均用主库。

#涉及到的库及表

CREATE DATABASE ds_master DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
CREATE DATABASE ds_slave0 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
CREATE DATABASE ds_slave1 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;


CREATE TABLE t_user(
 user_id BIGINT(20) PRIMARY KEY,
 user_name VARCHAR(40)
);

CREATE TABLE t_order(
 order_id BIGINT(20) PRIMARY KEY,
 user_id BIGINT(20),
 order_num VARCHAR(40)
);

二、不使用Spring

引入maven依赖

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>

这里使用到Mysql和dbcp2数据源

<!-- dbcp2 -->
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.7.0</version>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-pool2</artifactId>
  <version>2.7.0</version>
</dependency>

<!-- mysql-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.49</version>
</dependency>


基于Java编码的规则配置

/*
 * 读写分离
 * ① 插入、更新、删除只会影响主库的数据,即从库的数据不会被影响(不会同步插入、更新、删除)。因为Sharding-JDBC并没有主从库数据同步的功能。
 *  所以我们如果使用的是MySQL,可以采用binlog的方法进行同步。总之需要开发者额外处理
 * ② 查询时,如果主库没有数据,从库有数据,可以查询到数据,所以删除的时候必须保证主库和从库一起删除。
 */

// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();

// 配置主库
BasicDataSource masterDataSource = new BasicDataSource();
masterDataSource.setDriverClassName("com.mysql.jdbc.Driver");
masterDataSource.setUrl("jdbc:mysql://localhost:3305/ds_master");
masterDataSource.setUsername("root");
masterDataSource.setPassword("123456");
dataSourceMap.put("ds_master", masterDataSource);

// 配置第一个从库
BasicDataSource slaveDataSource1 = new BasicDataSource();
slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource1.setUrl("jdbc:mysql://localhost:3305/ds_slave0");
slaveDataSource1.setUsername("root");
slaveDataSource1.setPassword("123456");
dataSourceMap.put("ds_slave0", slaveDataSource1);

// 配置第二个从库
BasicDataSource slaveDataSource2 = new BasicDataSource();
slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource2.setUrl("jdbc:mysql://localhost:3305/ds_slave1");
slaveDataSource2.setUsername("root");
slaveDataSource2.setPassword("123456");

dataSourceMap.put("ds_slave1", slaveDataSource2);

// 配置读写分离规则
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", "ds_master", Arrays.asList("ds_slave0", "ds_slave1"));

// 获取数据源对象
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig, new Properties());
Connection conn = dataSource.getConnection();

// 插入数据
//ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
//long orderId = ((Long) keyGenerator.generateKey()).longValue();
//long userId = 1027543L;
//ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
//String orderNum = (String) orderGenerator.generateKey();
//
//String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
//PreparedStatement ps = conn.prepareStatement(insertSql);
//ps.setLong(1, orderId);
//ps.setLong(2, userId);
//ps.setString(3, orderNum);
//int result = ps.executeUpdate();
//System.out.println("执行结果数:" + result);

//读取数据
String querySql = "select * from t_order";
PreparedStatement qryPs = conn.prepareStatement(querySql);
ResultSet resultSet = qryPs.executeQuery();
while (resultSet.next()){
  String ud = resultSet.getString("user_id");
  String om = resultSet.getString("order_num");
  System.out.println(String.format("user_id = [%s], order_num = [%s]", ud, om));
}

// 删除数据
String deleteSql = "delete from t_order where user_id = 1027543";
PreparedStatement dropPs = conn.prepareStatement(deleteSql);
int delResult = dropPs.executeUpdate();
System.out.println("删除结果数:" + delResult);

基于Yaml的规则配置

配置文件sharddb.yml,内容如下:

dataSources:
 ds_master: !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3305/ds_master
  username: root
  password: 123456
 ds_slave0: !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3305/ds_slave0
  username: root
  password: 123456
 ds_slave1: !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3305/ds_slave1
  username: root
  password: 123456

masterSlaveRule:
 name: ds_ms
 masterDataSourceName: ds_master
 slaveDataSourceNames: [ds_slave0, ds_slave1]

props:
 sql.show: true

读取配置文件sharddb.yml:

ClassPathResource pathResource = new ClassPathResource("sharddb.yml");
DataSource dataSource = YamlMasterSlaveDataSourceFactory.createDataSource(pathResource.getFile());
Connection conn = dataSource.getConnection();

// 插入数据
ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
long orderId = ((Long) keyGenerator.generateKey()).longValue();
long userId = 1027548L;
ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
String orderNum = (String) orderGenerator.generateKey();

//String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
//PreparedStatement ps = conn.prepareStatement(insertSql);
//ps.setLong(1, orderId);
//ps.setLong(2, userId);
//ps.setString(3, orderNum);
//int result = ps.executeUpdate();
//System.out.println("执行结果数:" + result);

//读取数据
String querySql = "select * from t_order";
PreparedStatement qryPs = conn.prepareStatement(querySql);
ResultSet resultSet = qryPs.executeQuery();
while (resultSet.next()) {
  String ud = resultSet.getString("user_id");
  String om = resultSet.getString("order_num");
  System.out.println(String.format("user_id = [%s], order_num = [%s]", ud, om));
}


三、使用Spring

基于Spring boot的规则配置

① 引入Maven依赖

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>

② application.properties内容如下:

# 一主二从,一般都是部署在不同的机器上,数据库是名称是相同的
# jdbc:mysql://192.168.1.12:3306/am_stock
# jdbc:mysql://192.168.1.13:3306/am_stock
# jdbc:mysql://192.168.1.14:3306/am_stock
spring.shardingsphere.datasource.names=master,slave0,slave1

spring.shardingsphere.datasource.master.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_master
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

spring.shardingsphere.datasource.slave0.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://localhost:3306/ds_slave0
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.datasource.slave1.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://localhost:3306/ds_slave1
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1

spring.shardingsphere.props.sql.show=true

③ 直接通过注入的方式即可使用DataSource,或者将DataSource配置在JPA、Hibernate或MyBatis中使用。

@Resource
private DataSource dataSource;

④ 基于Spring boot + JNDI的规则配置

如果您计划使用Spring boot + JNDI的方式,在应用容器(如Tomcat)中使用Sharding-JDBC时,可使用spring.shardingsphere.datasource.${datasourceName}.jndiName来代替数据源的一系列配置。 如:

spring.shardingsphere.datasource.names=master,slave0,slave1

spring.shardingsphere.datasource.master.jndi-name=java:comp/env/jdbc/master
spring.shardingsphere.datasource.slave0.jndi-name=jdbc/slave0
spring.shardingsphere.datasource.slave1.jndi-name=jdbc/slave1

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1

spring.shardingsphere.props.sql.show=true

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持易盾网络。

网友评论