Record description : Project usage Spring+SpringMVC+Mybatis frame , The project has been in use before mysql database , After the project docking needs , Configure multiple data sources to increase sqlserver database .
Sort out the relevant documents :
1,pom.xml file ( Using maven management tool )
2,DataSource Interface annotation file
3,DataSourceAspect Class file
4,DynamicDataSourceHolder Class file
5,MultipleDataSource Class file
6,jdbc.properties configuration file
7,spring-mybatis.xml configuration file
None of the above documents is required !!

pom.xml File increase dependency jar
<dependency> <groupId>net.sourceforge.jtds</groupId> <artifactId>jtds</
artifactId> <version>1.2.4</version> </dependency> <dependency> <groupId>
com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>2.0
</version> </dependency>
jar Package download :https://download.csdn.net/download/qq_35393472/10671922
<https://download.csdn.net/download/qq_35393472/10671922>

DataSource Interface annotation file
import java.lang.annotation.Documented; import
java.lang.annotation.ElementType;import java.lang.annotation.Retention; import
java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target; /** *
This is used to define DataSource annotation , Specify the data source to be accessed by annotation * @author:lujingyu * @data
:2018 year 9 month 17 Afternoon 2:10:54 */ @Documented @Target({ElementType.TYPE,
ElementType.METHOD})@Retention(RetentionPolicy.RUNTIME) public @interface
DataSource { String value(); }
DataSourceAspect Class file
import java.lang.reflect.Method; import org.aspectj.lang.JoinPoint; import
org.aspectj.lang.reflect.MethodSignature;public class DataSourceAspect { /** *
Method of intercepting target , Obtained by @DataSource The specified data source ID , Set to thread store to switch data sources * * @param point * @throws
Exception */ public void intercept(JoinPoint point) throws Exception { Class<?>
target = point.getTarget().getClass(); MethodSignature signature =
(MethodSignature) point.getSignature();// By default, annotations of the target type are used , If not, use the annotation that implements the interface for
(Class<?> clazz : target.getInterfaces()) { resolveDataSource(clazz,
signature.getMethod()); } resolveDataSource(target, signature.getMethod()); }
/** * Extract the data source identification in the method annotation and type annotation of the target object * * @param clazz * @param method */ private void
resolveDataSource(Class<?> clazz, Method method) { try { Class<?>[] types =
method.getParameterTypes();// Type annotation is used by default if
(clazz.isAnnotationPresent(DataSource.class)) { DataSource source =
clazz.getAnnotation(DataSource.class);
DynamicDataSourceHolder.setDataSource(source.value()); }// Method annotations can override type annotations Method
m = clazz.getMethod(method.getName(), types);if (m != null &&
m.isAnnotationPresent(DataSource.class)) { DataSource source =
m.getAnnotation(DataSource.class);
DynamicDataSourceHolder.setDataSource(source.value()); } }catch (Exception e) {
System.out.println(clazz +":" + e.getMessage()); } } }
DynamicDataSourceHolder Class file
public class DynamicDataSourceHolder { /** * be careful : The data source ID is stored in the thread variable , Avoid mutual interference when multithreading operates data sources
*/ private static final ThreadLocal<String> THREAD_DATA_SOURCE = new
ThreadLocal<String>();public static String getDataSource() { return
THREAD_DATA_SOURCE.get(); }public static void setDataSource(String dataSource)
{ THREAD_DATA_SOURCE.set(dataSource); }public static void clearDataSource() {
THREAD_DATA_SOURCE.remove(); } }
MultipleDataSource Class file
Note that this document is for spring-mybatis.xml Required for file calls
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/** * Use of multiple data sources * @author:lujingyu * @data:2018 year 9 month 15 Afternoon 2:33:40 */ public class
MultipleDataSource extends AbstractRoutingDataSource { @Override protected
ObjectdetermineCurrentLookupKey() { // Get the data source ID from a custom location return
DynamicDataSourceHolder.getDataSource(); } }
jdbc.properties configuration file
Document description : It mainly depends on the configuration of the two data sources
It should be simple , General configuration according to their own needs to use , You can also use them all , But if you want to change it , that spring-mybatis.xml This should be modified when configuring
#mysql data source When using this database to query Please at ServiceImpl Add annotation to class :@DataSource("dataSource") jdbc
.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc\:mysql\://192.168.0.167\:
3306/online_db jdbc.username=root jdbc.password=123456 #sqlserver data source When using this database to query
Please at ServiceImpl Add annotation to class :@DataSource("sqlServerDataSource") jdbc.sqlserver.driver=net
.sourceforge.jtds.jdbc.Driver jdbc.sqlserver.url=jdbc:jtds:sqlserver://192.168.0
.117:1433/test_db jdbc.sqlserver.username=lujingyu jdbc.sqlserver.password=
123456 # General configuration jdbc.initialSize=3 jdbc.minIdle=2 jdbc.maxActive=60 jdbc.maxWait=
60000 jdbc.timeBetweenEvictionRunsMillis=60000 jdbc.minEvictableIdleTimeMillis=
30000 jdbc.validationQuery=SELECT 'x' jdbc.testWhileIdle=true jdbc.testOnBorrow
=false jdbc.testOnReturn=false jdbc.poolPreparedStatements=true jdbc
.maxPoolPreparedStatementPerConnectionSize=20 jdbc.removeAbandoned=true jdbc
.removeAbandonedTimeout=120 jdbc.logAbandoned=false jdbc.filters=stat
spring-mybatis.xml configuration file
The main documents are coming , I use all the configuration code , I added “ important ” You need to configure it !!!!!!!!
All important points must be configured !! Others can be ignored according to the demand , Believe that can do multi data source configuration can read these codes !
<?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:aop=
"http://www.springframework.org/schema/aop" xmlns:tx=
"http://www.springframework.org/schema/tx" xmlns:context=
"http://www.springframework.org/schema/context" xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.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
http://www.springframework.org/schema/jee
http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.0.xsd"> <!-- Automatic scanning ( Automatic injection ) -->
<context:component-scan base-package=
"com.newcsp.*.service;com.newcsp.*.*.service" /> <!-- Timed tasks --> <!--
<context:component-scan base-package="com.newcsp.common.timer"/> --> <bean id=
"log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter"> <property
name="resultSetLogEnabled" value="true" /> </bean> <!--
important !!! Configure data sources mysql Below the property According to you jdbc.properties Document matching --> <bean name="dataSource"
class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method
="close"> <property name="driverClassName" value="${jdbc.driverClassName}" /> <
property name="url" value="${jdbc.url}" /> <property name="username" value=
"${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <
property name="initialSize" value="${jdbc.initialSize}" /> <property name=
"minIdle" value="${jdbc.minIdle}" /> <property name="maxActive" value=
"${jdbc.maxActive}" /> <property name="maxWait" value="${jdbc.maxWait}" /> <
property name="timeBetweenEvictionRunsMillis" value=
"${jdbc.timeBetweenEvictionRunsMillis}" /> <property name=
"minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" /> <
property name="validationQuery" value="${jdbc.validationQuery}" /> <property
name="testWhileIdle" value="${jdbc.testWhileIdle}" /> <property name=
"testOnBorrow" value="${jdbc.testOnBorrow}" /> <property name="testOnReturn"
value="${jdbc.testOnReturn}" /> <property name="removeAbandoned" value=
"${jdbc.removeAbandoned}" /> <property name="removeAbandonedTimeout" value=
"${jdbc.removeAbandonedTimeout}" /> <!-- <property name="logAbandoned"
value="${jdbc.logAbandoned}" /> --> <property name="filters" value=
"${jdbc.filters}" /> <!-- close abanded Output error log on connection --> <property name="logAbandoned"
value="true" /> <property name="proxyFilters"> <list> <ref bean="log-filter" />
</list> </property> <!-- Monitoring database --> <!-- <property name="filters" value="stat"
/> --> <!-- <property name="filters" value="mergeStat" /> --> </bean>
<!-- important !!!! sqlserver data source You can use this configuration as a reference mysql Added some function configuration --> <bean id=
"sqlServerDataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close"> <property name="driverClassName" value=
"${jdbc.sqlserver.driver}"/> <property name="url" value="${jdbc.sqlserver.url}"
/> <property name="username" value="${jdbc.sqlserver.username}"/> <property name
="password" value="${jdbc.sqlserver.password}"/> <property name="initialSize"
value="${jdbc.initialSize}"/> <property name="minIdle" value="${jdbc.minIdle}"/>
<property name="maxActive" value="${jdbc.maxActive}"/> <property name="maxWait"
value="${jdbc.maxWait}"/> <property name="validationQuery" value=
"${jdbc.validationQuery}" /> <property name="testWhileIdle" value=
"${jdbc.testWhileIdle}"/> <property name="testOnBorrow" value=
"${jdbc.testOnBorrow}" /> <property name="testOnReturn" value=
"${jdbc.testOnReturn}" /> <property name="removeAbandoned" value=
"${jdbc.removeAbandoned}"/> <property name="removeAbandonedTimeout" value=
"${jdbc.removeAbandonedTimeout}"/> <property name=
"timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
<property name="minEvictableIdleTimeMillis" value=
"${jdbc.minEvictableIdleTimeMillis}"/> </bean> <!-- important !!! Here's class The above class file is called
--> <bean id="multipleDataSource" class=
"com.newcsp.core.mybatis.MultipleDataSource"> <!-- Specify the default data source --> <property name=
"defaultTargetDataSource" ref="dataSource"/> <property name="targetDataSources">
<map> <!-- Names of the two data sources --> <entry key="dataSource" value-ref="dataSource"/> <entry
key="sqlServerDataSource" value-ref="sqlServerDataSource"/> </map> </property>
</bean> <bean id="sqlSessionFactory" class=
"org.mybatis.spring.SqlSessionFactoryBean"> <!-- Single data source usage Class neglect --> <!-- <property
name="dataSource" ref="dataSource" /> --> <!-- important !!!! Use of multiple data sources --> <property name=
"dataSource" ref="multipleDataSource"/> <property name="configLocation" value=
"classpath:mybatis-config.xml" /> <property name="mapperLocations"> <list> <
value>classpath:com/newcsp/oracle/mapper/*.xml</value> </list> </property> </
bean> <bean id="sqlSessionTemplate" class=
"org.mybatis.spring.SqlSessionTemplate"> <constructor-arg index="0" ref=
"sqlSessionFactory" /> </bean> <bean id="baseMybatisDao" class=
"com.newcsp.core.mybatis.BaseMybatisDao"> <property name="sqlSessionFactory" ref
="sqlSessionFactory" /> </bean> <bean class=
"org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name=
"basePackage" value="com.newcsp.oracle.dao" /> <property name=
"sqlSessionFactoryBeanName" value="sqlSessionFactory" /> </bean> <bean id=
"transactionManager" class=
"org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property
name="dataSource" ref="dataSource" /> </bean> <tx:advice id="txAdvice"
transaction-manager="transactionManager"> <tx:attributes> <tx:method name=
"publish*" /> <tx:method name="save*" /> <tx:method name="add*" /> <tx:method
name="update*" /> <tx:method name="insert*" /> <tx:method name="create*" /> <
tx:method name="del*" /> <tx:method name="load*" /> <tx:method name="init*" /> <
tx:method name="*" read-only="true" /> </tx:attributes> </tx:advice> <!-- AOP to configure
--> <aop:config> <aop:pointcut id="myPointcut" expression="execution(public *
com.newcsp.*.service.impl.*.*(..))" /> <aop:advisor advice-ref="txAdvice"
pointcut-ref="myPointcut" /> </aop:config>
<!-- important !!!!! This is used to intercept serverimpl layer dataSource annotation For use with multiple data sources --> <bean id=
"dataSourceAspect" class="com.newcsp.core.mybatis.DataSourceAspect" /> <
aop:config> <aop:aspect ref="dataSourceAspect"> <!-- Intercept all service method --> <
aop:pointcut id="dataSourcePointcut" expression="execution(*
com.newcsp.*.service.*.*(..))" /> <aop:before pointcut-ref="dataSourcePointcut"
method="intercept" /> </aop:aspect> </aop:config> </beans>
How to use it :
stay spring-mybatis.xml Intercept annotations are configured in @DataSource Methods
In the use of writing code, no matter how it is written, it has to go through serviceImpl Implementation layer , Annotate the implementation layer
for example :@DataSource(“dataSource”)
Because in the configuration spring-mybatis.xml When my mysql to configure name call :dataSource
my sqlserver to configure name call sqlServerDataSource, You can add the annotation name of the database to which database you need to use !
for example :
@Service @DataSource("dataSource") public class UserServiceImpl implements
UserService { }
That's the end , If you don't understand, you can communicate . Welcome to our attention !
If I succeed in helping you ~~ You can give anything you like ~