java之学习记录 9 - 3 - Sharding-JDBC 操作公共表

1 什么是公共表

公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。

可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用Sharding-JDBC实现公共表的数据维护。

2 公共表配置与测试

1) 创建数据库,导入依赖

 

分别在 lg_order_1 , lg_order_2 , lg_user库中 创建 district 表

-- 区域表 
CREATE TABLE district ( 
    id BIGINT(20) PRIMARY KEY COMMENT '区域ID', 
    district_name VARCHAR(100) COMMENT '区域名称', 
    LEVEL INT COMMENT '等级' 
);

 

<dependency> 
    <groupId>org.apache.shardingsphere</groupId> 
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId> 
    <version>4.0.0-RC1</version> 
</dependency>

2) 在 Sharding-JDBC 的配置文件中 指定公共表

spring.application.name = sharding-jdbc-simple
server.servlet.context-path = /sharding-jdbc
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true

spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true

# 配置数据源
spring.shardingsphere.datasource.names = db1,db2,db3

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/lg_order_1?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3306/lg_order_2?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456


spring.shardingsphere.datasource.db3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url = jdbc:mysql://localhost:3306/lg_user?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db3.username = root
spring.shardingsphere.datasource.db3.password = 123456

# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作db1数据源，否则操作db2。
spring.shardingsphere.sharding.tables.pay_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.pay_order.database-strategy.inline.algorithm-expression = db$->{user_id % 2 + 1}


#配置数据节点,指定节点的信息  pay_order_2
spring.shardingsphere.sharding.tables.pay_order.actual-data-nodes = db$->{1..2}.pay_order_$->{1..2}

spring.shardingsphere.sharding.tables.users.actual-data-nodes = db$->{3}.users

#指定pay_order表 (逻辑表)的主键生成策略为 SNOWFLAKE
spring.shardingsphere.sharding.tables.pay_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.pay_order.key-generator.type=SNOWFLAKE

#指定pay_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.pay_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.pay_order.table-strategy.inline.algorithm-expression = pay_order_$->{order_id % 2 + 1}

spring.shardingsphere.sharding.tables.users.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.users.table-strategy.inline.algorithm-expression = users

# 指定district为公共表
spring.shardingsphere.sharding.broadcast-tables=district
# 主键生成策略
spring.shardingsphere.sharding.tables.district.key-generator.column=id
spring.shardingsphere.sharding.tables.district.key-generator.type=SNOWFLAKE

3) 编写代码 , 操作公共表

package com.lagou.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

@Mapper
@Component
public interface DistrictDao {

    /**
     * 插入操作
     * */
    @Insert("INSERT INTO district(district_name,level) VALUES(#{district_name},#{level})")
    public void insertDist(@Param("district_name") String district_name, @Param("level") int level);

    /**
     * 删除数据
     */
    @Delete("delete from district where id = #{id}")
    int deleteDict(@Param("id") Long id);
}

测试：

package com.lagou.dao;

import com.lagou.RunBoot;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class DistrictDaoTest {

    @Autowired
    DistrictDao districtDao;

    @Test
    public void testInsert(){
        districtDao.insertDist("昌平区",2);
        districtDao.insertDist("朝阳区",2);
    }

    @Test
    public void testDelete(){
        districtDao.deleteDict(523951640290525185L);
        districtDao.deleteDict(523951640684789760L);
    }
}