springboot集成mbatisplus+sharding-jdbc+水平分库

水平分库

创建两个数据库

edu_db_1

course_01
course_02

edu_db_2

course_01
course_02

数据库规则：
1.userid为偶数数据添加edu_db_1数据库，为奇数数据添加edu_db2数据库

表规则：
1.cid为偶数数据添加course_1表，
为奇数数据添加course_2表

 

 

在原来基础上增加一个pojo和多了一些配置文件mapper的东西。

# 配置Sharding-JDBC的分片策略
# 配置数据源，给数据源起名g1,g2...此处可配置多数据源
spring.shardingsphere.datasource.names=g1,m1,m2

# 配置数据源具体内容————————包含  连接池，  驱动，             地址，   用户名，    密码
# 由于上面配置数据源只有g1因此下面只配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password

# 水平分表
spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.g1.url=jdbc:mysql://192.168.31.200:3306/sharding_jdbc?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.g1.username=root
spring.shardingsphere.datasource.g1.password=player3.

##水平分库分表
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.31.200:3306/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=player3.

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.31.200:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=player3.

# 配置表的分布，表的策略  goods 是水平分表
spring.shardingsphere.sharding.tables.goods.actual-data-nodes=g1.goods_$->{1..2}
#水平分库分表的策略表是course m$->{1..2} 不是很懂,是上面数据源的别名，哈
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}


# 指定goods表 主键gid 生成策略为 SNOWFLAKE
spring.shardingsphere.sharding.tables.goods.key-generator.column=gid
spring.shardingsphere.sharding.tables.goods.key-generator.type=SNOWFLAKE

##指定course表的,主键cid  生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE


# 指定分片策略 约定gid值是偶数添加到goods_1表，如果gid是奇数添加到goods_2表
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column=gid
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression=goods_$->{gid % 2 + 1}


# 指定分片策略 约定cid值是偶数添加到course_1表，如果gid是奇数添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

##增加数据库的分片策略 user_id 根据这个字段走库，如果是 约定user_id值是偶数添加m1库，如果gid是奇数添加到m2库
#默认所有的数据库都走这个策略，我们不希望都用这个
#spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id
#spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=default-database-strategy-inline
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2+1}
        
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

　　pojo类

package com.player3.sharding.jdbc.pojo;

import lombok.Data;
import lombok.ToString;

/**
 * @author player3
 */
@Data
@ToString
public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;
}　

 

mapper 

/**
 * @author player3
 */
@Repository
public interface CourseMapper extends BaseMapper<Course> {
}

　　

sql:

CREATE TABLE course_1(
cid BIGINT(20) primary key comment '商品id,主键',
cname varchar(20) not null comment '商品名称',
user_id BIGINT(20) not null COMMENT '用户id',
cstatus VARCHAR(10) not null COMMENT '商品状态，已发布 or 未发布');


CREATE TABLE course_2(
cid BIGINT(20) primary key comment '商品id,主键',
cname varchar(20) not null comment '商品名称',
user_id BIGINT(20) not null COMMENT '用户id',
cstatus VARCHAR(10) not null COMMENT '商品状态，已发布 or 未发布');

　　

测试类：多了一个查询的方法

package com.player3.Shardingjdbc;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.player3.sharding.jdbc.ShardingJdbcApplication;
import com.player3.sharding.jdbc.dao.CourseMapper;
import com.player3.sharding.jdbc.dao.GoodsMapper;
import com.player3.sharding.jdbc.pojo.Course;
import com.player3.sharding.jdbc.pojo.Goods;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

@SpringBootTest(classes = ShardingJdbcApplication.class)
public class ShardingjdbcTests {

    @Resource
    GoodsMapper goodsMapper;

   @ Resource
    CourseMapper courseMapper;
   //添加的方法
    @Test
   public void addCourse(){
       for (int i = 20; i < 20000; i++) {
           Course course = new Course();
           course.setCname("java"+i);
           course.setUserId(Long.valueOf(i));
           course.setCstatus("Normal"+i);
           courseMapper.insert(course);
       }
   }
//查询的方法
    @Test
public void findCourseDb(){

        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("user_id",203l);
        wrapper.eq("cid",689457744721215489l);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }

    @Test
   public void addGoods() {
        System.out.println("访问成功");
        Goods good = new Goods();
        good.setGname("小米手机");
        good.setUserId(100L);
        good.setGstatus("已发布");
        goodsMapper.insert(good);
    }
}

　　插入的规则就是：根据userid去区分库，然后根据主键id区分用哪个表。

查询的sql打印：

2022-01-16 13:13:56.008 INFO 8384 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2022-01-16 13:13:56.009 INFO 8384 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT cid,cname,user_id,cstatus FROM course
WHERE user_id = ? AND cid = ?
2022-01-16 13:13:56.009 INFO 8384 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=course, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=course), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0}), Condition(column=Column(name=cid, tableName=course), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=course, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=SELECT cid,cname,user_id,cstatus FROM course
WHERE user_id = ? AND cid = ?)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=32, groupByLastIndex=0, items=[CommonSelectItem(expression=cid, alias=Optional.absent()), CommonSelectItem(expression=cname, alias=Optional.absent()), CommonSelectItem(expression=user_id, alias=Optional.absent()), CommonSelectItem(expression=cstatus, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2022-01-16 13:13:56.009 INFO 8384 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT cid,cname,user_id,cstatus FROM course_2
WHERE user_id = ? AND cid = ? ::: [203, 689457744721215489]
Course(cid=689457744721215489, cname=java203, userId=203, cstatus=Normal203)