Sharding-JDBC demo（配置文件）

分表：

1. 初始化数据库

    /*
    Navicat MySQL Data Transfer

    Source Server         : localhost
    Source Server Version : 50648
    Source Host           : localhost:3306
    Source Database       : course_db

    Target Server Type    : MYSQL
    Target Server Version : 50648
    File Encoding         : 65001

    Date: 2021-01-28 13:52:51
    */

    SET FOREIGN_KEY_CHECKS=0;

    -- ----------------------------
    -- Table structure for course_1
    -- ----------------------------
    DROP TABLE IF EXISTS `course_1`;
    CREATE TABLE `course_1` (
      `cid` bigint(20) NOT NULL,
      `cname` varchar(255) DEFAULT NULL,
      `user_id` int(11) DEFAULT NULL,
      `cstatus` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`cid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    -- ----------------------------
    -- Table structure for course_2
    -- ----------------------------
    DROP TABLE IF EXISTS `course_2`;
    CREATE TABLE `course_2` (
      `cid` bigint(20) NOT NULL,
      `cname` varchar(255) DEFAULT NULL,
      `user_id` int(11) DEFAULT NULL,
      `cstatus` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`cid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. application.properties

   # shardingjdbc分片策略
   # 配置数据源，给数据源起名称
   spring.shardingsphere.datasource.names=m1
   
   # 一个实体类对应两张表，覆盖
   spring.main.allow-bean-definition-overriding=true
   
   #配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
   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://localhost:3306/course_db?serverTimezone=GMT%2B8
   spring.shardingsphere.datasource.m1.username=root
   spring.shardingsphere.datasource.m1.password=Test@123
   
   #指定course表分布情况，配置表在哪个数据库里面，表名称都是什么  m1.course_1 , m1.course_2
   spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
   
   # 指定course表里面主键cid 生成策略  SNOWFLAKE
   spring.shardingsphere.sharding.tables.course.key-generator.column=cid
   spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
   
   # 指定分片策略  约定cid值偶数添加到course_1表，如果cid是奇数添加到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}
   
   # 打开sql输出日志
   spring.shardingsphere.props.sql.show=true
   

3. java测试

   //查询课程的方法 cid是分表字段，且用cid查询，精确匹配查询
       @Test
       public void findCourse() {
           QueryWrapper<Course> wrapper = new QueryWrapper<>();
           wrapper.eq("cid", 561542952397045760L);
           Course course = courseMapper.selectOne(wrapper);
           System.out.println(course);
       }
   
       //查询课程的方法 cid是分表字段，且用UserId查询 ,全量查询
       @Test
       public void findCourseByUserId() {
           QueryWrapper<Course> wrapper = new QueryWrapper<>();
           wrapper.eq("user_id", 100);
           System.out.println("count---" + courseMapper.selectCount(wrapper));
       }
   

   Wrapper的依赖是Mybatis-Plus

   		<dependency>
               <groupId>com.baomidou</groupId>
               <artifactId>mybatis-plus-boot-starter</artifactId>
               <version>3.0.5</version>
           </dependency>
   

   

4. 代码 github.com/r23456r/sha…

5. other

CHANGE MASTER TO
master_host = 'localhost',
master_user = 'db_sync',
master_password = 'db_sync',
master_log_file = 'mysql-bin.000002',
master_log_pos = 120;


GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';