一、核心功能和不支持项
核心功能
- 提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。
- 独立使用读写分离支持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