专辑：分库分表ShardingSphere_纯java api案例-Java充电社【公众号：Java充电社】

## 4.1、案例git地址

```html
https://gitee.com/javacode2018/shardingsphere-demo
```

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/408/10190fb0-492b-4a3c-8c77-7ec4ef05daea.png)

## 4.2、准备工作

1）创建一个springboot应用

2）引入shardingsphere的maven配置

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

3）完整的maven配置如下

```xml
<?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/> 
    </parent>
    <groupId>com.itsoku</groupId>
    <artifactId>sj-demo1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sj-demo1</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <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.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </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>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>
```

## 4.3、案例1：单库多表

**需求**

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

**sql脚本**

```sql
drop database if exists sj_ds0;
create database sj_ds0;
use sj_ds0;
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
);

drop table if exists t_user;
create table t_user(
	id bigint not null primary key auto_increment,
	name varchar(128) not null
);
```

**java代码**

```java
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.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class Demo1 {
    public static void main(String[] args) throws SQLException {
        /**
         * 1、配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource1());

/**
         * 2.配置表的规则
         */
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds0.t_order_$->{0..1}");
        // 指定表的分片策略（分片字段+分片算法）
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2}"));

/**
         * 3、分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //将表的分片规则加入到分片规则列表
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        DataSource dataSource = ShardingDataSourceFactory.
                createDataSource(dataSourceMap, shardingRuleConfig, props);

/**
         * 6、获取连接，执行sql
         */
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);

/**
         * 测试向t_order表插入8条数据，8条数据会分散到2个表
         */
        PreparedStatement ps = connection.prepareStatement("insert into t_order (order_id,user_id,price) values (?,?,?)");
        for (long i = 1; i <= 8; i++) {
            int j = 1;
            ps.setLong(j++, i);
            ps.setLong(j++, i);
            ps.setLong(j, 100 * i);
            System.out.println(ps.executeUpdate());
        }

connection.commit();
        ps.close();
        connection.close();
    }

private static DataSource dataSource1() {
        HikariDataSource dataSource1 = new HikariDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/sj_ds0?characterEncoding=UTF-8");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root123");
        return dataSource1;
    }

}
```

**运行输出**

```java
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds0 ::: insert into t_order_1 (order_id,user_id,price) values (?, ?, ?) ::: [1, 1, 100]
1
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds0 ::: insert into t_order_0 (order_id,user_id,price) values (?, ?, ?) ::: [2, 2, 200]
1
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds0 ::: insert into t_order_1 (order_id,user_id,price) values (?, ?, ?) ::: [3, 3, 300]
1
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds0 ::: insert into t_order_0 (order_id,user_id,price) values (?, ?, ?) ::: [4, 4, 400]
1
```

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/408/26b2ef16-adb9-4969-904d-9eb0b3c6dd07.png)

## 4.4、案例2：多库多表

**需求**

2个库：sj_ds0、sj_ds1

2个库中都包含2个表：t_order_0，t_order_1

根据 user_id%2 路由库，根据 order_id%2路由表。

**执行sql**

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
);

drop database if exists sj_ds1;
create database sj_ds1;
use sj_ds1;
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
);
```

**代码**

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class Demo2 {
    public static void main(String[] args) throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource1());
        dataSourceMap.put("ds1", dataSource2());

/**
         * 2.配置表的规则
         */
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds$->{0..1}.t_order_$->{0..1}");
        // 指定db的分片策略（分片字段+分片算法）
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds$->{user_id % 2}"));
        // 指定表的分片策略（分片字段+分片算法）
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2}"));

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        DataSource dataSource = ShardingDataSourceFactory.
                createDataSource(dataSourceMap, shardingRuleConfig, props);

/**
         * 6、获取连接，执行sql
         */
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement ps = connection.prepareStatement("insert into t_order (order_id,user_id,price) values (?,?,?)");
        // 插入4条数据测试,每个表会落入1条数据
        for (long user_id = 1; user_id <= 2; user_id++) {
            for (long order_id = 1; order_id <= 2; order_id++) {
                int j = 1;
                ps.setLong(j++, order_id);
                ps.setLong(j++, user_id);
                ps.setLong(j, 100);
                System.out.println(ps.executeUpdate());
            }
        }

connection.commit();
        ps.close();
        connection.close();
    }

private static DataSource dataSource2() {
        HikariDataSource dataSource1 = new HikariDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/sj_ds1?characterEncoding=UTF-8");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root123");
        return dataSource1;
    }
}
```

**3行关键代码**

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/408/73e5d7c1-a148-42bd-bd0c-322ccf8b4024.png)

**运行输出**

```java
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds1 ::: insert into t_order_1 (order_id,user_id,price) values (?, ?, ?) ::: [1, 1, 100]
1
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds1 ::: insert into t_order_0 (order_id,user_id,price) values (?, ?, ?) ::: [2, 1, 100]
1
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds0 ::: insert into t_order_1 (order_id,user_id,price) values (?, ?, ?) ::: [1, 2, 100]
1
Logic SQL: insert into t_order (order_id,user_id,price) values (?,?,?)
Actual SQL: ds0 ::: insert into t_order_0 (order_id,user_id,price) values (?, ?, ?) ::: [2, 2, 100]
1
```

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/408/c434eadb-daa5-4a0f-8200-70c23615eaa4.png)

## 4.5、案例3：单库无分表规则

若表未指定分片规则，则直接路由到对应的表。

**sql脚本**

> sj_ds0 库中有 t_user 表

```java
drop database if exists sj_ds0;
create database sj_ds0;
use sj_ds0;

drop table if exists t_user;
create table t_user(
	id bigint not null primary key auto_increment,
	name varchar(128) not null
);
```

**代码**

```java
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class Demo3 {
    public static void main(String[] args) throws SQLException {
        /**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource1());

/**
         * 2、无分片规则
         */

/**
         * 3、分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        DataSource dataSource = ShardingDataSourceFactory.
                createDataSource(dataSourceMap, shardingRuleConfig, props);

Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);

PreparedStatement ps = connection.prepareStatement("insert into t_user (name) values (?)");
        ps.setString(1, "张三");
        System.out.println(ps.executeUpdate());

connection.commit();
        ps.close();
        connection.close();
    }

}
```

**运行输出**

```sql
Logic SQL: insert into t_user (name) values (?)
Actual SQL: ds0 ::: insert into t_user (name) values (?) ::: [张三]
```

## 4.6、案例4：多库无分表规则

**需求**

2个库：sj_ds0、sj_ds1

2个库中都包含表：t_user

t_user表不指定路由规则的情况下，向t_user表写入数据会落入哪个库呢？来看下效果

**sql**

```java
drop database if exists sj_ds0;
create database sj_ds0;
use sj_ds0;

drop table if exists t_user;
create table t_user(
	id bigint not null primary key auto_increment,
	name varchar(128) not null
);

drop database if exists sj_ds1;
create database sj_ds1;
use sj_ds1;

drop table if exists t_user;
create table t_user(
	id bigint not null primary key auto_increment,
	name varchar(128) not null
);
```

**java代码**

> 下面配置2个数据源，向t_user表插入数据，看看数据会落在哪个库？

```java
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

public class Demo4 {
    public static void main(String[] args) throws SQLException {
        /**
         * 1.配置2个数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds0", dataSource1());
        dataSourceMap.put("ds1", dataSource2());

/**
         * 2、无分片规则
         */

/**
         * 3、分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        DataSource dataSource = ShardingDataSourceFactory.
                createDataSource(dataSourceMap, shardingRuleConfig, props);

Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);

//插入4条数据，测试效果
        for (int i = 0; i < 4; i++) {
            PreparedStatement ps = connection.prepareStatement("insert into t_user (name) values (?)");
            ps.setString(1, "张三");
            System.out.println(ps.executeUpdate());
        }

connection.commit();
        connection.close();
    }

**运行输出**

> 输出如下，落入的库是不确定的。

```java
Logic SQL: insert into t_user (name) values (?)
Actual SQL: ds1 ::: insert into t_user (name) values (?) ::: [张三]
1
Logic SQL: insert into t_user (name) values (?)
Actual SQL: ds1 ::: insert into t_user (name) values (?) ::: [张三]
1
Logic SQL: insert into t_user (name) values (?)
Actual SQL: ds0 ::: insert into t_user (name) values (?) ::: [张三]
1
Logic SQL: insert into t_user (name) values (?)
Actual SQL: ds1 ::: insert into t_user (name) values (?) ::: [张三]
1
```

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)