ShardingSphere 读写分离-代码零入侵

妖狐艹你老母 2022-01-23 08:53 211阅读 0赞

## 前言 ##

上文我们介绍Sharding-JDBC  分库分表（[ShardingSphere 数据分片][ShardingSphere] ||链接：[https://blog.csdn.net/weixin\_38003389/article/details/90518112][ShardingSphere]），我是用的是 java的配置方式，缺点就是有代码入侵性，其实我也完全可以用配置实现的。本文就给大家介绍一下 Sharding-JDBC 基于 配置进行读写分离。

## 正文 ##

**开始之前我们有两项很重要的准备工作**

第一项是在两台网络互通的 Centos7服务器安装mysql 5.7，请点我写好的安装步骤：[https://blog.csdn.net/weixin\_38003389/article/details/90696337][https_blog.csdn.net_weixin_38003389_article_details_90696337]

第二项是把这两台 mysql 配置成主从，请点我写好的配置步骤：

[https://blog.csdn.net/weixin\_38003389/article/details/90717879][https_blog.csdn.net_weixin_38003389_article_details_90717879]

以上工作都做好了之后我们来看一下怎么用 sharding-jdbc 配置来实现读写分离的。

创建一个maven 工程，pom文件如下

<parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.1.5.RELEASE</version>
            <relativePath/>
        </parent>
        <groupId>sharding-master-slave</groupId>
        <artifactId>sharding-master-slave</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>spring-boot-sharding-table</name>
        <description>基于 Spring Boot 2.1.5 使用sharding-sphere + JdbcTemplate 实现读写分离</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            <dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>3.1.0</version>
            </dependency>
            <dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-namespace</artifactId>
                <version>3.1.0</version>
            </dependency>
        </dependencies>

**核心配置类**

# 配置真实数据源
    sharding.jdbc.datasource.names=master,slave
    #主数据库
    sharding.jdbc.datasource.master.type=com.zaxxer.hikari.HikariDataSource
    sharding.jdbc.datasource.master.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
    sharding.jdbc.datasource.master.jdbc-url=jdbc:mysql://10.8.18.73:3306/master?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    
    sharding.jdbc.datasource.master.username=root
    sharding.jdbc.datasource.master.password=123456
    
    # 从数据库
    sharding.jdbc.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
    sharding.jdbc.datasource.slave.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
    sharding.jdbc.datasource.slave.jdbc-url=jdbc:mysql://10.8.18.74:3306/master?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    
    
    sharding.jdbc.datasource.slave.username=root
    sharding.jdbc.datasource.slave.password=123456
    
    # 配置读写分离
    # 配置从库选择策略，提供轮询与随机，这里选择用轮询
    sharding.jdbc.config.masterslave.load-balance-algorithm-type=round_robin
    sharding.jdbc.config.masterslave.name=ms
    sharding.jdbc.config.masterslave.master-data-source-name=master
    sharding.jdbc.config.masterslave.slave-data-source-names=slave
    
    # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志
    sharding.jdbc.config.props.sql.show=true
    spring.main.allow-bean-definition-overriding=true

**测试**

下面的类均是为了测试读写分离写的

@SpringBootApplication
    public class Application {
        public static void main(String[] args) {
            SpringApplication.run(Application.class, args);
        }
    }
    
    
    @RestController
    public class OrderController {
        @Autowired
        private OrderDao orderDao;
    
        @RequestMapping(path = "/createOrder/{userId}/{orderId}", method = {RequestMethod.GET})
        public String createOrder(@PathVariable("userId") Integer userId, @PathVariable("orderId") Integer orderId) {
            Order order = new Order();
            order.setOrderId(orderId);
            order.setUserId(userId);
            orderDao.createOrder(order);
            return "success";
        }
    
        @RequestMapping(path = "/{userId}", method = {RequestMethod.GET})
        public List<Order> getOrderListByUserId(@PathVariable("userId") Integer userId) {
            return orderDao.getOrderListByUserId(userId);
        }
    
        @RequestMapping(path = "/", method = {RequestMethod.GET})
        public List<Order> getOrderListByUserId() {
            return orderDao.getOrderList();
        }
    }
    
    
    
    
    public interface OrderDao {
        List<Order> getOrderListByUserId(Integer userId);
    
        List<Order> getOrderList();
    
        void createOrder(Order order);
    }
    
    
    
    
    @Service
    public class OrderDaoImpl implements OrderDao {
        @Autowired
        JdbcTemplate jdbcTemplate;
    
    
        @Override
        public List<Order> getOrderListByUserId(Integer userId) {
    //        StringBuilder sqlBuilder = new StringBuilder();
    //        sqlBuilder
    //                .append("select order_id, user_id from order where user_id=? ");
    //        return jdbcTemplate.query(sqlBuilder.toString(), new Object[]{userId},
    //                new int[]{Types.INTEGER}, new BeanPropertyRowMapper<Order>(
    //                        Order.class));
            return null;
    
        }
    
        @Override
        public List<Order> getOrderList() {
            String sql = "select order_id, user_id from order_0 ";
    
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Order>(
                    Order.class));
        }
    
        @Override
        public void createOrder(Order order) {
            StringBuffer sb = new StringBuffer();
            sb.append("insert into order_0(user_id, order_id)");
            sb.append("values(");
            sb.append(order.getUserId()).append(",");
            sb.append(order.getOrderId());
            sb.append(")");
            jdbcTemplate.update(sb.toString());
        }
    }

下面我们启动项目进行数据的增加和查询就可以了，观察控制台的打印信息，即我增加的时候数据源连接 master，我查询的时候数据源连接slave，读写分离成功。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODAwMzM4OQ_size_16_color_FFFFFF_t_70][]

在这个过程还碰到了很多坑，大多是在mysql配置问题，

首先拿到 root 是必须的，如果项目启动报错信息是与服务器连接超时时，我给定以下解决思路

*  本地与服务器网络是否通
 *  防火墙是否关闭
 *  mysql 连接账号是否允许外置连接

github 地址：[https://github.com/362460453/sharding-master-slave][https_github.com_362460453_sharding-master-slave]

如果你们碰到其他的问题可以自行百度或加我微信   。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODAwMzM4OQ_size_16_color_FFFFFF_t_70 1][]

[ShardingSphere]: https://blog.csdn.net/weixin_38003389/article/details/90518112
[https_blog.csdn.net_weixin_38003389_article_details_90696337]: https://blog.csdn.net/weixin_38003389/article/details/90696337
[https_blog.csdn.net_weixin_38003389_article_details_90717879]: https://blog.csdn.net/weixin_38003389/article/details/90717879
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODAwMzM4OQ_size_16_color_FFFFFF_t_70]: /images/20220123/a53421220c82449cb7d6ff40b934b09f.png
[https_github.com_362460453_sharding-master-slave]: https://github.com/362460453/sharding-master-slave
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODAwMzM4OQ_size_16_color_FFFFFF_t_70 1]: /images/20220123/0de27e0dad8b4656886f9871b6c271b5.png