springboot整合shardingsphere實作分庫分表實戰

一、概述

分布式資料庫中間件shardingsphere從2017年宣布開源，在2020年4⽉16⽇正式成為Apache 軟體基⾦會的頂級項目，社群活躍度高，版本疊代快，核心功能多元且豐富，深受各大公司好評。那麼如何使用shardingsphere呢？下面通過一個實戰案例來揭開它的神秘面紗。

二、案例實戰

1、pom依賴

<?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>

    <groupId>org.example</groupId>
    <artifactId>shardingsphere-toutiao</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>23.0</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
        </dependency>
    </dependencies>
</project>           

2、application.properties、application-share-table.properties

application.properties配置：

server.port = 8088
spring.profiles.active = share-table

#log config
logging.config = classpath:log/logback.xml
debug = false

#mp config
mybatis-plus.mapper-locations=classpath*:mapper/*.xml
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#ss config
spring.shardingsphere.enabled=true
spring.shardingsphere.props.sql.show=true           

application-share-table.properties配置：

#ss資料源
spring.shardingsphere.datasource.names = ds0
spring.shardingsphere.datasource.ds0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url = jdbc:mysql://localhost:3306/資料庫?characterEncoding=utf-8
spring.shardingsphere.datasource.ds0.username = 資料庫賬号
spring.shardingsphere.datasource.ds0.password = 資料庫密碼

#分片規則設定(根據學生編碼取模)
spring.shardingsphere.sharding.tables.student.actual-data-nodes = ds0.student_$->{ 0..1}
spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column = stu_code
spring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression = student_$->{Math.abs(stu_code.hashCode())%2}           

3、DemoApplication(啟動類)

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;

@MapperScan("com.test.demo.mapper")
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}           

4、Student實體、StudentMapper和對應xml檔案

public interface StudentMapper {
    Student getStudentByCode(@Param("stuCode") String stuCode);
}

<?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.test.demo.mapper.StudentMapper">

    <select id="getStudentByCode" resultType="com.test.demo.pojo.Student">
        SELECT id, stu_code, stu_name, create_time, update_time
        FROM student
        WHERE stu_code = #{stuCode}
    </select>
</mapper>


@Data
@TableName("student")
public class Student implements Serializable {

    private static final long serialVersionUID = -2059400457051643325L;

    /**
     * 主鍵值
     */
    @TableId("id")
    private Integer id;

    /**
     * 編碼
     */
    @TableField(value = "stu_code")
    private String stuCode;

    /**
     * 名稱
     */
    @TableField(value = "stu_name")
    private String stuName;

    /**
     * 建立時間
     */
    @TableField(value = "create_time")
    private Date createTime;

    /**
     * 更新時間
     */
    @TableField(value = "update_time")
    private Date updateTime;
}           

5、測試controller

@Slf4j
@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentMapper studentMapper;

    @GetMapping("/getStudentByCode")
    public Student getStudentByCode(String stuCode) {
        return studentMapper.getStudentByCode(stuCode);
    }
}           

6、啟動日志輸出：

7、調用測試controller日志輸出

http://localhost:8088/student/getStudentByCode?stuCode=101

3、總結

關于shardingsphere源碼分析，可關注之前寫的系列課程：shardingsphere-合集