ShardingSphere在SpringBoot3中的应用--数据分片

创建数据库

以下述sql语句创建user1和user2两个数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user0
-- ----------------------------
DROP TABLE IF EXISTS `user0`;
CREATE TABLE `user0`  (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for user1
-- ----------------------------
DROP TABLE IF EXISTS `user1`;
CREATE TABLE `user1`  (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 162 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

导入依赖

<!--sharding-->  
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->  
<dependency>  
    <groupId>org.apache.shardingsphere</groupId>  
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>  
    <version>5.2.1</version>  
</dependency>  
  
<!--mysql-->  
<!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->  
<dependency>  
    <groupId>com.mysql</groupId>  
    <artifactId>mysql-connector-j</artifactId>  
    <version>8.1.0</version>  
</dependency>  
<!--mybatis-->  
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->  
<dependency>  
    <groupId>com.baomidou</groupId>  
    <artifactId>mybatis-plus-boot-starter</artifactId>  
    <version>3.5.3.2</version>  
</dependency>  

创建实体层

@TableName("user")  
@Data  
public class User {  
    private int userId;  
    private String name;  
}

添加mapper

@Mapper  
public interface UserMapper extends BaseMapper<User> {}

在resourcs中还需要同时建立mapper作为xml映射。

创建service层

public interface UserService extends IService<User> {  
}

public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {  
}

添加shardingSphere数据分片设置

在application.yaml中配置：

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.Driver
        jdbc-url: jdbc:mysql://localhost:3306/user1
        username: root
        password: qb030929
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.Driver
        jdbc-url: jdbc:mysql://localhost:3306/user2
        username: root
        password: qb030929
    rules:
      sharding:
        tables:
          user:
            actual-data-nodes: ds$->{0..1}.user$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user_inline
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user_inline
        sharding-algorithms:
          user_inline:
            type: HASH_MOD
            props:
              sharding-count: 2

在test中测试功能

• 插入功能

@SpringBootTest(classes = DemoApplication.class)
class DemoApplicationTests {
	@Resource
	private UserMapper userMapper;
	@Test
	void contextLoads() {
	}
	@Test
	public void insert(){
            User user = new User();
            user.setUserId(12);
            user.setName("aaaa");
            userMapper.insert(user);
	}
}

最后会在user1.user0中插入该值。

• 查询功能：
  • 不带分片减即上述条件的user_id 查询结果为：所有库中的存储值.

    //不带分片键  
    @Test  
    public void Query(){  
        List<User> u = userMapper.selectList(null);  
        System.out.println(u);  
    }
    

  • 带分片键查询

    //带分片键查询  
    @Test  
    public void QueryByUserId(){  
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();  
        queryWrapper.eq(User::getUserId, 11);  
        List<User> users = userMapper.selectList(queryWrapper);  
        System.out.println(users);  
    }
    

注意：只是简单实现其数据分片功能，具体的数据分片算法需要按照实际情况转变，该样例仅仅实现简单的实现数据分片。