Spring Multi-data source management in SaaS mode

When developing a SaaS program, each company is required to use a single database, how to configure the data source when using spring so that each company uses a different database connection?

1, the company ID is stored in a threadlocal variable, each time the request is set, so that each access to the data source can be obtained from the threadlocal the current request to the company ID.

/** * The data used to hold the current thread */public class Threadholder {//company idprivate static threadlocal<string> Localdid = new THREADLOCAL&L T String> ();p ublic static void putsp (String sp) {localdid.set (sp);} public static String getsp () {if (localdid.get () = = null) return ""; Elsereturn (String) Localdid.get ();}}

2. Call the Threadholder.putsp method on each request to set the company ID.
You can define a filter that is invoked in the Dofilter method to achieve the effect of each request setting. As for the company ID parameter where the client request is placed, this can be set flexibly and can be placed in each request parameter or in a cookie.


3. Modify the spring data source configuration
Original configuration (typically using the C0P3 data source configuration)

<bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" ><property name= "Driverclass" Value= "xxxx"/><property name= "Jdbcurl" value= "xxx"/><property name= "user" value= "user"/>< Property name= "Password" value= "password"/><property name= "Minpoolsize" value= "1"/><property name= " Maxpoolsize "value=" "/><property name=" MaxIdleTime "value=" 25000 "/><property name=" acquireIncrement "Value=" 1 "/><property name=" maxstatements "value=" 0 "/><property name=" initialpoolsize "value=" 100 "/ ><property name= "Idleconnectiontestperiod" value= "18000"/><property name= "acquireRetryAttempts" value= "Ten"/><property name= "Acquireretrydelay" value= "" "/><property name=" breakafteracquirefailure "value = "false"/><property name= "checkouttimeout" value= "10000"/><property name= "Testconnectiononcheckout" Value= "false"/></bean>

Modified to:

    <bean id= "DataSource" class= "Com.sangfor.frame.multiclient.MultiClientDataSource" > <property name= "D Atasource "> <ref bean=" datasourcedefault "/> </property> </bean> <bea n id= "Datasourcedefault" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" ><property name= "Driverclass" Value= "xxxx"/><property name= "Jdbcurl" value= "xxx"/><property name= "user" value= "user"/>< Property name= "Password" value= "password"/><property name= "Minpoolsize" value= "1"/><property name= " Maxpoolsize "value=", "/><property name=" MaxIdleTime "value=",/><property name= "AcquireIncrement" Value= "1"/><property name= "maxstatements" value= "0"/><property name= "initialpoolsize" value= "+"/> <property name= "Idleconnectiontestperiod" value= "" "/><property name=" acquireretryattempts "value=" 10 "/ ><property name= "Acquireretrydelay" value= "$"/><property name= "Breakafteracquirefailure" value= "false"/><property name= "checkouttimeout" value= "10000"/></bean> 

Multiclientdatasource:

public class Multiclientdatasource implements DataSource {public static final String Dbname_prefix = "Client_";p rivate Com Bopooleddatasource DataSource = null;private map<string,datasource> dsMap = new hashmap<string,datasource> ();p rivate static Object Lock=new object (); Public DataSource AddDatasource (String do) {try{synchronized (lock) {if (dsmap==null) DsMap = new hashmap<string, Datasource> ();D atasource ds = Dsmap.get (did); if (ds! = null) return DS; Combopooleddatasource Newds = Getnewdatasource (did);d Smap.put (did, Newds);}} catch (Exception e) {e.printstacktrace ();} Return Dsmap.get (did);} public void Removedatasource (String do) {if (dsmap==null) Return;dsmap.remove (did);} Private Combopooleddatasource Getnewdatasource (String did) throws Exception{combopooleddatasource ds = new Combopooleddatasource ();d s.setdriverclass ("xxx")//driver classstring jdbcurl = "jdbc:mysql://127.0.0.1"// Typically IP is configured in Profile + ": 3306" + "/" + Dbname_prefix + did+ "? Useunicode=true&characterencoding=utf-8";d S.setjdbcurl (Jdbcurl);d s.setuser (" user ");d s.setpassword (" password ");d s.setminpoolsize (1);d s.setmaxpoolsize ( S.setmaxidletime);d;d s.setacquireincrement (1);d s.setmaxstatements (0);d s.setinitialpoolsize (100); Ds.setidleconnectiontestperiod (1800);d s.setacquireretryattempts (+);d s.setacquireretrydelay (1000); Ds.setbreakafteracquirefailure (False);d S.setcheckouttimeout (10000);d s.setpreferredtestquery ("Select FORMID from Flow_form WHERE 1 = 2 ");d S.settestconnectiononcheckout (false); return DS;} private void Initdsmap () throws Exception{try{if (dsmap==null) {dsMap = new hashmap<string,datasource> ();}} catch (Exception e) {throw new Exception (e);}} Public Connection getconnection () throws SQLException {return Getdatasource (). getconnection ();} Public Connection getconnection (string arg0, String arg1) throws SQLException {return Getdatasource (). getconnection ( ARG0, arg1);} Public PrintWriter Getlogwriter () throws SQLException {return Getdatasource (). Getlogwriter ();} public int getlogintimeout ()Throws SQLException {return Getdatasource (). Getlogintimeout ();} public void Setlogwriter (PrintWriter arg0) throws SQLException {Getdatasource (). Setlogwriter (arg0);} public void setlogintimeout (int arg0) throws SQLException {Getdatasource (). setLoginTimeout (arg0);} Public DataSource Getdatasource (String datasourcename) throws SQLException {try{if (datasourcename==null| | Datasourcename.equals ("")) {return this.datasource;} Else{datasource ds = Dsmap.get (DataSourceName); if (ds!=null) return Ds;elsereturn null;}} catch (Nosuchbeandefinitionexception ex) {throw new SQLException ("There is not the DataSource <name:" +datasourcename+ "> in the Applicationcontext!");}} public void Setdatasource (Combopooleddatasource dataSource) {this.datasource = DataSource;} Public Combopooleddatasource Getdatasource () throws sqlexception{string do = THREADHOLDER.GETSP ();D atasource ds = Getdatasource (did); if (ds = = null) return Null;elsereturn (Combopooleddatasource) DS;} Public DataSource Getdefaultdatasource () throws SQLEXception{return DataSource;} @Overridepublic boolean iswrapperfor (class<?> arg0) throws SQLException {//TODO auto-generated method Stubreturn F Alse;} @Overridepublic <T> T Unwrap (class<t> arg0) throws SQLException {//TODO auto-generated method Stubreturn null ;}}

Implementation principle:
Create a new Multiclientdatasource class (implement DataSource interface) to replace the original Combopooleddatasource injected DataSource, The essence of the Multiclientdatasource class is to use a map, with the company ID (did) as key,value as connection, to determine in the Getconnection method whether the did key already exists in the map, If not, create a new connection and add it to the map

Spring Multi-data source management in SaaS mode