MySQL中的分库分表框架-ShardingSphere

一、前言

ShardingSphere，大家多少都有听过吧，Apache顶级项目，国内大佬的巨作，Java中用的最多的一个分库分表框架，如果你们的系统中需要分库分表，强烈建议使用，完全可以满足你的所有需求。

本文并不会介绍什么是分库分表，而是通过大量案例，让你了解ShardingSphere可以做什么？如何做？以及SpringBoot中如何使用它等等。

ShardingSphere的Git官网地址：

https://github.com/apache/shardingsphere

ShardingSphere目前最新版本5.X了，大版本之间变化比较大，本次以4.1.1为例来介绍。

如果对分库分表没有概念，可以先去下面这个地址看看，然后再继续向下看。 

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

原文地址：

http://itsoku.com/course/25/410

 

二、纯Java API代码案例

1）引入shardingsphere的maven配置

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>4.1.1</version>
</dependency>

2）完整的maven配置如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.7.1</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.bc</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.2</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>4.1.1</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

其完整的项目结构如下所示：

  

2.1、案例1：单库多表

需求：一个库中有2个订单表，按照订单id取模，将数据路由到指定的表。

在db1数据库中创建表t_order_0和t_order_1：

drop database if exists db1;
create database db1;
use db1;

drop table if exists t_order_0;
create table t_order_0(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

drop table if exists t_order_1;
create table t_order_1(
    order_id bigint not null primary key,
    user_id bigint not null,
    price bigint not null
);

Java代码：

package com.bc.demo.apidemo;

import com.zaxxer.hikari.HikariDataSource;
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 org.apache.shardingsphere.underlying.common.config.properties.ConfigurationP