Sharding-JDBC读写分离

1. 环境介绍

• JDK 1.8
• SpringBoot 2.4.0
• MyBatis 2.1.4
• Druid 1.2.3
• Sharding-JDBC 4.1.1

2. 数据库创建

• 创建数据库 user_db
• 创建数据表 user

CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `ustatus` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3. application.properties文件配置

spring.application.name=sharding_demo05

spring.shardingsphere.datasource.names=m0,s0

# 模仿主从数据库
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3307/user_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456

spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3308/user_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=123456

# 主库从库逻辑数据源定义 t0为user_db
spring.shardingsphere.sharding.master-slave-rules.t0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.t0.slave-data-source-names=s0

# 库表信息
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=t0.t_user

# 主键生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

# sql输出日志
spring.shardingsphere.props.sql.show=true

mybatis.mapper-locations=classpath:/mapper/*.xml

4. 测试代码

@SpringBootTest
class ShardingDemoApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testAddUser() {
        User user = new User();
        user.setUserName("测试");
        user.setUstatus("正常");
        userMapper.insert(user);
    }

    @Test
    void testFind() {
        User user = userMapper.selectByPrimaryKey(546662776693587968L);
        System.out.println(user);
    }
}

• github地址

  sharding_demo05