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-pack Age= "Net.aazj.aop"/> <!--Introducing Properties file--<context:property-placeholder location= "Classpath:config/db.proper Ties "/> <!--configuration data source-<bean name=" DataSource "class=" Com.alibaba.druid.pool.DruidDataSource "Init-meth Od= "Init" destroy-method= "close" > <property name= "url" value= "${jdbc_url}"/> <property name= "us Ername "value=" ${jdbc_username} "/> <property name=" password "value=" ${jdbc_password} "/> < early!--  Start 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 Idle--<property name= "Minidle" value= "0"/> <!--get connection Max wait Time-&LT;PR Operty name= "Maxwait" ValUe= "60000"/> </bean> <bean id= "sqlsessionfactory" class= "Org.mybatis.spring.SqlSessionFactoryBean" &      Gt <property name= "DataSource" ref= "DataSource"/> <property name= "configlocation" value= "Classpath:config/myb Atis-config.xml "/> <property name=" mapperlocations "value=" Classpath*:config/mappers/**/*.xml "/> </b Ean> <!--Transaction Manager for a 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"/> <bean class= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > <propert Y name= "basepackage" value= "Net.aazj.mapper"/> <property name= "sqlsessionfactorybeanname" value= "SqlSessionFa Ctory "/> &LT;/BEAN&GT 
    <!--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 "value = "${jdbc_url_2}"/> <property name= "username" value= "${jdbc_username_2}"/> <property name= "Pass Word "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 "/> <property name=" ConfigLoca tion "value=" classpath:config/mybatis-config-2.xml "/> <property name=" mapperlocations "value=" classpath*:    Config/mappers2/**/*.xml "/> </bean> <!--Transaction Manager for a single JDBC DataSource-- <bean id= "transactionmanager_2" class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > & Lt;property name= "DataSource" ref= "datasource_2"/> </bean> <!--use annotation to define transactions--<tx:a Nnotation-driven transaction-manager= "transactionmanager_2"/> <bean class= "Org.mybatis.spring.mapper.Mapper Scannerconfigurer "> <property name=" basepackage "value=" Net.aazj.mapper2 "/> <property name=" sqlsess Ionfactorybeanname "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;/** * Data source Category: Master/slave */public enum datasources {    master, slave}

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

Package Net.aazj.util;import Net.aazj.enums.datasources;public class Datasourcetypemanager {    private static final threadlocal<datasources> datasourcetypes = new threadlocal<datasources> () {        @Override        protected DataSources InitialValue () {            return datasources.master;        }    };        public static datasources get () {        return datasourcetypes.get ();    }        public static void Set (DataSources datasourcetype) {        datasourcetypes.set (datasourcetype);    }        public static void Reset () {        datasourcetypes.set (DATASOURCES.MASTER0);}    }

3) define Threadlocalrountingdatasource, inherit Abstractroutingdatasource:

Package Net.aazj.util;import Org.springframework.jdbc.datasource.lookup.abstractroutingdatasource;public Class Threadlocalrountingdatasource 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-pack Age= "Net.aazj.aop"/> <!--Introducing Properties file--<context:property-placeholder location= "Classpath:config/db.proper Ties "/> <!--configuration Data source Master--<bean name=" Datasourcemaster "class=" Com.alibaba.druid.pool.DruidDataS Ource "init-method=" Init "destroy-method=" close "> <property name=" url "value=" ${jdbc_url} "/> <p        Roperty name= "username" value= "${jdbc_username}"/> <property name= "password" 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 Idle--<property name=" Minidle "value=" 0 "/> <!--get connection max wait time--&G        T <property NAMe= "maxwait" value= "60000"/> </bean> <!--configuration data source slave--<bean name= "Datasourceslave" Clas s= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "close" > <property name= "url" va  Lue= "${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=" M Inidle "value=" 0 "/> <!--get connection maximum wait time-<property name=" maxwait "value=" 60000 "/> </be an> <bean id= "DataSource" class= "Net.aazj.util.ThreadLocalRountingDataSource" > <property name= "D Efaulttargetdatasource "ref= "Datasourcemaster"/> <property name= "targetdatasources" > <map key-type= "net.aazj.enums. DataSources "> <entry key=" MASTER "value-ref=" Datasourcemaster "/> <entry key=" SLA VE "value-ref=" Datasourceslave "/> <!--Here you can also add more datasource--</map> </      property> </bean> <bean id= "sqlsessionfactory" class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property name= "DataSource" ref= "DataSource"/> <property name= "configlocation" value= "Classpath:config /mybatis-config.xml "/> <property name=" mapperlocations "value=" Classpath*:config/mappers/**/*.xml "/> &L T;/bean> <!--Transaction Manager for a single JDBC DataSource--<bean id= "TransactionManager" CLA ss= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name= "DataSource" ref= " DataSource "/> </bean> <!--using annotation to define transactions--<tx:annotation-driven transaction-manager= "TransactionManager"/> <bean class= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name= "basepackage" value= "net. Aazj.mapper "/> <!--<property name=" Sqlsessionfactorybeanname "value=" Sqlsessionfactory "/>--&lt         ;/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:

Package Net.aazj.aop;import Net.aazj.enums.datasources;import Net.aazj.util.datasourcetypemanager;import Org.aspectj.lang.joinpoint;import Org.aspectj.lang.annotation.aspect;import Org.aspectj.lang.annotation.Before; Import Org.aspectj.lang.annotation.pointcut;import org.springframework.stereotype.Component; @Aspect    //For Aop@component//For auto scan
@Order (0)//execute before @Transactionalpublic class Datasourceinterceptor {        @Pointcut ("Execution (public * Net.aazj.service. *.getuser (..)) ")    public void Datasourceslave () {};        @Before ("Datasourceslave ()") Public    Void before (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, the call Datasourcetypemanager.set (datasources.slave) sets the type of key to Datasources.slave, so DataSource is based on the key= Datasources.slave Choose Datasourceslave this datasource. Therefore, the method for the SQL statement will be executed on the slave database (by the Netizen old Liu 1987 reminder, there is a number of aspect between the execution order of the problem, must ensure that the switch data source aspect must be executed before @transactional this aspect, Therefore, @order (0) is used to ensure that the switching data source is executed prior to @transactional.

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.

6) Abstractroutingdatasource Principle Analysis

Threadlocalrountingdatasource inherits Abstractroutingdatasource, implements its abstract method protected abstract Object Determinecurrentlookupkey (); Thus, the routing function of different data sources is realized. We start from the source analysis of the principle of:

Public abstract class Abstractroutingdatasource extends Abstractdatasource implements Initializingbean

Implemented Initializingbean then spring invokes the Initializingbean interface when the bean is initialized
Let's look at how the Abstractroutingdatasource implements this interface:

    @Override public    void Afterpropertiesset () {        if (this.targetdatasources = = null) {            throw new IllegalArgumentException ("Property ' targetdatasources ' is required");        }        This.resolveddatasources = new Hashmap<object, datasource> (This.targetDataSources.size ());        For (Map.entry<object, object> entry:this.targetDataSources.entrySet ()) {            Object LookupKey = Resolvespecifiedlookupkey (Entry.getkey ());            DataSource DataSource = Resolvespecifieddatasource (Entry.getvalue ());            This.resolvedDataSources.put (LookupKey, DataSource);        }        if (This.defaulttargetdatasource! = null) {            This.resolveddefaultdatasource = Resolvespecifieddatasource ( This.defaulttargetdatasource);        }    }

The Afterpropertiesset method is to use the injected
Datasourcemaster and Datasourceslave to construct a hashmap--resolveddatasources. It is convenient to get the corresponding datasource from the map based on key later.
We are looking at the Connection getconnection () throws SQLException in the Abstractdatasource interface; How it's implemented:

    @Override public    Connection getconnection () throws SQLException {        return Determinetargetdatasource (). Getconnection ();    }

The key is Determinetargetdatasource (), according to the method name can be seen, it should be here to determine which dataSource to use:

    Protected DataSource Determinetargetdatasource () {        assert.notnull (this.resolveddatasources, "DataSource router Not initialized ");        Object LookupKey = Determinecurrentlookupkey ();        DataSource DataSource = This.resolvedDataSources.get (LookupKey);        if (DataSource = = null && (This.lenientfallback | | lookupkey = = NULL)) {            DataSource = This.resolveddefaultdata Source;        }        if (DataSource = = null) {            throw new IllegalStateException ("cannot determine target DataSource for lookup key [" + Look Upkey + "]");        }        return dataSource;    }

Object LookupKey = Determinecurrentlookupkey (); This method is implemented by us, where we get the key value saved in threadlocal. After the key has been obtained,
The resolveddatasources in this map, initialized from Afterpropertiesset (), gets the datasource corresponding to the key. And the key value saved in threadlocal
is done in an AOP manner before invoking the relevant methods in the service. OK, get it done!

7) Extended Threadlocalrountingdatasource

Above we just realized the choice of master-slave data source. If there are more than one master or multiple slave. Multiple master composed of one ha, to realize when one of the master hangs is, automatically switch to another master, this function can use lvs/keepalived to achieve, It can also be implemented by further extending the Threadlocalrountingdatasource, which can be implemented by adding a thread specifically to each second to test whether MySQL is normal. The same for multiple slave to achieve load balancing, while when a slave hangs, to realize it from the load balancer to remove, this function can be used lvs/keepalived to achieve, The same can be achieved by extending the threadlocalrountingdatasource in a step closer.

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