24°

基于springboot+mybatis+Sharding jdbc实现的分库分表、读写分离

这两天抽空搞了一下Sharding jdbc,鉴于现在做的项目中也用到了这块(不是我搞的),作为一名对技术痴迷的渣男,怎么可能无视它的存在,必须自己来搞一搞哈。

搞起~

项目搭建

1、新建一个spring boot工程,添加依赖

    <dependencies>
        <!--核心服务-->
        <dependency>
            <groupId>top.qrainly</groupId>
            <artifactId>bj_core</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--sharding-jdbc-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>
        <!--jmockdata-->
        <dependency>
            <groupId>com.github.jsonzou</groupId>
            <artifactId>jmockdata</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>

这里友情推荐一下,依赖里用到了朋友开源的一个工具插件JMockData,此乃开发测试之利器,强烈推荐!!!

2、在master库执行sql脚本,创建用户表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_2`;
CREATE TABLE `user_2` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_3`;
CREATE TABLE `user_3` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_4`;
CREATE TABLE `user_4` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(12) NOT NULL,
  `password` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx-username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

配置主从复制的内容请参考windows上mysql的主从配置

3、配置生成dao/domain文件 mybatis-generator配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
&lt;context id="DB2Tables"    targetRuntime="MyBatis3"&gt;
    &lt;commentGenerator&gt;
        &lt;property name="suppressDate" value="true"/&gt;
        &lt;property name="suppressAllComments" value="true"/&gt;
    &lt;/commentGenerator&gt;
    &lt;!--数据库链接地址账号密码--&gt;
    &lt;jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&amp;amp;characterEncoding=utf-8&amp;amp;useSSL=false&amp;amp;allowMultiQueries=true" userId="root" password="123456"&gt;
    &lt;/jdbcConnection&gt;
    &lt;javaTypeResolver&gt;
        &lt;property name="forceBigDecimals" value="false"/&gt;
    &lt;/javaTypeResolver&gt;
    &lt;!--生成Model类存放位置--&gt;
    &lt;javaModelGenerator targetPackage="top.qrainly.sharding.jdbc.domain" targetProject="src/main/java"&gt;
        &lt;property name="enableSubPackages" value="true"/&gt;
        &lt;property name="trimStrings" value="true"/&gt;
    &lt;/javaModelGenerator&gt;
    &lt;!--生成映射文件存放位置--&gt;
    &lt;sqlMapGenerator targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java"&gt;
        &lt;property name="enableSubPackages" value="true"/&gt;
    &lt;/sqlMapGenerator&gt;
    &lt;!--生成Dao类存放位置--&gt;
    &lt;!-- 客户端代码,生成易于使用的针对Model对象和XML配置文件 的代码
            type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper对象
            type="MIXEDMAPPER",生成基于注解的Java Model 和相应的Mapper对象
            type="XMLMAPPER",生成SQLMap XML文件和独立的Mapper接口
    --&gt;
    &lt;javaClientGenerator type="XMLMAPPER" targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java"&gt;
        &lt;property name="enableSubPackages" value="true"/&gt;
    &lt;/javaClientGenerator&gt;
    &lt;!--生成对应表及类名--&gt;
    &lt;table tableName="user" domainObjectName="User" mapperName="UserDAO" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true"&gt;&lt;/table&gt;
&lt;/context&gt;

</generatorConfiguration>

4、提供一个查询和添加的接口

controller

/**
 * @author v_liuwen
 * @date 2019-07-10
 */
@RestController
@RequestMapping(value = "/user")
@Slf4j
public class UserController {
@Autowired
private IUserService iUserService;

@GetMapping("/list")
public JsonResult&lt;List&lt;User&gt;&gt; list() {
    List&lt;User&gt; userList = iUserService.findUserList();
    return JsonResult.okJsonResultWithData(userList);
}

@GetMapping("/add")
public JsonResult&lt;Boolean&gt; add(@RequestParam(name = "user",required = false) User user) {
    log.info("新增用户信息--&gt;{}", JSONObject.toJSONString(user));
    boolean result = iUserService.addUser();
    return JsonResult.okJsonResultWithData(result);
}

@GetMapping("/batchAdd")
public JsonResult&lt;Boolean&gt; batchAdd() {
    boolean result = iUserService.batchAddUser();
    return JsonResult.okJsonResultWithData(result);
}

}

service


/**
 * @author v_liuwen
 * @date 2019-07-10
 */
@Service
@Slf4j
public class IUserServiceImpl implements IUserService {
private AtomicInteger num = new AtomicInteger(1);

@Resource
private UserDAO userDAO;

@Override
public boolean addUser() {
    User user = JMockData.mock(User.class);
    int i = userDAO.insertSelective(user);
    if(i == 1){
        return true;
    }
    return false;
}

@Override
public List&lt;User&gt; findUserList() {
    List&lt;User&gt; userList = userDAO.findUserList();
    return userList;
}

@Override
public boolean batchAddUser() {
    try{
        for (int i =100;i&lt;150;i++){
            User user = JMockData.mock(User.class);
            user.setId(num.getAndIncrement());
            userDAO.insertSelective(user);
        }
        return true;
    }catch (Exception e){
        log.error("批量插入失败  失败原因--&gt;{}",e.getMessage());
        return false;
    }
}

}

5、配置文件(重点在这里)

基础配置-->application.yml

server:
  port: 8018

spring: application: name: bj-sharding-jdbc main: allow-bean-definition-overriding: true profiles: # rw-读写分离配置 table-数据分表+读写分离 dt-分库分表+读写分离 active: dt mybatis: mapper-locations: classpath:/top/qrainly//dao//*.xml

读写分离配置-->application-rw.yml

sharding:
  jdbc:
    dataSource:
      names: db-test0,db-test1
      db-test0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        maxPoolSize: 20
      db-test1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        maxPoolSize: 20
    config:
        # 仅配置读写分离时打开此配置
      masterslave:
        # 配置从库选择策略,提供轮询与随机,这里选择用轮询//random 随机 //round_robin 轮询
        load-balance-algorithm-type: round_robin
        name: db1s2
        master-data-source-name: db-test0
        slave-data-source-names: db-test1
    props:
      sql:
        # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!!
        show: true

数据分表+读写分离配置-->application-table.yml

sharding:
  jdbc:
    dataSource:
      names: db-test0,db-test1
      db-test0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        maxPoolSize: 20
      db-test1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        maxPoolSize: 20
    config:
      # 配置数据分表
      sharding:
        tables:
          user:
            table-strategy:
              standard:
                sharding-column: id
                precise-algorithm-class-name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm
            # 读取ds_0数据源的user_0、user_1、user_2、user_3
            actual-data-nodes: ds_0.user_$->{0..3}
        master-slave-rules:
          ds_0:
            master-data-source-name: db-test0
            slave-data-source-names: db-test1
    props:
      sql:
        # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!!
        show: true

分库分表+读写分离配置-->application-dt.yml

---
sharding:
  jdbc:
    datasource:
      names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-1-slave-0
      # 主库0
      ds-master-0:
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
      # 主库0-从库0
      ds-master-0-slave-0:
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
      # 主库1
      ds-master-1:
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/bj_sharding1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
      # 主库1-从库0
      ds-master-1-slave-0:
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3307/bj_sharding1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
    config:
      sharding:
        tables:
          user:
            table-strategy:
              inline:
                sharding-column: id
                algorithm-expression: user_$->{id % 5}
            key-generator-column-name: id
            actual-data-nodes: ds_$->{0..1}.user_$->{0..4}
        default-database-strategy:
          inline:
            # 置的分库的字段,本案例是根据id进行分
            sharding-column: id
            # 置的分库的逻辑,根据id%2进行分
            algorithm-expression: ds_$->{id % 2}
        master-slave-rules:
          ds_1:
            slave-data-source-names: ds-master-1-slave-0
            master-data-source-name: ds-master-1
          ds_0:
            slave-data-source-names: ds-master-0-slave-0
            master-data-source-name: ds-master-0

注:分库分表配置下需要在@SpringBootApplication上添加参数exclude={DataSourceAutoConfiguration.class}

ok,切换spring.profiles.active在不同配置模式下耍吧!

持续更新中...

本文由【qrainly】发布于开源中国,原文链接:https://my.oschina.net/qrainly/blog/3072946

全部评论: 0

    我有话说: