Spring Boot整合Sharding-JDBC实现分库分表+读写分离io.shardingsphere(4)

1、数据库准备

       1、192.168.8.162  test1主

       2、192.168.8.134  test1从

       3、192.168.8.176  test1从

       4、192.168.8.162  test2主

       5、192.168.8.134  test2从

       6、192.168.8.176  test2从

2、准备分库分表

USE `test1`;

DROP TABLE IF EXISTS `t_user_0`;
CREATE TABLE `t_user_0` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_1`;
CREATE TABLE `t_user_1` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_2`;
CREATE TABLE `t_user_2` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_3`;
CREATE TABLE `t_user_3` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


USE `test2`;

DROP TABLE IF EXISTS `t_user_0`;
CREATE TABLE `t_user_0` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_1`;
CREATE TABLE `t_user_1` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_2`;
CREATE TABLE `t_user_2` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_3`;
CREATE TABLE `t_user_3` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、上代码

1、pom.xml配置引入maven依赖

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

        <!--springboot整合mybatis的依赖 -->
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- 这里用的是sharding-jdbc-spring-boot-starter 需要注意的是,此时druid不能用spring-boot-starter版本的,需要用正常的包: -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

2、在application.yml中配置引用数据源及分库分表信息

mybatis.config-location: classpath:META-INF/mybatis-config.xml
spring:
  profiles:
    active: sharding-tbl-ms
  main:
    allow-bean-definition-overriding: true
sharding:
  jdbc:
         ### 数据库
    dataSource:
          ### 数据库的别名
      names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-0-slave-1,ds-master-1-slave-0,ds-master-1-slave-1
       # 主库1 ,master数据库
      ds-master-0: 
         ###  数据源类别
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.8.162:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
         ### 主库1从库1 ,slave数据库
      ds-master-0-slave-0:
         ###  数据源类别
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.8.134:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
        ### 主库1从库1 ,slave数据库
      ds-master-0-slave-1:
         ###  数据源类别
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.8.176:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
        # 主库2 ,master数据库
      ds-master-1: 
         ###  数据源类别
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.8.162:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
         ### 主库2从库1 ,slave数据库
      ds-master-1-slave-0:
         ###  数据源类别
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.8.134:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
        ### 主库2从库2 ,slave数据库
      ds-master-1-slave-1:
         ###  数据源类别
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.8.176:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
        username: root
        password: root
        maxPoolSize: 20
    props:
      sql:
        show: true
    config:
      masterslave: # 配置读写分离
          # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round-robin 轮询
        load-balance-algorithm-type: round-robin
        name: datasource
      sharding:
        master-slave-rules:
          ds_0:
          ###配置的是主库的数据库名，本案例为ds-master-0，其中ds_0为分区名。
            master-data-source-name: ds-master-0
          ###配置的是从库的数据库名，本案例为ds-master-0-slave-0,ds-master-0-slave-1
            slave-data-source-names: ds-master-0-slave-0,ds-master-0-slave-1
          ds_1:
          ###配置的是主库的数据库名，本案例为ds-master-1，其中ds_1为分区名。
            master-data-source-name: ds-master-1
          ###配置的是从库的数据库名，本案例为ds-master-1-slave-0,ds-master-1-slave-1
            slave-data-source-names: ds-master-1-slave-0,ds-master-1-slave-1
        tables:
          ###需要分表的表名
          t_user:
          ###配置的分表信息，真实的数据库信息。ds_0.t_user_$->{0…3}，表示读取ds_0数据源的user_0、user_1、user_2、user_3。
            actual-data-nodes: ds_$->{0..1}.t_user_$->{0..3}
            database-strategy:
              standard:
            ###是配置数据分库的策略的类，这里是自定义的类MyDBPreciseShardingAlgorithm
                precise-algorithm-class-name: com.demo.shardingjdbc.MyDBPreciseShardingAlgorithm
           ###配置的数据分表的字段，是根据id来分的
                sharding-column: id
            table-strategy:
              standard:
          ###是配置数据分表的策略的类，这里是自定义的类MyTablePreciseShardingAlgorithm
                precise-algorithm-class-name: com.demo.shardingjdbc.MyTablePreciseShardingAlgorithm
          ###配置的数据分表的字段，是根据id来分的      
                sharding-column: id

3、配置分库分表分片规则（结合application.yml）

       分库规则（结合pplication.yml中database-strategy）

package com.demo.shardingjdbc;


import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

/**
 * 自定义分片算法
 * 
 * @author hzy
 *
 */
public class MyDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }

}

     分表规则（结合pplication.yml中table-strategy）

  

package com.demo.shardingjdbc;


import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

/**
 * 自定义分片算法
 * 
 * @author hzy
 *
 */
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(shardingValue.getValue() % 4 + "")) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }

}

4、mybatis操作数据库配置

User.java

package com.demo.shardingjdbc.entity;

import java.io.Serializable;

import lombok.Data;
@Data
public class User implements Serializable {

    private static final long serialVersionUID = -1205226416664488559L;
    private Integer id;
    private String name;
    private String sex;

}

mapper层

package com.demo.shardingjdbc.mapper;


import org.apache.ibatis.annotations.Mapper;

import com.demo.shardingjdbc.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {

    Integer addUser(User user);

    List<User> list();

}

mybatis配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <package name="com.demo.shardingjdbc.entity"/>
    </typeAliases>
    <mappers>
        <mapper resource="META-INF/mappers/User.xml"/>
    </mappers>
</configuration>

user.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.shardingjdbc.mapper.UserMapper">
    
    <resultMap id="baseResultMap" type="com.demo.shardingjdbc.entity.User">
        <result column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="VARCHAR" />
    </resultMap>
    
    <insert id="addUser" parameterType="com.demo.shardingjdbc.entity.User">
        INSERT INTO t_user (
          id, name, sex
        )
        VALUES (
        #{id,jdbcType=INTEGER},
        #{name,jdbcType=VARCHAR},
        #{sex,jdbcType=VARCHAR}
        )
    </insert>
   
    <select id="list" resultMap="baseResultMap">
        SELECT u.* FROM t_user u order by u.id
    </select>

</mapper>

5、service层

package com.demo.shardingjdbc.service.impl;


import com.demo.shardingjdbc.entity.User;
import com.demo.shardingjdbc.mapper.UserMapper;
import com.demo.shardingjdbc.service.UserService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl  implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public Integer addUser(User user) {

        // 强制路由主库
        return userMapper.addUser(user);
    }

    @Override
    public List<User> list() {

        return userMapper.list();
    }
}

6、controller层

package com.demo.shardingjdbc.controller;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.demo.shardingjdbc.entity.User;
import com.demo.shardingjdbc.service.UserService;

import lombok.extern.slf4j.Slf4j;

@RestController
@Slf4j
public class UserController {


    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public Object list() {
        return userService.list();
    }

    @GetMapping("/add")
    public Object add() {
        int num=0;
        for(int i=1;i<=300;i++) {
            User user = new User();
            user.setId(i);
            user.setName("hzy"+(i));
            String sex=(i%2==0)? "男":"女";
            user.setSex(sex);
                
           int resutl=   userService.addUser(user);
            log.info("insert:"+user.toString()+" result:"+resutl);
            num=num+resutl;
        }
        return num;
    }
}

 完成。在浏览器上执行localhost:8080/add，然后去数据库中查询，可以看到test1.t_user_0、test1.t_user_2、test2.t_user_1、test2.t_user_3分别插入了数据。

   然后访问localhost:8080/users，可以查询数据库中四个表中的所有数据。可见Sharding-JDBC在插入数据的时候，根据数据分库分表策略，将数据存储在不同库不同表中，查询时将数据库从多个表中查询并聚合。