读写数据库分离，前期没有用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)通过在方法上配置切面，来实现主动数据源选择。
主库读写，从库只读，数据库主动同步数据，从而实现了减轻了数据库的压力。