Spring implements database read-write separation

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 Slavedatasource when 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 interface DataSource extends Commondatasource,wrapper {/** * <p>att   Empts to establish a connection with the data source, that * this <code>DataSource</code> object represents. * * @return A connection to the data source * @exception SQLException If a database access error occurs */Conne  Ction getconnection () throws SQLException;  /** * <p>attempts to establish a connection with the data source that * this <code>DataSource</code>   Object represents. * * @param username The database user on whose behalf the connection is * being made * @param password the user s p Assword * @return A connection to the data source * @exception SQLException If a database access error occurs * @si NCE 1.4 */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 ();
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 ();}

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

public class Dynamicdatasource extends Abstractroutingdatasource {    @Override    protected Object Determinecurrentlookupkey () {        //TODO auto-generated method stub        return dynamicdatasourceholder.getdatasouce ();    }} public class Dynamicdatasourceholder {public    static final threadlocal<string> holder = new threadlocal< String> ();    public static void Putdatasource (String name) {        holder.set (name);    }    public static String Getdatasouce () {        return holder.get ();}    }

    As seen from the definition of Dynamicdatasource, he returns 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 class Datasourceaspect {public    void before (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        }}    }

    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" > <p Roperty name= "Driverclassname" value= "Com.mysql.jdbc.Driver"/> <property name= "url" value= "jdbc:mysql://127. 0.0.1:3306/shop "/> <property name=" username "value=" root "/> <property name=" password "value=" y angyanping0615 "/> </bean> <bean id=" Slavedatasource "class=" Org.springframework.jdbc.datasource. Drivermanagerdatasource "> <property name=" driverclassname "value=" Com.mysql.jdbc.Driver "/> <pro        Perty name= "url" value= "jdbc:mysql://127.0.0.1:3306/test"/> <property name= "username" value= "root"/> <property name= "Password" value= "yangyanping0615"/> </bean> <beans:bean id= "DataSource" class= "Com.air.shop.common.db.DynamicDataSource" > <property name= "targetdatasources" > <m AP Key-type= "Java.lang.sTring "> <!--write--<entry key=" master "value-ref=" Masterdatasource "/&gt                   ;                        <!--read--<entry key= "slave" value-ref= "Slavedatasource"/> </map>      </property> <property name= "Defaulttargetdatasource" ref= "Masterdatasource"/> </beans:bean> <bean id= "TransactionManager" class= "Org.springframework.jdbc.datasource.DataSourceTran Sactionmanager "> <property name=" dataSource "ref=" DataSource "/> </bean> <!--configuration sqlsessionf Actorybean--<bean id= "sqlsessionfactory" class= "Org.mybatis.spring.SqlSessionFactoryBean" > <prope Rty name= "DataSource" ref= "DataSource"/> <property name= "configlocation" value= "Classpath:config/mybatis-con Fig.xml "/> </bean>

Add AOP configuration in spring configuration

<!--configuration database annotations AOP--    <aop:aspectj-autoproxy></aop:aspectj-autoproxy>    <beans:bean id= " Manydatasourceaspect "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--

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 interface Usermapper {    @DataSource ("master") public    void Add (user user);    @DataSource ("master") public    void Update (user user);    @DataSource ("master") public    void Delete (int id);    @DataSource ("slave") public    User Loadbyid (int id);    @DataSource ("master") public    User loadbyname (String name);        @DataSource ("slave") public    list<user> List ();}

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 Master library, user list read slave library

Spring implements database read-write separation