本文共 4376 字，大约阅读时间需要 14 分钟。

1 公共表

1.1 存储固定数据的表，表数据很少发生变化，查询时候经常进行关联

1.2 在每个数据库中创建出相同结构公共表

2 在多个数据库中创建公共表

2.1 edudb1库中t_dict

CREATE TABLE `edudb1`.`t_dict` (  `dict_id` BIGINT NOT NULL,  `ustatus` VARCHAR(45) NOT NULL,  `uvalue` VARCHAR(45) NOT NULL,  PRIMARY KEY (`dict_id`));

2.2 edudb2库中t_dict

CREATE TABLE `edudb2`.`t_dict` (  `dict_id` BIGINT NOT NULL,  `ustatus` VARCHAR(45) NOT NULL,  `uvalue` VARCHAR(45) NOT NULL,  PRIMARY KEY (`dict_id`));

2.3 userdb库中t_dict

CREATE TABLE `userdb`.`t_dict` (  `dict_id` BIGINT NOT NULL,  `ustatus` VARCHAR(45) NOT NULL,  `uvalue` VARCHAR(45) NOT NULL,  PRIMARY KEY (`dict_id`));

3 创建po

package com.ccb.sharding.po;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;@TableName("t_dict")public class Dict {
       @TableId("dict_id")    private Long dictId;    private String ustatus;    private String uvalue;    public Long getDictId() {
           return dictId;    }    public void setDictId(Long dictId) {
           this.dictId = dictId;    }    public String getUstatus() {
           return ustatus;    }    public void setUstatus(String ustatus) {
           this.ustatus = ustatus;    }    public String getUvalue() {
           return uvalue;    }    public void setUvalue(String uvalue) {
           this.uvalue = uvalue;    }    @Override    public String toString() {
           return "Dict{" +                "dictId=" + dictId +                ", ustatus='" + ustatus + '\'' +                ", uvalue='" + uvalue + '\'' +                '}';    }}

4 创建mapper

package com.ccb.sharding.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.ccb.sharding.po.Dict;import org.springframework.stereotype.Repository;@Repositorypublic interface DictMapper extends BaseMapper
   
     {
   }
   

5 创建application.properties配置文件

# sharding-JDBC分片策略（公共表配置）# 配置数据源，给数据源命名spring.shardingsphere.datasource.names=ds0,ds1,ds2# 配置数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/userdb?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=chengwen# 配置数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/edudb1?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=chengwen# 配置数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/edudb2?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds2.username=rootspring.shardingsphere.datasource.ds2.password=chengwen# 一个实体类对应两张表，覆盖spring.main.allow-bean-definition-overriding=true# 配置公共表spring.shardingsphere.sharding.broadcast-tables=t_dict# 配置数据库中 t_dict 表主键 dict_id 生成策略 SNOWFLAKE 雪花算法spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_idspring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE# 打印sql输出日志spring.shardingsphere.props.sql.show=true

6 Test

package com.ccb.sharding;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.ccb.sharding.mapper.DictMapper;import com.ccb.sharding.po.Dict;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;@SpringBootTestclass ShardingApplicationTests {
       @Autowired    DictMapper dictMapper;    // ================= 测试公共表 ======================    @Test    public void addDict(){
           Dict dict = new Dict();        dict.setUstatus("S");        dict.setUvalue("已成功");        dictMapper.insert(dict);    }    @Test    public void getDict(){
           QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.eq("dict_id","1276077724019986434");        Dict dict = dictMapper.selectOne(queryWrapper);        System.out.println(dict);    }

7 Test result

edudb1

edudb2

userdb

getDict