Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 實現(xiàn)分庫分表

一姐浮、Sharding-JDBC簡介：

????Sharding-JDBC又稱ShardingSphere馆匿，一個開放源代碼生態(tài)系統(tǒng)惋增，由一組分布式數(shù)據庫中間件解決方案組成箩张，其中又包括JDBC阎肝，代理和Sidecar（計劃）挤渔。它們都提供數(shù)據分片，分布式事務和數(shù)據庫編排功能风题，適用于Java同構判导，異構語言和云原生等各種情況。

二沛硅、創(chuàng)建項目如下：

? ? 使用STS創(chuàng)建Spring Boot項目眼刃，使用MVC三層結構開發(fā)代碼：

? ? 1）配置POM添加以下依賴

? ??????

? ? ? ? <dependency>

? ? ? ? ? ? <groupId>mysql</groupId>

? ? ? ? ? ? <artifactId>mysql-connector-java</artifactId>

? ? ? ? ? ? <scope>runtime</scope>

? ? ? ? </dependency>

? ? ? ?

? ? ? ? <dependency>

? ? ? ? ? ? <groupId>com.baomidou</groupId>

? ? ? ? ? ? <artifactId>mybatis-plus-boot-starter</artifactId>

? ? ? ? ? ? <version>3.1.1</version>

? ? ? ? </dependency>

? ? ? ?

? ? ? ?

? ? ? ? <dependency>

? ? ? ? ? ? <groupId>io.shardingsphere</groupId>

? ? ? ? ? ? <artifactId>sharding-jdbc-spring-boot-starter</artifactId>

? ? ? ? ? ? <version>3.1.0</version>

? ? ? ? </dependency>

? ? ? ?

? ? ? ? <dependency>

? ? ? ? ? ? <groupId>io.shardingsphere</groupId>

? ? ? ? ? ? <artifactId>sharding-jdbc-spring-namespace</artifactId>

? ? ? ? ? ? <version>3.1.0</version>

? ? ? ? </dependency>

? ? ? ?

? ? ? ?

? ? ? ? <dependency>

? ? ? ? ? ? <groupId>org.projectlombok</groupId>

? ? ? ? ? ? <artifactId>lombok</artifactId>

? ? ? ? </dependency>

? ? 2）創(chuàng)建實體類

? ??????@Data

????????@EqualsAndHashCode(callSuper = true)

????????@Accessors(chain = true)

????????@TableName("book")

????????public class Book extends Model<Book> {

????????????private int id;

? ? ????????private String name;

? ????????? private int count;
? ? ? ? }

????3）創(chuàng)建service

????????public interface BookService {

????????????List<Book> getBookList();

????????????boolean save(Book book);

????????}

? ? 4）創(chuàng)建serviceImpl

? ??????@Service

????????public class BookServiceImpl extends ServiceImpl<BookMapper, Book>implements BookService{

? ? ????????@Override

? ????????? public List<Book> getBookList() {

? ? ? ????????? return baseMapper.selectList(Wrappers.<Book>lambdaQuery());

? ? ????????}

? ? ????????@Override

? ????????? public boolean save(Book book) {

? ? ? ????????? return super.save(book);

? ? ????????}

????}

? ? 5)創(chuàng)建controller，并實現(xiàn)以下兩個方法

? ??????@RestController

????????public class BookController {

????????@Autowired

? ? ? ? BookService bookService;

? ? ????@RequestMapping(value = "/book", method = RequestMethod.GET)

? ????? public List<Book> getItems(){

? ? ? ????????? return bookService.getBookList();

? ? ????????}

? ? ????@RequestMapping(value = "/book",method = RequestMethod.POST)

? ? ????public Boolean saveItem(Book book){

? ? ? ????? return bookService.save(book);

? ????????? }

????????}

????6）application.properties配置如下

? ??????# 數(shù)據源 db0,db1,db2

????????sharding.jdbc.datasource.names=db0,db1,db2

????????# 第一個數(shù)據庫

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

????????# 第二個數(shù)據庫

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

????????# 第三個數(shù)據庫

????????sharding.jdbc.datasource.db2.type=com.zaxxer.hikari.HikariDataSource

????????sharding.jdbc.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver

????????sharding.jdbc.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

????????sharding.jdbc.datasource.db2.username=root

????????sharding.jdbc.datasource.db2.password=root

????????# 水平拆分的數(shù)據庫（表）配置分庫 + 分表策略行表達式分片策略

????????# 分庫策略

????????sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id

????????sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 3}

????????# 分表策略其中book為邏輯表分表主要取決于id行

????????sharding.jdbc.config.sharding.tables.book.actual-data-nodes=db$->{0..2}.book_$->{0..1}

????????sharding.jdbc.config.sharding.tables.book.table-strategy.inline.sharding-column=count

????????# 分片算法表達式

????????sharding.jdbc.config.sharding.tables.book.table-strategy.inline.algorithm-expression=book_$->{count % 2}

????????# 主鍵 UUID 18位數(shù) 如果是分布式還要進行一個設置防止主鍵重復

????????#sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id

????????# 打印執(zhí)行的數(shù)據庫以及語句

????????sharding.jdbc.config.props..sql.show=true

????????spring.main.allow-bean-definition-overriding=true

????????#讀寫分離

????????#sharding.jdbc.datasource.dsmaster=

? ? 7)sql建表語句如下

? ??????#創(chuàng)建數(shù)據庫表數(shù)據

????????CREATE DATABASE IF NOT EXISTS `db0`;

????????USE `db0`;

????????DROP TABLE IF EXISTS `book_0`;

????????CREATE TABLE `book_0` (

? ????????`id` INT ( 11 ) NOT NULL ,

????????? `name` VARCHAR ( 255 ) DEFAULT NULL,

????????? `count` INT ( 11 ) DEFAULT NULL,

????????? PRIMARY KEY ( `id` )

????????) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

????????DROP TABLE IF EXISTS `book_1`;

????????CREATE TABLE `book_1` (

? ????????`id` INT ( 11 ) NOT NULL ,

? ????????`name` VARCHAR ( 255 ) DEFAULT NULL,

????????? `count` INT ( 11 ) DEFAULT NULL,

? ????????PRIMARY KEY ( `id` )

????????) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

????????CREATE DATABASE IF NOT EXISTS `db1`;

????????USE `db1`;

????????DROP TABLE IF EXISTS `book_0`;

????????CREATE TABLE `book_0` (

? ????????`id` INT ( 11 ) NOT NULL ,

? ????????`name` VARCHAR ( 255 ) DEFAULT NULL,

????????? `count` INT ( 11 ) DEFAULT NULL,

????????? PRIMARY KEY ( `id` )

????????) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

????????DROP TABLE IF EXISTS `book_1`;

????????CREATE TABLE `book_1` (

????????? `id` INT ( 11 ) NOT NULL ,

????????? `name` VARCHAR ( 255 ) DEFAULT NULL,

????????? `count` INT ( 11 ) DEFAULT NULL,

? ????????PRIMARY KEY ( `id` )

????????) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

????????CREATE DATABASE IF NOT EXISTS `db2`;

????????USE `db2`;

????????DROP TABLE IF EXISTS `book_0`;

????????CREATE TABLE `book_0` (

? ????????`id` INT ( 11 ) NOT NULL ,

????????? `name` VARCHAR ( 255 ) DEFAULT NULL,

????????? `count` INT ( 11 ) DEFAULT NULL,

????????? PRIMARY KEY ( `id` )

????????) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

????????DROP TABLE IF EXISTS `book_1`;

????????CREATE TABLE `book_1` (

? ????????`id` INT ( 11 ) NOT NULL ,

????????? `name` VARCHAR ( 255 ) DEFAULT NULL,

????????? `count` INT ( 11 ) DEFAULT NULL,

? ????????PRIMARY KEY ( `id` )

????????) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

三摇肌、啟動項目用postman訪問如下地址：

使用post請求插入參數(shù)擂红，點擊send按鈕

? ??

返回插入成功狀態(tài)為true

使用get請求獲取插入數(shù)據，如上圖所示