sharding-jdbc+druid实现主从库和分库分表

前置：

springboot2.2.13+shardingjdbc4.1.3+druid1.2.5

 

设计简单表结构：

CREATE TABLE sharding_db_0.`cms_merchant_0` (
  `merchant_id` bigint(20) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`merchant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE sharding_db_0.`cms_merchant_1` (
  `merchant_id` bigint(20) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`merchant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE sharding_db_1.`cms_merchant_0` (
  `merchant_id` bigint(20) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`merchant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE sharding_db_1.`cms_merchant_1` (
  `merchant_id` bigint(20) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`merchant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

 

springboot配置如下：

spring:
  profiles:
    active:
    - dev
#  datasource:
#    driver-class-name: com.mysql.jdbc.Driver
#    url: jdbc:mysql://127.0.0.1:3306/yiciyu?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull
#    username: root
#    password: 123456

  
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/sharding_db_0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull
        username: root
        password: 123456
      ds1:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3308/sharding_db_1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull
        username: root
        password: 123456
    sharding:
      tables:
        cms_merchant: 
          actual-data-nodes: msds$->{0..1}.cms_merchant_$->{0..1}
          database-strategy:
            inline:
              sharding-column: merchant_id
              #计算方式：value % [库数]
              algorithm-expression: msds$->{merchant_id % 2}
          table-strategy:
            inline:
              sharding-column: merchant_id
              #计算方式：value / [库数] % [表数]，示例中仅通过merchant_id后两位路由，为保障
              algorithm-expression: cms_merchant_$->{((int) (Integer.parseInt(Long.toString(merchant_id).substring(1)) / 2)) % 2}
      #可缺省，缺省时走单库方式
      master-slave-rules:
        msds0:
          master-data-source-name: ds0
          slave-data-source-names:
          - ds0
          - ds0
        msds1:
          master-data-source-name: ds1
          slave-data-source-names:
          - ds1
          - ds1
              
          
    
mybatis-plus:
  mapper-locations:  classpath:mapper/**/*.xml
  type-aliases-package: com.yiciyu.*.entity,com.yiciyu.*.model
  global-config:
    db-config:
      id-type: auto
      table-underline: true

 

测试代码：

package com.yiciyu.se;

import java.util.Arrays;
import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yiciyu.lu.util.JsonUtils;
import com.yiciyu.se.dao.MerchantDao;
import com.yiciyu.se.entity.MerchantEntity;

@SpringBootTest
class ShardingDbApplicationTests {
	
	@Autowired
	private MerchantDao merchantDao;

	@Test
	void insertTests() {
		for (long i = 100; i < 200; i++) {
			MerchantEntity entity = new MerchantEntity();
			entity.setMerchantId(i);
			entity.setMerchantName("商户名-" + i);
			merchantDao.insert(entity);
		}
	}
	
	@Test
	void updateTests() {
		for (long i = 100; i < 200; i++) {
			MerchantEntity entity = new MerchantEntity();
			entity.setMerchantId(i);
			entity.setMerchantName("update-商户名-" + i);
			merchantDao.updateById(entity);
		}
	}
	
	@Test
	void singleQueryTests() {
		for (long i = 100; i < 200; i++) {
			MerchantEntity entity = merchantDao.selectById(i);
			System.out.println(i + "===" + JsonUtils.toString(entity));
		}
	}
	
	@Test
	void multQueryTests() {
		//inline不支持range（即between、大于小于等范围查询）
		List<MerchantEntity> list = merchantDao.selectList(new QueryWrapper<MerchantEntity>()
				.in("merchant_id", Arrays.asList(100L, 101L, 102L, 103L)));
		System.out.println("list===" + JsonUtils.toString(list));
	}

}