背景
相信大家在開發工作中肯定遇到過一個應用可能需要連接兩個或以上的數據源進行數據操作，又或者數據庫單表數據量過大，導致查詢很慢時，一般都會考慮分庫分表，這時候也會涉及到多庫數據源操作。下面講解利用spring+mybatis 實現多數據源切換，話不多說直接上代碼。
jdbc和log4j的配置
log4j.properties
#定義輸出格式ConversionPattern=%d %-5p [%t] %c - %m%nlog4j.rootLogger=DEBUG,Consolelog4j.logger.com.cnblogs.lzrabbit=DEBUGlog4j.logger.org.springframework=ERRORlog4j.logger.org.mybatis=ERRORlog4j.logger.org.apache.ibatis=ERRORlog4j.logger.org.quartz=ERRORlog4j.logger.org.apache.axis2=ERRORlog4j.logger.org.apache.axiom=ERRORlog4j.logger.org.apache=ERRORlog4j.logger.httpclient=ERROR#log4j.additivity.org.springframework=false#Console log4j.appender.Console=org.apache.log4j.ConsoleAppenderlog4j.appender.Console.Threshold=DEBUG log4j.appender.Console.Target=System.out log4j.appender.Console.layout=org.apache.log4j.PatternLayout log4j.appender.Console.layout.ConversionPattern=${ConversionPattern}#log4j.appender.Console.encoding=UTF-8#org.apache.log4j.DailyRollingFileAppenderlog4j.appender.DailyFile=org.apache.log4j.DailyRollingFileAppenderlog4j.appender.DailyFile.DatePattern='.'yyyy-MM-dd'.log'log4j.appender.DailyFile.File=${myApp.root}/logs/daily.loglog4j.appender.DailyFile.Append=truelog4j.appender.DailyFile.Threshold=DEBUGlog4j.appender.DailyFile.layout=org.apache.log4j.PatternLayoutlog4j.appender.DailyFile.layout.ConversionPattern=${ConversionPattern}log4j.appender.DailyFile.encoding=UTF-8# %c 輸出日誌信息所屬的類的全名 # %d 輸出日誌時間點的日期或時間，默認格式為ISO8601，也可以在其後指定格式，比如：%d{yyy-MM-dd HH:mm:ss}，輸出類似：2002-10-18- 22：10：28# %f 輸出日誌信息所屬的類的類名 # %l 輸出日誌事件的發生位置，即輸出日誌信息的語句處於它所在的類的第幾行 # %m 輸出代碼中指定的信息，如log(message)中的message # %n 輸出一個回車換行符，Windows平臺為“rn”，Unix平臺為“n” # %p 輸出優先級，即DEBUG，INFO，WARN，ERROR，FATAL。如果是調用debug()輸出的，則為DEBUG，依此類推 # %r 輸出自應用啟動到輸出該日誌信息所耗費的毫秒數 # %t 輸出產生該日誌事件的線程名12345678910111213141516171819202122232425262728293031323334353637383940414243jdbc.properties
#============================================================================# MySQL#============================================================================jdbc.mysql.driver=com.mysql.jdbc.Driverjdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=truejdbc.mysql.username=rootjdbc.mysql.password=root#============================================================================# MS SQL Server#============================================================================#jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver#jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;database=test;#jdbc.sqlserver.username=sa#jdbc.sqlserver.password=sa#============================================================================# MS SQL Server (JTDS)#============================================================================jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driverjdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/testjdbc.sqlserver.username=sajdbc.sqlserver.password=sa#============================================================================# 通用配置#============================================================================jdbc.initialSize=5jdbc.minIdle=5jdbc.maxIdle=20jdbc.maxActive=100jdbc.maxWait=100000jdbc.defaultAutoCommit=falsejdbc.removeAbandoned=truejdbc.removeAbandonedTimeout=600jdbc.testWhileIdle=truejdbc.timeBetweenEvictionRunsMillis=60000jdbc.numTestsPerEvictionRun=20jdbc.minEvictableIdleTimeMillis=300000123456789101112131415161718192021222324252627282930313233343536373839404142
多數據源時Spring配置文件
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273
Java代碼編寫
MultipleDataSource 多數據源配置類
package com.xxx.gfw.pubfound;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/*** * * Project Name:gfw-public-foundation-impl*自定義多數據源配置類 ** @ClassName: MultipleDataSource* @date 2018年5月18日 下午4:47:20** @author youqiang.xiong* @version 1.0* @since */public class MultipleDataSource extends AbstractRoutingDataSource { private static final ThreadLocal
dataSourceKey = new InheritableThreadLocal (); public static void setDataSourceKey(String dataSource) { dataSourceKey.set(dataSource); } @Override protected Object determineCurrentLookupKey() { return dataSourceKey.get(); }}1234567891011121314151617181920212223242526272829
自定義註解DataSourceType
package com.xxx.pubfound.aop.anntion;import java.lang.annotation.*;/*** * * Project Name:gfw-base-common-service*自定義數據源類型註解，標誌當前的dao接口使用的數據源類型** @ClassName: DataSourceType* @date 2018年5月18日 下午5:09:49** @author youqiang.xiong* @version 1.0* @since */@Target({ ElementType.TYPE})@Retention(RetentionPolicy.RUNTIME)@Documented public @interface DataSourceType { String value() default "dataSource";}1234567891011121314151617181920212223
兩個service層分別加上DataSourceType 註解
ProvinceServiceImpl.java
package com.xxx.pubfound.service;import java.util.ArrayList;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.xxx.pubfound.aop.anntion.DataSourceType;import com.xxx.pubfound.dao.SysProvinceDao;import com.xxx.pubfound.dto.SysProvinceDTO;import com.xxx.pubfound.struct.SysProvinceListStruct;import com.xxx.pubfound.struct.SysProvinceStruct;import com.xxx.rpc.api.AbstractRpcService;/*** * * Project Name:gfw-public-foundation-impl*省份服務層實現類 ** @ClassName: ProvinceServiceImpl* @date 2018年5月18日 下午6:29:35** @author youqiang.xiong* @version 1.0* @since */@DataSourceType(value="gfwDataSource")@Servicepublic class ProvinceServiceImpl extends AbstractRpcService implements ProvinceService { @Autowired private SysProvinceDao sysProvinceDao; @Override public SysProvinceListStruct getProvinceList() { List
list = sysProvinceDao.getProvinceList(); return beanToStruct(list); } /*** * * Project Name: gfw-public-foundation-impl * 將dto對象封裝struct對象 * * @author youqiang.xiong * @date 2018年5月28日 下午3:31:42 * @version v1.0 * @since * @param provinceList * 省份列表dto * @return 省份列表struct */ private SysProvinceListStruct beanToStruct(List
provinceList){ if(provinceList == null || provinceList.size() == 0){ return null; } List resultList = new ArrayList (); for(SysProvinceDTO dto:provinceList){ SysProvinceStruct struct = new SysProvinceStruct(); struct.provinceId = dto.getProvinceId(); struct.provinceName = dto.getProvinceName(); resultList.add(struct); } SysProvinceListStruct rsStruct = new SysProvinceListStruct(resultList); return rsStruct; }}123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
DefaultExceptionCollector.java
package com.xxx.pubfound.service;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.alibaba.druid.pool.DruidDataSource;import com.github.pagehelper.PageInfo;import com.xxx.pubfound.aop.anntion.DataSourceType;import com.xxx.pubfound.dao.PfExceptionLogDao;import com.xxx.pubfound.dto.NotifyLevelEnum;import com.xxx.pubfound.entity.PfExceptionLog;import com.xxx.pubfound.struct.PagedPfExceptionLogStruct;import com.xxx.pubfound.struct.PfExceptionLogStruct;import com.xxx.rpc.api.AbstractRpcService;/*** * * Project Name:gfw-public-foundation-impl*異常採集** @ClassName: DefaultExceptionCollector* @date 2018年5月28日 下午8:01:43** @author youqiang.xiong* @version 1.0* @since */@DataSourceType(value="dataSource")@Servicepublic class DefaultExceptionCollector extends AbstractRpcService implements ExceptionCollector { @Autowired PfExceptionLogDao pfExceptionLogDao; @Autowired DruidDataSource dataSource; @Override public void collect(long reqTime, String exceptionClass, String stackTrace, int resultCode, String environment, String nodeNameEn) { //FIXME 採集異常！！ //TODO luoshan System.out.println(dataSource.getConnectProperties()); PfExceptionLog exceptionLog = new PfExceptionLog(); exceptionLog.setEnvironment(environment); exceptionLog.setExceptionClass(exceptionClass); exceptionLog.setExceptionTime(new Date(reqTime)); exceptionLog.setResultCode(resultCode); exceptionLog.setServiceName(nodeNameEn); exceptionLog.setStackTrace(stackTrace); pfExceptionLogDao.insert(exceptionLog); System.out.println("Exception ex:" + exceptionClass); System.out.println("Exception ex:" + resultCode); } @Override public void collectNotify(long reqTime, String exceptionClass, String stackTrace, int resultCode, String environment, String nodeNameEn, NotifyLevelEnum level) { try{ this.collect(reqTime, exceptionClass, stackTrace, resultCode, environment, nodeNameEn); }catch(Exception ex){ ex.printStackTrace(); } //FIXME 根據不停的level 分別執行策略 start if(level.compareTo(NotifyLevelEnum.WARN) == 0){ //發郵件！ }else if(level.compareTo(NotifyLevelEnum.ERROR) == 0){ //發郵件，1/3概率 發短信！ }else if(level.compareTo(NotifyLevelEnum.FATAL) == 0){ //發郵件，並且要發短信！ } //FIXME 根據不停的level 分別執行策略 end } /** * 分頁獲取異常日誌列表 * * @param pageNo 頁碼 * @param size 每頁數據量 * @param serviceName * @param beginTime * @param endTime * * @return */// @Override// public PagedPfExceptionLogStruct queryExceptionLogList(int pageNo, int size, String serviceName, Long beginTime, Long endTime) {// PageHelper.startPage(pageNo , size);// Date beginTimeDate = beginTime == null || beginTime <= 0 ? null : new Date(beginTime);// Date endTimeDate = endTime == null || beginTime <= 0 ? null : new Date(endTime);// List
list = pfExceptionLogDao.selectPfExceptionLogList(serviceName , beginTimeDate , endTimeDate);//// List structList = new ArrayList<>();// if (list != null || !list.isEmpty()) {// for (PfExceptionLog pfExceptionLog: list) {// structList.add(entityToStruct(pfExceptionLog));// }// }// PageInfo page = new PageInfo<>(structList);// PagedPfExceptionLogStruct result = new PagedPfExceptionLogStruct(page.getPageNum(), page.getTotal(), page.getPages(), page.getList());// return result;// } @Override public PagedPfExceptionLogStruct queryExceptionLogList(int pageNo, int size, String serviceName, Long beginTime, Long endTime) { Date beginTimeDate = beginTime == null || beginTime <= 0 ? null : new Date(beginTime); Date endTimeDate = endTime == null || beginTime <= 0 ? null : new Date(endTime); int offset = pageNo < 1 ? 0 : (pageNo - 1) * size; List list = pfExceptionLogDao.selectPfExceptionLogList(offset , size , serviceName , beginTimeDate , endTimeDate); List structList = new ArrayList<>(); if (list != null || !list.isEmpty()) { for (PfExceptionLog pfExceptionLog: list) { structList.add(entityToStruct(pfExceptionLog)); } } int total = pfExceptionLogDao.selectPfExceptionLogListCount(serviceName , beginTimeDate , endTimeDate); int pages = total % size == 0 ? total/size : total/size + 1; PageInfo page = new PageInfo<>(structList); PagedPfExceptionLogStruct result = new PagedPfExceptionLogStruct(pageNo, total, pages, page.getList()); return result; } private PfExceptionLogStruct entityToStruct(PfExceptionLog pfExceptionLog) { if (pfExceptionLog == null) { return null; } PfExceptionLogStruct pfExceptionLogStruct = new PfExceptionLogStruct(); pfExceptionLogStruct.id = pfExceptionLog.getId(); pfExceptionLogStruct.environment = pfExceptionLog.getEnvironment(); pfExceptionLogStruct.exceptionClass = pfExceptionLog.getExceptionClass(); pfExceptionLogStruct.exceptionTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(pfExceptionLog.getExceptionTime()); pfExceptionLogStruct.resultCode = pfExceptionLog.getResultCode(); pfExceptionLogStruct.serviceName = pfExceptionLog.getServiceName(); pfExceptionLogStruct.stackTrace = pfExceptionLog.getStackTrace(); return pfExceptionLogStruct; } /** * 根據異常日誌id 獲取異常日誌詳情 * * @param id 異常日誌id * * @return */ @Override public PfExceptionLogStruct queryExceptionLogById(int id) { PfExceptionLog pfExceptionLog = pfExceptionLogDao.selectByPrimaryKey(id); return entityToStruct(pfExceptionLog); }}123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159
MultipleDataSourceAop.java多數據源自動切換切面類
package com.xxx.pubfound.aop;import org.aspectj.lang.JoinPoint;import org.aspectj.lang.annotation.Aspect;import org.aspectj.lang.annotation.Before;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.stereotype.Component;import com.shangde.gfw.util.proxy.ProxyUtil;import com.xxx.pubfound.MultipleDataSource;import com.xxx.pubfound.aop.anntion.DataSourceType;/*** * * Project Name:gfw-public-foundation-impl*多數據源自動切換通知類（攔截com.xxx.pubfound.dao中所有的類中的方法）
* 首先判斷當前類是否被該DataSourceType註解進行註釋，如果沒有采用默認的uam數據源配置；
* 如果有，則讀取註解中的value值，將數據源切到value指定的數據源* @ClassName: MultipleDataSourceAspectAdvice* @date 2018年5月18日 下午5:13:51** @author youqiang.xiong* @version 1.0* @since */@Component@Aspectpublic class MultipleDataSourceAop { private final Logger logger = LoggerFactory.getLogger(getClass()); /*** * * Project Name gfw-public-foundation-impl ** 攔截 pubfound.service中所有的方法，根據情況進行數據源切換 * * @author youqiang.xiong * @date 2018年5月18日 下午5:49:48 * @version v1.0 * @since * @param pjp * 連接點 * @throws Throwable * 拋出異常 */ @Before("execution(* com.xxx.pubfound.service.*.*(..))") public void changeDataSource(JoinPoint joinPoint) throws Throwable { // 攔截的實體類，就是當前正在執行的service Object target = ProxyUtil.getTarget(joinPoint.getTarget()); if(target.getClass().isAnnotationPresent(DataSourceType.class)){ DataSourceType dataSourceType = target.getClass().getAnnotation(DataSourceType.class); String type = dataSourceType.value(); logger.info("數據源切換至--->{}",type); MultipleDataSource.setDataSourceKey(type); }else{ logger.info("此{}不涉及數據源操作.",target.getClass()); } }}1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465
以上就是通過spring aop 切面實現多數據源自動切換的代碼和配置，請根據各各自實際情況稍作修改.
閱讀更多 程序員小新人學習 的文章
