基于 SSM项目多数据源配置实现（eg_ oracle+sqlServer）

迷南。 2022-03-15 06:36 244阅读 0赞

# **项目初期数据库用的是oracle数据库，后期因为一个需求需要跟另外一个平台交互，用的是SqlServer数据库，需要在向Oracle中插入数据的同时向SqlServer数据库中也插入一些数据，所以需要此项目再加入跟SqlServer交互的配置——** #

![20190301113718318.png][]

下面是重点配置文件：——

1. config.properties  (有的喜欢叫db.properties)

jdbc.driver=oracle.jdbc.driver.OracleDriver
    jdbc.url=jdbc:oracle:thin:@47.108.209.57:1521:orcl
    jdbc.username=sco
    jdbc.password=oracleAdm
    initialSize=0
    maxActive=20
    maxIdle=20
    minIdle=1
    maxWait=60000
    sqlserver.jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
    sqlserver.jdbc.url=jdbc:sqlserver://49.109.209.59:1433;DatabaseName=BK_ZLBGtt
    sqlserver.jdbc.username=saa
    sqlserver.jdbc.password=!@#admin@cecn1

2.mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <settings>
    		  
            <setting name="logImpl" value="LOG4J2" />
            
        </settings>
       
        
        <typeAliases>
         
          <package name="cn.beikong.risk.entity"/>
          <package name="cn.beikong.quality.entity"/>
          <package name="cn.beikong.table.entity"/>
        </typeAliases>
    
        <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="oracle"/>
            <property name="offsetAsPageNum" value="false"/>
            <property name="rowBoundsWithCount" value="false"/>
            <property name="pageSizeZero" value="true"/>
            <property name="reasonable" value="false"/>
            <property name="supportMethodsArguments" value="false"/>
            <property name="returnPageInfo" value="none"/>
        </plugin>
        </plugins>
    </configuration>

3. spring-mvc.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans default-lazy-init="true"
        xmlns="http://www.springframework.org/schema/beans" 
        xmlns:p="http://www.springframework.org/schema/p"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:mvc="http://www.springframework.org/schema/mvc"
        xmlns:task="http://www.springframework.org/schema/task"
       xsi:schemaLocation="
           http://www.springframework.org/schema/task
           http://www.springframework.org/schema/task/spring-task-3.2.xsd
           http://www.springframework.org/schema/beans   
           http://www.springframework.org/schema/beans/spring-beans-4.3.xsd  
           http://www.springframework.org/schema/mvc   
           http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd   
           http://www.springframework.org/schema/tx   
           http://www.springframework.org/schema/tx/spring-tx-4.3.xsd   
           http://www.springframework.org/schema/context  
           http://www.springframework.org/schema/context/spring-context-4.3.xsd
           http://www.springframework.org/schema/task
     	   http://www.springframework.org/schema/task/spring-task-3.0.xsd"> 
    
        
        <context:component-scan base-package="cn.beikong" />
        
        <mvc:annotation-driven conversion-service="conversionService" />
        <bean id="conversionService"
            class="org.springframework.format.support.FormattingConversionServiceFactoryBean">
        </bean>
        
        <bean id="multipartResolver"
            class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
            
            <property name="defaultEncoding" value="utf-8" />
            
            <property name="maxUploadSize" value="10485760000" />
            
            <property name="maxInMemorySize" value="40960" />
        </bean>
        
    	<bean id="mappingJackson2HttpMessageConverter"
    		class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter">
    		<property name="supportedMediaTypes">
    			<list>
    				<value>application/json; charset=utf-8</value>
    			</list>
    		</property>
    	</bean>
        
        
         
         <task:annotation-driven/>
         <task:scheduler id="myScheduler" pool-size="5"/>
         
         
         
    		
    		<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		    <property name="dataSource" ref="dataSource" />
    		</bean>
    		
    		<tx:annotation-driven transaction-manager="txManager" />
    </beans>

4.spring-mybatis.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:context="http://www.springframework.org/schema/context"
           xsi:schemaLocation="http://www.springframework.org/schema/beans 
           http://www.springframework.org/schema/beans/spring-beans.xsd 
           http://www.springframework.org/schema/context 
           http://www.springframework.org/schema/context/spring-context.xsd">
    
        
        <context:property-placeholder location="classpath:config.properties"/>
    
        
        <bean id="dataSourceOracle" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            
            <property name="driverClass" value="${jdbc.driver}" />
            
            <property name="jdbcUrl" value="${jdbc.url}" />
            <property name="user" value="${jdbc.username}" />
            <property name="password" value="${jdbc.password}" />
            
            <property name="maxPoolSize" value="30"/>
            <property name="minPoolSize" value="10"/>
            
            <property name="autoCommitOnClose" value="false"/>
            
            <property name="checkoutTimeout" value="1000"/>
            
            <property name="acquireRetryAttempts" value="2"/>
        </bean>
        
        <bean name="dataSourceSql" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
            <property name="driverClass" value="${sqlserver.jdbc.driver}" />
            <property name="jdbcUrl" value="${sqlserver.jdbc.url}" />
            <property name="user" value="${sqlserver.jdbc.username}" />
            <property name="password" value="${sqlserver.jdbc.password}" />
            <property name="maxPoolSize" value="30"/>
            <property name="minPoolSize" value="10"/>
            
            <property name="autoCommitOnClose" value="false"/>
            
            <property name="checkoutTimeout" value="1000"/>
            
            <property name="acquireRetryAttempts" value="2"/>
        </bean>
    
        
        <bean id="dataSource" class="cn.beikong.data.DynamicDataSource">
            <property name="targetDataSources">
                <map key-type="java.lang.String">
                    <entry key="dataSourceOracle" value-ref="dataSourceOracle"/>
                    <entry key="dataSourceSql" value-ref="dataSourceSql"/>
                </map>
            </property>
            <property name="defaultTargetDataSource" ref="dataSourceOracle"/>
        </bean>
    
        
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            
            
            <property name="dataSource" ref="dataSource"/>
    
            <property name="configLocation" value="classpath:mybatis-config.xml"/>
            <property name="typeAliasesPackage" value="com.bird.business.domain"/>
            <property name="mapperLocations" >
    			<list>
    			 <value>classpath:cn/beikong/*/dao/*.xml</value>
    			</list>
    		</property>
        </bean>
        
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
            
            <property name="basePackage"
    		          value="cn.beikong.**.dao "/>
        </bean>
    
    </beans>

5.web.xml

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xmlns="http://java.sun.com/xml/ns/javaee"
    	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    	version="3.0">
      <display-name>beiKongPort</display-name>
       <servlet>
        <servlet-name>dispatcherServlet</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>classpath:spring-*.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
      </servlet>
      <servlet-mapping>
        <servlet-name>dispatcherServlet</servlet-name>
        <url-pattern>/*</url-pattern>
      </servlet-mapping>
      
      <filter>
        <filter-name>CharacterEncodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>utf-8</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>CharacterEncodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
    </web-app>

5.DataSourceContextHolder （自定义类）

package cn.beikong.data;
    
    public class  DataSourceContextHolder {
    
        private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    
        public static void setDbType(String dbType) {
            contextHolder.set(dbType);
            System.out.println(contextHolder);
        }
    
        public static String getDbType() {
            return ((String) contextHolder.get());
        }
    
        public static void clearDbType() {
            contextHolder.remove();
        }
    
    }

6.DynamicDataSource

package cn.beikong.data;
    
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    public class DynamicDataSource  extends AbstractRoutingDataSource {
    
    
        @Override
        protected Object determineCurrentLookupKey() {
            return DataSourceContextHolder.getDbType();
        }
    }

7.测试——

public class SqlserverTest {
    	private SqlserverTestPerDao sqlserverTestPerDao;
    	private TableMenuDao tableMenuDao;
    	public void init() {
    		@SuppressWarnings("resource")
    		ApplicationContext ac = new ClassPathXmlApplicationContext("spring-mybatis.xml");
    		System.out.println("ac==========="+ac);
    		sqlserverTestPerDao=ac.getBean("sqlserverTestPerDao",SqlserverTestPerDao.class);
    		tableMenuDao=ac.getBean("tableMenuDao",TableMenuDao.class);
    	}
    	@Test
    	public void test(){
    		init();
    		DataSourceContextHolder.setDbType("dataSourceSql"); 
    //		List<TestPerEntity> testPer=sqlserverTestPerDao.getPerson();
    //		System.out.println(testPer);
    		
    		System.out.println(tableMenuDao.getList());
    		
    	}

重点是在使用SqlServer之前加上  ————       DataSourceContextHolder.setDbType("dataSourceSql");

因为我这里用orale的时候多，所以oracle的数据源被设置为默认的了，在使用SqlServer之前加上这句就可以了；

当然，这个写起来麻烦的话，可以自定义注解的方式来用注解替换；

[20190301113718318.png]: /images/20220315/a7b50c6d3604433fb72938a794d5dbb2.png