ShardingSphere入门实战(1)-Sharding-JDBC使用
ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。具体介绍可以查看官网https://shardingsphere.apache.org/。本文主要介绍在Spring Boot工程中使用Sharding-JDBC，文中使用到的软件版本：Java 1.8.0_191、sharding-jdbc-core 4.1.1、MySQL 5.7.26。
1、引入依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-test</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> </dependency>
2、数据分片
2.1、对应配置
spring:
shardingsphere:
datasource:
names: ds0,ds1 #数据源名称，多数据源以逗号分隔
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.49.196.10:3306/itest
username: admin
password: Root_123!
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.49.196.20:3306/itest
username: admin
password: Root_123!
sharding:
tables:
t_user: #逻辑表名,在一个库里分表:ds0.t_user_0,ds0.t_user_1
actual-data-nodes: ds0.t_user_$->{0..1}
table-strategy: #表分片策略
inline: #行表达式分片策略
sharding-column: user_id #分片的字段
algorithm-expression: t_user_$->{user_id % 2} #分片的算法
t_dept: #分库,两个库里建系统的表:ds0.t_dept,ds1.t_dept
actual-data-nodes: ds$->{0..1}.t_dept
database-strategy: #数据库分片策略
inline: #行表达式分片策略
sharding-column: dept_id #分片的字段
algorithm-expression: ds$->{dept_id % 2} #分片的算法
props:
sql:
show: true
2.2、对应程序
/** * 数据分片 * t_user在同一个库里分表(t_user_0,t_user_1) * t_dept分库 */ @Test public void fragmentation() { Connection con = null; try { con = dataSource.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //插入ds0.t_user_0表 st.executeUpdate("insert into t_user(user_id,user_name,age) values(10,'赵云', 30)"); //插入ds0.t_user_1表 st.executeUpdate("insert into t_user(user_id,user_name,age) values(11,'张飞', 31)"); //插入ds0.t_dept表 st.executeUpdate("insert into t_dept(dept_id,dept_name) values(10,'dept10')"); //插入ds1.t_dept表 st.executeUpdate("insert into t_dept(dept_id,dept_name) values(11,'dept11')"); ResultSet rs = st.executeQuery("select user_id,user_name from t_user where user_id in(10,11)"); while (rs.next()) { logger.info("user_id={},user_name={}", rs.getString("user_id"), rs.getString("user_name")); } rs = st.executeQuery("select dept_id,dept_name from t_dept where dept_id in(10,11)"); while (rs.next()) { logger.info("dept_id={},dept_name={}", rs.getString("dept_id"), rs.getString("dept_name")); } con.commit(); } catch (Exception e) { JdbcUtil.rollback(con); e.printStackTrace(); } finally { JdbcUtil.close(con); } }
3、读写分离
3.1、对应配置
spring:
shardingsphere:
datasource:
names: master,slave0
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.49.196.10:3306/itest
username: admin
password: Root_123!
slave0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.49.196.20:3306/itest
username: admin
password: Root_123!
masterslave:
name: ms
master-data-source-name: master
slave-data-source-names: slave0
props:
sql:
show: true
3.2、对应程序
/** * 读写分离，主库写，从库读 * 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性 */ @Test public void readWrite() { Connection con = null; try { con = dataSource.getConnection(); Statement st = con.createStatement(); //从slave0读数据 ResultSet rs = st.executeQuery("select * from t_student"); while (rs.next()) { System.out.println(rs.getString("id") + "|" + rs.getString("name")); } //写入master st.executeUpdate("insert into t_student(id,name) values(100,'测试')"); //从master读数据 rs = st.executeQuery("select * from t_student"); while (rs.next()) { System.out.println(rs.getString(1) + "|" + rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(con); } } /** * 读写分离，强制主库路由 */ @Test public void hintMasterRouteOnly() { Connection con = null; try { con = dataSource.getConnection(); Statement st = con.createStatement(); HintManager hintManager = HintManager.getInstance(); //主库路由 hintManager.setMasterRouteOnly(); //从master读数据 ResultSet rs = st.executeQuery("select * from a_orm"); while (rs.next()) { logger.info(rs.getString(1) + "|" + rs.getString(2)); } hintManager.close(); } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(con); } }
4、完整代码
package com.abc.demo.shardingsphere; import com.abc.demo.shardingsphere.util.JdbcUtil; import org.apache.shardingsphere.api.hint.HintManager; import org.junit.Test; import org.junit.runner.RunWith; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; /** * ShardingJdbc使用样例 */ @RunWith(SpringRunner.class) @SpringBootTest public class ShardingJdbcCase { private static Logger logger = LoggerFactory.getLogger(ShardingJdbcCase.class); @Resource private DataSource dataSource; /** * 数据分片 * t_user在同一个库里分表(t_user_0,t_user_1) * t_dept分库 */ @Test public void fragmentation() { Connection con = null; try { con = dataSource.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //插入ds0.t_user_0表 st.executeUpdate("insert into t_user(user_id,user_name,age) values(10,'赵云', 30)"); //插入ds0.t_user_1表 st.executeUpdate("insert into t_user(user_id,user_name,age) values(11,'张飞', 31)"); //插入ds0.t_dept表 st.executeUpdate("insert into t_dept(dept_id,dept_name) values(10,'dept10')"); //插入ds1.t_dept表 st.executeUpdate("insert into t_dept(dept_id,dept_name) values(11,'dept11')"); ResultSet rs = st.executeQuery("select user_id,user_name from t_user where user_id in(10,11)"); while (rs.next()) { logger.info("user_id={},user_name={}", rs.getString("user_id"), rs.getString("user_name")); } rs = st.executeQuery("select dept_id,dept_name from t_dept where dept_id in(10,11)"); while (rs.next()) { logger.info("dept_id={},dept_name={}", rs.getString("dept_id"), rs.getString("dept_name")); } con.commit(); } catch (Exception e) { JdbcUtil.rollback(con); e.printStackTrace(); } finally { JdbcUtil.close(con); } } /** * 读写分离，主库写，从库读 * 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性 */ @Test public void readWrite() { Connection con = null; try { con = dataSource.getConnection(); Statement st = con.createStatement(); //从slave0读数据 ResultSet rs = st.executeQuery("select * from t_student"); while (rs.next()) { System.out.println(rs.getString("id") + "|" + rs.getString("name")); } //写入master st.executeUpdate("insert into t_student(id,name) values(100,'测试')"); //从master读数据 rs = st.executeQuery("select * from t_student"); while (rs.next()) { System.out.println(rs.getString(1) + "|" + rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(con); } } /** * 读写分离，强制主库路由 */ @Test public void hintMasterRouteOnly() { Connection con = null; try { con = dataSource.getConnection(); Statement st = con.createStatement(); HintManager hintManager = HintManager.getInstance(); //主库路由 hintManager.setMasterRouteOnly(); //从master读数据 ResultSet rs = st.executeQuery("select * from a_orm"); while (rs.next()) { logger.info(rs.getString(1) + "|" + rs.getString(2)); } hintManager.close(); } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(con); } } }
