Spring + Mybatis: How to dynamically switch data sources. springmybatis

Spring + Mybatis: How to dynamically switch data sources. springmybatis

The functional requirement is that the company wants to build a big operating platform:

1. The operating platform has its own database to maintain basic functions such as users, roles, menus, parts, and permissions.

2. The operating platform also needs to provide background operations for different services (service A and service B). databases serving services A and service B are independent.

Therefore, the operating platform must have at least three databases: Operation database, database A, and database B, in addition, we hope that each function request can be automatically switched to the corresponding data source (I finally switched to the Service method level, or the DAO layer method can be switched. The functions of our system are relatively independent from each other ).

Step 1: Configure multiple data sources

1. Define the Data source:

The data source I use is Alibaba's DruidDataSource (you can use DBCP. This is free of charge ). The configuration is as follows:

<! -- Op dataSource --> <bean id = "opDataSource" class = "com. alibaba. druid. pool. druidDataSource "init-method =" init "destroy-method =" close "> <property name =" url "value =" $ {db. master. url} "/> <property name =" username "value =" $ {db. master. user} "/> <property name =" password "value =" $ {db. master. password} "/> <property name =" driverClassName "value =" $ {db. master. driver} "/> <property name =" initialSize "value =" 5" /> <Property name = "maxActive" value = "100"/> <property name = "minIdle" value = "10"/> <property name = "maxWait" value =" 60000 "/> <property name =" validationQuery "value =" SELECT 'x' "/> <property name =" testOnBorrow "value =" false "/> <property name =" testOnReturn "value =" false "/> <property name =" testWhileIdle "value =" true "/> <property name =" timeBetweenEvictionRunsMillis "value =" 600000 "/> <property nam E = "minEvictableIdleTimeMillis" value = "300000"/> <property name = "removeAbandoned" value = "true"/> <property name = "removeAbandonedTimeout" value = "1800"/> <property name = "logAbandoned" value = "true"/> <! -- Configure the filters intercepted by monitoring statistics --> <property name = "filters" value = "config, mergeStat, wall, log4j2 "/> <property name =" connectionProperties "value =" config. decrypt = true "/> </bean> <! -- ServerA dataSource --> <bean id = "serverADataSource" class = "com. alibaba. druid. pool. druidDataSource "init-method =" init "destroy-method =" close "> <property name =" url "value =" $ {db. serverA. master. url} "/> <property name =" username "value =" $ {db. serverA. master. user} "/> <property name =" password "value =" $ {db. serverA. master. password} "/> <property name =" driverClassName "value =" $ {db. serverA. master. driver }" /> <Property name = "initialSize" value = "5"/> <property name = "maxActive" value = "100"/> <property name = "minIdle" value =" 10 "/> <property name =" maxWait "value =" 60000 "/> <property name =" validationQuery "value =" SELECT 'x' "/> <property name =" testOnBorrow "value =" false "/> <property name =" testOnReturn "value =" false "/> <property name =" testWhileIdle "value =" true "/> <property name = "timeBetweenEvictionR UnsMillis "value =" 600000 "/> <property name =" minEvictableIdleTimeMillis "value =" 300000 "/> <property name =" removeAbandoned "value =" true "/> <property name = "removeAbandonedTimeout" value = "1800"/> <property name = "logAbandoned" value = "true"/> <! -- Configure the filters intercepted by monitoring statistics --> <property name = "filters" value = "config, mergeStat, wall, log4j2 "/> <property name =" connectionProperties "value =" config. decrypt = true "/> </bean> <! -- ServerB dataSource --> <bean id = "serverBDataSource" class = "com. alibaba. druid. pool. druidDataSource "init-method =" init "destroy-method =" close "> <property name =" url "value =" $ {db. serverB. master. url} "/> <property name =" username "value =" $ {db. serverB. master. user} "/> <property name =" password "value =" $ {db. serverB. master. password} "/> <property name =" driverClassName "value =" $ {db. serverB. master. driver }" /> <Property name = "initialSize" value = "5"/> <property name = "maxActive" value = "100"/> <property name = "minIdle" value =" 10 "/> <property name =" maxWait "value =" 60000 "/> <property name =" validationQuery "value =" SELECT 'x' "/> <property name =" testOnBorrow "value =" false "/> <property name =" testOnReturn "value =" false "/> <property name =" testWhileIdle "value =" true "/> <property name = "timeBetweenEvictionR UnsMillis "value =" 600000 "/> <property name =" minEvictableIdleTimeMillis "value =" 300000 "/> <property name =" removeAbandoned "value =" true "/> <property name = "removeAbandonedTimeout" value = "1800"/> <property name = "logAbandoned" value = "true"/> <! -- Configure the filters intercepted by monitoring statistics --> <property name = "filters" value = "config, mergeStat, wall, log4j2 "/> <property name =" connectionProperties "value =" config. decrypt = true "/> </bean>

I have configured three data sources: oPDataSource, serverADataSource, and serverBDataSource.

2. Configure multipleDataSource

The multipleDataSource is equivalent to a proxy of the above three data sources. When combined with Spring/Mybatis, The multipleDataSource and the separately configured DataSource are not used separately:

<! -- Integrate Mybatis with Spring: Configure multipleDatasource --> <bean id = "sqlSessionFactory" class = "com. baomidou. mybatisplus. spring. mybatisSqlSessionFactoryBean "> <property name =" dataSource "ref =" multipleDataSource "/> <! -- Automatically scans Mapping. xml file --> <property name = "mapperLocations"> <list> <value> classpath *:/sqlMapperXml /*. xml </value> <value> classpath *:/sqlMapperXml /*/*. xml </value> </list> </property> <property name = "configLocation" value = "classpath: xml/mybatis-config.xml "> </property> <property name =" typeAliasesPackage "value =" com. XXX. platform. model "/> <property name =" globalConfig "ref =" globalConfig "/> <property name = "Plugins"> <array> <! -- Pagination plug-in configuration --> <bean id = "paginationInterceptor" class = "com. baomidou. mybatisplus. plugins. paginationInterceptor "> <property name =" dialectType "value =" mysql "/> <property name =" optimizeType "value =" aliDruid "/> </bean> </array> </property> </bean> <! -- MyBatis dynamic implementation --> <bean id = "mapperScannerConfigurer" class = "org. mybatis. spring. mapper. MapperScannerConfigurer"> <! -- For dynamic implementation of the Dao interface, you need to know where the interface is --> <property name = "basePackage" value = "com. XXX. platform. mapper "/> <property name =" sqlSessionFactoryBeanName "value =" sqlSessionFactory "> </property> </bean> <! -- MP global configuration --> <bean id = "globalConfig" class = "com. baomidou. mybatisplus. entity. globalConfiguration "> <property name =" idType "value =" 0 "/> <property name =" dbColumnUnderline "value =" true "/> </bean> <! -- Transaction Management Configuration multipleDataSource --> <bean id = "transactionManager" class = "org. springframework. jdbc. datasource. dataSourceTransactionManager "> <property name =" dataSource "ref =" multipleDataSource "> </property> </bean>

After learning about the location of multipleDataSource, let's focus on how to implement multipleDataSource. The configuration file is as follows:

<bean id="multipleDataSource" class="com.xxxx.platform.commons.db.MultipleDataSource">    <property name="defaultTargetDataSource" ref="opDataSource" />    <property name="targetDataSources">      <map>        <entry key="opDataSource" value-ref="opDataSource" />        <entry key="serverADataSource" value-ref="serverADataSource" />        <entry key="serverBDataSource" value-ref="serverBDataSource" />      </map>    </property>  </bean>

The implemented Java code is as follows, which does not require too much explanation and is clear at a glance:

Import org. springframework. jdbc. datasource. lookup. abstractRoutingDataSource;/***** @ ClassName: MultipleDataSource * @ Description: Configure multiple data sources <br> * @ author: yuzhu. peng * @ date: January 12, 2018 4:37:25 */public class MultipleDataSource extends actroutingdatasource {private static final ThreadLocal <String> performancekey = new InheritableThreadLocal <String> (); public static void setDataSourceKey (String dataSource) {performancekey. set (dataSource) ;}@ Override protected Object determineCurrentLookupKey () {return performancekey. get ();} public static void removeperformancekey () {performancekey. remove ();}}

Inherit from spring's AbstractRoutingDataSource and implement the abstract method determineCurrentLookupKey. This method determines the data source of this Connection before each database Connection is obtained, you can see the Spring Code clearly:

/* Get Connection */public Connection getConnection () throws SQLException {return determineTargetDataSource (). getConnection ();} protected DataSource determineTargetDataSource () {Assert. notNull (this. resolvedDataSources, "DataSource router not initialized");/* The determineCurrentLookupKey here is an abstract interface to obtain the specific data source name */Object lookupKey = determineCurrentLookupKey (); DataSource dataSource = (DataSource) this. resolvedDataSources. get (lookupKey); if (dataSource = null) & (this. lenientFallback) | (lookupKey = null) {dataSource = this. resolveddefadatasource dataSource;} if (DataSource = null) {throw new IllegalStateException ("Cannot determine target dataSource for lookup key [" + lookupKey + "]");} return dataSource ;} /* abstract interface: The interface implemented by multipleDataSource */protected abstract Object determineCurrentLookupKey ();

Step 2: Each request (Service method level) dynamically switches the data source

The implementation idea is to use Spring's AOP idea to intercept each Service method call, and then dynamically switch the data key in multipleDataSource based on the overall path name of the method. Our project is independent of each other for different services, that is, operations on different databases. It is not recommended to call different data sources in the same service method, in this case, you need to dynamically determine whether to switch the frequency (the frequency of AOP interception) at the DAO level, that is, the SQL level. In addition, transaction management is not convenient.

Let's take a look at the dynamic data source switching AOP implementation:

Import java. lang. reflect. proxy; import org. apache. commons. lang. classUtils; import org. aspectj. lang. joinPoint; import org. aspectj. lang. annotation. after; import org. aspectj. lang. annotation. aspect; import org. aspectj. lang. annotation. before; import org. springframework. core. annotation. order;/*** AOP for data source switching ** @ author yuzhu. peng * @ since 2018-01-15 */@ Aspect @ Order (1) public class MultipleDataSourceInterceptor {/*** Special attention should be paid to the data source switching before the interceptor implements class requests for all businesses. Because multiple data sources are used, it is best to call ER er only in * ServiceImpl, otherwise, the report does not have an exception ** @ param joinPoint * @ throws Throwable */@ Before ("execution (* com. xxxx. platform. service .. *. * ServiceImpl. *(..)) ") public void setDataSoruce (JoinPoint joinPoint) throws Throwable {Class <?> Clazz = joinPoint. getTarget (). getClass (); String className = clazz. getName (); if (ClassUtils. isAssignable (clazz, Proxy. class) {className = joinPoint. getSignature (). getDeclaringTypeName ();} // set the class name to the serverA data source. Otherwise, the default value is the background data source if (className. contains (". serverA. ") {MultipleDataSource. setperformancekey (DBConstant. DATA_SOURCE_serverA);} else if (className. contains (". serverB. ") {MultipleDataSource. setperformancekey (DBConstant. DATA_SOURCE_serverB);} else {MultipleDataSource. setperformancekey (DBConstant. DATA_SOURCE_OP) ;}}/*** when the operation is complete, if the current data source is released but not frequently clicked, a data source conflict occurs. This is the table of another data source, the report does not exist ** @ param joinPoint * @ throws Throwable */@ After ("execution (* com. xxxx. service .. *. * ServiceImpl. *(..)) ") public void removeDataSoruce (JoinPoint joinPoint) throws Throwable {MultipleDataSource. removeDataSourceKey ();}}

Intercept all ServiceImpl methods, determine the function of the data source based on the full qualified name of the method, select the corresponding data source, release the current data source after the release is completed. Note that I have used the @ Order annotation of Spring. Next I will talk about it. order is useful when multiple AOP types are defined.

Others:

Transactions are not introduced in the project at the beginning, so everything is OK and the correct data source can be accessed each time. After adding SPring transaction management, the data source cannot be switched dynamically (for example, the transaction is not effective, but the two are not valid at the same time). Later I found that the cause was the execution sequence of AOP, so I used the SPring Order mentioned above:

The smaller the order, the execution first. At this point, both the data source can be dynamically switched and transactions can be successfully used (in the same data source ).

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.