Dynamic Data Source switching (spring + hibernate)

Cause: A project requires multiple data sources and comes from different types of databases... for many historical reasons, the data source of this project is MySQL, and the entire system operates on this database.

However, there are two other databases used for data collection: MSSQL, ACCESS. Fortunately, they are only used for data collection and do not need to cross-database transactions, which saves a lot of work.
Environment: The test environment I set up is spring2.5.6 + hibernate3.2.

Idea: dynamically switching data sources is exactly the same type of database. That is to say, the databases used in the system are distributed across multiple database servers or multiple databases on the same server. During the running period, the database in the current operation is dynamically selected based on a certain identifier.
1. The data source is a database of the same type: One SessionFactory + Dynamic Data Source + One Transaction Manager
2. Data sources are different types of databases: Configure multiple sets of SessionFactory according to the type
Simulation: Two mysql data sources + one Access data source

Implementation:
 
1. An identifier is required to switch the data source. The identifier is of the Object type.
Package lhp. example. context;
Public enum DBType {
Performance1, performance2;
}
 
2. Create a tool class for switching data sources (setting or obtaining context ).
Package lhp. example. context;

Public class ContextHolder {
Private static final ThreadLocal <Object> holder = new ThreadLocal <Object> ();

Public static void setDbType (DBType dbType ){
Holder. set (dbType );
}

Public static DBType getDbType (){
Return (DBType) holder. get ();
}

Public static void clearDbType (){
Holder. remove ();
}
}

3. Create a dynamic data source class and inherit from the org. springframework. jdbc. datasource. lookup. AbstractRoutingDataSource class.
Package lhp. example. context;

Import java. util. logging. Logger;

Import org. springframework. jdbc. datasource. lookup. AbstractRoutingDataSource;

Public class DynamicDataSource extends actroutingdatasource {
Public static final Logger logger = Logger. getLogger (DynamicDataSource. class. toString ());

@ Override
Protected Object determineCurrentLookupKey (){
DBType key = ContextHolder. getDbType (); // get the current data source identifier
// Logger.info ("Current Data source:" + key );
Return key;
}

}

4. Configure multiple data sources
<! -- Data source 1: mysql -->
<Bean id = "performance1" class = "com. mchange. v2.c3p0. ComboPooledDataSource">
<Property name = "driverClass" value = "com. mysql. jdbc. Driver"/>
<Property name = "jdbcUrl" value = "jdbc: mysql: // 127.0.0.1: 3306/dec"/>
<Property name = "user" value = "root"/>
<Property name = "password" value = ""/>
</Bean>
<! -- Data source 2: mysql -->
<Bean id = "dataSource2" class = "com. mchange. v2.c3p0. ComboPooledDataSource">
<Property name = "driverClass" value = "com. mysql. jdbc. Driver"/>
<Property name = "jdbcUrl" value = "jdbc: mysql: // 127.0.0.1: 3306/lms"/>
<Property name = "user" value = "root"/>
<Property name = "password" value = ""/>
</Bean>

<! -- Data Source 3: access -->
<Bean id = "cmdc4" class =" com. mchange. v2.c3p0. ComboPooledDataSource ">
<Property name = "driverClass" value = "sun. jdbc. odbc. JdbcOdbcDriver"/>
<Property name = "jdbcUrl" value = "jdbc: odbc: accessTest"/>
<Property name = "user" value = "administrator"/>
<Property name = "password" value = "XLZX0309"/>
</Bean>

<! -- Mysql Dynamic Data Source settings -->
<Bean id = "mysqlDynamicDataSource" class = "lhp. example. context. DynamicDataSource">
<Property name = "targetDataSources">
<! -- Identifier type -->
<Map key-type = "lhp. example. context. DBType">
<Entry key = "performance1" value-ref = "performance1"/>
<Entry key = "performance2" value-ref = "performance2"/>
</Map>
</Property>
<Property name = "defaultTargetDataSource" ref = "performance1"/>
</Bean>

5. Configure sessionFactory
<! -- Mysql sessionFactory -->
<Bean id = "mysqlSessionFactory" class = "org. springframework. orm. hibernate3.LocalSessionFactoryBean">
<Property name = "dataSource" ref = "mysqlDynamicDataSource"/>
<Property name = "hibernateProperties">
<Props>
<Prop key = "hibernate. dialect"> org. hibernate. dialect. MySQLDialect </prop>
<Prop key = "hibernate. show_ SQL"> true </prop>
<Prop key = "hibernate. hbm2ddl. auto"> update </prop> <! -- Create validate -->
<Prop key = "hibernate. query. substitutions"> true 1, false 0 </prop>
</Props>
</Property>
</Bean>

<! -- Access sessionFactory -->
<Bean id = "aceessSessionFactory" class = "org. springframework. orm. hibernate3.LocalSessionFactoryBean">
<Property name = "dataSource" ref = "rjc4"/>
<Property name = "hibernateProperties">
<Props>
<! -- The access syntax is similar to MSSQL, so MSSQL dialect is used, or a third-party dialect can be used. -->
<Prop key = "hibernate. dialect"> org. hibernate. dialect. SQLServerDialect </prop>
<Prop key = "hibernate. jdbc. batch_size"> 30 </prop>
<Prop key = "hibernate. jdbc. fetch_size"> 50 </prop>
<Prop key = "hibernate. show_ SQL"> true </prop>
<Prop key = "hibernate. format_ SQL"> false </prop>
<Prop key = "hibernate. hbm2ddl. auto"> update </prop> <! -- Create validate -->
<Prop key = "hibernate. query. substitutions"> true 1, false 0 </prop>
<Prop key = "hibernate. cglib. use_reflection_optimizer"> true </prop>
<! -- <Prop key = "hibernate. cache. use_second_level_cache"> true </prop> -->
<! -- <Prop key = "hibernate. cache. provider_class"> org. hibernate. cache. EhCacheProvider </prop> -->
<! -- <Prop key = "hibernate. cache. use_query_cache"> true </prop> -->
<! -- <Prop key = "hibernate. generate_statistics"> true </prop> -->
<! -- <Prop key = "hibernate. cache. provider_configuration_file_resource_path"> classpath: ehcache. xml </prop> -->
</Props>
</Property>
</Bean>

6. Test Cases
Package lhp. example. junit;

Import static org. junit. Assert .*;
Import java. SQL. DatabaseMetaData;
Import lhp. example. context. ContextHolder;
Import lhp. example. context. DBType;
Import org. hibernate. Session;
Import org. hibernate. SessionFactory;
Import org. junit. Before;
Import org. junit. Test;
Import org. springframework. context. ApplicationContext;
Import org. springframework. context. support. ClassPathXmlApplicationContext;

Public class ServiceTest {
Private ApplicationContext context;
// URL of the three data sources
Private String performance1_url = "jdbc: mysql: // 127.0.0.1: 3306/dec ";
Private String performance2_url = "jdbc: mysql: // 127.0.0.1: 3306/lms ";
Private String performance3_url = "jdbc: odbc: accessTest ";
Private SessionFactory mysqlSessionFactory;
Private SessionFactory aceessSessionFactory;

@ Before
Public void setUp () throws Exception {
// Select a data source to initialize spring
ContextHolder. setDbType (DBType. performance1 );
//
String [] xmlFiles = new String [] {
"ApplicationContext-dataSource.xml ",
"ApplicationContext-hibernate.xml ",
"ApplicationContext-spring.xml "};
//
Context = new ClassPathXmlApplicationContext (xmlFiles );
//
MysqlSessionFactory = (SessionFactory) context. getBean ("mysqlSessionFactory ");
AceessSessionFactory = (SessionFactory) context. getBean ("aceessSessionFactory ");
}

@ SuppressWarnings ("deprecation ")
@ Test
Public void mysqlDataSourceTest (){
Try {

Session mysqlSession = mysqlSessionFactory. openSession ();
// Obtain database metadata
DatabaseMetaData meatData = http://www.cnblogs.com/coveted/archive/2011/10/22/mysqlSession.connection (). getMetaData ();

// The data source performance1 is started by default.
// Assert whether the current data source URL is the URL of performance1
AssertEquals (performance1_url, meatData. getURL ());

// Switch to the data source performance2
ContextHolder. setDbType (DBType. performance2 );
MysqlSession = mysqlSessionFactory. openSession ();
MeatData = http://www.cnblogs.com/coveted/archive/2011/10/22/mysqlSession.connection (). getMetaData ();
// Assert whether the current data source URL is the URL of performance2
AssertEquals (performance2_url, meatData. getURL ());

} Catch (Exception e ){
E. printStackTrace ();
}
}

@ SuppressWarnings ("deprecation ")
@ Test
Public void accessDataSourceTest (){
Try {
Session accessSession = aceessSessionFactory. openSession ();
// Obtain database metadata
DatabaseMetaData meatData = http://www.cnblogs.com/coveted/archive/2011/10/22/accessSession.connection (). getMetaData ();
// Assert whether the current data source URL is the URL of 2017s3.
AssertEquals (performance3_url, meatData. getURL ());

} Catch (Exception e ){
E. printStackTrace ();
}
}

}