接上篇,使用文档。
使用文档
数据分片
不使用Spring
引入Maven依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
基于Java编码的规则配置
Sharding-JDBC的分库分表通过规则配置描述,以下例子是根据user_id取模分库, 且根据order_id取模分表的两库两表的配置。
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第一个数据源
BasicDataSource dataSource1 = new BasicDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0");
dataSource1.setUsername("root");
dataSource1.setPassword("");
dataSourceMap.put("ds0", dataSource1);
// 配置第二个数据源
BasicDataSource dataSource2 = new BasicDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1");
dataSource2.setUsername("root");
dataSource2.setPassword("");
dataSourceMap.put("ds1", dataSource2);
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
orderTableRuleConfig.setLogicTable("t_order");
orderTableRuleConfig.setActualDataNodes("ds${0..1}.t_order${0..1}");
// 配置分库 + 分表策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
// 省略配置order_item表规则...
// ...
// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());
基于Yaml的规则配置
或通过Yaml方式配置,与以上配置等价:
dataSources:
ds0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0
username: root
password:
ds1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1
username: root
password:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmInlineExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmInlineExpression: t_order${order_id % 2}
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmInlineExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmInlineExpression: t_order_item${order_id % 2}
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(yamlFile);
使用原生JDBC
通过ShardingDataSourceFactory或者YamlShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource,ShardingDataSource实现自JDBC的标准接口DataSource。然后可通过DataSource选择使用原生JDBC开发,或者使用JPA, MyBatis等ORM工具。 以JDBC原生实现为例:
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(yamlFile);
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setInt(1, 10);
preparedStatement.setInt(2, 1001);
try (ResultSet rs = preparedStatement.executeQuery()) {
while(rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}
}
使用Spring
引入Maven依赖
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
基于Spring boot的规则配置
sharding.jdbc.datasource.names=ds0,ds1
sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp2.BasicDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=
sharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp2.BasicDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
sharding.jdbc.config.sharding.tables.t-order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
sharding.jdbc.config.sharding.tables.t-order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t-order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t-order-item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
sharding.jdbc.config.sharding.tables.t-order-item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t-order-item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
基于Spring命名空间的规则配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/sharding
http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd
">
<bean id="ds0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds$->{user_id % 2}" />
<sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order$->{order_id % 2}" />
<sharding:inline-strategy id="orderItemTableStrategy" sharding-column="order_id" algorithm-expression="t_order_item$->{order_id % 2}" />
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="ds0,ds1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy" />
<sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds$->{0..1}.t_order_item$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderItemTableStrategy" />
</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
在Spring中使用DataSource
直接通过注入的方式即可使用DataSource,或者将DataSource配置在JPA、Hibernate或MyBatis中使用。
@Resource
private DataSource dataSource;
读写分离
不使用Spring
引入Maven依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
基于Java编码的规则配置
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置主库
BasicDataSource masterDataSource = new BasicDataSource();
masterDataSource.setDriverClassName("com.mysql.jdbc.Driver");
masterDataSource.setUrl("jdbc:mysql://localhost:3306/ds_master");
masterDataSource.setUsername("root");
masterDataSource.setPassword("");
dataSourceMap.put("ds_master", masterDataSource);
// 配置第一个从库
BasicDataSource slaveDataSource1 = new BasicDataSource();
slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource1.setUrl("jdbc:mysql://localhost:3306/ds_slave0");
slaveDataSource1.setUsername("root");
slaveDataSource1.setPassword("");
dataSourceMap.put("ds_slave0", slaveDataSource1);
// 配置第二个从库
BasicDataSource slaveDataSource2 = new BasicDataSource();
slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource2.setUrl("jdbc:mysql://localhost:3306/ds_slave1");
slaveDataSource2.setUsername("root");
slaveDataSource2.setPassword("");
dataSourceMap.put("ds_slave1", slaveDataSource2);
// 配置读写分离规则
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", "ds_master", Arrays.asList("ds_slave0", "ds_slave1"));
// 获取数据源对象
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRuleConfig, new HashMap<String, Object>(), new Properties());
基于Yaml的规则配置
或通过Yaml方式配置,与以上配置等价:
dataSources:
ds_master: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_master
username: root
password:
ds_slave0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_slave0
username: root
password:
ds_slave1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_slave1
username: root
password:
masterSlaveRule:
name: ds_ms
masterDataSourceName: ds_master
slaveDataSourceNames: [ds_slave0, ds_slave1]
props:
sql.show: true
configMap:
key1: value1
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(yamlFile);
使用原生JDBC
通过MasterSlaveDataSourceFactory工厂和规则配置对象获取MasterSlaveDataSource,MasterSlaveDataSource实现自JDBC的标准接口DataSource。然后可通过DataSource选择使用原生JDBC开发,或者使用JPA, MyBatis等ORM工具。 以JDBC原生实现为例:
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(yamlFile);
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setInt(1, 10);
preparedStatement.setInt(2, 1001);
try (ResultSet rs = preparedStatement.executeQuery()) {
while(rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}
}
使用Spring
引入Maven依赖
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
基于Spring boot的规则配置
sharding.jdbc.datasource.names=master,slave0,slave1
sharding.jdbc.datasource.master.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master.url=jdbc:mysql://localhost:3306/master
sharding.jdbc.datasource.master.username=root
sharding.jdbc.datasource.master.password=
sharding.jdbc.datasource.slave0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.slave0.url=jdbc:mysql://localhost:3306/slave0
sharding.jdbc.datasource.slave0.username=root
sharding.jdbc.datasource.slave0.password=
sharding.jdbc.datasource.slave1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.slave1.url=jdbc:mysql://localhost:3306/slave1
sharding.jdbc.datasource.slave1.username=root
sharding.jdbc.datasource.slave1.password=
sharding.jdbc.config.masterslave.name=ms
sharding.jdbc.config.masterslave.master-data-source-name=master
sharding.jdbc.config.masterslave.slave-data-source-names=slave0,slave1
sharding.jdbc.config.masterslave.props.sql.show=true
基于Spring命名空间的规则配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/masterslave
http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd
">
<bean id="ds_master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_slave0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_slave0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_slave1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_slave1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<master-slave:data-source id="masterSlaveDataSource" master-data-source-name="ds_master" slave-data-source-names="ds_slave0, ds_slave1" >
<master-slave:props>
<prop key="sql.show">${sql_show}</prop>
<prop key="executor.size">10</prop>
<prop key="foo">bar</prop>
</master-slave:props>
</master-slave:data-source>
</beans>
在Spring中使用DataSource
直接通过注入的方式即可使用DataSource,或者将DataSource配置在JPA、Hibernate或MyBatis中使用。
@Resource
private DataSource dataSource;
强制路由
简介
Sharding-Sphere使用ThreadLocal管理分片键值进行Hint强制路由。可以通过编程的方式向HintManager中添加分片条件,该分片条件仅在当前线程内生效。 方式主要使用场景:
1.分片字段不存在SQL中、数据库表结构中,而存在于外部业务逻辑。因此,通过Hint实现外部指定分片结果进行数据操作。
2.强制在主库进行某些数据操作。
基于暗示(Hint)的数据分片
配置
使用hint进行强制数据分片,需要使用HintManager搭配分片策略配置共同使用。若DatabaseShardingStrategy配置了Hint分片算法,则可使用HintManager进行分库路由结果的注入。同理,若TableShardingStrategy配置了Hint分片算法,则同样可 使用HintManager进行分表路由结果的注入。所以使用Hint之前,需要配置Hint分片算法。
参考代码如下:
shardingRule:
tables:
t_order:
actualDataNodes: demo_ds_${0..1}.t_order_${0..1}
databaseStrategy:
hint:
algorithmClassName: io.shardingsphere.userAlgo.HintAlgorithm
tableStrategy:
hint:
algorithmClassName: io.shardingsphere.userAlgo.HintAlgorithm
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: demo_ds_${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
props:
sql.show: true
实例化
HintManager hintManager = HintManager.getInstance();
添加分片键值
● 使用hintManager.addDatabaseShardingValue来添加数据源分片键值。
● 使用hintManager.addTableShardingValue来添加表分片键值。
分库不分表情况下,强制路由至某一个分库时,可使用hintManager.setDatabaseShardingValue方式添加分片。通过此方式添加分片键值后,将跳过SQL解析和改写阶段,从而提高整体执行效率。
清除分片键值
分片键值保存在ThreadLocal中,所以需要在操作结束时调用hintManager.close()来清除ThreadLocal中的内容。
hintManager实现了AutoCloseable接口,可推荐使用try with resource自动关闭。
完整代码示例
// Sharding database and table with using hintManager.
String sql = "SELECT * FROM t_order";
try (HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.addDatabaseShardingValue("t_order", 1);
hintManager.addTableShardingValue("t_order", 2);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
// ...
}
}
}
// Sharding database without sharding table and routing to only one database with using hintManger.
String sql = "SELECT * FROM t_order";
try (HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.setDatabaseShardingValue(3);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
// ...
}
}
}
基于暗示(Hint)的强制主库路由
实例化
与基于暗示(Hint)的数据分片相同。
设置主库路由
● 使用hintManager.setMasterRouteOnly设置主库路由。
清除分片键值
与基于暗示(Hint)的数据分片相同。
完整代码示例
String sql = "SELECT * FROM t_order";
try (
HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.setMasterRouteOnly();
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {