Spring configures Dynamic Data sources-read-write separations and multiple data sources

In the current Internet system, with the increase of user volume, the single data source is usually unable to meet the load requirements of the system. Therefore, in order to solve the pressure of the increase of user volume, the technology of read-write separation and database splitting will be adopted at the database level. Read-write separation is a master database, multiple slave databases, the master database is responsible for data write operations, Slave library is responsible for data read operations, through the slave library to reduce the load of the master library. Because in the actual application, the database is read more write less (the frequency of reading data is high, the frequency of updating data is relatively small), and the reading data is usually time-consuming, occupy the database server more CPU, thereby affecting the user experience. Our common practice is to extract queries from the main repository, using multiple slave libraries, and use load balancing to reduce the pressure on each query from the library. As the business grows, the database is split and the business-related database tables are split into different databases based on the business. Either read-write separation or database splitting is one of the main ways to resolve database stress. This article focuses on how spring configures read-write separation and multi-data source methods.

1. Read and write separation

Specific to the development, how to facilitate the realization of the separation of read and write? There are two ways to do it now:

1. The first is the most common way to define 2 database connections, one is Masterdatasource and the other is Slavedatasource. When working with a database, obtain datasource based on requirements and then manipulate the database through DataSource. This approach is simple to configure, but lacks flexibility to be new.
2. The second way of dynamic Data source switching is to dynamically weave the data source into the program while the program is running, thus choosing to read the main library or the library. The main techniques used are: annotation,spring AOP, Reflection. The implementation method is described in detail below.　

Before introducing the implementation approach, prepare some necessary knowledge of Spring's Abstractroutingdatasource class. Abstractroutingdatasource This class is added after spring2.0, let's first look at the definition of Abstractroutingdatasource:

　Abstractroutingdatasource inherits the Abstractdatasource and implements the Initializingbean, so Abstractroutingdatasource automatically initializes the instance at system startup.

Public abstract class Abstractroutingdatasource extends Abstractdatasource implements Initializingbean {private map< Object, object> targetdatasources;private object Defaulttargetdatasource;private datasourcelookup Datasourcelookup = new Jndidatasourcelookup ();p rivate map<object, datasource> resolveddatasources;private DataSource Resolveddefaultdatasource;        ...}

Abstractroutingdatasource inherits the Abstractdatasource, and Abstractdatasource is the subclass of DataSource. DataSource is the data source interface for Javax.sql, which is defined as follows:

Public interface DataSource  extends Commondatasource,wrapper {  Connection getconnection () throws SQLException ;  Connection getconnection (string Username, string password)    throws SQLException;}

　　The DataSource interface defines 2 methods, all of which obtain a database connection. We're looking at how the Abstractroutingdatasource implements the DataSource interface:

Public Connection getconnection () throws SQLException {return Determinetargetdatasource (). getconnection ();} Public Connection getconnection (string Username, string password) throws SQLException {return Determinetargetdatasource (). getconnection (username, password);}

　It is obvious that you call your own Determinetargetdatasource () method to get to connection. The Determinetargetdatasource method is defined as follows:

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;}

　We are most concerned about the following 2 words:　

Object LookupKey = Determinecurrentlookupkey ();D Atasource DataSource = This.resolvedDataSources.get (LookupKey);

The Determinecurrentlookupkey method returns the Lookupkey,resolveddatasources method by obtaining a data source from the map based on LookupKey. Resolveddatasources and Determinecurrentlookupkey are defined as follows:

Private Map<object, datasource> resolveddatasources;protected abstract Object determinecurrentlookupkey ()

　　See the above definition, we are not a bit of thinking, resolveddatasources is the map type, we can put Masterdatasource and slavedatasource to map. By writing a class Dynamicdatasource inherit Abstractroutingdatasource, implementing its Determinecurrentlookupkey () method, which returns the Key,master or slave of the map.

public class Dynamicdatasource extends abstractroutingdatasource{    @Override    protected Object Determinecurrentlookupkey () {        return databasecontextholder.getcustomertype ();     }}

　　Define Databasecontextholder

public class Databasecontextholder {    private static final threadlocal<string> Contextholder = new threadlocal& Lt String> ();       public static void Setcustomertype (String customertype) {          contextholder.set (customertype);      }      public static String Getcustomertype () {          return contextholder.get ();      }      public static void Clearcustomertype () {          contextholder.remove ();      }  }

From the definition of Dynamicdatasource, he returned the Dynamicdatasourceholder.getdatasouce () value, We need to call the Dynamicdatasourceholder.putdatasource () method when the program is running and assign it a value. Here is the core part of our implementation, the AOP section, Datasourceaspect defined as follows:

@Aspect @order (1) @Componentpublic class Datasourceaspect {    @Before (value = "Execution (* Com.netease.nsip.DynamicDataSource.dao. *.insert* (..)) "            + "|| Execution (* Com.netease.nsip.DynamicDataSource.dao). *.add* (..)) "            + "|| @org. springframework.transaction.annotation.Transactional * * (..) ")    Public Object before (Proceedingjoinpoint joinpoint) throws Throwable {        Databasecontextholder.setcustomertype (" Master ");        Object object = Joinpoint.proceed ();        Databasecontextholder.setcustomertype ("Slave");        return object;    }}

　For the convenience of testing, I have defined 2 databases, master library and Slave library, the person table structure in two libraries is identical, but the data is different, the properties file is configured as follows:

#commondb-driver=com.mysql.jdbc.driver#master master-url=jdbc:mysql://127.0.0.1:3306/master?servertimezone= Utcmaster-user=rootmaster-password=root#salveslave-url=jdbc:mysql://127.0.0.1:3306/slave?servertimezone= Utcslave-user=rootslave-password=root

　　The XML in spring is defined as follows:

<!--Configure Data source common parameters--><bean name= "Basedatasource" class= " Org.springframework.jdbc.datasource.DriverManagerDataSource "><property name=" Driverclassname ">< value>${db-driver}</value></property></bean><!--Configuring the primary data source--><bean name= " Masterdatasource "class=" Org.springframework.jdbc.datasource.DriverManagerDataSource "><property name=" url " ><value>${master-url}</value></property><property name= "username" ><value>${ Master-user}</value></property><property name= "Password" ><value>${master-password}</ value></property></bean><!--configuration from the data source--><bean name= "Slavuedatasource" class= " Org.springframework.jdbc.datasource.DriverManagerDataSource "><property name=" url "><value>${ Slave-url}</value></property><property name= "username" ><value>${slave-user}</value ></property><property name= "Password" ><value>${slave-password}</Value></property></bean><bean id= "DataSource" class= " Com.netease.nsip.DynamicDataSource.commom.DynamicDataSource "><property name=" Targetdatasources ">< Map key-type= "java.lang.String" ><entry key= "master" value-ref= "Masterdatasource"/><entry key= "Slave" value-ref= "Slavuedatasource"/></map></property><property name= "DefaultTargetDataSource" ref= " Slavuedatasource "/></bean><!--configuration Sqlsessionfactorybean--><bean id=" sqlSessionFactory "class=" Org.mybatis.spring.SqlSessionFactoryBean "><property name=" configlocation "value=" classpath: Sqlmapconfig.xml "/><property name=" DataSource "ref=" DataSource "/></bean><!--persistent layer access to templated tools, thread safety, Build sqlsessionfactory--><bean id= "sqlsessiontemplate" class= "Org.mybatis.spring.SqlSessionTemplate" >< Constructor-arg index= "0" ref= "sqlsessionfactory"/></bean><!--transaction manager--><bean id= "TxManager" class = "Org.springframework.jdbc.datasource.DataSOurcetransactionmanager "><property name=" DataSource "ref=" DataSource "/&GT;&LT;/BEAN&GT;&LT;TX: Annotation-driven transaction-manager= "Txmanager" proxy-target-class= "true" order= "$"/><!--rollback mode-->< Tx:advice id= "Txadvice" transaction-manager= "Txmanager" ><tx:attributes><tx:method name= "*" Rollback-for= "Throwable"/></tx:attributes></tx:advice><!--define @transactional annotations go to transaction manager-- <aop:config><aop:pointcut id= "Transactionpointcuttype" expression= "@within ( org.springframework.transaction.annotation.Transactional) "/><aop:pointcut id=" Transactionpointcutmethod " Expression= "@annotation (org.springframework.transaction.annotation.Transactional)"/><aop:advisor advice-ref= "Txadvice" pointcut-ref= "Transactionpointcuttype"/><aop:advisor advice-ref= "TxAdvice" pointcut-ref= "Transactionpointcutmethod"/></aop:config>

　　Until now the read and write separation has been configured, all the DAO layers with insert and add, and the main library with transaction annotations, the other database operations go from the library. Of course, you can also modify pointcut expressions to let the update and delete methods go to the main library. The above method is an AOP-based read-write separation configuration, which uses an example with annotations to describe the configuration of a multi-data source.

2. Multi-Data Source configuration

The above example uses AOP to configure the read-write separation, which is followed by configuring a multi-data source with spring annotations, which can also be used to configure read-write separations. First Look at the definition of annotation:

@Retention (retentionpolicy.runtime) @Target (elementtype.method) public @interface profile {    String value ();}

　　Define Multidatasourceaspect, where Multidatasourceaspect gets the data source based on the annotations.

public class Multidatasourceaspect {public    void before (Joinpoint joinpoint) throws Throwable {        Object target = Jo Inpoint.gettarget ();          String method = Joinpoint.getsignature (). GetName ();          class<?>[] Classz = Target.getclass (). Getinterfaces ();            Class<?>[] Parametertypes = ((methodsignature) joinpoint.getsignature ()).                GetMethod (). Getparametertypes ();          try {              method M = Classz[0].getmethod (method, parametertypes);              if (M! = null&&m.isannotationpresent (Profile.class)) {profile                  data = M.  getannotation (profile.class );                  Databasecontextholder.setcustomertype (Data.value ());              }                } catch (Exception e) {          }}    }

Also for testing, the data source properties file is as follows:

#commondb-driver=com.mysql.jdbc.driver#master account-url=jdbc:mysql://127.0.0.1:3306/master?servertimezone= Utcaccount-user=rootaccount-password=root#salvegoods-url=jdbc:mysql://127.0.0.1:3306/slave?servertimezone= Utcgoods-user=rootgoods-password=root

　　The XML file for spring is defined as follows:

<!--Configure Data source common parameters--><bean name= "Basedatasource" class= " Org.springframework.jdbc.datasource.DriverManagerDataSource "><property name=" Driverclassname ">< value>${db-driver}</value></property></bean><!--Configuring the primary data source--><bean name= " Accountdatasource "class=" Org.springframework.jdbc.datasource.DriverManagerDataSource "><property name=" url "><value>${account-url}</value></property><property name=" username "><value>${ Account-user}</value></property><property name= "Password" ><value>${account-password} </value></property></bean><!--configuration from the data source--><bean name= "Goodsdatasource" class= " Org.springframework.jdbc.datasource.DriverManagerDataSource "><property name=" url "><value>${ Goods-url}</value></property><property name= "username" ><value>${goods-user}</value ></property><property name= "Password" &GT;&LT;VALUE&GT;${GOODS-PASSWORD}&LT;/value></property></bean><bean id= "DataSource" class= " Com.netease.nsip.DynamicDataSource.commom.MultiDataSource "><property name=" Targetdatasources "><map Key-type= "java.lang.String" ><entry key= "goods" value-ref= "Goodsdatasource"/><entry key= "account" value-ref= "Accountdatasource"/></map></property></bean><!--configuration Sqlsessionfactorybean-- ><bean id= "Sqlsessionfactory" class= "Org.mybatis.spring.SqlSessionFactoryBean" ><property name= " Configlocation "value=" Classpath:multiSqlMapConfig.xml "/><property name=" DataSource "ref=" DataSource "/> </bean><!--Persistent layer access to templated tools, thread safety, build sqlsessionfactory--><bean id= "sqlsessiontemplate" class= " Org.mybatis.spring.SqlSessionTemplate "><constructor-arg index=" 0 "ref=" sqlsessionfactory "/></bean ><!--Configuring AOP--><bean id= "Multiaspect" class= " Com.netease.nsip.DynamicDataSource.commom.MultiDataSourceAspect "/><aop:config><aop:aspect ID= "Datasourceaspect" ref= "Multiaspect" ><aop:pointcutexpression= "Execution (* Com.netease.nsip.DynamicDataSource.dao. *.insert* (..)) " Id= "TX"/><aop:before pointcut-ref= "TX" method= "Before"/></aop:aspect></aop:config>

　　The DAO layer interface is defined as follows:

Public interface Iaccountdao {    @Profile (' account ') public    boolean insert (Accounts Accounts);} Public interface Igoodsdao {    @Profile ("goods") public    Boolean insert (goods goods);  }

　　The main way spring configures a multi-data source is as shown above, in the case where the choice of the data source is made in DAO. In the process of daily development, the transaction is usually at the service layer, and the transaction is bound to the data source, so in order to use the transaction at the service layer, the choice of the data source can be made at the service layer.

Spring configures Dynamic Data sources-read-write separations and multiple data sources