Spring Dynamic Switching data source multiple database __ Database

1. BackgroundA single MySQL database is supported for a data volume of 10 million, but if the amount of data is greater than this number, such as 100 million, then the performance of the query can be very low. At this point need to do horizontal segmentation of the database, the common practice is to follow the user's account to hash, and then select the corresponding database. Horizontal segmentation diagram, data falling into different Couchen
2. To achieve 2.1 schematic

Let's look at the rough schematic:

-Figure 1 is a more common scenario where a single database
-Figure 2 shows a middle tier between the Web application and the database, and the middle tier chooses which database to use. 2.2 Database Configuration First we need to configure more than one data source, I am using XML to configure the other methods are similar, that is, the creation of a few more beans.

    <bean id= "Parentdatasource" abstract= "true" class= "Org.apache.tomcat.jdbc.pool.DataSource" dest 
          Roy-method= "Close" p:maxwait= "10000" p:removeabandoned= "true" p:removeabandonedtimeout= "180" P:connectionproperties= "Clientencoding=utf-8" p:validationquery= "SELECT 1" p:validationinte
          Rval= "30000" p:testonborrow= "false" p:testonreturn= "false" p:testwhileidle= "true"
          P:timebetweenevictionrunsmillis= "10000" p:minevictableidletimemillis= "60000" p:logabandoned= "false" P:defaultautocommit= "true"/> <bean id= "DataSource" parent= "Parentdatasource" P:driverclassna Me= "Com.mysql.jdbc.Driver" P:username= "${jdbc.user}" p:password= "${jdbc.password}" p:initial Size= "p:maxactive=" p:maxidle= "p:minidle=" 5 "/> <bean id=" Childdataso Urce1 "Parent=" DatasourcE "> <property name=" url "value=" ${jdbc.url1} "/> </bean> <bean id=" ChildDataSource2 "pa
 Rent= "DataSource" > <property name= "url" value= "${jdbc.url2}"/> </bean>

Here I built two data source bean IDs are childdatasource1,childdatasource2, for convenience, only the database URL is different, ancient capital inherited datasource. 2.3 Java Implementation

First put the multiple database beans defined, and look at the support for dynamic selection data sources in spring. There is an abstract class Abstractroutingdatasource class in spring that enables you to implement a dynamic selection of data sources. Look at the member variables for this class.

    Private Map<object, object> targetdatasources;
    Private Object Defaulttargetdatasource;
    Private Map<object, datasource> resolveddatasources;

The mapping relationship between key and database connection is saved in Targetdatasources, Defaulttargetdatasource represents the default link, Resolveddatasources This data structure is constructed by targetdatasources, and the storage structure is also a mapping relationship between the database identity and the data source. The following needs to inherit the Abstractroutingdatasource class, implement our own database selection logic Datasourceswitcher class, first code:

public class Datasourceswitcher extends abstractroutingdatasource{

    private static final Logger Logger = Loggerfactory.getlogger ("Interactive_logger");

    private static final threadlocal<string> Datasourcekey = new threadlocal<string> ();


    public static void Cleardatasourcetype () {
        logger.debug ("thread:{},remove,datasource:{}", Thread.CurrentThread ( ). GetName ());
        Datasourcekey.remove ();
    }

    @Override
    protected Object Determinecurrentlookupkey () {
        String s = datasourcekey.get ();
        Logger.debug ("thread:{},determine,datasource:{}", Thread.CurrentThread (). GetName (), s);
        return s;
    }

    public static void Setdatasourcekey (String dataSource) {
        logger.debug ("thread:{},set,datasource:{}") Thread.CurrentThread (). GetName (), dataSource);
        Datasourcekey.set (DataSource);
    }

Line 5th, the threadlocal member variable datasource (because the data source required for different requests may not be the same), is used to store the data source identity. Line 8th, clear the data source action. Line 14th, which determines which database you need to use, is an abstract method that must be implemented by us, so now let's see how this method is used

    Protected DataSource Determinetargetdatasource () {
        assert.notnull (this.resolveddatasources, "DataSource router Not initialized ");
        Object LookupKey = Determinecurrentlookupkey ()//Here Gets the database identity
        DataSource DataSource = This.resolvedDataSources.get ( LookupKey)//Get specific data source
        if (DataSource = = null && (This.lenientfallback | | | lookupkey = = NULL)) {
            DataSource = This.resolveddefaultdatasource;
        }
        if (DataSource = = null) {
            throw new IllegalStateException ("cannot determine target DataSource for lookup key [] + look Upkey + "]");
        }
        return dataSource;
    }

So we need to return the database ID in the Determinecurrentlookupkey method to the 20th row, set the data source method. 2.4 The combination of database configuration and Datasourceswitcher class now merges our previous database configuration and Datasourceswitcher, and I add the following configuration to the XML configuration of the database:

    <bean id= "Datasourceswitcher" class= "Com.netease.mail.activity.service.switcher.DataSourceSwitcher" > ;p roperty name= "targetdatasources" > <map> <entry key= "DS1" value-ref= "Childdatasou" Rce1 "/> <entry key= ds2" value-ref= "ChildDataSource2"/> </map> </pro perty> <property name= "Defaulttargetdatasource" ref= "ChildDataSource1"/> </bean> <bean Id= "TransactionManager" class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > ;p roperty name= "DataSource" ref= "Datasourceswitcher"/> </bean> <bean id= "sqlsessionfactory" class= "O Rg.mybatis.spring.SqlSessionFactoryBean "> <property name=" configlocation "value=" classpath:mybatis/sql-map- Config.xml "/> <property name= mapperlocations" value= "Classpath:mybatis/mapper/*.xml"/> Erty name= "DataSource" ref= "Datasourceswitcher"/> </bean> 

As you can see, I have initialized the targetdatasources, DS1 corresponding to the data source childdatasource1;ds2 corresponding to the data source ChildDataSource2. When used, just call Datasourceswitcher.setdatasourcekey ("DS1"), the data source is switched to the ChildDataSource1。 2.5 increase slice processingIt is a hassle to set the data source for each method of execution, and we need to select the database after hashing a key, which is not implemented. These two issues can now be solved with the help of the spring section. The general idea is as follows:
2.5.1 Custom AnnotationsDefine useDataSource annotations

/**
 * Data source annotation
 * Created by Hzlaojiaqi on 2017/12/26.
 */
@Target (Elementtype.method)
@Retention (retentionpolicy.runtime) public
@interface useDataSource {

     /**
      * Data Source
      * @return * * *
     datasourcetype value () default datasourcetype.source_1;


     /**
      * Whether to use HashKey, if true, use the hash value of the corresponding field to compute, select the data source,
      * and the specified {@link DatasourceType} does not work
      * @return
     boolean Usehashkey () default false;

DatasourceType is an enumeration type, as follows

@Getter public
enum DatasourceType {
    source_1 ("DS1", "Data source 1-default data Source"),
    source_2 ("Ds2", "Data source 2");

    DatasourceType (string source, String desc) {
        this.source = source;
        THIS.DESC = desc;
    }

    String Source;

    String desc;

    *
     * @param hashkey
     * @return
    /public static string Getbykey (String hashkey) {
        // According to HashKey to obtain the required data source
        int i = Math.Abs (Hashkey.hashcode ())% datasourcetype.values (). length;
        return Datasourcetype.values () [I].getsource ();
    }

}

Usehashkey whether to use HashKey. defines the Dskey annotation, which is used to annotate the corresponding method variable to indicate a hash of the variable's value.

/**
 * *
 data Source Selection Note
 * is used on parameters, indicating that the hashcode of the corresponding field is used to select the database
 * Created by Hzlaojiaqi on 2017/12/26.
 */
@Target (elementtype.parameter)
@Retention (retentionpolicy.runtime) public
@interface Dskey {

    String value () default "";
}

2.5.2 AOP for interceptionBecause only the usedatasource annotation needs to be intercepted, the pointcut can be set as follows

   @Pointcut ("@annotation (Com.netease.mail.activity.aop.annotation.UseDataSource)") Public
    void useDataSource () {
    

The flowchart shows the following

The Pointcut processing around method is as follows:

/** * @param joinpoint * @return * @throws throwable/@Around ("UseD Atasource () && @annotation (anno)) public Object Datasourceswitcher (Proceedingjoinpoint joinpoint, Usedatasou
        Rce anno) throws Throwable {String ds= ""; If you are using HashKey, select the data source based on HashKey if (Anno.usehashkey ()) {Ds=datasourcetype.getbykey Gethashkeyfrommethod (
        Joinpoint));
            }else{//Direct access to the data source DatasourceType value = Anno.value ();
        Ds=value.getsource ();
        //Set Data source Datasourceswitcher.setdatasourcekey (DS);
            try {//execution method Object result = Joinpoint.proceed ();
        return result;
        }catch (Exception e) {throw e; }finally {//Switch back to the original data source (important) Datasourceswitcher.setdatasourcekey (DataSourceType.SOURCE_1.getSource
        ()); }
    }

The Gethashkeyfrommethod method obtains the value of the variable that is annotated with @dskey, as follows:

    /**
     * @param joinpoint
     * @return
    /Public String Gethashkeyfrommethod (Proceedingjoinpoint joinpoint ) {
        methodsignature signature=methodsignature.class.cast (Joinpoint.getsignature ());
        Method method = Signature.getmethod ();
        object[] args = Joinpoint.getargs ();
        parameter[] Declaredfields = Method.getparameters ();
        int index=0;
        for (Parameter temp:declaredfields) {
            annotation[] annotations = Temp.getannotations ();
            for (Annotation antemp:annotations) {
                if (antemp instanceof Dskey) {return
                    string.valueof (Args[index));
                }
            }
            index++;
        }
        throw new RuntimeException ("Can not get field with @DsKey annotation");
    }

3. Use Let's look at the actual effect, define two controller, insert with a fixed dasrouce.source_1,insert2 with a UID hash value for the data source selection.

    @RequestMapping (value = "/ajax/insert.do", method = Requestmethod.get) @ResponseBody @UseDataSource (Datasourcet Ype. source_1) public ajaxresult Insert (@RequestParam String uid, httpservletrequest httpservletrequest) {Webcoupon
        Winner webcouponwinner=new Webcouponwinner ();
        Webcouponwinner.setuid (UID);
        Webcouponwinner.setinserttime (Timeutil.now ());
        Webcouponwinnerdao.insert (Webcouponwinner);
    return new Ajaxresult (retcode.success); @RequestMapping (value = "/ajax/insert2.do", method = Requestmethod.get) @ResponseBody @UseDataSource (Usehash
        Key = true) public Ajaxresult insert2 (@RequestParam @DSKey String uid, httpservletrequest httpservletrequest) {
        Webcouponwinner webcouponwinner=new Webcouponwinner ();
        Webcouponwinner.setuid (UID);
        Webcouponwinner.setinserttime (Timeutil.now ());
        Webcouponwinnerdao.insert (Webcouponwinner);
    return new Ajaxresult (retcode.success); }

The execution/ajax/insert.do,uid 1 and 2 respectively, and the following results are obtained

Two records are in the same database

Then execute/ajax/insert2.do,uid 1 and 2 respectively, and get the following results

And

The two records are in different libraries and are in line with expectations.

Complete datasourceasp, you need to be aware that the slice must be set before the transaction annotation @transactional, because the data source needs to be determined before the transaction is started , so the @Order of the datasourceasp ( ORDERED.LOWEST_PRECEDENCE-1), @Transactional order is the minimum value

Package COM.NETEASE.MAIL.ACTIVITY.AOP;
Import Com.netease.mail.activity.aop.annotation.DSKey;
Import Com.netease.mail.activity.aop.annotation.UseDataSource;
Import Com.netease.mail.activity.aop.type.DataSourceType;
Import com.netease.mail.activity.exception.custom.BizException;
Import Com.netease.mail.activity.service.complex.MonitorService;
Import Com.netease.mail.activity.service.switcher.DataSourceSwitcher;
Import lombok.extern.slf4j.Slf4j;
Import Org.aspectj.lang.ProceedingJoinPoint;
Import Org.aspectj.lang.annotation.Around;
Import Org.aspectj.lang.annotation.Aspect;
Import Org.aspectj.lang.annotation.Pointcut;
Import Org.aspectj.lang.reflect.MethodSignature;
Import org.springframework.beans.factory.annotation.Autowired;
Import org.springframework.core.Ordered;
Import Org.springframework.core.annotation.Order;

Import org.springframework.stereotype.Component;
Import java.lang.annotation.Annotation;
Import Java.lang.reflect.Method;

Import Java.lang.reflect.Parameter; /** * * DataSOURCE Switch * Created by Hzlaojiaqi on 2017/12/26. * * @Component @Aspect @Slf4j (topic = "Thirdparty_logger") @Order (ordered.lowest_precedence-1) public class

    datasourceasp {@Autowired Monitorservice mmonitor; /** * for all mapped * * * * @Pointcut ("@annotation (Com.netease.mail.activity.aop.annotation.UseDataSource)") Pu Blic void useDataSource () {}/** * @param joinpoint * @return * @throws throwable/@Ar Ound ("useDataSource () && @annotation (anno)") Public Object Datasourceswitcher (Proceedingjoinpoint joinpoint,
        useDataSource anno) throws Throwable {String ds= "";
        if (Anno.usehashkey ()) {Ds=datasourcetype.getbykey (Gethashkeyfrommethod (joinpoint));
            }else{DatasourceType value = Anno.value ();
        Ds=value.getsource ();
        } datasourceswitcher.setdatasourcekey (DS);
            try {Object result = Joinpoint.proceed ();
     return result;   }catch (Exception e) {throw e;
        }finally {Datasourceswitcher.setdatasourcekey (DataSourceType.SOURCE_1.getSource ()); }/** * @param joinpoint * @return/Public String Gethashkeyfrommethod (proceedingjoinpoin
        T joinpoint) {methodsignature signature=methodsignature.class.cast (joinpoint.getsignature ());
        Method method = Signature.getmethod ();
        object[] args = Joinpoint.getargs ();
        parameter[] Declaredfields = Method.getparameters ();
        int index=0;
            for (Parameter temp:declaredfields) {annotation[] annotations = temp.getannotations (); for (Annotation antemp:annotations) {if (antemp instanceof Dskey) {return string.valueof
                (Args[index]);
        }} index++;
    } throw new Bizexception ("Can not get field with @DsKey annotation");
 }


}