springmvc+mybatis+shardingsphere（shardingjdbc）实现数据库（mysql）读写分离架构

短命女 2022-05-22 01:12 590阅读 0赞

在存在大量读操作的场景，可以采用数据库读写分离的机制来加快查询速度。

mysql本身就支持多服务实现读写分离，而springmvc要实现可以自己写读写分离的代码实现，其基本原理就是采用aop原理，拦截特定的自定义注解方法，通过不同的参数调用不同的数据源，这个网上有很多例子。

如果是已经存在的系统改造或者自己不想写代码，也可以采用已经成熟的框架，本人使用的是sharding-sphere的sharding-jdbc，官网是：[http://shardingjdbc.io/][http_shardingjdbc.io]。

首先，要在springmvc项目的pom文件中添加包，代码如下：

<dependency>
    	    <groupId>io.shardingsphere</groupId>
    	    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    	    <version>3.0.0.M1</version>
    	</dependency>
    	
    	<dependency>
    	    <groupId>io.shardingsphere</groupId>
    	    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    	    <version>3.0.0.M1</version>
    	</dependency>

然后，准备好三个数据库，一个主库两个从库，新增回加入主库，查询回随机走从库，如果mysql配置了读写分离机制，会自动把主库的数据同步到从库。创建脚本如下：

-- 主库
    CREATE DATABASE `master`;
    
    CREATE TABLE `t_order` (
      `order_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `business_id` int(4) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `t_order` VALUES (1,1,112);
    
    -- 从库1
    CREATE DATABASE `slave_1` ; 
    CREATE TABLE `t_order` (
      `order_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `business_id` int(4) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
    
    INSERT INTO `t_order` VALUES (2,2,112);
    
    -- 从库2
    CREATE DATABASE `slave_2` ; 
    CREATE TABLE `t_order` (
      `order_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `business_id` int(4) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `t_order` VALUES (3,3,112)；

数据库信息配置文件db.properties配置如下：

#master
    jdbc.master.driverClassName=com.mysql.jdbc.Driver
    jdbc.master.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8
    jdbc.master.username=root
    jdbc.master.password=123456
    
    #slave1
    jdbc.slave1.driverClassName=com.mysql.jdbc.Driver
    jdbc.slave1.url=jdbc:mysql://localhost:3306/slave_1?useUnicode=true&characterEncoding=utf8
    jdbc.slave1.username=root
    jdbc.slave1.password=123456
    
    #slave2
    jdbc.slave2.driverClassName=com.mysql.jdbc.Driver
    jdbc.slave2.url=jdbc:mysql://localhost:3306/slave_2?useUnicode=true&characterEncoding=utf8
    jdbc.slave2.username=root
    jdbc.slave2.password=123456

spring配置文件，配置数据源代码如下：

<beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:tx="http://www.springframework.org/schema/tx"
           xmlns:mongo="http://www.springframework.org/schema/data/mongo"
           xmlns:rabbit="http://www.springframework.org/schema/rabbit"
           xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans.xsd
           http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context.xsd
           http://www.springframework.org/schema/tx
           http://www.springframework.org/schema/tx/spring-tx.xsd
           http://www.springframework.org/schema/data/mongo
    	   http://www.springframework.org/schema/data/mongo/spring-mongo-1.5.xsd
           http://www.springframework.org/schema/rabbit
           http://www.springframework.org/schema/rabbit/spring-rabbit-1.6.xsd
           http://shardingsphere.io/schema/shardingsphere/masterslave  
           http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd">
        
        <context:annotation-config/>
    
        <context:component-scan base-package="com.shan">
            <context:include-filter type="annotation" expression="org.springframework.stereotype.Service"/>
            <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
            <context:exclude-filter type="annotation" expression="org.springframework.web.bind.annotation.RestController"/>
        </context:component-scan>
    
        
        <bean id="property" class="com.shan.framework.property.MutilPropertyPlaceholderConfigurer">
            <property name="locations">
                <list>
                    <value>classpath*:property/*.properties</value>
                </list>
            </property>
        </bean>
    
        
        <bean id="master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="${jdbc.master.driverClassName}" />
            <property name="url" value="${jdbc.master.url}" />
            <property name="username" value="${jdbc.master.username}" />
            <property name="password" value="${jdbc.master.password}" />
            
            <property name="maxActive" value="100"/>
    	    <property name="initialSize" value="10"/>
    	    <property name="maxWait" value="60000"/>
    	    <property name="minIdle" value="5"/>
            
            <property name="testOnBorrow">
                <value>true</value>
            </property>
            <property name="validationQuery">
                <value>SELECT 1 FROM DUAL</value>
            </property>
        </bean>
        
        <bean id="slave1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="${jdbc.slave1.driverClassName}" />
            <property name="url" value="${jdbc.slave1.url}" />
            <property name="username" value="${jdbc.slave1.username}" />
            <property name="password" value="${jdbc.slave1.password}" />
            
            <property name="maxActive" value="100"/>
    	    <property name="initialSize" value="10"/>
    	    <property name="maxWait" value="60000"/>
    	    <property name="minIdle" value="5"/>
            
            <property name="testOnBorrow">
                <value>true</value>
            </property>
            <property name="validationQuery">
                <value>SELECT 1 FROM DUAL</value>
            </property>
        </bean>
        
        <bean id="slave2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="${jdbc.slave2.driverClassName}" />
            <property name="url" value="${jdbc.slave2.url}" />
            <property name="username" value="${jdbc.slave2.username}" />
            <property name="password" value="${jdbc.slave2.password}" />
            
            <property name="maxActive" value="100"/>
    	    <property name="initialSize" value="10"/>
    	    <property name="maxWait" value="60000"/>
    	    <property name="minIdle" value="5"/>
            
            <property name="testOnBorrow">
                <value>true</value>
            </property>
            <property name="validationQuery">
                <value>SELECT 1 FROM DUAL</value>
            </property>
        </bean>
        
        <bean id="randomStrategy" class="io.shardingsphere.core.api.algorithm.masterslave.RandomMasterSlaveLoadBalanceAlgorithm" />
    
    	<master-slave:data-source id="shardingDataSource" master-data-source-name="master" slave-data-source-names="slave1,slave2" strategy-ref="randomStrategy" />
    	
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="shardingDataSource" />
        </bean>
    
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="shardingDataSource" />
            <property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" />
            <property name="mapperLocations">
                <list>
                    <value>classpath*:mybatis/mapper/**/*Mapper.xml</value>
                </list>
            </property>
        </bean>
    
        <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
            <constructor-arg index="0" ref="sqlSessionFactory" />
        </bean>
    
        <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />
    
    </beans>

model文件UserModel.java代码如下：

package com.shan.ssm.model;
    
    public class UserModel {
    	
    	private Integer orderId;
    	
    	private Integer userId;
    	
    	private Integer businessId;
    
    	public Integer getOrderId() {
    		return orderId;
    	}
    
    	public void setOrderId(Integer orderId) {
    		this.orderId = orderId;
    	}
    
    	public Integer getUserId() {
    		return userId;
    	}
    
    	public void setUserId(Integer userId) {
    		this.userId = userId;
    	}
    
    	public Integer getBusinessId() {
    		return businessId;
    	}
    
    	public void setBusinessId(Integer businessId) {
    		this.businessId = businessId;
    	}
    
    }

ShardingJdbcController.java代码如下：

package com.shan.ssm.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import com.shan.ssm.service.IShardingJdbcService;
    
    @Controller
    @RequestMapping("sjc")
    public class ShardingJdbcController {
    	
    	@Autowired
    	private IShardingJdbcService iShardingJdbcService;
    	
    	@RequestMapping("/insert")
    	@ResponseBody
    	public String insert() {
    		String msg = iShardingJdbcService.insert();
    		return msg;
    	}
    	
    	@RequestMapping("/list")
    	@ResponseBody
    	public String list() {
    		String msg = iShardingJdbcService.list();
    		return msg;
    	}
    	
    }

IShardingJdbcService.java接口代码如下：

package com.shan.ssm.service;
    
    public interface IShardingJdbcService {
    	
    	public String insert();
    	
    	public String list();
    	
    }

ShardingJdbcService.java实现代码如下：

package com.shan.ssm.service.impl;
    
    import java.util.List;
    import java.util.Map;
    
    import javax.annotation.Resource;
    
    import org.apache.commons.lang3.RandomUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.springframework.stereotype.Service;
    
    import com.alibaba.fastjson.JSONObject;
    import com.shan.ssm.model.UserModel;
    import com.shan.ssm.service.IShardingJdbcService;
    
    @Service
    public class ShardingJdbcService implements IShardingJdbcService {
    	
    	@Resource
    	private SqlSession sqlSession;
    	
    	@Override
    	public String insert() {
    		UserModel um = new UserModel();
    		um.setOrderId(RandomUtils.nextInt(10, 10000));
    		um.setUserId(RandomUtils.nextInt(10, 10000));
    		um.setBusinessId(RandomUtils.nextInt(10, 10000));
    		int num = sqlSession.insert(ShardingJdbcService.class.getName() + ".insert", um);
    		JSONObject obj = new JSONObject();
    		if (num >= 0) {
    			obj.put("code", 0);
    			obj.put("msg", "成功");
    		} else {
    			obj.put("code", 1);
    			obj.put("msg", "失败");
    		}
    		return obj.toJSONString();
    	}
    	
    	@Override
    	public String list() {
    		List<Map<String, Object>> list= sqlSession.selectList(ShardingJdbcService.class.getName() + ".list");
    		JSONObject obj = new JSONObject();
    		obj.put("list", list);
    		return obj.toJSONString();
    	}
    	
    }

mybatis的mapper文件ShardingJdbcServiceMapper.xml代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.shan.ssm.service.impl.ShardingJdbcService">
    	
    	<insert id="insert" parameterType="com.shan.ssm.model.UserModel">
    		INSERT INTO t_order
    		(order_id,
    		user_id,
    		business_id)
    		VALUES
    		(#{orderId},
    		#{userId},
    		#{businessId}
    		)
        </insert>
    	
    	<select id="list" resultType="java.util.HashMap">
    		SELECT * FROM t_order 
    	</select>
    	
    </mapper>

启动程序后，测试插入，发现只会往主库中插入数据，如图：

![70][]

![70 1][]

查询也是随机调用的两个从库，如图：

![70 2][]

![70 3][]

[http_shardingjdbc.io]: http://shardingjdbc.io/
[70]: /images/20220522/c579f866cc2c42ea8fd1a5abee5f3db8.png
[70 1]: /images/20220522/b471c1f60e7b466f9de262ac78ebb6b6.png
[70 2]: /images/20220522/eee99ca1ec4d4fac8366240af38f723d.png
[70 3]: /images/20220522/fa9d6d54cc90433298ffe6dcfd11eae5.png