利用spring aop和AbstractRoutingDataSource类实现数据库的读写分离

最新推荐文章于 2024-07-10 18:33:40 发布

weixin_34051201

最新推荐文章于 2024-07-10 18:33:40 发布

阅读量145

点赞数

文章标签：数据库 java python

原文链接：https://my.oschina.net/wangxindong/blog/883912

版权

2019独角兽企业重金招聘Python工程师标准>>>

web业务开发中可以不做分库分表，但读写分离是一定要做的，如果只靠一个库来承载读和写的请求。风险可想而知。当然单表行数超过500万行或者单表容量超过2GB，就要进行分库分表，如果预计三年后根本达不到这个级别请不要创建时就分库分表。这篇文章我们只介绍读写分离。现在假设我们的数据库服务器已经实现了主从复制，如下图（如何做，可参看这篇文章：http://raye.wang/2017/04/14/mysqlzhu-cong-fu-zhi-da-jian-ji-yu-ri-zhi-binlog/）

我要实现读写分离，思路很清晰，主要是两点，

第一点：想办法在读操作之前，设置DataSource的数据源为读，写操作之前设置DataSource为写。

之前和之后的操作，我们很容易想到用AOP来实现。

第二点：设置好了DataSource之后，数据源是如何切换，在getConnection的时候如何用到刚才设置的DataSource值，这个时候我们就可以利用org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource

主要是用到了determineCurrentLookupKey（）这个方法，每次getConnection的时候会确定目标数据源，而这个目标数据源，正是我们再AOP中方法执行前，设置的，读或者写。这样就达到了动态切换数据源的目的。

详细代码文件列表

自定义注解DataSource.java

package com.mytest.datasource.core;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Created by 新栋BOOK on 2017/4/10.
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSource {
    String value();
}

DynamicDataSource.java 实现 determineCurrentLookupKey()方法

package com.mytest.datasource.core;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * Created by 新栋BOOK on 2017/4/10.
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<String>();

    public static void setDataSourceKey(String dataSource) {
        dataSourceKey.set(dataSource);
    }

    public static String getDatasourcekey() {
        return dataSourceKey.get();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return dataSourceKey.get();
    }
}

DataSourceAspect.java切面类

package com.mytest.datasource;

import com.mytest.datasource.core.DataSource;
import com.mytest.datasource.core.DynamicDataSource;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import java.lang.reflect.Method;

/**
 * Created by 新栋BOOK on 2017/4/10.
 */
public class DataSourceAspect {

    public DataSourceAspect(){
        System.out.println();
    }
    public void before(JoinPoint joinPoint) throws NoSuchMethodException {
        if (TransactionSynchronizationManager.isActualTransactionActive()
                && DynamicDataSource.getDatasourcekey() != null)
            return;
        // 获取方法签名
        Method declareMethod = ((MethodSignature) joinPoint.getSignature()).getMethod();
        Method instanceMethod = joinPoint.getTarget().getClass().getMethod(declareMethod.getName(),
                declareMethod.getParameterTypes());
        DataSource methodAnnotation = AnnotationUtils.findAnnotation(instanceMethod, DataSource.class);
        if (methodAnnotation == null)
            return;
        if (methodAnnotation != null) {
            DynamicDataSource.setDataSourceKey(methodAnnotation.value());
        }
    }

    /**
     * 方法执行完后置空
     */
    public void after(JoinPoint joinPoint) {
        if (TransactionSynchronizationManager.isActualTransactionActive())
            return;
        if (TransactionSynchronizationManager.isSynchronizationActive())
            TransactionSynchronizationManager.clearSynchronization();
        DynamicDataSource.setDataSourceKey(null);
    }


}

TbUserMapper.java 使用的时候，只需要通过注解标明要使用的数据源即可

package com.mytest.dao;

import com.mytest.datasource.core.DataSource;
import com.mytest.domain.TbUser;


/**
 * Created by 新栋BOOK on 2017/4/10.
 */

public interface TbUserMapper {


    @DataSource("master")
    public TbUser queryById(int id);

    @DataSource("slave")
    public TbUser queryByName(String userName);

}

XML文件，配置数据源和AOP

spring-jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!--主（写）库数据源-->
    <bean id="masterdataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mytest1" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
    <!--从（读）库数据源-->
    <bean id="slavedataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mytest2" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>

    <bean id="dynamicDataSource" class="com.mytest.datasource.core.DynamicDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <!-- write -->
                <entry key="master" value-ref="masterdataSource"/>
                <!-- read -->
                <entry key="slave" value-ref="slavedataSource"/>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="masterdataSource"/>
    </bean>

    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dynamicDataSource" />
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.mytest.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

    <!-- 配置SqlSessionFactoryBean -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dynamicDataSource" />
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="mapperLocations" value="classpath:sqlmap/*.xml"/>
    </bean>

    <!--<context:annotation-config />-->
    <!--<context:component-scan base-package="com.mytest"/>-->
    <!-- 配置数据库注解aop -->
    <bean id="manyDataSourceAspect" class="com.mytest.datasource.DataSourceAspect" />
    <aop:config>
        <aop:pointcut id="dsPointcut" expression="execution(* com.mytest.dao.*.*(..))"/>
        <aop:aspect id="dsAspect" ref="manyDataSourceAspect">
            <aop:before pointcut-ref="dsPointcut" method="before"/>
            <aop:after pointcut-ref="dsPointcut" method="after"/>
        </aop:aspect>
    </aop:config>
    <!-- 配置数据库注解aop -->


    <bean id="tbUserService" class="com.mytest.service.impl.TbUserServiceImpl" />
</beans>

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="useGeneratedKeys" value="true"/>
        <!-- 开启驼峰命名转换：Table(plugin_code) -> Entity(pluginCode) -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

spring-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">


    <import resource="spring-jdbc.xml"></import>
</beans>

TbUser.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.mytest.dao.TbUserMapper">

    <sql id="Base_Column_List">
        id, user_name
    </sql>

    <select id="queryById" timeout="10" parameterType="java.lang.Integer" resultType="com.mytest.domain.TbUser">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_user1
    </select>

    <select id="queryByName" timeout="10" parameterType="java.lang.String" resultType="com.mytest.domain.TbUser">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_user1
    </select>
</mapper>

测试代码

DataSourceTest

package com.mytest.datasource;

import com.mytest.service.TbUserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

/**
 * Created by 新栋BOOK on 2017/4/10.
 */
@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations={"classpath*:/spring-config.xml"})
public class DataSourceTest {
    @Resource
    private TbUserService tbUserService;

    @Test
    public void testDataSource(){
        System.out.println("slave db:"+tbUserService.queryByName(""));
        System.out.println("0420");
        System.out.println("master db:"+tbUserService.queryById(0));
    }

}

并发测试

DataSourceThreadTest

package com.mytest.datasource;

import com.mytest.service.TbUserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

/**
 * Created by 新栋BOOK on 2017/4/14.
 */
@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations={"classpath*:/spring-config.xml"})
public class DataSourceThreadTest {
    @Resource
    private TbUserService tbUserService;

    @Test
    public void testDataSource() throws Exception{
        ExecutorService executorService = Executors.newFixedThreadPool(200);

        Callable<String> call1 = new Callable<String>() {
            public String call() throws Exception {
                return "master db:" + tbUserService.queryById(0);
            }
        };

        Callable<String> call2 = new Callable<String>() {
            public String call() throws Exception {
                return "slave  db:" + tbUserService.queryByName("");
            }
        };

        List<Callable<String>> list = new ArrayList<Callable<String>>();
        for(int i=0;i<200;i++){
            if(i%2==0){
                list.add(call1);
            }else {
                list.add(call2);
            }
        }

        List<Future<String>> futures = executorService.invokeAll(list);

        for(Future f:futures){
            Object o = f.get();
            if(o.toString().contains("slave")&&o.toString().contains("2")){
                System.out.println(o+"[true]");
            }else if(o.toString().contains("master")&&o.toString().contains("1")){
                System.out.println(o+"[true]");
            }else {
                System.out.println(o+"[false]");

            }
        }

/*
        System.out.println("master db:"+tbUserService.queryById(0));
        System.out.println("slave db:"+tbUserService.queryByName("abcd"));*/
    }



}

最后总结几个问题。

1、如果IP变更了，读库和写库不就乱了吗？

不会，看上面第一张图，我们采用的是域名的方式连接。本例子中为了演示直接使用的是IP连接。但实际引用的时候，我们

主库配置

<property name="url" value="jdbc:mysql://my-test-m.mysql.db.com:3306/mytest1" />

从库配置

<property name="url" value="jdbc:mysql://my-test-sa.mysql.db.com:3306/mytest2" />

这样写库和读库的域名是固定的，当主库挂掉，DBA选择从库重新当选为主库的时候，应用层代码是不用更改的。

2、会有并发的问题吗，也就是线程安全

不会，

private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<String>();

充分保证了这一点，大家可以自行了解ThreadLocal的作用。

转载于:https://my.oschina.net/wangxindong/blog/883912

weixin_34051201

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫