专辑：分库分表ShardingSphere_yml方式-Java充电社【公众号：Java充电社】

纯java api的方式，写起来比较繁琐，shardingsphere为我们提供了更简单的方式：yml配置文件的方式。

## 11.1、使用步骤

- 创建一个yml格式的文件，将分库分表信息配置到yml中
- 通过yml文件创建DataSource
- 使用DataSource执行db操作

下面整个案例感受下效果。

## 11.2、需求

2个库：sj_ds0、sj_ds1

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

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

## 11.3、执行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 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
);
```

## 11.4、test.yml

resource目录创建test.yml，和SpringBoot项目的application.properties同一个目录，内容如下，将所有分库分表信息都丢到这个文件中

```yaml
dataSources:
  ds0: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/sj_ds0?characterEncoding=UTF-8
    username: root
    password: root123
  ds1: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/sj_ds1?characterEncoding=UTF-8
    username: root
    password: root123
shardingRule:
  tables:
    t_order:
      actualDataNodes: ds$->{0..1}.t_order_$->{0..1}
      databaseStrategy:
        inline:
          algorithmExpression: ds$->{user_id % 2}
          shardingColumn: user_id
      logicTable: t_order
      tableStrategy:
        inline:
          algorithmExpression: t_order_$->{order_id % 2}
          shardingColumn: order_id
```

## 11.5、java代码

> 代码如下，通过test.yml来创建dataSource，是不是方便了很多。

```java
import org.apache.shardingsphere.shardingjdbc.api.yaml.YamlShardingDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class YmlShardingTest {

public static void main(String[] args) throws IOException, SQLException {
        //1.读取demo2.yml文件
        InputStream inputStream = ClassLoader.getSystemResourceAsStream("test.yml");
        byte[] bytes = new byte[inputStream.available()];
        inputStream.read(bytes);

//2.创建数据源
        DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(bytes);

/**
         * 3、获取连接，执行sql
         */
        String sql = "insert into t_order (order_id,user_id,price) values (?,?,?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);) {
            // 插入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());
                }
            }
        }
    }
}
```

## 11.6、运行输出

```sqlite
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
```

## 11.7、yml中可以配置哪些？

有2种方法可以知道。

### 1）方法1：官方文档

```java
https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/
```

###  2）方法2：看源码

yml配置的方式主要在下面代码中

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/415/0660a3c6-8244-406b-9d33-ac771d58c233.png)

进入`org.apache.shardingsphere.shardingjdbc.api.yaml.YamlShardingDataSourceFactory#createDataSource(byte[])`源码，如下，这个类，会读取yaml配置，将其转换为`YamlRootShardingConfiguration`，那么我们看这个类就知道yaml中如何配置了。

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/415/3e3a0b7b-3ef3-40a8-9f76-8b6f32037b11.png)

进入`YamlRootShardingConfiguration`中看看，如下，就是普通的javabean，这个类中有的属性，都是yaml中可以配置的类容，很简单，大家自行摸索摸索。

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/415/fe64ad13-6ec8-41d7-8be3-9caf53cb8cdf.png)

## 11.8、本章案例完整代码

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

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/415/e464b45f-1334-494b-997d-94291295d954.png)

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