SSM+Druid实现动态多数据源切换（已实践）

我不是女神ヾ 2023-10-18 15:19 128阅读 0赞

## 场景 ##

两个数据库，完成增删改查时同时对两个数据库进行操作。

##    实现 ##

1.在web.xml中配置spring的配置文件

<context-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:spring.xml</param-value>
     </context-param>

2.在spring.xml中引入属性文件

<context:property-placeholder location="classpath:config.properties" />
     <context:property-placeholder location="classpath:databaseType.properties" />

3.打开config.properties

除了其他正常配置外，添加两个连接数据库的url。

添加如下：

driverClassName=com.mysql.jdbc.Driver
    validationQuery=SELECT 1
    
    sys_url=jdbc:mysql://数据库1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    sys_username=
    sys_password=
    
    demo_url=jdbc:mysql://数据库2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    demo_username=
    demo_password=

4.打开databaseType.properties

#sysDs
    1 = sysDs
    #demoDS
    2 = demoDs

5.再回到spring.xml

其他配置正常，这里省略。

导入其他spring配置文件

<import resource="spring-database.xml" />

6.打开spring-database.xml

设置动态切换数据源

<bean id="dataSource" class="com.wongoing.datasource.DynamicDataSource">
            <property name="targetDataSources">
                <map key-type="java.lang.String">
                    <entry key="sysDs" value-ref="sysDs" />
                    <entry key="demoDs" value-ref="demoDs" />
                </map>
            </property>
            
            <property name="defaultTargetDataSource" ref="sysDs" />
        </bean>

紧接着配置两个数据源

<bean name="sysDs" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
      <property name="driverClassName" value="${driverClassName}" />
      <property name="url" value="${sys_url}" />
      <property name="username" value="${sys_username}" />
      <property name="password" value="${sys_password}" />
      
      <property name="initialSize" value="0" />
      
      <property name="minIdle" value="0" />
      
      <property name="maxActive" value="50" />
      
      <property name="maxWait" value="60000" />
      
      <property name="timeBetweenEvictionRunsMillis" value="300000" />
      
      <property name="minEvictableIdleTimeMillis" value="300000" />
      
      
      <property name="validationQueryTimeout" value="300000" />
      <property name="validationQuery" value="${validationQuery}" />
      <property name="testWhileIdle" value="true" />
      <property name="testOnBorrow" value="false" />
      <property name="testOnReturn" value="false" />  
      
      
      
      <property name="poolPreparedStatements" value="true" />
      <property name="maxPoolPreparedStatementPerConnectionSize" value="50" />
      
      
      <property name="removeAbandoned" value="true" />
      
      <property name="removeAbandonedTimeout" value="3600" />
      
      <property name="logAbandoned" value="true" />
     </bean>
     
     
     <bean name="demoDs" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
      <property name="driverClassName" value="${driverClassName}" />
      <property name="url" value="${demo_url}" />
      <property name="username" value="${demo_username}" />
      <property name="password" value="${demo_password}" />
      
      <property name="initialSize" value="0" />
      
      <property name="minIdle" value="0" />
      
      <property name="maxActive" value="50" />
      
      <property name="maxWait" value="60000" />
      
      <property name="timeBetweenEvictionRunsMillis" value="300000" />
      
      <property name="minEvictableIdleTimeMillis" value="300000" />
      
      
      <property name="validationQueryTimeout" value="300000" />
      <property name="validationQuery" value="${validationQuery}" />
      <property name="testWhileIdle" value="true" />
      <property name="testOnBorrow" value="false" />
      <property name="testOnReturn" value="false" />  
      
      
      
      <property name="poolPreparedStatements" value="true" />
      <property name="maxPoolPreparedStatementPerConnectionSize" value="50" />
      
      
      <property name="removeAbandoned" value="true" />
      
      <property name="removeAbandonedTimeout" value="3600" />
      
      <property name="logAbandoned" value="true" />
     </bean>

然后配置事务管理

<tx:annotation-driven /> 
         
        <bean id="transactionManager" 
            class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 
            <property name="dataSource" ref="dataSource" /> 
        </bean>

7.新建DataSourceHolder类

新建datasource包 ，包下新建DataSourceHolder.java

package com.badao.datasource;
    
    public class DataSourceHolder {
     //线程本地环境
        private static final ThreadLocal<String> dataSources = new ThreadLocal<String>();
        //设置数据源
        public static void setDataSource(String customerType) {
            dataSources.set(customerType);
        }
        //获取数据源
        public static String getDataSource() {
            return (String) dataSources.get();
        }
        //清除数据源
        public static void clearDataSource() {
            dataSources.remove();
        }
    }

8.业务实现

import com.badao.datasource.DataSourceHolder;
    
    
    @Description("信息保存")
     @ResponseBody
     @RequestMapping(value = "/doSave")
     public Map<String, Object> doSave(TbMessage entity, String op, String base64Str, String[] itemIdStr) {
      Map<String, Object> jsonResult = null;
      String msg = "";
      try {
       if (base64Str.length()!= 0) {
        String result = ImageUtil.getInstance().baseUploadImg(
          Constants.UPLOAD_IMG_MESSAGE, base64Str);
        entity.setThemeImageUrl((String) result);
       }
       String tabid = tabid(ModelAndViewConstants.MESSAGE_SYS_ID);
       Date now = new Date();
       ShiroUser currentUser = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
       // 添加
       if (null == op || ModelAndViewConstants.OPERATION_VALUE_ADD.equals(op)) {
        entity.setStatus("0");
        entity.setRecordTime(now);
        entity.setUserId(currentUser.getUserId());
        this.service.insert(entity);
        DataSourceHolder.setDataSource("demoDs");//切换云端数据源
        this.service.insert(entity);
        DataSourceHolder.setDataSource("sysDs");//切换本地数据源
       } else {// 编辑
        entity.setModifyDate(now);
        this.service.updateByPrimaryKey(entity);
        DataSourceHolder.setDataSource("demoDs");//切换云端数据源
        this.service.updateByPrimaryKey(entity);
        DataSourceHolder.setDataSource("sysDs");//切换本地数据源
       }
       Integer statusCode = 200;
       String Msg = "消息信息保存成功";
       jsonResult = JsonResult.jsonReturn(statusCode, Msg, tabid);
       return jsonResult;
    
      } catch (Exception e) {
       msg = "接口调用异常";
       jsonResult = JsonResult.jsonWsReturn(1, msg, e.getMessage());
       LogService.getInstance(this).error("保存消息信息失败" + e.getMessage());
       String mg = "保存消息信息失败：" + e.getMessage();
       jsonResult = JsonResult.jsonReturnErr(mg);
       return jsonResult;
      }
    
     }