这是我写的一个测试例子,程序要求在test和tests两个库中切换,spring主要配置如下:
<bean id="ParentDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
<property name="driver" >
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="user">
<value>root</value>
</property>
<property name="password" >
<value>123</value>
</property>
<!-- 最少保持的空闲连接数 (默认5个)-->
<property name="prototypeCount">
<value>5</value>
</property>
<!-- 最大连接数 (默认15个)指因没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受 -->
<property name="maximumConnectionCount">
<value>100</value>
</property>
<!-- 最小连接数 (默认5个) -->
<property name="minimumConnectionCount">
<value>15</value>
</property>
<!-- 侦察时间间隔 proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁-->
<property name="houseKeepingSleepTime">
<value>604800</value>
</property>
<!-- 测试的SQL执行语句 -->
<property name="houseKeepingTestSql">
<value>select CURRENT_DATE</value>
</property>
<!-- 执行语句是否要记录 -->
<property name="trace">
<value>false</value>
</property>
<!-- 记录执行的详细信息 -->
<property name="verbose">
<value>false</value>
</property>
<!-- 最大活动时间(超过此时间线程将被kill,默认为5分钟) -->
<property name="maximumActiveTime">
<value>5</value>
</property>
</bean>
<bean id="DataSourcetest" parent="ParentDataSource">
<property name="driverUrl">
<value>jdbc:mysql://127.0.0.1:3306/test?user=root&password=123&useUnicode=true&characterEncoding=utf-8</value>
</property>
</bean>
<bean id="DataSourcetests" parent="ParentDataSource">
<property name="driverUrl">
<value>jdbc:mysql://127.0.0.1:3306/tests?user=root&password=123&useUnicode=true&characterEncoding=utf-8</value>
</property>
</bean>
<bean id="dynamicDataSource" class="com.test.datasource.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="DataSourcetest" key="test"></entry>
<entry value-ref="DataSourcetests" key="tests"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="DataSourcetests"></property>
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dynamicDataSource"/>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<!-- <prop key="connection.autocommit">true</prop> -->
<prop key="hibernate.cache.use_second_level_cache">true</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
<prop key="hibernate.connection.release_mode">auto</prop>
<prop key="hibernate.connection.autocommit">true</prop>
<prop key="hibernate.proxool.existing_pool">true</prop>
<prop key="hibernate.connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.use_sql_comments">true</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>com/test/entity/User.hbm.xml</value>
</list>
</property>
</bean>
<bean id="udao" class="com.test.dao.impl.UserDaoImpl">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>有一个继承AbstractRoutingDataSource的类,实现其中的determineCurrentLookupKey()
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}//设置动态数据源类型
public class DataSourceContextHolder { private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setDataSourceType(String dataSourceName){
contextHolder.set(dataSourceName);
}
public static String getDataSourceType(){
return (String)contextHolder.get();
}
public static void clearDataSourceType(){
contextHolder.remove();
}
}
还有一个filter,调用DataSourceContextHolder.setDataSourceType(..)设置数据源类型,对于每一次请求都进行过滤
但是数据库始终无法切换,请各位帮帮忙,谢谢啦
<bean id="ParentDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
<property name="driver" >
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="user">
<value>root</value>
</property>
<property name="password" >
<value>123</value>
</property>
<!-- 最少保持的空闲连接数 (默认5个)-->
<property name="prototypeCount">
<value>5</value>
</property>
<!-- 最大连接数 (默认15个)指因没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受 -->
<property name="maximumConnectionCount">
<value>100</value>
</property>
<!-- 最小连接数 (默认5个) -->
<property name="minimumConnectionCount">
<value>15</value>
</property>
<!-- 侦察时间间隔 proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁-->
<property name="houseKeepingSleepTime">
<value>604800</value>
</property>
<!-- 测试的SQL执行语句 -->
<property name="houseKeepingTestSql">
<value>select CURRENT_DATE</value>
</property>
<!-- 执行语句是否要记录 -->
<property name="trace">
<value>false</value>
</property>
<!-- 记录执行的详细信息 -->
<property name="verbose">
<value>false</value>
</property>
<!-- 最大活动时间(超过此时间线程将被kill,默认为5分钟) -->
<property name="maximumActiveTime">
<value>5</value>
</property>
</bean>
<bean id="DataSourcetest" parent="ParentDataSource">
<property name="driverUrl">
<value>jdbc:mysql://127.0.0.1:3306/test?user=root&password=123&useUnicode=true&characterEncoding=utf-8</value>
</property>
</bean>
<bean id="DataSourcetests" parent="ParentDataSource">
<property name="driverUrl">
<value>jdbc:mysql://127.0.0.1:3306/tests?user=root&password=123&useUnicode=true&characterEncoding=utf-8</value>
</property>
</bean>
<bean id="dynamicDataSource" class="com.test.datasource.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="DataSourcetest" key="test"></entry>
<entry value-ref="DataSourcetests" key="tests"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="DataSourcetests"></property>
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dynamicDataSource"/>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<!-- <prop key="connection.autocommit">true</prop> -->
<prop key="hibernate.cache.use_second_level_cache">true</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
<prop key="hibernate.connection.release_mode">auto</prop>
<prop key="hibernate.connection.autocommit">true</prop>
<prop key="hibernate.proxool.existing_pool">true</prop>
<prop key="hibernate.connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.use_sql_comments">true</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>com/test/entity/User.hbm.xml</value>
</list>
</property>
</bean>
<bean id="udao" class="com.test.dao.impl.UserDaoImpl">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>有一个继承AbstractRoutingDataSource的类,实现其中的determineCurrentLookupKey()
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}//设置动态数据源类型
public class DataSourceContextHolder { private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setDataSourceType(String dataSourceName){
contextHolder.set(dataSourceName);
}
public static String getDataSourceType(){
return (String)contextHolder.get();
}
public static void clearDataSourceType(){
contextHolder.remove();
}
}
还有一个filter,调用DataSourceContextHolder.setDataSourceType(..)设置数据源类型,对于每一次请求都进行过滤
但是数据库始终无法切换,请各位帮帮忙,谢谢啦
我使用的框架是struts2+hibernate+spring
在service中无法准确切换,需要将切换数据源的代码放在struts2的action中
因为struts2是每个请求对应一个线程,因此切换数据源应该放在action的入口