Spring 動態資料源和AOP實現資料庫讀寫分離__資料庫

互連網架構演化中 資料庫最佳化是不可或缺的一環，資料庫層面最佳化分為兩個階段：讀寫分離、分庫分表。

今天要說的是 資料庫讀寫分離技術，其原理就是一個Master資料庫，多個Slave資料庫。Master庫負責資料更新和即時資料查詢，Slave庫當然負責非即時資料查詢。因為在實際的應用中，資料庫都是讀多寫少（讀取資料的頻率高，更新資料的頻率相對較少），而讀取資料通常耗時比較長，佔用資料庫伺服器的CPU較多，從而影響使用者體驗。

我們通常的做法就是把查詢從主庫中抽取出來，分發到多個從庫上，減輕主庫的壓力。
採用讀寫分離技術的目標：有效減輕Master庫的壓力，又可以把使用者查詢資料的請求分發到不同的Slave庫，從而保證系統的健壯性。 實現原理

在DAO實作類別的方法加上@RoutingDataSource註解，然後通過Spring AOP技術在運行時攔截DAO的方法，擷取方法上的@RoutingDataSource註解值動態切換資料來源。 代碼實現 1、Spring動態資料源

1、spring-dao.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:tx="http://www.springframework.org/schema/tx"       xmlns:context="http://www.springframework.org/schema/context"       xmlns:util="http://www.springframework.org/schema/util"       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/util       http://www.springframework.org/schema/util/spring-util.xsd       http://www.springframework.org/schema/context       http://www.springframework.org/schema/context/spring-context.xsd"       default-lazy-init="false">    <bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource"           destroy-method="close"  abstract="true" init-method="init" >        <!-- 初始化串連大小 -->        <property name="initialSize" value="2" />        <!-- 串連池最大使用串連數量 -->        <property name="maxActive" value="10" />        <!-- 串連池最小空閑 -->        <property name="minIdle" value="5" />        <!-- 擷取串連最大等待時間 -->        <property name="maxWait" value="30000" />        <!-- <property name="poolPreparedStatements" value="true" /> -->        <!-- <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->        <property name="validationQuery" value="SELECT 1" />        <property name="testOnBorrow" value="false" />        <property name="testOnReturn" value="false" />        <property name="testWhileIdle" value="true" />        <!-- 配置間隔多久才進行一次檢測，檢測需要關閉的空閑串連，單位是毫秒 -->        <property name="timeBetweenEvictionRunsMillis" value="60000" />        <!-- 配置一個串連在池中最小生存的時間，單位是毫秒 -->        <property name="minEvictableIdleTimeMillis" value="25200000" />        <!-- 開啟removeAbandoned功能 -->        <property name="removeAbandoned" value="true" />        <!-- 1800秒，也就是30分鐘 -->        <property name="removeAbandonedTimeout" value="1800" />        <!-- 關閉abanded串連時輸出錯誤記錄檔 -->        <property name="logAbandoned" value="true" />        <!-- 監控資料庫 -->        <!-- <property name="filters" value="stat" /> -->        <property name="filters" value="mergeStat" />    </bean>    <!-- 配置資料來源-->    <bean id="masterDataSource" parent="parentDataSource">        <property name="url" value="#{jdbc['master.jdbc.url']}" />        <property name="username" value="#{jdbc['master.jdbc.username']}" />        <property name="password" value="#{jdbc['master.jdbc.password']}" />        <property name="driverClassName" value="#{jdbc['master.jdbc.driver']}" />        <property name="maxActive" value="15" />    </bean>    <bean id="slaveDataSource" parent="parentDataSource">        <property name="url" value="#{jdbc['slave1.jdbc.url']}" />        <property name="username" value="#{jdbc['slave1.jdbc.username']}" />        <property name="password" value="#{jdbc['slave1.jdbc.password']}" />        <property name="driverClassName" value="#{jdbc['slave1.jdbc.driver']}" />    </bean>    <!--動態資料源-->    <bean id="dataSource" class="com.bytebeats.spring4.sample.ds.DynamicRoutingDataSource">        <property name="targetDataSources">            <map key-type="com.bytebeats.spring4.sample.ds.RoutingStrategy">                <entry key="WRITE" value-ref="masterDataSource"/>                <entry key="READ" value-ref="slaveDataSource"/>            </map>        </property>        <!-- 預設目標資料來源為主庫資料來源 -->        <property name="defaultTargetDataSource" ref="masterDataSource"/>    </bean>    <!--Spring JdbcTemplate-->    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dataSource"/>    </bean>    <!-- 註解方式配置事物 -->    <tx:annotation-driven transaction-manager="transactionManager" />    <!-- 配置交易管理員 -->    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">        <property name="dataSource" ref="masterDataSource" />    </bean></beans>

2、動態資料源實現
DynamicRoutingDataSource.java

package com.bytebeats.spring4.sample.ds;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/** * 動態資料源 * * @author Ricky Fung * @create 2016-12-30 11:12 */public class DynamicRoutingDataSource extends AbstractRoutingDataSource {    @Override    protected Object determineCurrentLookupKey() {        return DynamicRoutingContextHolder.getRouteStrategy();    }}

DynamicRoutingContextHolder.java

package com.bytebeats.spring4.sample.ds;import org.springframework.util.Assert;/** * ${DESCRIPTION} * * @author Ricky Fung * @create 2016-12-30 11:14 */public class DynamicRoutingContextHolder {    private static final ThreadLocal<RoutingStrategy> contextHolder =            new ThreadLocal<>();    public static void setRouteStrategy(RoutingStrategy customerType) {        Assert.notNull(customerType, "customerType cannot be null");        contextHolder.set(customerType);    }    public static RoutingStrategy getRouteStrategy() {        return (RoutingStrategy) contextHolder.get();    }    public static void clearRouteStrategy() {        contextHolder.remove();    }}

RoutingStrategy.java

package com.bytebeats.spring4.sample.ds;/** * 讀寫策略 * * @author Ricky Fung * @create 2016-12-30 11:14 */public enum RoutingStrategy {    WRITE, READ;}

資料來源註解

package com.bytebeats.spring4.sample.annotation;import com.bytebeats.spring4.sample.ds.RoutingStrategy;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * ${DESCRIPTION} * * @author Ricky Fung * @date 2016-12-30 15:26 */@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface RoutingDataSource {    RoutingStrategy value();}

Spring AOP

1、spring-aop.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:context="http://www.springframework.org/schema/context"       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-4.0.xsd        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">    <!-- 配置AOP -->    <aop:aspectj-autoproxy />    <bean id="readWriteSeparateAspect" class="com.bytebeats.spring4.sample.aop.DBReadWriteSeparateAspect" />    <aop:config>        <aop:aspect ref="readWriteSeparateAspect">            <aop:pointcut id="rw" expression="execution(* com.bytebeats.spring4.sample.dao.*.*(..))" />            <aop:before pointcut-ref="rw" method="before" />        </aop:aspect>    </aop:config></beans>

2、切面類

package com.bytebeats.spring4.sample.aop;import com.bytebeats.spring4.sample.annotation.RoutingDataSource;import com.bytebeats.spring4.sample.ds.DynamicRoutingContextHolder;import org.aspectj.lang.JoinPoint;import org.aspectj.lang.reflect.MethodSignature;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.lang.reflect.Method;/** * ${DESCRIPTION} * * @author Ricky Fung * @date 2016-12-30 16:59 */public class DBReadWriteSeparateAspect {    private final Logger logger = LoggerFactory.getLogger(getClass());    public void before(JoinPoint point) {        Object target = point.getTarget();        String methodName = point.getSignature().getName();        Class<?> clazz = target.getClass();        logger.info("before class:{} method:{} execute", clazz.getName(), methodName);        Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();        try {            Method method = clazz.getMethod(methodName, parameterTypes);            if (method != null && method.isAnnotationPresent(RoutingDataSource.class)) {                RoutingDataSource data = method.getAnnotation(RoutingDataSource.class);                DynamicRoutingContextHolder.setRouteStrategy(data.value());                logger.info("class:{} method:{} 切換資料來源:{} 成功", clazz.getName(), methodName, data.value());            }        } catch (Exception e) {            logger.error("資料來源切換切面異常", e);        }    }}

如何使用

假設有一個訂單庫，現在要實現訂單表的插入和查詢，IOrderDao介面定義如下：

package com.bytebeats.spring4.sample.dao;import com.bytebeats.spring4.sample.domain.Order;import java.util.List;/** * ${DESCRIPTION} * * @author Ricky Fung * @create 2016-12-30 11:15 */public interface IOrderDao {    long insert(Order order);    List<Order> queryOrders();}

我們期望insert路由到主庫上，queryOrders路由到從庫上，那麼我們只需要在這兩個方法上增加@RoutingDataSource註解即可，OrderDaoImpl 代碼如下：

package com.bytebeats.spring4.sample.dao.impl;import com.bytebeats.spring4.sample.annotation.RoutingDataSource;import com.bytebeats.spring4.sample.dao.IOrderDao;import com.bytebeats.spring4.sample.domain.Order;import com.bytebeats.spring4.sample.ds.RoutingStrategy;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.stereotype.Repository;import javax.annotation.Resource;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;/** * ${DESCRIPTION} * * @author Ricky Fung * @create 2016-12-30 11:16 */@Repository("orderDao")public class OrderDaoImpl implements IOrderDao {    @Resource(name = "jdbcTemplate")    private JdbcTemplate jdbcTemplate;    @RoutingDataSource(RoutingStrategy.WRITE)    @Override    public long insert(Order order) {        String sql = "INSERT INTO tb_order(customer_name,total_price,amount,address) VALUES (?,?,?,?)";        return jdbcTemplate.update(sql, order.getCustomerName(),                order.getTotalPrice(), order.getAmount(), order.getAddress());    }    @RoutingDataSource(RoutingStrategy.READ</