springboot-druid-shardingsphere-mybatis整合

项目背景

最近发现各小组之间的项目在搭建脚手架的时候，或多或少存在很多共性，于是想在已有的脚手架基础上引入sharding作为数据库代理实现分库分表；

Maven管理

使用的springboot版本1.5.9.RELEASE

主要框架版本

Springboot	1.5.9.RELEASE
druid	1.1.23
shardingsphere	4.1.1

 

 

 

 

由于公司一些保密原则，列举的pom依赖去掉了一些公司的内部jar版本，如果缺了什么jar请同学自行补齐；

<?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>1.5.9.RELEASE</version>
        <relativePath/>
        <!-- lookup parent from repository -->
    </parent>
    <groupId>com.shardingdruid</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>log4j2-ttl-thread-context-map</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <!--exclude log-->
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
                <exclusion>
                    <artifactId>jboss-logging</artifactId>
                    <groupId>org.jboss.logging</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <dependency>
            <groupId>com.lmax</groupId>
            <artifactId>disruptor</artifactId>
            <version>3.4.2</version>
        </dependency>
        <!--sharding jdbc begin-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!--sharding jdbc   end-->
        <!--mybatis begin-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!--mybatis end-->
        <!--pagehelper begin-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.13</version>
        </dependency>
        <!--pagehelper end-->
        <!--mysql begin-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mysql end-->
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.7</version>
                <configuration>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

表结构

本次脚手架搭建测试分库数：2

本次脚手架搭建测试分表数：16

1、建2个数据库，shardingdb0、shardingdb1

2、建表语句如下，参照此表结构建表1-16，每个分库都需要建满16张表；例如：t_userinfo_1，后面要_1还是直接1都可以，后面配置文件跟着变就好：

CREATE TABLE `t_userinfo` (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

代码

根据mybatis-generator来生成代码

生成的mapper检查一下表名是否正确，这里提供一份生成代码的配置文件

        
<table tableName="t_userinfo" domainObjectName="UserinfoDO"
               mapperName="UserinfoMapper"  enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>

生成好mapper之后，准备配置文件，这里使用了行表达式，指定分库分表计算的字段和计算逻辑：

分库逻辑：id%分库数

分表逻辑：(id/分库数)%分表数 + 1

例如id=100000015，则分库数=15%2=1，分表数=(15/2)%16 + 1 = 8，因此该数据落在ds1.t_userinfo_8表中

server.port=8300
management.port=8301
spring.application.name=wrangler-service
server.context-path=/wrangler
logging.config=classpath:log4j2.xml

#codis
spring.codis.nodes-addrs=10.190.15.122:6379
spring.redis.host=10.190.15.122
spring.redis.port=6379

#error
server.error.include-stacktrace=always

#sharding数据源
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://10.78.128.20:10087/shardingdb0?autoReconnect=true&useSSL=false&autoReconnectForPools=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
spring.shardingsphere.datasource.ds0.maximumPoolSize=20
spring.shardingsphere.datasource.ds0.minimumIdle=20
spring.shardingsphere.datasource.ds0.idleTimeout=30000

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://10.78.128.20:10087/shardingdb1?autoReconnect=true&useSSL=false&autoReconnectForPools=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
spring.shardingsphere.datasource.ds1.maximumPoolSize=20
spring.shardingsphere.datasource.ds1.minimumIdle=20
spring.shardingsphere.datasource.ds1.idleTimeout=30000

#显示sql
spring.shardingsphere.props.sql.show=true
#未配置分片规则的表将通过默认数据源定位
spring.shardingsphere.sharding.default-data-source-name=ds0
#自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID/LEAF_SEGMENT
spring.shardingsphere.sharding.default-key-generator.type=SNOWFLAKE
#自增列名称，缺省表示不使用自增主键生成器
spring.shardingsphere.sharding.default-key-generator.column=id
#属性配置, 注意：使用SNOWFLAKE算法，需要配置worker.id与max.tolerate.time.difference.milliseconds属性。若使用此算法生成值作分片值，建议配置max.vibration.offset属性
#spring.shardingsphere.sharding.tables.<logic-table-name>.key-generator.props.<property-name>=

####################单分片键####################
#分库分表计算列名
#spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=id
#分库逻辑:后8位对分库数取模
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithmExpression=ds${Long.parseLong(id.substring(id.length()-8)) % 2}
#精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.xiaoju.am.wrangler.algorithm.DatabasePreciseShardingAlgorithm
#范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.t_userinfo_.database-strategy.standard.range-algorithm-class-name=
####################单分片键####################

####################用于多分片键的复合分片场景####################
#分片列名称，多个列以逗号分隔
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.complex.sharding-columns=
#复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.complex.algorithm-class-name=
####################用于多分片键的复合分片场景####################

####################行表达式分片策略####################
#分片列名称
#spring.shardingsphere.sharding.database-strategy.inline.sharding-column=id
#分片算法行表达式，需符合groovy语法
#spring.shardingsphere.sharding.database-strategy.inline.algorithm-expression=ds${Long.parseLong(id.substring(id.length()-8)) % 2}

# 配置数据库和表t_userinfo对应到mapper.xml
spring.shardingsphere.sharding.tables.t_userinfo.actual-data-nodes=ds$->{0..1}.t_userinfo_$->{1..16}
# 配置根据哪个字段选择数据库
spring.shardingsphere.sharding.tables.t_userinfo.database-strategy.inline.sharding-column=id
# 配置选择哪个数据库的规则
spring.shardingsphere.sharding.tables.t_userinfo.database-strategy.inline.algorithm-expression=ds$->{id.substring(id.length() - 8)) % 2}
# 配置选择根据哪个字段选择表
spring.shardingsphere.sharding.tables.t_userinfo.table-strategy.inline.sharding-column=id
# 配置选择哪个表的规则
spring.shardingsphere.sharding.tables.t_userinfo.table-strategy.inline.algorithm-expression=t_userinfo_$->{(Long)(id.substring(id.length() - 8) / 2) % 16 + 1}
####################行表达式分片策略####################

####################Hint分片策略####################
#Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.hint.algorithm-class-name=
####################Hint分片策略####################

#绑定表规则列表，多个表名之间逗号分隔
#ssphere.sharding.binding-tables=t_userinfo_1,t_userinfo_2,t_userinfo_3,t_userinfo_4,t_userinfo_5,t_userinfo_6,t_userinfo_7,t_userinfo_8,t_userinfo_9,t_userinfo_10\
#,t_userinfo_11,t_userinfo_12,t_userinfo_13,t_userinfo_14,t_userinfo_15,t_userinfo_16

#广播表规则列表，如果每个库中都有look_up这种系统配置的基本表，又想做到一次CRUD操作能在所有库中都生效
#如果项目中分库后，有些表又是所有库中共用的，可以使用下面配置将其在sharding-sphere里指定为公共表
#spring.shardingsphere.sharding.broadcast-tables[0]=

准备一个Controller把生成的insert接口暴露出来

    @Resource
    UserinfoMapper userinfoMapper;

    @GetMapping("/insert")
    public int query(Long id) {
        UserinfoDO userinfoDO = new UserinfoDO();
        userinfoDO.setId(id);
        return userinfoMapper.insert(userinfoDO);
    }

使用postman进行测试

http://ip:port/test/query?id=20210000015

看控制台日志出现形如下面的计算结果则表示分库分表成功：

2021-01-18 16:24:24.010  [http-nio-8300-exec-1] INFO  [ShardingSphere-SQL:74] - _am||_msg=Actual SQL: ds1 ::: insert into t_userinfo_8 (id)
    values (?) ::: [20210000015]

参考文献：

http://shardingsphere.apache.org/document/legacy/3.x/document/cn/features/sharding/other-features/inline-expression/

http://shardingsphere.apache.org/document/legacy/1.x/cn/02-guide/configuration/

https://blog.csdn.net/womenyiqilalala/article/details/106115295

https://github.com/fahomlee/springboot-sharding/blob/master/pom.xml

https://github.com/lmxdawn/sharding-spring-boot-mybatis-example