ShardingSphere-JDBC读写分离（四）

落日映苍穹つ 2024-04-01 09:26 70阅读 0赞

## ShardingSphere-JDBC读写分离 ##

### 1、创建SpringBoot程序 ###

#### 1.1、创建项目 ####

项目类型：Spring Initializr

SpringBoot脚手架：http://start.aliyun.com

项目名：sharding-jdbc-demo

SpringBoot版本：2.3.7.RELEASE

#### 1.2、添加依赖 ####

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>
    
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
    
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

#### 1.3、创建实体类 ####

package com.atguigu.shardingjdbcdemo.entity;
    
    @TableName("t_user")
    @Data
    public class User {
        
        @TableId(type = IdType.AUTO)
        private Long id;
        private String uname;
    }

#### 1.4、创建Mapper ####

package com.atguigu.shardingjdbcdemo.mapper;
    
    @Mapper
    public interface UserMapper extends BaseMapper<User> {
        
    }

#### 1.5、配置读写分离 ####

application.properties：

# 应用名称
    spring.application.name=sharging-jdbc-demo
    # 开发环境设置
    spring.profiles.active=dev
    # 内存模式
    spring.shardingsphere.mode.type=Memory
    
    # 配置真实数据源
    spring.shardingsphere.datasource.names=master,slave1,slave2
    
    # 配置第 1 个数据源
    spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.100.201:3306/db_user
    spring.shardingsphere.datasource.master.username=root
    spring.shardingsphere.datasource.master.password=123456
    
    # 配置第 2 个数据源
    spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.100.201:3307/db_user
    spring.shardingsphere.datasource.slave1.username=root
    spring.shardingsphere.datasource.slave1.password=123456
    
    # 配置第 3 个数据源
    spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.100.201:3308/db_user
    spring.shardingsphere.datasource.slave2.username=root
    spring.shardingsphere.datasource.slave2.password=123456
    
    # 读写分离类型，如: Static，Dynamic
    spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.type=Static
    # 写数据源名称
    spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.write-data-source-name=master
    # 读数据源名称，多个从数据源用逗号分隔
    spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.read-data-source-names=slave1,slave2
    
    # 负载均衡算法名称
    spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=alg_round
    
    # 负载均衡算法配置
    # 负载均衡算法类型
    spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_round.type=ROUND_ROBIN
    spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_random.type=RANDOM
    spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.type=WEIGHT
    spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.props.slave1=1
    spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.props.slave2=2
    
    # 打印SQl
    spring.shardingsphere.props.sql-show=true

### 2、测试 ###

#### 2.1、读写分离测试 ####

package com.atguigu.shardingjdbcdemo;
    
    @SpringBootTest
    class ReadwriteTest {
        
    
        @Autowired
        private UserMapper userMapper;
    
        /**
         * 写入数据的测试
         */
        @Test
        public void testInsert(){
        
    
            User user = new User();
            user.setUname("张三丰");
            userMapper.insert(user);
        }
    
    }

#### 2.2、事务测试 ####

为了保证主从库间的事务一致性，避免跨服务的分布式事务，ShardingSphere-JDBC的`主从模型中，事务中的数据读写均用主库`。

*  不添加@Transactional：insert对主库操作，select对从库操作
 *  添加@Transactional：则insert和select均对主库操作
 *  \*\*注意：\*\*在JUnit环境下的@Transactional注解，默认情况下就会对事务进行回滚（即使在没加注解@Rollback，也会对事务回滚）

/**
         * 事务测试
         */
    @Transactional//开启事务
    @Test
    public void testTrans(){
        
    
        User user = new User();
        user.setUname("铁锤");
        userMapper.insert(user);
    
        List<User> users = userMapper.selectList(null);
    }

#### 2.3、负载均衡测试 ####

/**
         * 读数据测试
         */
    @Test
    public void testSelectAll(){
        
        List<User> users = userMapper.selectList(null);
        List<User> users = userMapper.selectList(null);//执行第二次测试负载均衡
        users.forEach(System.out::println);
    }

也可以在web请求中测试负载均衡

package com.atguigu.shardingjdbcdemo.controller;
    
    @RestController
    @RequestMapping("/userController")
    public class UserController {
        
    
        @Autowired
        private UserMapper userMapper;
    
        /**
         * 测试负载均衡策略
         */
        @GetMapping("selectAll")
        public void selectAll(){
        
            List<User> users = userMapper.selectList(null);
            users.forEach(System.out::println);
        }
    }