170615、spring不同数据库数据源动态切换

2022-07-17 19:03:07

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">

      &lt;!&ndash; 基本属性 url、user、password &ndash;&gt;

      <property name="url" value="${jdbc.url}" />

      <property name="username" value="${jdbc.username}" />

      <property name="password" value="${jdbc.password}" />

      &lt;!&ndash; 配置初始化大小、最小、最大 &ndash;&gt;

      <property name="initialSize" value="1" />

      <property name="minIdle" value="1" />

      <property name="maxActive" value="20" />

      &lt;!&ndash; 配置获取连接等待超时的时间 &ndash;&gt;

      <property name="maxWait" value="60000" />

      &lt;!&ndash; 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 &ndash;&gt;

      <property name="timeBetweenEvictionRunsMillis" value="60000" />

      &lt;!&ndash; 配置一个连接在池中最小生存的时间，单位是毫秒 &ndash;&gt;

      <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" />

      &lt;!&ndash; 打开PSCache，并且指定每个连接上PSCache的大小 &ndash;&gt;

      <property name="poolPreparedStatements" value="true" />

      <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />

      &lt;!&ndash; 打开removeAbandoned功能 &ndash;&gt;

      <property name="removeAbandoned" value="true" />

       &lt;!&ndash; 1800秒，也就是30分钟 &ndash;&gt;

       <property name="removeAbandonedTimeout" value="1800" />

       &lt;!&ndash; 关闭abanded连接时输出错误日志 &ndash;&gt;

       <property name="logAbandoned" value="true" />

      &lt;!&ndash; 配置监控统计拦截的filters &ndash;&gt;

      <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数据源

码农公寓

相关文章