springboot 整合shardingsphere单裤分表 springboot分表查询

中文官网 https://shardingsphere.apache.org/index_zh.html

参考地址 https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-spring-boot/

本文Gitee地址https://gitee.com/newACheng/sharding-jdbc-demo/tree/master/

两个目的水平分表，水平分库水平分表

一、准备工作

基础环境
SpringBoot2.4.2 + Mybatis + ShardingJDBC4+Mysql8 + Druid连接池
表结构非常简单
此处注意下主键用 bigint
建表语句

CREATE TABLE `user_info1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL COMMENT '用户名称',
  `age` int DEFAULT NULL COMMENT '用户年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
CREATE TABLE `user_info2` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL COMMENT '用户名称',
  `age` int DEFAULT NULL COMMENT '用户年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=571018916663394305 DEFAULT CHARSET=utf8;

简单点来，三个字段够用就行
先把公共部分列出来，核心配置是在在yml文件
pom

<?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.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com</groupId>
    <artifactId>mysql_master_slave</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mysql_master_slave</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
<!--            <scope>runtime</scope>-->
        </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>
        <!--        shardingJDBC-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <!--        druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</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>

mapper.xml
注意下，查询别写select *，虽然只有三个字段，也别写，已经踩过的坑

<?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.mapper.UserInfoMapper">
    <resultMap id="BaserMapper" type="com.entity.UserInfo">
        <result column="id" jdbcType="BIGINT" property="id" ></result>
        <result column="user_name" jdbcType="VARCHAR" property="userName"></result>
        <result column="age" jdbcType="INTEGER" property="age"></result>
    </resultMap>
    <sql id="baseColumn">
        id,user_name,age
    </sql>
    <select id="getUserInfoList" resultMap="BaserMapper">
        select <include refid="baseColumn"></include> from user_info
    </select>
    <insert id="addUserInfo" parameterType="com.entity.UserInfo" useGeneratedKeys="true" keyProperty="id">
        insert into user_info (user_name,age) values (#{userName},#{age})
    </insert>
   

</mapper>

application.yml

server:
  port: 8081
  servlet:
    context-path: /
spring:
  profiles:
    active: shardingDataBase
#    active: mysqlProxy
#    active: shardingTable
#    active: dev # 单库单表操作
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.example.entity

二、水平分表

新建一个application-shardingTable.yml

# 数据分片应用于单库分表操作
spring:
  shardingsphere:
    datasource:
      names: db1
      db1: # 数据库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.119.50:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: root

    sharding:
      tables:
        user_info:
        # 配置表的分布，表的策略
          actual-data-nodes: db1.user_info->{1..2}
          # 指定user_info表 主键id 生成策略为 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
          # 指定分片策略
          table-strategy:
            inline:
              #根据年龄插入不同的表，如果年龄大于15岁插入user_info1表，如果年龄小于或等于15插入user_info2表
              sharding-column: age
              algorithm-expression: user_info->{age>15?1:2}
              #约定id值是偶数添加到user_info1表，如果id是奇数添加到user_info2表
#                sharding-column: id
#                algorithm-expression: user_info$->{id%2+1}
    props:
      # 打开sql输出日志
      sql:
        show: true

开始测试，application.yml中的active值改成shardingTable启动项目

出现下面信息标识启动成功

测试

插入数据，年龄为 12，根据策略应该存入user_info2表

日志

数据库结果

年龄变成16试下，应该插入user_info表中

日志

数据库

查询测试，应该会查询到两条数据

日志

结果

三、水平分库水平分表

application-shardingDataBase.yml

#水平分库水平分表配置,两个数据库，每个数据库中两张表
spring:
  shardingsphere:
    # 数据源名称 db1 、db2
    datasource:
      names: db1,db2
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.119.51:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: root
      db2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.119.52:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: root

    # 配置数据库的分布，表的分布
    sharding:
      tables:
        user_info:
          # db1:user_info1 user_info2; db2:user_info1,user_info2;
          actual-data-nodes: db$->{1..2}.user_info$->{1..2}
          # 指定user_info表 主键id 生成策略为 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
          # 指定数据库分片策略 约定user_info值是偶数添加到user_info1中，奇数添加到user_info2中
          database-strategy:
            inline:
              sharding-column: id
              algorithm-expression: db$->{id%2+1}
            # 指定表分片策略
          table-strategy:
            inline:
              #根据年龄插入不同的表，如果年龄大于15岁插入user_info1表，如果年龄小于或等于15插入user_info2表
#              sharding-column: age
#              algorithm-expression: user_info$->{age>15?1:2}
              #约定id值是偶数添加到user_info1表，如果id是奇数添加到user_info2表
              sharding-column: id
              algorithm-expression: user_info$->{id%2+1}
    props:
      sql:
        show: true

开始测试，application.yml中的active值改成shardingDataBase启动项目

出现下面信息标识启动成功

开始测试

返回值为偶数

控制台日志

根据策略，在第一个数据库的user_info1表中

查询测试结果