1. Why use multiple data sources?

In doing the project in many cases will involve many database, two mature products of the company, implement products to customers, docking customer of many systems (such as the customer’s system is outsourcing, other system supplier is busy, not very fit, and obtain the consent of the customer, directly connected to the system data to operate).

2. Use steps

2.1. Modify the jdbc.properties file

# oracle JDBC. DriverClassName = oracle. JDBC. OracleDriver JDBC. DatabaseUrl = JDBC: oracle: thin: @ 192.168.187.25:1521: oratest jdbc.username=mdm_fssc jdbc.password=123456 #ERPoracle erp.jdbc.driverClassName=oracle.jdbc.OracleDriver Erp. JDBC. DatabaseUrl = JDBC: oracle: thin: @ 192.168.187.27:1531 / UAT erp. JDBC. Username = apps erp. JDBC. Password = CLONECopy the code

2.2. Modify mybatis. XML file

<! 1 - - - data source configuration > < bean name = "dataSourceMdm" class = "com. Alibaba. Druid. Pool. DruidDataSource" init - method = "init" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.databaseUrl}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> <! <property name="initialSize" value="50"/> <! Property name="maxActive" value="200"/> <! -- Connection pool maximum free time --> <! -- <property name="maxIdle" value="20" /> --> <! Property name="minIdle" value="5"/> <! <property name="maxWait" value="6000"/> <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" value="100"/> <property name="validationQuery" value="SELECT 1 FROM DUAL"/> <property name="testOnBorrow" value="false"/> <property name="testOnReturn" value="false"/> <property name="testWhileIdle" value="true"/> <! - how soon a detection configuration interval, testing needs to be closed free connection, unit: ms - > < property name = "timeBetweenEvictionRunsMillis" value = "60000" / > <! - configure a connection in the pool minimum survival time, unit: ms - > < property name = "minEvictableIdleTimeMillis" value = "25200000" / > <! <property name="removeAbandoned" value="true"/> <! <property name="removeAbandonedTimeout" value=" abandonedTimeout "/> <! <property name="logAbandoned" value="true"/> <! <property name="proxyFilters"> <list> <ref bean="stat-filter" /> <ref bean="wall-filter"/> </list> </property> </bean> <bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter"/> <bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter"> <property name="config" ref="wall-config" /> </bean> <bean id="wall-config" class="com.alibaba.druid.wall.WallConfig"> <property name="multiStatementAllow" value="true" /> </bean> <! - data source configuration 2 - > < bean name = "dataSourceErp" class = "com. Alibaba. Druid. Pool. DruidDataSource" init - method = "init" destroy-method="close"> <property name="driverClassName" value="${erp.jdbc.driverClassName}"/> <property name="url" value="${erp.jdbc.databaseUrl}"/> <property name="username" value="${erp.jdbc.username}"/> <property name="password" value="${erp.jdbc.password}"/> <! <property name="initialSize" value="50"/> <! Property name="maxActive" value="200"/> <! -- Connection pool maximum free time --> <! -- <property name="maxIdle" value="20" /> --> <! Property name="minIdle" value="5"/> <! <property name="maxWait" value="6000"/> <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" value="100"/> <property name="validationQuery" value="SELECT 1 FROM DUAL"/> <property name="testOnBorrow" value="false"/> <property name="testOnReturn" value="false"/> <property name="testWhileIdle" value="true"/> <! - how soon a detection configuration interval, testing needs to be closed free connection, unit: ms - > < property name = "timeBetweenEvictionRunsMillis" value = "60000" / > <! - configure a connection in the pool minimum survival time, unit: ms - > < property name = "minEvictableIdleTimeMillis" value = "25200000" / > <! <property name="removeAbandoned" value="true"/> <! <property name="removeAbandonedTimeout" value=" abandonedTimeout "/> <! <property name="logAbandoned" value="true"/> <! <property name="proxyFilters"> <list> <ref bean="stat-filter" /> <ref bean="wall-filter"/> </list> </property> </bean> <! -- data switch - > < bean id = "dataSource" class = "com. Epoch. Customproject. Sinohttpinterface. Dao. DynamicDataSource" > < property name="targetDataSources"> <map key-type="java.lang.String"> <! <entry key="dataSourceMdm" value-ref="dataSourceMdm"></entry> <entry key="dataSourceErp" value-ref="dataSourceErp"></entry> </map> </property> <! <property name="defaultTargetDataSource" /> </bean> <bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <! -- Mybatis XML mapping file path --> <property name="configLocation" value=" CLASSPath: Mybatis -config.xml"/> <property name="mapperLocations"> <array> <! Classpath *:com/epoch/**/dao/**/ dao. XML </value> </array> </property> <property name="typeHandlers"> <list> <bean class="com.epoch.infrastructure.util.service.typehandler.BooleanTypeHandler" /> <bean class="com.epoch.infrastructure.util.service.typehandler.StringArrayTypeHandler" /> <bean class="com.epoch.infrastructure.util.service.typehandler.StringListTypeHandler" /> <bean class="com.epoch.infrastructure.util.service.typehandler.I18nStringHandler"/> </list> </property> <property name="typeAliasesPackage" value="com.epoch.**.model"/> </bean> <! - configure mybatis mapper interfaces - > < bean class = "org. Mybatis. Spring. Mapper. MapperScannerConfigurer" > < property name = "basePackage" value="com.epoch.**.dao"/> <property name="sqlSessionFactoryBeanName" value="sessionFactory"/> </bean>Copy the code

2.3, create DynamicDataSource. Java classes inherit AbstractRoutingDataSource

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import java.util.logging.Logger; public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceHolder.getDbType(); } @Override public Logger getParentLogger() { return null; }}Copy the code

2.4, create DynamicDataSourceHolder. Java classes

public class DynamicDataSourceHolder { private static final ThreadLocal<String> THREAD_DATA_SOURCE = new ThreadLocal<String>(); private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>(); public static void setDbType(String dbType) { contextHolder.set(dbType); } public static String getDbType() { return ((String) contextHolder.get()); } public static void clearDbType() { contextHolder.remove(); }}Copy the code

2.5. Method of use

Call DynamicDataSourceHolder. SetDbType switch () method of the data source

DynamicDataSourceHolder.setDbType("dataSourceErp"); // It is better to use enumerations here to reduce the error rateCopy the code

3. Possible problems

/ / start times wrong Java. SQL. SQLRecoverableException: IO error: Got minus one from a read the callCopy the code

3.1 Reasons:

The maximum number of connections for this database instance is exceeded

3.2 Solutions:

1. Adjust the maximum number of database connections (for companies, of course, contact the DBA to adjust) 2. Modify the initial connection in mybatis. XML

4. Custom annotations

4.1. Create @interface annotation

package com.epoch.customproject.utils;
import java.lang.annotation.*;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSource {
    String value() default "dataSourceMdm";
}
Copy the code

4.2 Create an AOP facet class

package com.epoch.customproject.utils; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.After; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.reflect.MethodSignature; import java.lang.reflect.Method; import org.aspectj.lang.annotation.Before; import org.springframework.core.annotation.Order; @order (1) @aspect public class DataSourceAspect {// Before("@annotation(DataSource)") public void BeforeSwitchDS (JoinPoint Point){// Get the current class class <? > className = point.getTarget().getClass(); String methodName = point.getSignature().getName(); Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes(); String dataSource = ""; Method = classname.getMethod (methodName, argClass); / / determine whether there are @ DS comments if (method. IsAnnotationPresent (DataSource. Class)) {= DataSource an annotation method.getAnnotation(DataSource.class); // dataSource = annotate.value (); } } catch (Exception e) { e.printStackTrace(); } / / switch data DynamicDataSourceHolder setDbType (dataSource); } @after ("@annotation(DataSource)") public void After(JoinPoint point) DynamicDataSourceHolder.setDbType("dataSourceMdm"); }}Copy the code

4.3. Modify applicationContext. XML

Injection of AOP

<! - specifies the AOP cut class - > < bean id = "dataSourceAspect" class = "com. Epoch. Customproject. Utils. DataSourceAspect" / > < AOP: config > <aop:aspect id="dataAspect" ref="dataSourceAspect"> <! < AOP :pointcut id="tx" expression="execution(* com.epoch.*.service(..)) ) "/ > <! < AOP :before pointcut-ref="tx" method="beforeSwitchDS"/> </ AOP :aspect> </ AOP :config>Copy the code

4.4. Method of use

When used, it is written to the service implementation class method

@datasource (value = "DataSource key")Copy the code

This has realized the database switch function!

5, test,

package com.example.thread.example; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.ResponseBody; /** * @Description * @Author: makejava * @Date: 2021/3/20 14:12 */ @Controller @ResponseBody public class JiSuanQi{ @DataSource(value = "${datasource.key1}") public void test(){ TestService testService = new TestService(); User user=new User(); User.setusername (" Data source switch "); user.setPassword("123"); testService.insert(user); }}Copy the code

In this way, dynamic switching is realized, and the new one is successful!!