ShardingSphere入门实战(1)-Sharding-JDBC使用

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。具体介绍可以查看官网https://shardingsphere.apache.org/。本文主要介绍在Spring Boot工程中使用Sharding-JDBC，文中使用到的软件版本：Java 1.8.0_191、sharding-jdbc-core 4.1.1、MySQL 5.7.26。

1、引入依赖

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

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

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

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
</dependency>

2、数据分片

2.1、对应配置

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1 #数据源名称，多数据源以逗号分隔
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.10:3306/itest
        username: admin
        password: Root_123!
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.20:3306/itest
        username: admin
        password: Root_123!
    sharding:
      tables:
        t_user: #逻辑表名,在一个库里分表:ds0.t_user_0,ds0.t_user_1
          actual-data-nodes: ds0.t_user_$->{0..1}
          table-strategy: #表分片策略
            inline: #行表达式分片策略
              sharding-column: user_id #分片的字段
              algorithm-expression: t_user_$->{user_id % 2} #分片的算法
        t_dept: #分库,两个库里建系统的表:ds0.t_dept,ds1.t_dept
          actual-data-nodes: ds$->{0..1}.t_dept
          database-strategy: #数据库分片策略
            inline: #行表达式分片策略
              sharding-column: dept_id #分片的字段
              algorithm-expression: ds$->{dept_id % 2} #分片的算法

    props:
      sql:
        show: true

2.2、对应程序

/**
 * 数据分片
 * t_user在同一个库里分表(t_user_0,t_user_1)
 * t_dept分库
 */
@Test
public void fragmentation() {
    Connection con = null;
    try {
        con = dataSource.getConnection();
        con.setAutoCommit(false);
        Statement st = con.createStatement();

        //插入ds0.t_user_0表
        st.executeUpdate("insert into t_user(user_id,user_name,age) values(10,'赵云', 30)");
        //插入ds0.t_user_1表
        st.executeUpdate("insert into t_user(user_id,user_name,age) values(11,'张飞', 31)");

        //插入ds0.t_dept表
        st.executeUpdate("insert into t_dept(dept_id,dept_name) values(10,'dept10')");
        //插入ds1.t_dept表
        st.executeUpdate("insert into t_dept(dept_id,dept_name) values(11,'dept11')");

        ResultSet rs = st.executeQuery("select user_id,user_name from t_user where user_id in(10,11)");
        while (rs.next()) {
            logger.info("user_id={},user_name={}", rs.getString("user_id"), rs.getString("user_name"));
        }

        rs = st.executeQuery("select dept_id,dept_name from t_dept where dept_id in(10,11)");
        while (rs.next()) {
            logger.info("dept_id={},dept_name={}", rs.getString("dept_id"), rs.getString("dept_name"));
        }

        con.commit();
    } catch (Exception e) {
        JdbcUtil.rollback(con);
        e.printStackTrace();
    } finally {
        JdbcUtil.close(con);
    }
}

3、读写分离

3.1、对应配置

spring:
  shardingsphere:
    datasource:
      names: master,slave0
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.10:3306/itest
        username: admin
        password: Root_123!
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.20:3306/itest
        username: admin
        password: Root_123!
    masterslave:
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave0
    props:
      sql:
        show: true

3.2、对应程序

/**
 * 读写分离，主库写，从库读
 * 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性
 */
@Test
public void readWrite() {
    Connection con = null;
    try {
        con = dataSource.getConnection();
        Statement st = con.createStatement();

        //从slave0读数据
        ResultSet rs = st.executeQuery("select * from t_student");
        while (rs.next()) {
            System.out.println(rs.getString("id") + "|" + rs.getString("name"));
        }

        //写入master
        st.executeUpdate("insert into t_student(id,name) values(100,'测试')");

        //从master读数据
        rs = st.executeQuery("select * from t_student");
        while (rs.next()) {
            System.out.println(rs.getString(1) + "|" + rs.getString(2));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(con);
    }
}

/**
 * 读写分离，强制主库路由
 */
@Test
public void hintMasterRouteOnly() {
    Connection con = null;
    try {
        con = dataSource.getConnection();
        Statement st = con.createStatement();
        HintManager hintManager = HintManager.getInstance();
        //主库路由
        hintManager.setMasterRouteOnly();

        //从master读数据
        ResultSet rs = st.executeQuery("select * from a_orm");
        while (rs.next()) {
            logger.info(rs.getString(1) + "|" + rs.getString(2));
        }
        hintManager.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(con);
    }
}

4、完整代码

package com.inspur.demo.shardingsphere;

import com.inspur.demo.shardingsphere.util.JdbcUtil;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * ShardingJdbc使用样例
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingJdbcCase {
    private static Logger logger = LoggerFactory.getLogger(ShardingJdbcCase.class);

    @Resource
    private DataSource dataSource;

    /**
     * 数据分片
     * t_user在同一个库里分表(t_user_0,t_user_1)
     * t_dept分库
     */
    @Test
    public void fragmentation() {
        Connection con = null;
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            Statement st = con.createStatement();

            //插入ds0.t_user_0表
            st.executeUpdate("insert into t_user(user_id,user_name,age) values(10,'赵云', 30)");
            //插入ds0.t_user_1表
            st.executeUpdate("insert into t_user(user_id,user_name,age) values(11,'张飞', 31)");

            //插入ds0.t_dept表
            st.executeUpdate("insert into t_dept(dept_id,dept_name) values(10,'dept10')");
            //插入ds1.t_dept表
            st.executeUpdate("insert into t_dept(dept_id,dept_name) values(11,'dept11')");

            ResultSet rs = st.executeQuery("select user_id,user_name from t_user where user_id in(10,11)");
            while (rs.next()) {
                logger.info("user_id={},user_name={}", rs.getString("user_id"), rs.getString("user_name"));
            }

            rs = st.executeQuery("select dept_id,dept_name from t_dept where dept_id in(10,11)");
            while (rs.next()) {
                logger.info("dept_id={},dept_name={}", rs.getString("dept_id"), rs.getString("dept_name"));
            }

            con.commit();
        } catch (Exception e) {
            JdbcUtil.rollback(con);
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con);
        }
    }


    /**
     * 读写分离，主库写，从库读
     * 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性
     */
    @Test
    public void readWrite() {
        Connection con = null;
        try {
            con = dataSource.getConnection();
            Statement st = con.createStatement();

            //从slave0读数据
            ResultSet rs = st.executeQuery("select * from t_student");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "|" + rs.getString("name"));
            }

            //写入master
            st.executeUpdate("insert into t_student(id,name) values(100,'测试')");

            //从master读数据
            rs = st.executeQuery("select * from t_student");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "|" + rs.getString(2));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con);
        }
    }

    /**
     * 读写分离，强制主库路由
     */
    @Test
    public void hintMasterRouteOnly() {
        Connection con = null;
        try {
            con = dataSource.getConnection();
            Statement st = con.createStatement();
            HintManager hintManager = HintManager.getInstance();
            //主库路由
            hintManager.setMasterRouteOnly();

            //从master读数据
            ResultSet rs = st.executeQuery("select * from a_orm");
            while (rs.next()) {
                logger.info(rs.getString(1) + "|" + rs.getString(2));
            }
            hintManager.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con);
        }
    }

}