sharding-jdbc分库使用

初始化一个Springboot项目
在mysql中创建两个测试库，在两个测试库中创建相同的一张表user
引入sharding-jdbc的yaml配置
项目演示
启动测试

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar这3款相互独立的产品组成，本次使用sharding-jdbc来分库。项目采用Spring boot + mybatis 。

初始化一个Springboot项目

引入项目依赖


<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </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>

    <!--web核心依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!--mysql数据库驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>

    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.0.5</version>
    </dependency>

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

</dependencies>

在mysql中创建两个测试库，在两个测试库中创建相同的一张表user


建表语句

DROP TABLE IF EXISTS `user`;

CREATE TABLE "user" (

  "id" bigint(20) NOT NULL,

  "name" varchar(60) NOT NULL,

  "age" int(3) NOT NULL,

  PRIMARY KEY ("id")

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

引入sharding-jdbc的yaml配置


spring:
  shardingsphere:
    datasource:
      names: ds0,ds1 # 配置ds0 和ds1两个数据源，ds01/02分别是测试库的别名
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://数据库ip地址1:32636/数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx #数据库用户名
        password: xxx #数据库链接密码
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://数据库ip地址2:32636/数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx #数据库用户名
        password: xxx #数据库链接密码
    sharding:
      default-data-source-name: ds0 #默认数据源，未分片的表默认执行库
      default-database-strategy:
        inline:  # 分库策略 根据id取模确定数据进哪个数据库
          sharding-column: id
          algorithm-expression: ds$->{id % 2}
      tables:
        user:
          actual-data-nodes: ds$->{0..1}.user # 具体分表策略
          key-generator:
            column: id
            type: SNOWFLAKE #使用雪花id,注意ID不能设置为自增长
    props:
      sql:
        show: true #打印sql日志

项目演示

创建user controller


@RestController
@RequestMapping("/v1/user")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @PostMapping
    public User addUser(@RequestBody User user) {
        System.out.println(user.toString());
        userMapper.insert(user);
        return user;
    }

    @GetMapping
    public List<User> findAll(@RequestBody User user) {
        return userMapper.selectList(null);
    }
}

穿件domain


@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User {

    private Long id;

    private String name;

    private Integer age;
}

service层看自己自行实现，本次测试不需要
创建mapper


public interface UserMapper extends BaseMapper<User> {
}

创建mapper.xml


<?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.example.demo.mapper.UserMapper">

</mapper>