Sharding-JDBC之读写分离

缺乏、安全感 2022-12-29 14:20 118阅读 0赞

# 读写分离 #

本文介绍SpringBoot使用Sharding-JDBC进行读写分离，废话不多说，直接写代码。

场景介绍：

*  主库（Master）负责写入。
 *  从库（Slave）负责查询。

## POM文件 ##

pom文件引入如下相关依赖：

<?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>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.4.RELEASE</version>
        </parent>
    
        <groupId>com.morris</groupId>
        <artifactId>shardingsphere-demo</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <dependencies>
    
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>4.0.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <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>1.3.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.44</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.11</version>
                <exclusions>
                    <exclusion>
                        <groupId>com.alibaba</groupId>
                        <artifactId>jconsole</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>com.alibaba</groupId>
                        <artifactId>tools</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
    
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.1</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
        <repositories>
            <repository>
                <id>aliyun</id>
                <name>aliyun</name>
                <url>http://maven.aliyun.com/nexus/content/groups/public</url>
            </repository>
    
        </repositories>
    
    </project>

## OrderController ##

package com.morris.controller;
    
    import com.morris.entity.Order;
    import com.morris.service.OrderService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController
    @RequestMapping("order")
    public class OrderController { 
    
        @Autowired
        private OrderService orderService;
    
        @RequestMapping("list")
        public List<Order> list() { 
            return orderService.list();
        }
    
        @RequestMapping("add")
        public String add() { 
            Order order = Order.builder().userId(10).status("成功").build();
            orderService.add(order);
            return "success";
        }
    
    }

## OrderService ##

package com.morris.service;
    
    import com.morris.entity.Order;
    
    import java.util.List;
    
    public interface OrderService { 
    
        List<Order> list();
    
        void add(Order order);
    }

## OrderServiceImpl ##

package com.morris.service.impl;
    
    import com.morris.entity.Order;
    import com.morris.mapper.OrderMapper;
    import com.morris.service.OrderService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    @Service
    public class OrderServiceImpl implements OrderService { 
    
        @Autowired
        private OrderMapper orderMapper;
    
        @Override
        public List<Order> list() { 
            return orderMapper.list();
        }
    
        @Override
        public void add(Order order) { 
            orderMapper.insert(order);
        }
    }

## OrderMapper ##

package com.morris.mapper;
    
    import com.morris.entity.Order;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    @Mapper
    public interface OrderMapper { 
    
        @Select("select * from t_order")
        List<Order> list();
    
        @Select("insert into t_order(user_id, status) values(#{userId}, #{status})")
        void insert(Order order);
    }

## Order ##

package com.morris.entity;
    
    import lombok.Builder;
    import lombok.Data;
    
    @Data
    @Builder
    public class Order { 
        private Long orderId;
        private Integer userId;
        private String status;
    }

## Application ##

package com.morris;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    @MapperScan("com.morris.mapper")
    public class Application { 
    
        public static void main(String[] args) { 
            SpringApplication.run(Application.class, args);
        }
    }

## application.yml ##

server:
      port: 8800
    
    mybatis:
      configuration:
        map-underscore-to-camel-case: true
        use-generated-keys: true
    
    spring:
      shardingsphere:
        datasource:
          names: master,slave
          master:
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://127.0.0.1:3306/order-master?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
            username: master
            password: master
          slave:
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://127.0.0.1:3306/order-slave?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull
            username: slave
            password: slave
    
        masterslave:
          master-data-source-name: master
          load-balance-algorithm-type: round_robin # 负责均衡算法
          name: dataSource
          slave-data-source-names: slave
        props:
          sql:
            show: true

验证写数据走主库：

$ curl localhost:8800/order/add
    success

日志打印如下：

2020-12-23 11:34:26.688  INFO 6404 --- [nio-8800-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
    2020-12-23 11:34:26.688  INFO 6404 --- [nio-8800-exec-2] ShardingSphere-SQL                       : SQL: insert into t_order(user_id, status) values(?, ?) ::: DataSources: master

验证读数据走从库：

$ curl localhost:8800/order/list
    [{ "orderId":1,"userId":10,"status":"成功"}]

日志打印如下：

2020-12-23 11:34:16.231  INFO 6404 --- [nio-8800-exec-1] ShardingSphere-SQL                       : Rule Type: master-slave
    2020-12-23 11:34:16.231  INFO 6404 --- [nio-8800-exec-1] ShardingSphere-SQL                       : SQL: select * from t_order ::: DataSources: slave