springboot整合数据库中间件sharding-jdbc，难吗？

最新推荐文章于 2022-05-17 10:16:45 发布

零度念者

最新推荐文章于 2022-05-17 10:16:45 发布

阅读量319

点赞数

分类专栏：中间件 SpringBoot 文章标签： mysql java spring boot 中间件数据库

本文链接：https://blog.csdn.net/weixin_38997187/article/details/105949058

版权

SpringBoot 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

中间件

9 篇文章 2 订阅

订阅专栏

springboot2.0 + mysql8.0 + mybatis-plus + sharding-jdbc，本文分别从只分表、只分库、分表分库、读写分离等几方面介绍，话不多说，直接上代码，文末附demo。业务场景详见：数据库大数据量、高并发、高可用解决方案！

添加依赖

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

只分表

对库中的tb_user表分成0..5个表

测试建表语句

CREATE TABLE `tb_user0`  (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `mobile` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
  `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户' ROW_FORMAT = Dynamic;

配置文件

#数据源名称，多个数据源请用逗号分隔，此处只分表，数据源只有一个
spring.shardingsphere.datasource.names=ds

#数据源配置
spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://xxxx:3306/question?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=xxxx

#分表配置
#t_user数据节点配置(tb_user0, tb_user1, tb_user2, tb_user3, tb_user4, tb_user5)
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=ds.tb_user$->{0..5}
#拆分字段
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=id
#拆分策略
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user$->{id % 6}
#主键
spring.shardingsphere.sharding.tables.tb_user.key-generator.column=id
#主键生成方式
spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE

mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.jc.testshardingjdbc.*.entity

插入测试

package com.jc.testshardingjdbc;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jc.testshardingjdbc.dao.UserMapper;
import com.jc.testshardingjdbc.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;
import java.util.List;

@SpringBootTest
class TestShardingJdbcApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testInsertTable() {
        for (int i = 0; i < 1000; i++) {
            User user = new User();
            user.setMobile((13766549872L + i) + "");
            user.setPassword("sdsdsd" + i);
            user.setUsername("user" + i);
            user.setCreateDate(new Date());
            userMapper.insert(user);
        }
    }

}

查看插入效果

查看查询测试

package com.jc.testshardingjdbc;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jc.testshardingjdbc.dao.UserMapper;
import com.jc.testshardingjdbc.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;
import java.util.List;

@SpringBootTest
class TestShardingJdbcApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testQueryTable() {
        List<User> list = userMapper.selectList(new QueryWrapper<User>()
            .lambda().eq(User::getMobile, "13766549873")
        );
        list.forEach(System.out::println);
    } 

}

效果：对开发人员来说完全感应不到分表的存在

只分库

配置文件

#数据源名称，多个数据源请用逗号分隔，此处只分库
spring.shardingsphere.datasource.names=ds0,ds1

#数据源配置
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://xxxx:3306/question?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=xxxx

#数据源配置
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://xxxx:3306/question?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=xxxx

#分库字段
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
#分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}

#t_user数据节点配置 ds0.tb_user ds1.tb_user
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user
#主键
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
#主键生成方式
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.jc.testshardingjdbc.*.entity

插入效果

分表分库

配置文件

#数据源名称，多个数据源请用逗号分隔
spring.shardingsphere.datasource.names=ds0,ds1

#数据源配置
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://xxxx:3306/question?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=xxxx

#数据源配置
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://xxxx:3306/question?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=xxxx

#数据库分库字段
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
#分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}

#分表配置
#表节点 ds0.tb_user0 ds0.tb_user1 ds1.tb_user0 ds1.tb_user1
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=ds$->{0..1}.tb_user$->{0..1}
#分表字段
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=mobile
#分表策略
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user$->{mobile.toBigInteger() % 2}
#主键
spring.shardingsphere.sharding.tables.tb_user.key-generator.column=id
#主键生成方式
spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE

mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.jc.testshardingjdbc.*.entity

插入效果

读写分离

mysql主从模式搭建可以参考我的另外一篇博客十分钟学会mysql数据库主从模式搭建

配置文件

#数据源名称，多个数据源请用逗号分隔，此处只分库
spring.shardingsphere.datasource.names=master,slave0,slave1

#数据源配置master
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://xxxx:3306/question?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=xxxx

#数据源配置slave0
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://xxxx:3306/question_slave0?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=xxxx

#数据源配置slave1
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://xxxx:3306/question_slave1?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=xxxx

#读写分离配置 #读写分离数据源名称
spring.shardingsphere.masterslave.name=master_slave
#主库数据源名称
spring.shardingsphere.masterslave.master-data-source-name=master
#从库数据源名称列表 逗号分隔
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1
#从库负载均衡算法 可选值：ROUND_ROBIN，RANDOM。若`load-balance-algorithm-class-name`存在则设置为自己定义的type 该类需实现MasterSlaveLoadBalanceAlgorithm接口且提供无参数构造器
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.jc.testshardingjdbc.*.entity

demo地址 https://download.csdn.net/download/weixin_38997187/12412221

零度念者

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
springboot整合数据库中间件sharding-jdbc，难吗？

springboot2.0 + mysql8.0 + mybatis-plus +sharding-jdbc，话不多说，直接上代码。添加依赖<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-b...
复制链接

扫一扫