sharding-jdbc水平分库分表，广播表

本文主要是介绍sharding-jdbc水平分库分表，广播表，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1.建表语句，创建两个库 order_db_1，order_db_2 分别执行以下语句

-- ----------------------------
-- Table structure for c_order_1
-- ----------------------------
DROP TABLE IF EXISTS `c_order_1`;
CREATE TABLE `c_order_1`  (
  `order_id` bigint(20) NOT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for c_order_2
-- ----------------------------
DROP TABLE IF EXISTS `c_order_2`;
CREATE TABLE `c_order_2`  (
  `order_id` bigint(20) NOT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.pom文件引入依赖，连接池依赖和sharding-jdbc依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dt</groupId>
    <artifactId>demo</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath />
    </parent>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.7</version>
                <configuration>
                    <!--mybatis的代码生成器的配置文件-->
                    <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
                </configuration>
                <dependencies>
                    <!-- mysql的JDBC驱动 -->
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.46</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

3.水平分库分表配置文件

#分片规则
#数据源
spring.shardingsphere.datasource.names=m1,m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.230.145:3306/order_db_1?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.230.145:3306/order_db_2?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

#分库策略
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=m$->{user_id%2+1}

#指定表数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=m$->{1..2}.c_order_$->{1..2}

#主键生成策略:指定主键列+雪花算法
spring.shardingsphere.sharding.tables.c_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

#分表策略，分表键+分表算法
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order_$->{order_id % 2 + 1}

#输出日志，可以查看到逻辑表和真实表的sql语句
spring.shardingsphere.props.sql.show=true

#公共表，广播表
spring.shardingsphere.sharding.broadcast-tables=c_dict

4.新建一个mapper写sql语句

@Mapper
public interface OrderMapper {

    @Insert("insert into c_order (price,user_id,status) value (#{price},#{userId},#{status})")
    void insert(@Param("price") Double price, @Param("userId") Long userId,@Param("status") Integer status);

    @Select("select * from c_order where order_id = #{orderId}")
    Map selectById(@Param("orderId") Long orderId);

    @Select("<script>" +
            "select * from c_order " +
            "where order_id in" +
            "<foreach collection='orderIds' open='(' close=')' separator=',' item='item'>" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    List<Map> selectBatchOrderIds(@Param("orderIds") List<Long> orderIds);
}

5.测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {Bootstrap.class})
public class DemoTest {

    @Autowired
    OrderMapper orderMapper;

    @Test
    public void testInsert(){
        for (int i = 0; i<10;i++){
            orderMapper.insert(1.1,2L,10);
        }
    }

    @Test
    public void testSelect(){
        Map map = orderMapper.selectById(598198952046624768L);
    }

    @Test
    public void testBatchSelect(){
        List<Long> orderIds = new ArrayList<>();
        orderIds.add(598194125375799297L);
        orderIds.add(598194125392576512L);
        List<Map> maps = orderMapper.selectBatchOrderIds(orderIds);
    }
}

广播表是公共表，相当于字典表，在各个库里都保存一份，方便关联查询，我们对广播表的操作像单库里的表使用即可，由sharding-jdbc负责更新所有库里的数据

这篇关于sharding-jdbc水平分库分表，广播表的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！