170615、spring不同数据库数据源动态切换
spring mvc+mybatis+多数据源切换 选取Oracle,MySQL作为例子切换数据源。mysql为默认数据源，在测试的action中，进行mysql和oracle的动态切换。
1、web.xml配置
<context-param>
<param-name>webAppRootKey</param-name>
<param-value>trac</param-value>
</context-param>
<!-- Spring的log4j监听器 -->
<listener>
<listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
</listener>
<!-- 字符集 过滤器 -->
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>utf8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- Spring view分发器 -->
<servlet>
<servlet-name>dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/dispatcher.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcher</servlet-name>
<url-pattern>*.action</url-pattern>
</servlet-mapping>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
2、dispatcher.xm
<mvc:annotation-driven /> <context:component-scan base-package="com.trac" /> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver" /> <!-- freemarker config --> <bean id="freemarkerConfig" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer"> <property name="templateLoaderPath" value="/WEB-INF/freemarker/" /> <property name="freemarkerVariables"> <map> <entry key="xml_escape" value-ref="fmXmlEscape" /> </map> </property> <property name="freemarkerSettings"> <props> <prop key="defaultEncoding">UTF-8</prop> </props> </property> </bean> <bean id="fmXmlEscape" class="freemarker.template.utility.XmlEscape" /> <!-- View resolvers can also be configured with ResourceBundles or XML files. If you need different view resolving based on Locale, you have to use the resource bundle resolver. --> <bean id="viewResolver" class="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver"> <property name="exposeRequestAttributes" value="true" /> <property name="exposeSessionAttributes" value="true" /> <property name="exposeSpringMacroHelpers" value="true" /> <property name="contentType" value="text/html;charset=UTF-8" /> <property name="cache" value="true" /> <property name="prefix" value="" /> <property name="suffix" value=".ftl" /> </bean>
3、applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <bean id="parentDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"></bean> <bean id="mySqlDataSource" parent="parentDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/icbc_wx"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> </bean> <bean id="oracleDataSource" parent="parentDataSource"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property> <property name="url" value="jdbc:oracle:thin:@192.168.1.168:1521:orcl"></property> <property name="username" value="zcgd"></property> <property name="password" value="zcgd"></property> </bean> <!--多数据源--> <bean id="dataSource" class="com.strongunion.common.datasource.DataSources"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry value-ref="mySqlDataSource" key="MYSQL"></entry> <entry value-ref="oracleDataSource" key="ORACLE"></entry> </map> </property> <property name="defaultTargetDataSource" ref="mySqlDataSource"></property> </bean> <!--单一数据源--> <!--<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <!– 基本属性 url、user、password –> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <!– 配置初始化大小、最小、最大 –> <property name="initialSize" value="1" /> <property name="minIdle" value="1" /> <property name="maxActive" value="20" /> <!– 配置获取连接等待超时的时间 –> <property name="maxWait" value="60000" /> <!– 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 –> <property name="timeBetweenEvictionRunsMillis" value="60000" /> <!– 配置一个连接在池中最小生存的时间，单位是毫秒 –> <property name="minEvictableIdleTimeMillis" value="300000" /> <property name="validationQuery" value="SELECT 'x'" /> <property name="testWhileIdle" value="true" /> <property name="testOnBorrow" value="false" /> <property name="testOnReturn" value="false" /> <!– 打开PSCache，并且指定每个连接上PSCache的大小 –> <property name="poolPreparedStatements" value="true" /> <property name="maxPoolPreparedStatementPerConnectionSize" value="20" /> <!– 打开removeAbandoned功能 –> <property name="removeAbandoned" value="true" /> <!– 1800秒，也就是30分钟 –> <property name="removeAbandonedTimeout" value="1800" /> <!– 关闭abanded连接时输出错误日志 –> <property name="logAbandoned" value="true" /> <!– 配置监控统计拦截的filters –> <property name="filters" value="stat" /> </bean>--> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <tx:annotation-driven transaction-manager="transactionManager" /> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:mybatis-config.xml" /> </bean> <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg ref="sqlSessionFactory" /> </bean> </beans>
4、DataSourceInstances.java
package com.strongunion.common.datasource; /** * Desc : 定义数据源的标识, 和applicationContext.xml中 DataSources 的 targetDataSources 的key对应 * User : RICK * Time : 2017/8/18 9:41 */ public class DataSourceInstances { /**mysql数据源*/ public static final String MYSQL="MYSQL"; /**oralce数据源*/ public static final String ORACLE="ORACLE"; }
5、DataSourceSwitch.java
package com.strongunion.common.datasource; /** * Desc : 切换数据源开关 * User : RICK * Time : 2017/8/18 10:49 */ public class DataSourceSwitch { private static final ThreadLocal contextHolder=new ThreadLocal(); public static void setDataSourceType(String dataSourceType){ contextHolder.set(dataSourceType); } public static String getDataSourceType(){ return (String) contextHolder.get(); } public static void clearDataSourceType(){ contextHolder.remove(); } }
6、DataSources.java
package com.strongunion.common.datasource; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * Desc : 配置于applicationContext 中，线程局部变量ThreadLocal contextHolder * 保存当前需要的数据源类型，当 DataSourceSwitch. setDataSourceType(DataSourceInstances.XXX) * 保存当前需要的数据源类型的时候，DataSources 会从当前线程中查找线程变量的数据源类型，从而决定使用何种数据源 * User : RICK * Time : 2017/8/18 9:43 */ public class DataSources extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceSwitch.getDataSourceType(); } }
7、测试类TestController.java
package com.strongunion.apps.controller; import com.strongunion.apps.service.IUserService; import com.strongunion.common.ResultJson; import com.strongunion.common.datasource.DataSourceInstances; import com.strongunion.common.datasource.DataSourceSwitch; import com.strongunion.generator.entity.User; import com.strongunion.utils.Util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; /** * Desc : 测试数据源 * User : RICK * Time : 2017/8/18 9:27 */ @RestController @RequestMapping("/demo") public class TestController { private static final Logger log = LoggerFactory.getLogger(TestController.class); @Resource private IUserService userService; @RequestMapping(value = "/index", method = RequestMethod.GET) public String index(){ return "hello"; } @RequestMapping(value = "/db/{type}", method = RequestMethod.GET) public ResultJson testDataBase(@PathVariable("type") String type) throws Exception{ ResultJson resultJson = null; if (Util.isNotNull(type)){ if(DataSourceInstances.MYSQL.equals(type)){ DataSourceSwitch.setDataSourceType(DataSourceInstances.MYSQL); } else if(DataSourceInstances.ORACLE.equals(type)){ DataSourceSwitch.setDataSourceType(DataSourceInstances.ORACLE); } } else { DataSourceSwitch.setDataSourceType(DataSourceInstances.MYSQL); } List<User> userList = userService.queryAllUsers(); log.info("userList------------------------------>" + userList); resultJson = ResultJson.buildSuccessInstance(userList); return resultJson; } }
8、运行效果
mysql数据库
oralce数据库
mysql数据源
oracle数据源
