ShardingSphere 读写分离-代码零入侵

胖虎

发布于 2019-06-26 17:15:35

2.2K0

发布于 2019-06-26 17:15:35

文章被收录于专栏：晏霖晏霖

封面为好友拍摄的照片，想查看更多微信公众号搜索：JavaBoy王皓或csdn博客搜索：TenaciousD

前言

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

正文

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

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

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

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，读写分离成功。