一个比较简单的集成,只用到了分库,没有分表。
pom完整依赖:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- lombok 插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- hu tool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.10</version>
</dependency>
<!-- mybatis plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.4</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- json -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.69</version>
</dependency>
<!-- sharding jdbc -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
数据库建表,我这里有两个数据库db1、db2,每个库都建了一张t_user表,建表语句如下:
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`account` varchar(32) DEFAULT NULL,
`pwd` varchar(64) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
application.yml配置:
spring:
main:
allow-bean-definition-overriding: true
application:
name: shardingjdbc
#配置mp
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath*:mapper/*Mapper.xml
global-config:
db-config:
id-type: none
#分库分表配置
sharding:
jdbc:
datasource:
#指定数据源 这里有两个数据库 所以指定两个数据源
names: db0,db1
#每个数据源指定不通的数据库就可以了
db0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
jdbc-url: jdbc:mysql://***.***.***.***:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
db1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
jdbc-url: jdbc:mysql://***.***.***.***:3306/db2?useUnicode=true&characterEncoding=utf-8&useSSL=false
config:
sharding:
tables:
t_user:
#这个配置似乎没有生效 因为这个id是我用时间戳生成的
key-generator:
column: id
type: SNOWFLAKE
#哪个库下面的t_user表
actual-data-nodes: db$->{0..1}.t_user
#策略就是根据id对2取余
database-strategy:
inline: #行表达式
sharding-column: id #列名称,多个列以逗号分隔
algorithm-expression: db${id % 2}
binding-tables: t_user
这个主要是配置可能会稍微多一些,其他都和平时开发是一样的,建立实体类、mapper、service、controller,然后调用接口去添加数据,shardingjdbc会根据id进行分库插入,效果如下:
数据id为偶数的分配到了db1库中的用户表,数据id为奇数的分配到了db2中的用户表。查询的时候则会将两张表的数据都查询出来。效果如下:
{
"records": [
{
"id": 1640764482208,
"name": "张三5",
"account": "a5",
"pwd": "p5",
"createTime": "2021-12-29 15:54:42"
},
{
"id": 1640763954228,
"name": "张三4",
"account": "a4",
"pwd": "p4",
"createTime": "2021-12-29 15:45:54"
},
{
"id": 1640763948224,
"name": "张三3",
"account": "a3",
"pwd": "p3",
"createTime": "2021-12-29 15:45:48"
},
{
"id": 1640763941891,
"name": "张三",
"account": "a2",
"pwd": "p2",
"createTime": "2021-12-29 15:45:42"
},
{
"id": 1640763937284,
"name": "张三",
"account": "a1",
"pwd": "p1",
"createTime": "2021-12-29 15:45:37"
}
],
"total": 5,
"size": 10,
"current": 1,
"orders": [],
"optimizeCountSql": true,
"searchCount": true,
"countId": null,
"maxLimit": null,
"pages": 1
}