下面我将为您介绍如何通过Spring Boot整合sharding-jdbc实现分库分表和读写分离。
一、概述
sharding-jdbc是一个基于JDBC规范的分布式数据库中间件,它提供了易于使用、高度定制化的分片、读写分离、柔性事务等功能。Spring Boot是一款开发快速的Java企业开发框架,在分布式项目中得到广泛应用。
对于需要进行数据分片和读写分离的应用,可以通过Spring Boot集成sharding-jdbc来轻松实现。下面我们就来讲解如何使用Spring Boot整合sharding-jdbc实现分库分表和读写分离。
二、示例1:实现分库分表
- 引入相关依赖:
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.apache.ibatis</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
- 在application.yml中配置数据源和分片规则,示例如下:
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
hikari:
maximum-pool-size: 10
minimum-idle: 5
connection-test-query: SELECT 1
sharding:
jdbc:
# 数据源分片数量
datasource-names: ds0, ds1
# 分片规则
# 分库规则
sharding:
databases:
# 分库策略
# 采用user_id取模来确定分库策略
# 这里表示以user_id % 2的值作为分库键,分到ds0或ds1中
# user_id为分表键,例表规则在下面配置
algorithm-expression: ds$->{user_id % 2}
# 分库数据源配置
ds0:
url: jdbc:mysql://localhost:3306/test0?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
type: com.zaxxer.hikari.HikariDataSource
ds1:
url: jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
type: com.zaxxer.hikari.HikariDataSource
# 分表规则
sharding:
tables:
user:
# 分表策略
# 采用user_id取模来确定分表策略
# 这里表示以user_id % 2的值作为分表键,分到user_0或user_1表中
# user_id为分库键,分库规则在上面配置
actual-data-nodes: ds$->{0..1}.user_$->{0..1}
table-strategy:
inline:
sharding-column: user_id
algorithm-expression: user_$->{user_id % 2}
key-generator-column-name: id
- 编写实体类和Mapper接口,示例代码如下:
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Long id;
private Long userId;
private String name;
private Integer age;
}
@Mapper
public interface UserMapper {
@Insert("insert into user (id,user_id,name,age) values(#{id},#{userId},#{name},#{age})")
void insert(User user);
@Select("select id,user_id,name,age from user where id = #{id}")
User selectById(Long id);
@Select("select id,user_id,name,age from user where user_id = #{userId}")
List<User> selectByUserId(Long userId);
}
- 编写测试类,测试分库分表效果,示例代码如下:
@SpringBootTest
class ShardingJdbcApplicationTests {
private static final int USER_NUM = 3;
@Autowired
private UserMapper userMapper;
@Test
public void testInsert() {
for (int i = 1; i <= USER_NUM; i++) {
long userId = i;
User user = User.builder().id(i).userId(userId).name("User" + i).age(18).build();
userMapper.insert(user);
}
}
@Test
public void testSelectById() {
for (int i = 1; i <= USER_NUM; i++) {
User user = userMapper.selectById(Long.valueOf(i));
System.out.println(user);
}
}
@Test
public void testSelectByUserId() {
for (int i = 1; i <= USER_NUM; i++) {
List<User> userList = userMapper.selectByUserId(Long.valueOf(i));
System.out.println(userList);
}
}
}
三、示例2:实现读写分离
- 引入相关依赖:
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.apache.ibatis</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>${hikari.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>${spring.boot.version}</version>
</dependency>
- 在application.yml中配置数据源和分片规则,示例如下:
spring:
datasource:
# 主库配置(写数据操作)
master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
hikari:
maximum-pool-size: 10
minimum-idle: 5
connection-test-query: SELECT 1
# 从库配置(读数据操作)
slave:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3307/test?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
hikari:
maximum-pool-size: 10
minimum-idle: 5
connection-test-query: SELECT 1
# 读写分离配置
shardingsphere:
datasource:
# 数据库的名称,这里是rule
name: rule
# 数据库类型
type: MySQL
# 主备数据源分别指向主从数据源
master-slave-rule:
master-data-source-name: master
slave-data-source-names: slave
# 分片策略按照id分库,不分表
sharding:
tables:
user:
key-generator:
type: SNOWFLAKE
column: id
worker-id:
max-vibration-offset:
database-strategy:
inline:
sharding-column: id
algorithm-expression: rule_${id % 2}
- 编写实体类和Mapper接口,示例代码如下:
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User {
private Long id;
private String name;
private Integer age;
}
@Mapper
public interface UserMapper {
@Insert("insert into user(id,name,age) values(#{id},#{name},#{age})")
void insert(User user);
@Select("select id,name,age from user where id = #{id}")
User selectById(Long id);
@Select("select id,name,age from user")
List<User> selectAll();
}
- 编写测试类,测试主从库读写效果,示例代码如下:
@SpringBootTest
class ShardingJdbcApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void testInsert() {
User user = User.builder().id(1L).name("User1").age(18).build();
userMapper.insert(user);
}
@Test
public void testSelectById() {
User user = userMapper.selectById(1L);
System.out.println(user);
}
@Test
public void testSelectAll() {
List<User> userList = userMapper.selectAll();
System.out.println(userList);
}
}
这样我们就完成了分库分表和读写分离的实现,开发者可以根据自己的实际需求进行调整。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:SpringBoot整合sharding-jdbc实现分库分表与读写分离的示例 - Python技术站