ShardingSphere入门实战(1)-Sharding-JDBC使用

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈,它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar(java面试题计划中)这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能,可适用于数据库系统的核心是如Java同构、异构语言、云原生等各种多样化的应用场景。具体介绍可以查看官网​https://shardingsphere.apache.org/">​https://shardingsphere.apache.org/java语言​​。本文主要介绍在Spring Boot数据库管理系统工程中使用Sharding-javascriptJDBC,文中使用到的软件版本:Java 1mysql创建表.8.0_191、shaspringfieldrding-jdbc-corejava怎么读 4.1.1、springmvc的工作原理MySQL 5.7.26。

1javascript、引入依赖

<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、数据分mysql数据库

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、读mysql增删改查语句写分离

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.inspur.demo.shardingsphere;

import com.inspur.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);
}
}

}