Spring implements database read/write separation (RPM)

Now large-scale e-commerce system, at the database level most of the use of read-write separation technology, is a master database, multiple slave database. Master Library is responsible for data update and real-time data query, slave library is of course responsible for non-real-time data query. 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.

The goal of using read-write separation technology is to reduce the pressure of master library effectively, and to distribute the requests of user query data to different slave libraries to ensure the robustness of the system. Let's look at the background with read and write separations .

As the business of the website expands continuously, the data increases unceasingly, the user is more and more, the pressure of the database is more and more big, uses the traditional way, for example: the database or the SQL optimization basically has not reached the request, this time may adopt the reading and writing separation strategy to change the present situation.

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 way is the most common way we define 2 database connections, one is Masterdatasource and the other is Slavedatasource. When we update the data we read Masterdatasource, we read the slavedatasourcewhen we query the data. This is a very simple way, I will not repeat it.

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 from the library. The main techniques used are:annotation, Spring AOP, reflection . The implementation method is described in detail below.

Before we introduce the implementation approach, we prepare some necessary knowledge, spring's Abstractroutingdatasource class

Abstractroutingdatasource This class is added after spring2.0, let's first look at the definition of abstractroutingdatasource :

Public abstract class Abstractroutingdatasource extends Abstractdatasource implements Initializingbean {}

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 InterfaceDataSourceextendsCommondatasource,wrapper {/*** <p>attempts to establish a connection with the data source that * this <code>datasource</code> ;   Object represents. *   * @returna connection to the data source *@exceptionSQLException If a database access error occurs*/Connection getconnection ()throwsSQLException; /*** <p>attempts to establish a connection with the data source that * this <code>datasource</code> ;   Object represents. *   * @paramusername The database user on whose behalf the connection is * being made *@parampassword The user ' s password *@returna connection to the data source *@exceptionSQLException If a database access error occurs *@since1.4*/Connection getconnection (string Username, string password)throwsSQLException;}

View Code

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 throws SQLException {        return  determinetargetdatasource (). getconnection ();    }      Public throws SQLException {        return  determinetargetdatasource (). getconnection (username, password);    }

View Code

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

protectedDataSource 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. Resolveddefaultdatasource; }        if(DataSource = =NULL) {            Throw NewIllegalStateException ("Cannot determine target DataSource for lookup key [" + LookupKey + "]"); }        returnDataSource; }

View Code

We are most concerned about the following 2 words:

Object LookupKey = Determinecurrentlookupkey ();
DataSource 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 into the map, as follows:

Key value

Master Masterdatasource

Slave Slavedatasource

We are writing a class Dynamicdatasource inheriting Abstractroutingdatasource, implementing its Determinecurrentlookupkey () method, which returns the Key,master or slave of the map.

Well, said so much, a little annoyed, below we see how to achieve.

We have already mentioned the technology we are going to use, so let's look at the definition of annotation:

@Retention (retentionpolicy.runtime) @Target (Elementtype.method)  public @Interface  DataSource {    String value ();}

View Code

We also need to implement spring's abstract class Abstractroutingdatasource, which is to implement the Determinecurrentlookupkey method:

 Public classDynamicdatasourceextendsAbstractroutingdatasource {@OverrideprotectedObject Determinecurrentlookupkey () {//TODO auto-generated Method Stub        returndynamicdatasourceholder.getdatasouce (); }} Public classDynamicdatasourceholder { Public Static Finalthreadlocal<string> holder =NewThreadlocal<string>();  Public Static voidPutdatasource (String name) {holder.set (name); }     Public StaticString getdatasouce () {returnHolder.get (); }}

View Code

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:

 Public classDatasourceaspect { Public voidbefore (Joinpoint point) {Object target=Point.gettarget (); String Method=point.getsignature (). GetName (); Class<?>[] Classz =Target.getclass (). Getinterfaces (); Class<?>[] Parametertypes =((methodsignature) point.getsignature ()). GetMethod (). Getparametertypes (); Try{Method m= Classz[0].getmethod (method, parametertypes); if(M! =NULL&& m.isannotationpresent (DataSource.class) {DataSource data=M. Getannotation (DataSource.class);                Dynamicdatasourceholder.putdatasource (Data.value ());            System.out.println (Data.value ()); }                    } Catch(Exception e) {//Todo:handle Exception        }    }}

View Code

For the convenience of testing, I have defined 2 databases, shop simulation Master Library, test simulation slave library, shop and test table structure consistent, but the data is different, the database configuration is as follows:

<bean id= "Masterdatasource"class= "Org.springframework.jdbc.datasource.DriverManagerDataSource" > <property name= "driverclassname" value= " Com.mysql.jdbc.Driver "/> <property name=" url "value=" Jdbc:mysql://127.0.0.1:3306/shop "/> <prop     Erty name= "username" value= "root"/> <property name= "password" value= "yangyanping0615"/> </bean> <bean id= "Slavedatasource"class= "Org.springframework.jdbc.datasource.DriverManagerDataSource" > <property name= "driverclassname" value= " Com.mysql.jdbc.Driver "/> <property name=" url "value=" Jdbc:mysql://127.0.0.1:3306/test "/> <prop             Erty name= "username" value= "root"/> <property name= "password" value= "yangyanping0615"/> </bean> <beans:bean id= "DataSource"class= "Com.air.shop.common.db.DynamicDataSource" > <property name= "targetdatasources" > <map ke Y-type= "Java.lang.String" > <!--write--<entry key= "master" value-ref= "Ma Sterdatasource "/> <!--read--<entry key=" slave "value-ref=" Slavedatasourc E "/> </map> </property> <property name=" Defaulttargetdata Source "ref=" Masterdatasource "/> </beans:bean> <bean id=" TransactionManager "class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name= "DataSource" ref= " DataSource "/> </bean> <!--configuration Sqlsessionfactorybean-<bean id=" Sqlsessionfactory "class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property name= "dataSource" ref= "DataSource"/> &LT;PR Operty name= "configlocation" value= "Classpath:config/mybatis-config.xml"/> </bean>

View Code

Add AOP configuration in spring configuration

<!--configuration database annotations AOP--    <aop:aspectj-autoproxy></aop:aspectj-autoproxy>    class= " Com.air.shop.proxy.DataSourceAspect "/>    <aop:config>        <aop:aspect id=" C "ref=" Manydatasourceaspect ">            <aop:pointcut id=" TX "expression=" Execution (* com.air.shop.mapper.*.* (..)) " />            <aop:before pointcut-ref= "TX" method= "before"/>        </aop:aspect>    </aop:config>    <!--configuration database annotations AOP--

View Code

The following is the definition of MyBatis usermapper, in order to facilitate testing, login reads the master library, the user list reads the slave library:

 Public Interfaceusermapper {@DataSource ("Master")     Public voidAdd (user user); @DataSource ("Master")     Public voidUpdate (user user); @DataSource ("Master")     Public voidDeleteintID); @DataSource ("Slave")     PublicUser Loadbyid (intID); @DataSource ("Master")     PublicUser loadbyname (String name); @DataSource ("Slave")     PublicList<user>list ();}

View Code

Well, run our eclipse to see the effect, enter the Username admin login to see the effect

　　

　　

As you can see, the data of the logged in user and user list is different, also verified our implementation, login read the master library, the user list reads the slave library.

Spring implements database read/write separation (RPM)