Spring Boot集成Sharding-JDBC实现数据分表+读写分离(三)

最新推荐文章于 2023-10-26 20:32:51 发布

寅灯

最新推荐文章于 2023-10-26 20:32:51 发布

阅读量1k

点赞数

分类专栏： mysql和oracle数据库相关文章标签： spring boot java 数据库

本文链接：https://blog.csdn.net/nandao158/article/details/122704795

版权

mysql和oracle数据库相关专栏收录该内容

42 篇文章 5 订阅

订阅专栏

分享之前先参考：数据分离数据源配置

今天紧接着分享数据分表和读写分离，也是我们常用的业务场景之一：

1、相关jar包和三个数据源同上一篇保持一致：

2、相关建表语句/实体类/mapper/xml配置：

2.1、建表语句：

CREATE TABLE `tbl_user0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_user1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;

2.2、实体类：


import java.io.Serializable;
import java.util.Date;
public class TblUser implements Serializable {
    private Long id;

    private String name;

    private Integer cityId;

    private Boolean sex;

    private String phone;

    private String email;

    private Date createTime;

    private String password;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public Integer getCityId() {
        return cityId;
    }

    public void setCityId(Integer cityId) {
        this.cityId = cityId;
    }

    public Boolean getSex() {
        return sex;
    }

    public void setSex(Boolean sex) {
        this.sex = sex;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone == null ? null : phone.trim();
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }
}

2.2、mapper：

 
public interface TblUserMapper  {
   

    int insertOne(TblUser record);

  
}

2.3、xml配置：

  <insert id="insertOne" parameterType="com.nandao.admin.db.entity.TblUser" >
    insert into tbl_user (id, name, city_id,
      sex, phone, email, create_time, 
      password)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{cityId,jdbcType=INTEGER},
      #{sex,jdbcType=BIT}, #{phone,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{password,jdbcType=VARCHAR})
  </insert>

注意：保存时一定要采用mybatis原声的xml,如果采用mybatis-plus封装的保存接口，会读取不到后面的分表策略类，最终导致每张表保村相同的全量数据，导致分表失败！

3、分表和读写分离配置策略：数据源参考上篇文章，这里省略了


    config:
      sharding:
        tables:
          tbl_user:
            actual-data-nodes: ds_0.tbl_user$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                precise-algorithm-class-name: com.nandao.admin.db.rule.MyPreciseShardingAlgorithm

        default-data-source-name: ds_0
        binding-tables: tbl_user #绑定表规则列表
        master-slave-rules:
          ds_0:
            master-data-source-name: user0
            slave-data-source-names: user1,user2

      props:
        sql:
          show: true #是否开启SQL显示，默认值: false
        executor:
          size: 20 #工作线程数量，默认值: CPU核数
        check:
          table:
            metadata:
              enabled: true #是否在启动时检查分表元数据一致性，默认值: false

4、数据分表算法类：

/**
 * @author wanghuainan
 * @date 2022/1/22
 */
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }

}

注意：PreciseShardingAlgorithm<Long> 接口的范型类型一定要和数据分表的字段的类型保持一致（比如id 的类型是Long,这里也要这样），否则保存时报错；

  
 org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.lang.NullPointerException
### The error may involve com.nandao.admin.db.mapper.TblUserMapper.insertOne-Inline
### The error occurred while setting parameters
### SQL: insert into tbl_user (id, name, city_id,       sex, phone, email, create_time,        password)     values (?, ?, ?,       ?, ?, ?, ?,        ?)
### Cause: java.lang.NullPointerException
  at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1013)
  at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
  at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)

5、测试API

     for(Long i=10L;i<15;i++) {
			TblUser user = new TblUser();
			user.setId(i);
			user.setName("nandao"+i+"");
			user.setCityId(1 % 2 == 0 ? 1 : 2);
			user.setCreateTime(new Date());
			user.setSex(true);
			user.setPhone("11111111");
			user.setEmail("xxxxx");
			user.setCreateTime(new Date());
			user.setPassword("eeeeeeeeeeee");

			tblUserMapper.insertOne(user);
		}

执行的部分日志：

  2022-01-26 16:28:44.106 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO  ShardingSphere-SQL - Rule Type: sharding
2022-01-26 16:28:44.106 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO  ShardingSphere-SQL - Logic SQL: insert into tbl_user (id, name, city_id,
      sex, phone, email, create_time, 
      password)
    values (?, ?, ?,
      ?, ?, ?, ?, 
      ?)
2022-01-26 16:28:44.106 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO  ShardingSphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@1576be0c), columns=[Column(name=id, tableName=tbl_user), Column(name=name, tableName=tbl_user), Column(name=city_id, tableName=tbl_user), Column(name=sex, tableName=tbl_user), Column(name=phone, tableName=tbl_user), Column(name=email, tableName=tbl_user), Column(name=create_time, tableName=tbl_user), Column(name=password, tableName=tbl_user)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=(?, ?, ?,
      ?, ?, ?, ?, 
      ?), parametersCount=8)]), columnsListLastPosition=94, generateKeyColumnIndex=-1, insertValuesListLastPosition=144)
2022-01-26 16:28:44.107 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into tbl_user0 (id, name, city_id,
      sex, phone, email, create_time, 
      password)
    values (?, ?, ?,
      ?, ?, ?, ?, 
      ?) ::: [[14, nandao14, 2, true, 11111111, xxxxx, 2022-01-26 16:28:26.094, eeeeeeeeeeee]]

6、执行后数据库两张表的数据显示：

tbl_user0:

tbl_user1:

7、批量查询时每个表依次查。前几篇文章已经分享过，可以去参考！到此分享完成。下篇我们分享分库分表加读写分离，敬请期待！

寅灯

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Spring Boot集成Sharding-JDBC实现数据分表+读写分离(三)

分享之前先参考：数据分离数据源配置今天紧接着分享数据分表和读写分离，也是我们常用的业务场景之一：1、相关jar包和三个数据源同上一篇保持一致：2、相关建表语句/实体类/mapper/xml配置：2.1、建表语句：CREATE TABLE `tbl_user0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL COMMENT '名称', `city_id` int(12.
复制链接

扫一扫