Dynamically add data sources to switch databases based on user logins. Programmatic spring transactions.

With spring data source routing, it seems that there is no way to add data sources directly, but only with reflection.

Add user data source in spring Security userdetailservice.loaduserbyusername when user login is successful

/* *             * Add user data source              *             /Routingdatasource.adddatasource (userid);

    /** * Create data sources based on user*/     Public voidAddDatasource (String userid) {if(Stringutils.isblank (userid))return; DbInfo DbInfo=Getdbinfoservice (). Getdbinfobyuserid (userid); Try{Field targetdatasources= Abstractroutingdatasource.class. Getdeclaredfield ("targetdatasources"); Field resolveddatasources= Abstractroutingdatasource.class. Getdeclaredfield ("resolveddatasources"); Targetdatasources.setaccessible (true); Resolveddatasources.setaccessible (true); Map<object, object> datasources = (map<object, object>) targetdatasources.Get( This); if(datasources.Get(Userinfo.getid (). toString ())! =NULL)                return; Map<object, datasource> dataSources2 = (map<object, datasource>) resolveddatasources.Get( This); Druiddatasource DDS=NewDruiddatasource (); Dds.seturl ("jdbc:mysql://"+ dbinfo.getdbaddr () +":"+ dbinfo.getdbport () +"/"+ dbinfo.getdbname () +"?useunicode=true&characterencoding=utf-8&zerodatetimebehavior=converttonull& Transformedbitisboolean=true&usessl=true");            Dds.setusername (Dbinfo.getusername ());            Dds.setpassword (Dbinfo.getpwd ());            Datasources.put (UserID, DDS);        Datasources2.put (UserID, DDS); } Catch(nosuchfieldexception e) {e.printstacktrace (); } Catch(illegalaccessexception e) {e.printstacktrace (); }    }

Added the data source, of course, need to delete, can be in the session listener, destroy the session when deleted

    /*** Delete data source based on user*/     Public voidRemovedatasource (String userid) {if(Stringutils.isblank (userid))return; Try{Field targetdatasources= Abstractroutingdatasource.class. Getdeclaredfield ("Targetdatasources"); Field resolveddatasources= Abstractroutingdatasource.class. Getdeclaredfield ("Resolveddatasources"); Targetdatasources.setaccessible (true); Resolveddatasources.setaccessible (true); Map<object, object> datasources = (map<object, object>) Targetdatasources.get ( This); if(Datasources.get (Userinfo.getusrno ())! =NULL) {Map<object, datasource> dataSources2 = (map<object, datasource>) Resolveddatasources.get ( This);                Datasources.remove (userid);            Datasources2.remove (userid); }        } Catch(nosuchfieldexception e) {e.printstacktrace (); } Catch(illegalaccessexception e) {e.printstacktrace (); }    }

Annotation plus AOP Toggle data source

Annotations

/***/@Target (Elementtype.method) @Retention (retentionpolicy.runtime)public @Interface  switchdatasource {}

Spring AOP, the new version of SPRINGAOP can be a good way to cut into the listener, because the listener can be managed by the spring container, in disguise to strengthen the SPRINGAOP, so that no need to use the native ASPECTJ

/*** Created by IS on 2017-4-27.*/@Component @aspect@order (0)//When configuring Spring annotation transactions, switch the data source before the transaction Public classSWITCHDATASOURCEASPECTJ {//Defining Pointcuts@Pointcut ("@annotation (Com.lzw.common.annotation.SwitchDataSource)")     Public voidSwitchdatasource () {} @Around ("Switchdatasource ()")     PublicObject Arounduserdatasource (proceedingjoinpoint joinpoint) {datasourcecontextholder.user (); Try {            returnjoinpoint.proceed (); } Catch(Throwable throwable) {throwable.printstacktrace (); }finally{datasourcecontextholder.write (); }        return NULL; }}

This allows you to add a callout switch data source on a method ( note the order of annotations for transactions and switching data sources ), but if you need to switch to a different data source to query the data multiple times in one method, you will consume many connections, in order to better control the number of database connections, you need to use spring transaction

Programmatic Spring transactions

Inject TransactionManager

    @Resource    private Platformtransactionmanager Platformtransactionmanager;

Start transaction, open transaction is for database connection reuse, individual database per user, access is not large, so no connection pool is configured

     //to save the number of connections, try to get the required data in one switchDatasourcecontextholder.user (); //Transactiontemplate must come out every time, cannot use Spring Singleton injection, the set of data will always exist.Transactiontemplate transactiontemplate =Newtransactiontemplate (Platformtransactionmanager);        Transactiontemplate.setpropagationbehavior (Propagation.REQUIRES_NEW.value ()); Transactiontemplate.execute (NewTransactioncallbackwithoutresult () {@Override Public voidDointransactionwithoutresult (transactionstatus status) {//Database Operation code            }        }); Datasourcecontextholder.write ();

Dynamically add data sources to switch databases based on user logins. Programmatic spring transactions.