Sharding-jdbc 分库分表专题内容（二）springboot+mybatis+Hikari

一、单库分表

pom.xml配置

<?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 http://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>2.1.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.payne</groupId>
    <artifactId>sprintboot-sharding</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <java.version>1.8</java.version>
        <mybatis.version>2.1.0</mybatis.version>
        <sharding.jdbc.version>3.0.0</sharding.jdbc.version>
        <druid.version>1.1.10</druid.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding.jdbc.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

添加实体类

@Getter
@Setter
@ToString
public class User {
    private Long id;
    private String name;
    private Long cityId;
    private String sex;
}

创建Mapper

public interface UserMapper {
    /**
     * 保存
     */
    void save(User user);

    /**
     * 查询
     * @param id
     * @return
     */
    User get(Long id);
}

<?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.payne.user.mapper.UserMapper">
    <insert id="save" parameterType="com.payne.user.model.User">
        INSERT INTO t_user(name,city_id,sex)
        VALUES
        (
        #{name},#{cityId},#{sex}
        )
    </insert>

    <select id="get" parameterType="long" resultType="com.payne.user.model.User">
        select * from t_user where id = #{id}
    </select>
</mapper>

Controller

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

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/save")
    public String save() {
        for (int i = 0; i <10 ; i++) {
            User user=new User();
            user.setName("test"+i);
            user.setSex(i%2==0?"M":"F");
            userMapper.save(user);
        }

        return "success";
    }

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

application.properties

spring.application.name=sharding-springboot-mybatis
# mybatis
mybatis.mapper-locations=classpath*:mapper/**/*.xml
# 数据源 db0,db1,db2
sharding.jdbc.datasource.names=db0
# 数据源ds0
sharding.jdbc.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/db0?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db0.username=root
sharding.jdbc.datasource.db0.password=1234

sharding.jdbc.config.sharding.default-data-source-name=db0
sharding.jdbc.config.sharding.binding-tables=t_user
#数据节点,均匀分布
sharding.jdbc.config.sharding.tables.t_user.actualDataNodes=db0.t_user${0..1}
#指定主键名称，sharding-jdbc默认生成主键策略为雪花算法（SnowFlake）
sharding.jdbc.config.sharding.tables.t_user.keyGeneratorColumnName=id
#分表策略 行表达式
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.shardingColumn=sex
#分表策略 按条件运算 性别等于M的存入t_user0表，其它存入t_user1表
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user$->{sex.equalsIgnoreCase("M")?0:1}

# 打印执行的数据库以及语句
sharding.jdbc.config.sharding.props.sql.show=true

初始化sql

CREATE TABLE `t_user0` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `sex` varchar(2) DEFAULT NULL COMMENT '性别',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_user1` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `sex` varchar(2) DEFAULT NULL COMMENT '性别',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

分析执行结果

分片策略：sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user$->{sex.equalsIgnoreCase(“M”)?0:1}

http://localhost:8080/user/save

一个简单的水平分片单库分表就完成了。进行测试就发现数据分别存储到t_user0和t_user1两个表中。这里采用的事按照字段sex类型分片存储。

二、分库分表

application.properties

# 第二个数据库
sharding.jdbc.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
sharding.jdbc.datasource.db1.username=root
sharding.jdbc.datasource.db1.password=1234


# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
# 分库策略
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 2}
#数据节点,均匀分布
sharding.jdbc.config.sharding.tables.t_user.actualDataNodes=db$->{0..1}.t_user${0..1}

@GetMapping("/save")
public String save() {
    for (int i = 0; i <10 ; i++) {
        User user=new User();
        #设置id
        user.setId(Long.valueOf(i));
        user.setName("test"+i);
        user.setSex(i%2==0?"M":"F");
        userMapper.save(user);
    }

    return "success";
}

分析结果

根据sharding-jdbc日志，可以看出sharding-jdbc的执行步骤：

SQL解析 ⇒ sql改写 ⇒ sql路由 ⇒ sql执行 ⇒ 结果合并

ShardingSphere 官方文档

集成PageHelper进行分页操作

 <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper-spring-boot-starter</artifactId>
     <version>1.2.12</version>
 </dependency>

@GetMapping("/all")
public List<User> selectAll() {
    PageHelper.startPage(2, 5);
    List<User> list =  userMapper.selectAll();
    return list;
}

Sharding-jdbc 完全支持Pagehelp分页操作