Spring, MyBatis Configuration and management of multiple data sources

The same project can sometimes involve multiple databases, or multiple data sources. Multiple data sources can be divided into two situations:

1) Two or more databases are not relevant, independent of each other, in fact, this can be developed as two projects. For example, in the game development of a database is a platform database, and other platforms under the game corresponding database;

2) Two or more databases are master-slave relationships, such as having MySQL to build a master-master, followed by a plurality of slave, or MHA-built master-slave replication;

There are probably two ways to build a Spring multi-data source that you can choose from multiple data sources.

1. Configure multiple data sources directly with spring configuration file

For example, if there is no correlation for two databases, you can configure multiple data sources directly in the spring configuration file and then configure the transactions separately, as follows:

<context:component-scan base- Package= "Net.aazj.service,net.aazj.aop"/> <context:component-scan base- Package= "Net.aazj.aop"/> <!--Introducing Properties file--<context:property-placeholder location= "Classpath:config/db.propertie S "/> <!--configuration data source-<bean name=" DataSource "class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "close" > <property name= "url" val Ue= "${jdbc_url}"/> <property name= "username" value= "${jdbc_username}"/> <property name= "Passwo        Rd "value=" ${jdbc_password} "/> <!--Initialize connection size-<property name=" initialsize "value=" 0 "/>        <!--connection Pool maximum usage connections-<property name= "maxactive" value= "/> <!--connection Pool Max free-- <property name= "Maxidle" value= "/> <!--connection pool min Free--<property name=" Minidle "value=" 0 "/ > <!--get connection maximum wait time-<property name= "maxwait" value= "60000"/> </bean> <b Ean id= "Sqlsessionfactory"class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property name= "dataSource" ref= "DataSource"/> <proper Ty name= "configlocation" value= "Classpath:config/mybatis-config.xml"/> <property name= "mapperLocations" value = "Classpath*:config/mappers/**/*.xml"/> </bean> <!--Transaction Manager forA single JDBC DataSource---<bean id= "TransactionManager"class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name= "DataSource" ref= " DataSource "/> </bean> <!--defining transactions with annotation--<tx:annotation-driven transaction-manager= "TransactionManager"/> <beanclass= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name= "basepackage" value= "Net.aazj.mapper"/ > <property name= "sqlsessionfactorybeanname" value= "Sqlsessionfactory"/> </bean>
<!--enables the use of the @AspectJ style of Spring AOP--<aop:aspectj-autoproxy/> <!--====== ========= configuration of the second data source ===============-<bean name= "datasource_2"class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "close" > <property name= "url" val Ue= "${jdbc_url_2}"/> <property name= "username" value= "${jdbc_username_2}"/> <property name= "pa ssWOrd "value=" ${jdbc_password_2} "/> <!--Initialize connection size--<property name=" initialsize "value=" 0 "/&        Gt <!--connection Pool maximum usage connections-<property name= "maxactive" value= "/> <!--connection Pool Max Idle---<        Property Name= "Maxidle" value= "/> <!--connection pool min Free--<property name=" Minidle "value=" 0 "/> <!--get connection maximum wait time-<property name= "maxwait" value= "60000"/> </bean> <bean I D= "Sqlsessionfactory_slave"class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property name= "dataSource" ref= "datasource_2"/> <prop Erty name= "configlocation" value= "Classpath:config/mybatis-config-2.xml"/> <property name= "MapperLocations" V Alue= "Classpath*:config/mappers2/**/*.xml"/> </bean> <!--Transaction Manager forA single JDBC DataSource---<bean id= "transactionmanager_2"class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name= "DataSource" ref= " Datasource_2 "/> </bean> <!--defining transactions with annotation--<tx:annotation-driven transaction-manage R= "transactionmanager_2"/> <beanclass= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name= "basepackage" value= "Net.aazj.mapper2" /> <property name= "sqlsessionfactorybeanname" value= "sqlsessionfactory_2"/> </bean>

As shown above, we have configured two DataSource, two sqlsessionfactory, two TransactionManager, and the key is Mapperscannerconfigurer configuration--Using The Sqlsessionfactorybeanname attribute, injected with the name of a different sqlsessionfactory, injects the corresponding sqlsessionfactory for the Mapper interface of the different database.

It is important to note that this configuration of multiple databases does not support distributed transactions, that is, in the same transaction, multiple databases cannot be manipulated. The advantages of this configuration approach are simple, but not flexible. For master-slave types of multi-data source configuration is not very suitable, master-slave multi-data source configuration, need to be particularly flexible, need to be based on the type of business detailed configuration. For example, for some very time-consuming SELECT statements, we want to be executed on slave, and for Update,delete, and so on, the operation must only be performed on master, in addition, for some real-time high-demand SELECT statements, We may also need to put it on master-for example, a scene is I go to the mall to buy a weapon, the purchase operation is determined to master, while the purchase is completed, you need to re-query the weapons and coins I have, then this query may also need to prevent master on the execution, Instead of putting it on the slave, because there may be delays on the slave, we don't want the player to find that the weapon was found in the backpack after the purchase was successful.

So for the configuration of master-slave types of multi-data sources, it needs to be flexibly configured according to the business, which select can be placed on slave and which select cannot be placed on slave. So the configuration of the data source above is not very suitable.

2. Configuration of multi-data sources based on Abstractroutingdatasource and AOP

The basic principle is that we define ourselves a DataSource class Threadlocalrountingdatasource to inherit Abstractroutingdatasource, Then inject the master and slave data sources into the Threadlocalrountingdatasource in the configuration file, and then use AOP to flexibly configure where the master data source is selected and where the slave data source needs to be selected. Here's a look at the code implementation:

1) first define an enum to represent the different data sources:

 Package net.aazj.enums; /**  */publicenum  datasources {    MASTER, SLAVE}

2) use Theadlocal to save the flag (key) of which data source each thread chooses:

 PackageNet.aazj.util;Importnet.aazj.enums.DataSources; Public classDatasourcetypemanager {Private Static FinalThreadlocal<datasources> datasourcetypes =NewThreadlocal<datasources>() {@Overrideprotecteddatasources InitialValue () {returnDatasources.master;        }    };  Public Staticdatasources Get () {returnDatasourcetypes.get (); }         Public Static voidset (datasources datasourcetype) {datasourcetypes.set (datasourcetype); }         Public Static voidReset () {datasourcetypes.set (DATASOURCES.MASTER0); }}

3) define Threadlocalrountingdatasource, inherit Abstractroutingdatasource:

 Package Net.aazj.util; Import Org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  Public class extends Abstractroutingdatasource {    @Override    protected  Object Determinecurrentlookupkey () {        return  datasourcetypemanager.get ();    }}

4) inject the master and slave data sources into the Threadlocalrountingdatasource in the configuration file:

<context:component-scan base- Package= "Net.aazj.service,net.aazj.aop"/> <context:component-scan base- Package= "Net.aazj.aop"/> <!--Introducing Properties file--<context:property-placeholder location= "Classpath:config/db.propertie S "/> <!--configuration Data source Master--<bean name=" Datasourcemaster "class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "close" > <property name= "url" val Ue= "${jdbc_url}"/> <property name= "username" value= "${jdbc_username}"/> <property name= "Passwo        Rd "value=" ${jdbc_password} "/> <!--Initialize connection size-<property name=" initialsize "value=" 0 "/>        <!--connection Pool maximum usage connections-<property name= "maxactive" value= "/> <!--connection Pool Max free-- <property name= "Maxidle" value= "/> <!--connection pool min Free--<property name=" Minidle "value=" 0 "/ > <!--get connection maximum wait time-<property name= "maxwait" value= "60000"/> </bean> <! --Configure Data source slave--<bean name= "Datasourceslave"class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "close" > <property name= "url" val Ue= "${jdbc_url_slave}"/> <property name= "username" value= "${jdbc_username_slave}"/> <property Name= "Password" value= "${jdbc_password_slave}"/> <!--Initialize connection size--<property name= "InitialSize" value= "0"/> <!--connection Pool maximum usage connection number-<property name= "maxactive" value= "/> <!--connection pool Max Idle--<property name= "Maxidle" value= "/> <!--connection pool min Free---<property name=" mi Nidle "value=" 0 "/> <!--get connection maximum wait time-<property name=" maxwait "value=" 60000 "/> </bea n> <bean id= "DataSource"class= "Net.aazj.util.ThreadLocalRountingDataSource" > <property name= "Defaulttargetdatasource" ref= " Datasourcemaster "/> <property name=" targetdatasources "> <map key-type=" Net.aazj.enums.DataS Ources "> <entry key=" MASTER "value-ref=" Datasourcemaster "/> <entry key=" SLAVE "V alue-ref= "Datasourceslave"/> <!--Here you can also add more datasource--</map> </prope rty> </bean> <bean id= "Sqlsessionfactory"class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property name= "dataSource" ref= "DataSource"/> <proper Ty name= "configlocation" value= "Classpath:config/mybatis-config.xml"/> <property name= "mapperLocations" value = "Classpath*:config/mappers/**/*.xml"/> </bean> <!--Transaction Manager forA single JDBC DataSource---<bean id= "TransactionManager"class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name= "DataSource" ref= " DataSource "/> </bean> <!--defining transactions with annotation--<tx:annotation-driven transaction-manager= "TransactionManager"/> <beanclass= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name= "basepackage" value= "Net.aazj.mapper"/ > <!--<property name= "Sqlsessionfactorybeanname" value= "Sqlsessionfactory"/>--</bean>

In the spring configuration file above, we defined Datasourcemaster and Datasourceslave two datasource for the master database and the slave database, and then injected it into <bean id= " DataSource " class=" Net.aazj.util.ThreadLocalRountingDataSource "> , so that our dataSource can come in accordance with key Different to choose Datasourcemaster and Datasourceslave.

5) use spring AOP to specify DataSource key so that DataSource will select Datasourcemaster and Datasourceslave based on key:

 PackageNET.AAZJ.AOP;Importnet.aazj.enums.DataSources;ImportNet.aazj.util.DataSourceTypeManager;ImportOrg.aspectj.lang.JoinPoint;ImportOrg.aspectj.lang.annotation.Aspect;ImportOrg.aspectj.lang.annotation.Before;ImportOrg.aspectj.lang.annotation.Pointcut;Importorg.springframework.stereotype.Component; @Aspect//For AOP@Component//For Auto Scan Public classdatasourceinterceptor {@Pointcut ("Execution (Public * net.aazj.service). *.getuser (..)) ")     Public voidDatasourceslave () {}; @Before ("Datasourceslave ()")     Public voidbefore (Joinpoint JP) {datasourcetypemanager.set (datasources.slave); }
// ... ...}

Here we define a Aspect class that we use @Before to conform to @Pointcut ("Execution (public * net.aazj.service). *.getuser (..)) " Before the method is called, call Datasourcetypemanager.set (datasources.slave) sets the type of key to Datasources.slave, so DataSource will choose Datasourceslave this dataSource according to key=Datasources.slave . So the SQL statement for this method is executed on the slave database.

We can continue to expand datasourceinterceptor This Aspect, in a variety of definitions, to a service of a method to specify the appropriate data source corresponding to the datasource.

This allows us to use the power of Spring AOP to be very flexible to configure.

3. Summary

From this article we can realize the power and flexibility of AOP.

This article uses MyBatis, and hibernate should be a similar configuration.

Spring, MyBatis Configuration and management of multiple data sources