Processing of spring multi-data sources mybatis for cross-Database Query

From: http://www.javaeye.com/topic/931843

Implement myibatis dynamic SQL cross-database processing
Spring dynamically configures multiple data sources, that is, splitting data in large applications and managing multiple database instances, which can effectively improve the horizontal scalability of the system. This solution is different from the common single data instance solution, in this case, the program dynamically determines the database instance in which the data is stored and the database from which the data is extracted based on the current request and system status.
Spring configures multiple data sources and the specific usage process.
Spring uses database tables as a reference for multiple data sources and can be divided into two categories:
First, cross-database at the table level. That is, different databases have the same table (the table name and table structure are identical ).
Second, non-table-level cross-database. That is, multiple data sources do not have the same table.
Spring2.x adopts the proxy mode, that is, we implement a virtual data source in the solution and use it to encapsulate the data source selection logic, in this way, the data source selection logic can be effectively separated from the client. The client provides the context (because this is known to the client) for selection. The virtual datasource selects the data source based on the context provided by the client.
The specific implementation is that the virtual datasource only needs to inherit the abstractroutingdatasource to implement the determinecurrentlookupkey () in which the data source selection logic is encapsulated.
I. dynamic configuration of multiple data sources
1. Data Source Name constant class:
Package com. Hope. Common. Data;

Public class performanceconst {
Public static final string manager = "manager ";
Public static final string stmt = "stmt ";
Public static final string nodb = "nodb ";
}
2. Create a class for obtaining and setting the context environment, which is mainly responsible for changing the name of the Context Data source:

Public class performancehandle {
Private Static final threadlocal contextholder = new threadlocal (); // local thread Environment

// Set the data source type
Public static void setdatasourcetype (string datasourcetype ){
Contextholder. Set (cetcetype );
}

// Obtain the data source type
Public static string getdatasourcetype (){
Return (string) contextholder. Get ();
}

// Clear the data source type
Public static void cleardatasourcetype (){
Contextholder. Remove ();
}
}

3. Create a dynamic data source class. Note that this class must inherit the abstractroutingdatasource and implement the determinecurrentlookupkey method. This method returns an object, which generally returns a string:
Package com. Hope. datasource;
Public class dynamicdatasource extends actroutingdatasource {

Public dynamicdatasource (){

}
@ Override
Protected object determinecurrentlookupkey (){
// Obtain the data source type through context variables before performing DaO operations
Return cecehandle. getdatasourcetype ();
}

} 4. Compile the spring configuration file to configure multiple data sources.
<! -- Configure multiple data sources in spring -->
<Bean id = "parentdatasource"
Class = "org. springframework. JDBC. datasource. drivermanagerdatasource">
<Property name = "driverclassname" value = "net. SourceForge. jtds. JDBC. Driver"> </property>
<Property name = "username" value = "sa"> </property>
<Property name = "password" value = "Sa </property>

</Bean>
<! -- Configure the features of each database -->

<! -- Database -->

<Bean id = "nodbdatasource" parent = "parentdatasource">
<Property name = "url" value = "JDBC: jtds: sqlserver: // localhost: 1433/"/>
</Bean>
<Bean id = "managerdatasource" parent = "parentdatasource">
<Property name = "url"
Value = "JDBC: jtds: sqlserver: // localhost: 1433/test1
</Bean>
<Bean id = "statdatasource" parent = "parentdatasource">
<Property name = "url" value = "JDBC: jtds: sqlserver: // localhost: 1433/Test2
</Bean>

<Bean id = "datasource" class = "com. Hope. Common. Data. dynamicdatasource">
<Property name = "targetdatasources">
<Map key-type = "Java. Lang. String">
<Entry value-ref = "managerdatasource" Key = "manager"> </entry>
<Entry value-ref = "statdatasource" Key = "stmt"> </entry>
<Entry value-ref = "nodbdatasource" Key = "nodb"> </entry>
</Map>
</Property>
<Property name = "defaulttargetdatasource" ref = "managerdatasource"> </property>
</Bean>

5. ibatis related SQL

<Select id = "selectdbdictionary" resultmap = "baseresultmap" parametertype = "Java. Lang. Integer">

Select. ID,. parentid,. cname,. priority,. ifactive from hopetas_manager.dbo.dictionary a inner join ZJ. DBO. dictionary B on. id = B. ID where. parentid =#{ ID, jdbctype = integer}

</SELECT>

6. program call: Set setdatasourcetype value before Dao operation

// Use recursion to process Fields
Public void getdict0 (eement element, integer ID, integer J ){
// Set the data source
Performancehandle. setdatasourcetype (performanceconst. nodb );
Arraylist list = (arraylist) This. dictmapper
. Selectdbdictionary (ID );
String STR = "dir ";
J ++;
If (list. Size ()! = 0 & list! = NULL ){
For (iterator iter = List. iterator (); ITER. hasnext ();){
Dictionary dict = (dictionary) ITER. Next ();
Element El = element. addelement (STR + J );
Element prop1 = El. addattribute ("ID", dict. GETID (). tostring ());
Element prop2 = El. addattribute ("name", dict. getcname ()
. Tostring ());
Getdict0 (El, dict. GETID (), J );
}
}

}