扩展 spring 的 AbstractRoutingDataSource 实现读写数据库分离

读写数据库分离,前期没有用spring,实现起来想当复杂,后来

 

 

通过扩展 AbstractRoutingDataSource ,实现方式简单很多 mark 一下。

主从 切面 代码:

 1 package com.lixiaodao.datasource.aspect;
 2 
 3 import java.util.List;
 4 
 5 import org.aspectj.lang.JoinPoint;
 6 import org.slf4j.Logger;
 7 import org.slf4j.LoggerFactory;
 8 
 9 import com.lixiaodao.datasource.select.MasterSlaveSelector;
10 
11 /**
12  * 主从切面 (切面要从 spring 配置)
13  * 
14  * @author Cookie
15  * 
16  */
17 public class MasterSlaveAspect {
18 
19     private static final Logger logger = LoggerFactory.getLogger(MasterSlaveAspect.class);
20 
21     /**
22      * 这个要采用 注入的方式,将方法名的前缀注进来
23      */
24     private List<String> prefixMasters;
25 
26     public List<String> getPrefixMasters() {
27         return prefixMasters;
28     }
29 
30     public void setMasterSlaveSelector(MasterSlaveSelector masterSlaveSelector) {
31         this.masterSlaveSelector = masterSlaveSelector;
32     }
33 
34     private MasterSlaveSelector masterSlaveSelector;
35     /**
36      * 如果在spring 中 配置了 这个切面处理的方法,spring 会自动传参数joinPoint
37      * 
38      * @param joinPoint
39      */
40     public void beforeExcute(JoinPoint joinPoint) {
41         String methodName = joinPoint.getSignature().getName();
42         if(isPrefix(methodName,prefixMasters)){
43             masterSlaveSelector.master();
44             logger.debug("{} use write db",methodName);
45         }else {
46             masterSlaveSelector.slave();
47             logger.debug("{} use read db",methodName);
48         }
49         
50     }
51 
52     private boolean isPrefix(String methodName, List<String> prefixs) {
53         boolean hs = false;
54         for (String prefix : prefixs) {
55             if (methodName.startsWith(prefix)) {
56                 hs = true;
57                 break;
58             }
59         }
60         return hs;
61     }
62     
63     public void setPrefixMasters(List<String> prefixMasters) {
64         this.prefixMasters = prefixMasters;
65     }
66 
67 }

主从切面 配置:

 

<!-- 数据库切面 -->
    <bean id="masterSlaveAspect" class="com.lixiaodao.datasource.aspect.MasterSlaveAspect">
        <property name="prefixMasters">
            <list>
                <value>update</value>
                <value>create</value>
                <value>edit</value>
                <value>delete</value>
                <value>clear</value>
                <value>cancel</value>
                <value>active</value>
                <value>change</value>
                <value>confirm</value>
                <value>mark</value>
                <value>save</value>
                <value>set</value>
            </list>
        </property>
        <property name="masterSlaveSelector" ref="dataSelector"></property>
    </bean>
    <aop:config>  
        <aop:aspect id="c" ref="masterSlaveAspect">  
            <aop:pointcut id="tx" expression="execution(* com.lixiaodao.service..*.*(..))"/>  
            <aop:before pointcut-ref="tx" method="beforeExcute"/>  
        </aop:aspect>  
    </aop:config>  

 

数据源代码:

 1 package com.lixiaodao.datasource;
 2 
 3 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
 4 
 5 import com.lixiaodao.datasource.select.MasterSlaveSelector;
 6 /**
 7  * 主从数据源(采用spring 注入)
 8  * @author Cookie
 9  *
10  */
11 public class MasterSlaveDataSource extends AbstractRoutingDataSource {
12     // spring  配置 注入
13     private MasterSlaveSelector masterSlaveSelector;
14     
15     // 另外需要注入的 是 父类的 属性targetDataSources,defaultTargetDataSource
16 
17     /**
18      * 重写这个方法,是为了获取 配置文件中 配置的 targetDataSources 对应的 key,从的拿到对应的连接
19      */
20     @Override
21     protected Object determineCurrentLookupKey() {
22         return masterSlaveSelector.get();
23     }
24 
25     public void setMasterSlaveSelector(MasterSlaveSelector masterSlaveSelector) {
26         this.masterSlaveSelector = masterSlaveSelector;
27     }
28 
29 }

bean 配置

 1 <!-- 配置写数据源 -->
 2     <bean id="masterDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">      
 3         <property name="driverClassName" value="${jdbc.driverClassName}" />
 4         <property name="url" value="${jdbc.url}" />
 5         <property name="username" value="${jdbc.username}" />
 6         <property name="password" value="${jdbc.password}" />
 7         <property name="initialSize" value="${initialSize}"/>
 8         <property name="maxActive" value="${maxActive}"/>
 9         <property name="maxIdle" value="${maxIdle}"/>
10         <property name="minIdle" value="${minIdle}"/>
11     </bean>    
12     
13     <!-- 配置读数据源 --><!-- 把公共的配置放一起,用于子类的继承 -->
14     <bean id="parentSlaveDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">      
15         <property name="driverClassName" value="${jdbc.driverClassName}" />
16         <property name="initialSize" value="${slave.initialSize}"/>
17         <property name="maxActive" value="${slave.maxActive}"/>
18         <property name="maxIdle" value="${slave.maxIdle}"/>
19         <property name="minIdle" value="${slave.minIdle}"/>
20     </bean>
21     <bean id="slaveDataSource1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" parent="parentSlaveDataSource">      
22         <property name="url" value="${slave1.jdbc.url}" />
23         <property name="username" value="${slave1.jdbc.username}" />
24         <property name="password" value="${slave1.jdbc.password}" />
25     </bean>
26     <bean id="slaveDataSource2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" parent="parentSlaveDataSource">      
27         <property name="url" value="${slave2.jdbc.url}" />
28         <property name="username" value="${slave2.jdbc.username}" />
29         <property name="password" value="${slave2.jdbc.password}" />
30     </bean>
31     <bean id="slaveDataSource3" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" parent="parentSlaveDataSource">      
32         <property name="url" value="${slave3.jdbc.url}" />
33         <property name="username" value="${slave3.jdbc.username}" />
34         <property name="password" value="${slave3.jdbc.password}" />
35     </bean>
36     <bean id="slaveDataSource4" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" parent="parentSlaveDataSource">      
37         <property name="url" value="${slave4.jdbc.url}" />
38         <property name="username" value="${slave4.jdbc.username}" />
39         <property name="password" value="${slave4.jdbc.password}" />
40     </bean>
41     <bean id="slaveDataSource5" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" parent="parentSlaveDataSource">      
42         <property name="url" value="${slave5.jdbc.url}" />
43         <property name="username" value="${slave5.jdbc.username}" />
44         <property name="password" value="${slave5.jdbc.password}" />
45     </bean>
46     <bean id="slaveDataSource6" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" parent="parentSlaveDataSource">      
47         <property name="url" value="${slave6.jdbc.url}" />
48         <property name="username" value="${slave6.jdbc.username}" />
49         <property name="password" value="${slave6.jdbc.password}" />
50     </bean>
51     
52     <bean id ="dataSource" class="com.lixiaodao.datasource.MasterSlaveDataSource">
53         <property name="targetDataSources">
54             <map>
55              <entry key="master" value-ref="masterDataSource"/>
56              <entry key="slave1" value-ref="slaveDataSource1"/>    
57              <entry key="slave2" value-ref="slaveDataSource2"/>    
58              <entry key="slave3" value-ref="slaveDataSource3"/>    
59              <entry key="slave4" value-ref="slaveDataSource4"/>    
60              <entry key="slave5" value-ref="slaveDataSource5"/>    
61              <entry key="slave6" value-ref="slaveDataSource6"/> 
62             </map>
63         </property>
64         <property name="defaultTargetDataSource" ref="masterDataSource"></property>
65         <property name="masterSlaveSelector" ref="dataSelector"></property>
66     </bean>

 

主从选择器代码:

  1 package com.lixiaodao.datasource.select.impl;
  2 
  3 import java.sql.Connection;
  4 import java.sql.SQLException;
  5 import java.util.ArrayList;
  6 import java.util.Collections;
  7 import java.util.List;
  8 import java.util.Map;
  9 import java.util.concurrent.atomic.AtomicInteger;
 10 
 11 import javax.sql.DataSource;
 12 
 13 import org.slf4j.Logger;
 14 import org.slf4j.LoggerFactory;
 15 
 16 import com.lixiaodao.datasource.select.MasterSlaveSelector;
 17 /**
 18  * 主从选择器
 19  * @author Cookie
 20  *
 21  */
 22 public class MasterSlaveSelectorByPoll implements MasterSlaveSelector {
 23     
 24     private static final Logger logger = LoggerFactory.getLogger(MasterSlaveSelectorByPoll.class);
 25     
 26     public static final ThreadLocal<String> holder = new ThreadLocal<String>();
 27     
 28     /**
 29      * 对应的 targetDataSources 的key
 30      */
 31     
 32     private List<String> masters;
 33     
 34     private List<String> slaves;
 35     
 36     /**
 37      * 解决并发问题
 38      */
 39     private AtomicInteger selectedMasterIndex = new AtomicInteger(0);
 40     private AtomicInteger selectedSlavesIndex = new AtomicInteger(0);
 41     
 42     private List<String> badMasters = Collections.synchronizedList(new ArrayList<String>());
 43     private List<String> badSlaves = Collections.synchronizedList(new ArrayList<String>());
 44     
 45     /**
 46      * 将string 对应的数据源 注入 PS 如果 MasterSlaveDataSource注入的 datasource 的map 的key 都是 datasource 类型的话
 47      * 这里的map 就不用注入了,直接用key 用key 就可以拿到 datasource 对象
 48      */
 49     private Map<String, DataSource> datasourceMap;
 50     
 51 
 52     private String defaultDataSource;
 53     
 54     @Override
 55     public String get() {
 56         String dataSource = holder.get();
 57         return dataSource == null ? defaultDataSource : dataSource;
 58     }
 59 
 60     @Override
 61     public void master() {
 62          logger.debug("change master!");
 63          if(!masters.contains(holder.get())){
 64              holder.set(getNext(selectedMasterIndex, masters));
 65          }
 66     }
 67 
 68     private String getNext(AtomicInteger selectedIndex,
 69             List<String> sources) {
 70         if(sources.isEmpty()){
 71             throw new RuntimeException("No datasource available");
 72         }
 73         selectedIndex.weakCompareAndSet(sources.size(), 0);
 74         return sources.get((selectedIndex.getAndIncrement() % (sources.size())));
 75     }
 76 
 77     @Override
 78     public void slave() {
 79         logger.debug("change slave");
 80         if(!slaves.contains(holder.get())){
 81             holder.set(getNext(selectedSlavesIndex, slaves));
 82         }
 83     }
 84 
 85     @Override
 86     public void monitor() {
 87         checkRestore(badMasters,masters);
 88         checkRestore(badSlaves,slaves);
 89         
 90         checkBadDataSource(masters, badMasters);
 91         checkBadDataSource(slaves, badSlaves);
 92     }
 93 
 94     private void checkBadDataSource(List<String> normalDataSources,
 95             List<String> badDataSources) {
 96         for (String ds : normalDataSources) {
 97             try {
 98                 DataSource data = datasourceMap.get(ds);
 99                 Connection conn = data.getConnection();
100                 conn.close();
101             } catch (SQLException e) {
102                 badDataSources.add(ds);
103                 // TODO 各种 通知 ,,微信或者 邮件 等等
104                 logger.error("Check new database error! database:" + ds, e.toString());
105             }
106         }
107         
108     }
109 
110     private void checkRestore(List<String> badDataSource, List<String> normalDataSource) {
111         for (String ds : badDataSource) {
112             try {
113                 DataSource data = datasourceMap.get(ds);
114                 Connection conn = data.getConnection();
115                 conn.close();
116                 normalDataSource.add(ds);
117             } catch (SQLException e) {
118                 // TODO 各种 通知 ,,微信或者 邮件 等等
119                 logger.error("Continue database error! database:" + ds, e.toString());
120             }
121         }
122     }
123 
124     public void setMasters(List<String> masters) {
125         this.masters = masters;
126     }
127 
128     public void setSlaves(List<String> slaves) {
129         this.slaves = slaves;
130     }
131 
132     public void setDefaultDataSource(String defaultDataSource) {
133         this.defaultDataSource = defaultDataSource;
134     }
135     
136     public void setDatasourceMap(Map<String, DataSource> datasourceMap) {
137         this.datasourceMap = datasourceMap;
138     }
139 }

 

 

主从 选择器 bean 配置:

 

<bean id = "dataSelector" class="com.lixiaodao.datasource.select.impl.MasterSlaveSelectorByPoll">
        <property name="masters">
            <list>
                <value>master</value>  
            </list>
        </property>
        <property name="slaves">
            <list>
                <value>slave1</value>  
                <value>slave2</value>
                <value>slave3</value>
                <value>slave4</value>
                <value>slave5</value>
                <value>slave6</value>
            </list>
        </property>
        <property name="defaultDataSource" value="master"></property>
        <property name="datasourceMap">
            <map>
             <entry key="master" value-ref="masterDataSource"/>
             <entry key="slave1" value-ref="slaveDataSource1"/>    
             <entry key="slave2" value-ref="slaveDataSource2"/>    
             <entry key="slave3" value-ref="slaveDataSource3"/>    
             <entry key="slave4" value-ref="slaveDataSource4"/>    
             <entry key="slave5" value-ref="slaveDataSource5"/>    
             <entry key="slave6" value-ref="slaveDataSource6"/> 
            </map>
        </property>
    </bean>

要点:1)通过 threadlocal,来从数据源池中去hash 去取连接(通过重写 determineCurrentLookupKey方法实现)

         2)通过在方法上配置切面,来实现主动数据源选择。

       主库读写,从库只读,数据库主动同步数据,从而实现了减轻了数据库的压力。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。