分库分表-springboot结合sharding-jdbc实战

背景

随之目前一些业务数据量越来越大，分库分表已经成为一种趋势，而sharding-jdbc也成为了一种趋势，本文就如何引入sharding-jdbc做一个简单介绍，代码框架已经是具备增删改查的能力的来说明。

步骤

• 先引入maven配置：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid.version}</version>
</dependency>
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding.jdbc.version}</version>
</dependency>

• 数据库配置文件：

sharding.jdbc.datasource.names=ds0,ds1
sharding.jdbc.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/demo_sharding?useUnicode=true&characterEncoding=utf-8\
  &useSSL=true\
  &serverTimeZone=UTC
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=123456

sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/demo_sharding1?useUnicode=true&characterEncoding=utf-8\
  &useSSL=true\
  &serverTimeZone=UTC
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=123456


spring.shardingsphere.props.sql.show = true

sharding.jdbc.config.sharding.tables.t_user.key-generator-column-name=id
sharding.jdbc.config.sharding.tables.t_user.actual-data-nodes=ds${0..1}.t_user$->{0..1}
sharding.jdbc.config.sharding.tables.t_user.database-strategy.inline.sharding-column=city_id
sharding.jdbc.config.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds${city_id%2}
sharding.jdbc.config.sharding.tables.t_user.table-strategy.inline.sharding-column=sex
sharding.jdbc.config.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user${sex%2}

sharding.jdbc.config.sharding.tables.t_address.key-generator-column-name=id
sharding.jdbc.config.sharding.tables.t_address.actual-data-nodes=ds${0..1}.t_address
sharding.jdbc.config.sharding.tables.t_address.database-strategy.inline.sharding-column=lit
sharding.jdbc.config.sharding.tables.t_address.database-strategy.inline.algorithm-expression=ds${lit%2}

用户名、密码以及库要修改成自己的，策略可以自行变更。

• 数据库表结构

CREATE TABLE `t_address` (
  `id` bigint(20) NOT NULL,
  `code` varchar(64) DEFAULT NULL COMMENT '编码',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `pid` varchar(64) NOT NULL DEFAULT '0' COMMENT '父id',
  `type` int(11) DEFAULT NULL COMMENT '1国家2省3市4县区',
  `lit` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user0` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user1` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 可以上代码了 从实体层开始，User.java

package com.lbb.demo.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;

/**
 * @author lbb
 * @date 2020/9/4 下午2:29
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class User {
    private Long id;
    private String name;
    private String phone;
    private String email;
    private String password;
    private Integer cityId;
    private Date createTime;
    private Integer sex;
}

Address.java

package com.lbb.demo.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author lbb
 * @date 2021/6/23 8:06 下午
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Address {
    private Long id;
    private String code;
    private String name;
    private String pid;
    private Integer type;
    private Integer lit;
}

再来配置Dao层：

package com.lbb.demo.dao;

import com.lbb.demo.entity.Address;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

/**
 * @author lbb
 * @date 2021/6/23 8:08 下午
 */
@Mapper
public interface AddressMapper {
    /**
     * 保存
     */
    @Insert("INSERT INTO t_address(code,name,pid,type,lit) VALUES(#{code},#{name},#{pid},#{type},#{lit})")
    void save(Address address);

    /**
     * 查询
     * @param id
     * @return
     */
    @Select("select * from t_address where id = #{id}")
    Address get(Long id);
}

package com.lbb.demo.dao;

import com.lbb.demo.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

/**
 * @author lbb
 * @date 2020/9/4 下午2:31
 */
@Mapper
public interface UserMapper {
    /**
     * 保存
     */
    @Insert("INSERT INTO t_user(name,phone,email,city_id,sex,password) VALUES(#{name},#{phone},#{email}," +
            "#{cityId}," +
            "#{sex},#{password})")
    void save(User user);

    /**
     * 查询
     * @param id
     * @return
     */
    @Select("select * from t_user where id = #{id}")
    User get(Long id);

}

最后直接跳过service层，上Controller层，这里只是测试，正常在项目里一般是有service层的。

package com.lbb.demo.controller;

import com.lbb.demo.dao.UserMapper;
import com.lbb.demo.entity.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import javax.annotation.Resource;

/**
 * @author lbb
 * @date 2020/9/4 下午2:39
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Resource
    private UserMapper userMapper;

    @PostMapping("/user/save")
    public String save() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setName("test" + i);
            user.setCityId(2 % 2 == 0 ? 1 : 2);
            user.setCreateTime(new Date());
            user.setSex(i % 2 == 0 ? 1 : 2);
            user.setPhone("11111111" + i);
            user.setEmail("xxxxx");
            user.setCreateTime(new Date());
            user.setPassword("eeeeeeeeeeee");
            userMapper.save(user);
        }
        return "success";
    }

    @GetMapping("/user/get/{id}")
    public User get(@PathVariable Long id) {
        User user = userMapper.get(id);
        System.out.println(user.getId());
        return user;
    }

}

package com.lbb.demo.controller;

import com.lbb.demo.dao.AddressMapper;
import com.lbb.demo.entity.Address;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;

/**
 * @author lbb
 * @date 2021/6/23 8:11 下午
 */
@RestController
public class AddressController {

    @Resource
    private AddressMapper addressMapper;

    @PostMapping("/address/save")
    public String save() {
        for (int i = 0; i < 10; i++) {
            Address address = new Address();
            address.setCode("code_" + i);
            address.setName("name_" + i);
            address.setPid(i + "");
            address.setType(0);
            address.setLit(i % 2 == 0 ? 1 : 2);
            addressMapper.save(address);
        }

        return "success";
    }

    @GetMapping("/address/get/{id}")
    public Address get(@PathVariable Long id) {
        return addressMapper.get(id);
    }

}

测试

这样都实现完之后，可以上测试了，期待的握握小手，我们先启动项目，然后通过postman调用新增用户接口：

看数据库的结果：

我们这边指定是把数据打到了ds1，不一一截图展示，证明分库分表实现正常

总结

sharding-jdbc是一种无侵入代码的实现分库分表，打call。这只是一篇入门文章，具体底层原理还需要大家自行深入研究，fighting~

附录

• 搭建过程参考了这篇文章：blog.csdn.net/u010391342/…
• 官网地址：shardingsphere.apache.org/document/le…