首页 > 代码库 > 扩展 spring 的 AbstractRoutingDataSource 实现读写数据库分离
扩展 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 Cookie15 * 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 会自动传参数joinPoint37 * 38 * @param joinPoint39 */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，defaultTargetDataSource16 17 /**18 * 重写这个方法，是为了获取 配置文件中 配置的 targetDataSources 对应的 key，从的拿到对应的连接19 */20 @Override21 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 =http://www.mamicode.com/ 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 =http://www.mamicode.com/ 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)通过在方法上配置切面，来实现主动数据源选择。
主库读写，从库只读，数据库主动同步数据，从而实现了减轻了数据库的压力。
扩展 spring 的 AbstractRoutingDataSource 实现读写数据库分离
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。