背景:出于工作的需要,非常需要详细了解数据库分表分库的内容,这样便于选择版本,了解原理及性能,做出更好的选择。
Sharding-JDBC是ShardingSphere的第一个产品,也是ShardingSphere的前身。 它定位为轻量级JAVA框架,在Java的JDBC层提供的额外服务。它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动,完全兼容JDBC和各种ORM框架。
Apache ShardingSphere,一套开源的分布式数据库解决方案组成的生态圈。
4.x文档地址:
https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/
准备MySQL数据库,分别建立库:ds1和ds2。
ds1、ds2库中分别执行:
CREATE TABLE `t_user` (
`id` BIGINT PRIMARY KEY ,
`name` VARCHAR(45) NOT NULL,
`status` VARCHAR(45) NOT NULL
);
create table `t_order0`(
`id` BIGINT PRIMARY KEY ,
`user_id` BIGINT NOT NULL,
`code` VARCHAR(45) NOT NULL
);
create table `t_order1`(
`id` BIGINT PRIMARY KEY ,
`user_id` BIGINT NOT NULL,
`code` VARCHAR(45) NOT NULL
);
pom.xml:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
<!-- pagehelper分页 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- shardingsphere -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
<version>4.1.1</version>
</dependency>
<!-- 使用XA事务时,需要引入此模块 -->
<!--
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-xa-core</artifactId>
<version>4.1.1</version>
</dependency>
-->
<!-- 使用BASE事务时,需要引入此模块 -->
<!--
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-base-seata-at</artifactId>
<version>4.1.1</version>
</dependency>
-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.1.22</version>
</dependency>
</dependencies>
代码案例1:
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
public class ShardingJDBCDemo01 {
public static void main(String[] args) {
// =====================================================================//
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第一个数据源
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://192.168.1.11:3306/ds1?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
dataSource1.setUsername("root");
dataSource1.setPassword("root1234");
dataSourceMap.put("ds1", dataSource1);
// 配置第二个数据源
DruidDataSource dataSource2 = new DruidDataSource();
dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://192.168.1.11:3306/ds2?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
dataSource2.setUsername("root");
dataSource2.setPassword("root1234");
dataSourceMap.put("ds2", dataSource2);
// =====================================================================//
// 配置User表规则
TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration("t_user");
// 配置分库
userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds$->{id%2+1}"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfig);
// =====================================================================//
// 获取数据源对象
// =====================================================================//
DataSource dataSource = null;
try {
dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
} catch (SQLException e) {
e.printStackTrace();
}
// =====================================================================//
// 插入数据
// =====================================================================//
for (int i = 41; i <= 60; i++) {
try {
String sql = "insert into t_user(`id`,`name`,`status`) values(" + i + ",'1','1')";
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
System.out.println("插入:" + i);
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("操作完成...");
}
}
代码案例2:
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
public class ShardingJDBCDemo02 {
public static void main(String[] args) {
// =====================================================================//
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第一个数据源
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://192.168.1.11:3306/ds1?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
dataSource1.setUsername("root");
dataSource1.setPassword("root1234");
dataSourceMap.put("ds1", dataSource1);
// 配置第二个数据源
DruidDataSource dataSource2 = new DruidDataSource();
dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://192.168.1.11:3306/ds2?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
dataSource2.setUsername("root");
dataSource2.setPassword("root1234");
dataSourceMap.put("ds2", dataSource2);
// =====================================================================//
// 配置User表规则
// 配置User表规则
TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration("t_user");
// 配置分库
userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds$->{id%2+1}"));
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds${1..2}.t_order${0..1}");
// 配置分库 + 分表策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id%2+1}"));
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_order${id%2}"));
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfig);
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
// =====================================================================//
// 获取数据源对象
// =====================================================================//
DataSource dataSource = null;
try {
dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
} catch (SQLException e) {
e.printStackTrace();
}
// =====================================================================//
// 插入数据
// =====================================================================//
for (int i = 1; i <= 10; i++) {
try {
String sql = "insert into t_order(`id`,`user_id`,`code`) values(" + i + "," + i + ",'1')";
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
System.out.println("插入:" + i);
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("操作完成...");
}
}