Spring和alibaba连接池druid实现双数据源

﹏ヽ暗。殇╰゛Y 2024-04-17 16:45 34阅读 0赞

### 背景： ###

1.  项目架构，SpringMVC + Spring + Mybatis
2.  随着业务的增加，数据库的压力倍增，经和开发人员讨论想实现读写分离的功能，由运维人员实现Mysql之间的同步，开发人员实现 主备库之间的切换。

### 设计思路： ###

1.  在 application.xml中配置双数据源，命名为 master 和 slaver。

<bean id="master" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="${driver}" />
            <property name="url" value="${master_url}" />
            <property name="username" value="${master_user}" />
            <property name="password" value="${master_password}" />
            <property name="initialSize" value="1" />
            <property name="minIdle" value="1" />
            <property name="maxActive" value="10" />
            <property name="maxWait" value="60000" />
            <property name="timeBetweenEvictionRunsMillis" value="50000" />
            <property name="minEvictableIdleTimeMillis" value="300000" />
            <property name="validationQuery" value="SELECT 1" />
            <property name="testWhileIdle" value="true" />
            <property name="testOnBorrow" value="false" />
            <property name="testOnReturn" value="false" />
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
            <property name="filters" value="stat" />
        </bean>
    
        
        <bean id="slaver" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="${driver}" />
            <property name="url" value="${slaver_url}" />
            <property name="username" value="${slaver_user}" />
            <property name="password" value="${slaver_password}" />
            <property name="initialSize" value="1" />
            <property name="minIdle" value="1" />
            <property name="maxActive" value="10" />
            <property name="maxWait" value="60000" />
            <property name="timeBetweenEvictionRunsMillis" value="50000" />
            <property name="minEvictableIdleTimeMillis" value="300000" />
            <property name="validationQuery" value="SELECT 1" />
            <property name="testWhileIdle" value="true" />
            <property name="testOnBorrow" value="false" />
            <property name="testOnReturn" value="false" />
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
            <property name="filters" value="stat" />
        </bean>

1.  创建 DataSourceRouter.java 继承 Spring中AbstractRoutingDataSource.java 并实现其中的抽象方法determineCurrentLookupKey。 原因： AbstractRoutingDataSource是Spring 提供的多数据源的抽象类，spring会在使用事务的地方来调用此类determineCurrentLookupKey(),获取数据源的key值，最后我们使用 ThreadLocal 来存储要使用的数据源key【master，slaver】，用于线程上下文获取

public class DataSourceRouter extends AbstractRoutingDataSource {
    
        // 数据源名称线程池，用于上下午中获取
        private static  final ThreadLocal<String> holder = new ThreadLocal<String>();
    
        //主库
        public static final String MASTER = "master";
    
        // 从库
        public static final String SLAVER = "slaver";
    
    
        /**
         * 获取数据源的名称 -->Spring会自行调用
         * @return
         */
        @Override
        protected Object determineCurrentLookupKey() {
            return DataSourceRouter.getDataSource();
        }
    
        /**
         * 设置数据源
         * @param datasource 数据源名称
         */
        public static void setDataSource(String datasource){
            holder.set(datasource);
        }
    
        /**
         * 获取数据源名称
         * @return 数据源名称
         */
        public static String getDataSource(){
            return  holder.get();
        }
    
        /**
         * 清空数据源
         */
        public static void clearDataSource(){
            holder.remove();
        }
    }

1.  在 application.xml 中加入 DataSourceRouter.java 配置，并将其作为dataSource 放入到sqlSessionFactory中，全部配置如下:

<?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"
           xmlns:tx="http://www.springframework.org/schema/tx"
           xmlns:context="http://www.springframework.org/schema/context"
           xsi:schemaLocation="
            http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/tx
            http://www.springframework.org/schema/tx/spring-tx.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop.xsd">
    
        
        <context:property-placeholder location="classpath:properties/mysql.properties"/>
    
        
        <bean id="master" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="${driver}" />
            <property name="url" value="${master_url}" />
            <property name="username" value="${master_user}" />
            <property name="password" value="${master_password}" />
            <property name="initialSize" value="1" />
            <property name="minIdle" value="1" />
            <property name="maxActive" value="10" />
            <property name="maxWait" value="60000" />
            <property name="timeBetweenEvictionRunsMillis" value="50000" />
            <property name="minEvictableIdleTimeMillis" value="300000" />
            <property name="validationQuery" value="SELECT 1" />
            <property name="testWhileIdle" value="true" />
            <property name="testOnBorrow" value="false" />
            <property name="testOnReturn" value="false" />
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
            <property name="filters" value="stat" />
        </bean>
    
        
        <bean id="slaver" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="${driver}" />
            <property name="url" value="${slaver_url}" />
            <property name="username" value="${slaver_user}" />
            <property name="password" value="${slaver_password}" />
            <property name="initialSize" value="1" />
            <property name="minIdle" value="1" />
            <property name="maxActive" value="10" />
            <property name="maxWait" value="60000" />
            <property name="timeBetweenEvictionRunsMillis" value="50000" />
            <property name="minEvictableIdleTimeMillis" value="300000" />
            <property name="validationQuery" value="SELECT 1" />
            <property name="testWhileIdle" value="true" />
            <property name="testOnBorrow" value="false" />
            <property name="testOnReturn" value="false" />
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
            <property name="filters" value="stat" />
        </bean>
    
        
        <bean id="dataSource" class="com.morning.all.utils.spring.DataSourceRouter" >
            <description>多数据源路由</description>
            <property name="targetDataSources">
                <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                    
                    <entry key="master" value-ref="master" />
                    <entry key="slaver" value-ref="slaver" />
                </map>
            </property>
            
            <property name="defaultTargetDataSource" ref="master" />
        </bean>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            
            <property name="mapperLocations" value="classpath*:mapper/**/*.xml" />
        </bean>
    
        
        <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
            <constructor-arg index="0" ref="sqlSessionFactory" />
        </bean>
    
        
        <bean id="transactionManager"
              class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
    
        
        <tx:annotation-driven order="2"/>
        
        <context:component-scan base-package="com.lot." />
    
        
        <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
        
        
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.lot.dao" />
        </bean>
    </beans>

mysql.properties

# Mysql 配置
    driver=com.mysql.jdbc.Driver
    
    master_url=jdbc:mysql://127.0.0.1:3306/master?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    master_user=root
    master_password=root
    
    slaver_url=jdbc:mysql://127.0.0.1:3306/slaver?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    slaver_user=root
    slaver_password=root

1.  上面的配置基本上已经完成多数据源的基本配置了，下面我们使用SpringAop加注解的技术，在执行Spring method的时候，把对应的数据源的 key 值放到DataSourceRouter.holder中.
2.  定义可以作用在方法上的注解DataSource：

@Target({ElementType.METHOD})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DataSource{
        String value();
    }

1.  使用SpringAop技术，新建DataSourceAspect，定义切点作用于全部Spring方法，通过获取method 上注解DataSource 的配置来确定数据源的选择：

import org.apache.commons.lang3.StringUtils;
    import org.aspectj.lang.JoinPoint;
    import org.aspectj.lang.Signature;
    import org.aspectj.lang.annotation.After;
    import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Before;
    import org.aspectj.lang.annotation.Pointcut;
    import org.aspectj.lang.reflect.MethodSignature;
    import org.springframework.context.annotation.EnableAspectJAutoProxy;
    import org.springframework.core.annotation.Order;
    import org.springframework.stereotype.Component;
    
    import java.lang.reflect.Method;
    
    
    @Aspect
    @Component
    @Order(1)
    @EnableAspectJAutoProxy(proxyTargetClass = true)
    public class DataSourceAspect {
    
        /**
         * 切入点 service 包以及子孙包下的所有类
         */
        @Pointcut("execution(* com.morning.all.service..*.*(..))")
        public void aspect(){
        }
    
        // 前置方法
        @Before("aspect()")
        public void before(JoinPoint point){
            MethodSignature signature = (MethodSignature)point.getSignature();
            Method method = signature.getMethod();
            DataSource dataSource = null;
            // 获取方法注解
            if(method != null && method.isAnnotationPresent(DataSource.class)){
                dataSource =  method.getAnnotation(DataSource.class);
            }
            if (dataSource != null && StringUtils.isNotEmpty(dataSource.value())) {
                DataSourceRouter.setDataSource(dataSource.value());
            }
        }
    
        // 后置方法
        @After("aspect()")
        public void after(JoinPoint point){
            // 清空配置,避免对以后的Service 产生影响
            DataSourceRouter.clearDataSource();
        }
    }

1.  重点 DataSourceAspect 需要加入注解 @Order(1) 时其在 <tx:annotation-driven order=“2”/> 之前执行，保证Spring 先获取到正确的数据源，再开启事务

### 应用： ###

1.走主库，可以配置 @DataSource(DataSourceRouter.MASTER)，一般不去配置，因为默认数据源是Master， 走从库，则需要加入配置 @DataSource(DataSourceRouter.SLAVER)

/**
         * 数据源切换 代码测试
         * @return
         */
        @Override
        @DataSource(DataSourceRouter.SLAVER)
        public List<ClassInfo> getClassListDistSource(){
            return classDao.getClassListDistSource();
        }