SpringMVC+Mybatis两个数据源实现（二）

梦里梦外; 2023-10-18 18:21 45阅读 0赞

之前写了一篇[《SpringMVC+Mybatis两个数据源实现 一》][SpringMVC_Mybatis_]介绍了在SpringMVC环境下实现两个数据源的一种方案，今天介绍另外一种，配置相对于更简单些，下面请看正文。

###### 一、配置jdbc.properties ######

#============================================================================
    # datasource 1
    #============================================================================
    #数据库连接
    jdbc.driverClass=com.mysql.jdbc.Driver
    #数据库连接url
    jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1
    #数据库用户名
    jdbc.user=user
    #数据库密码
    jdbc.password=test
    
    #============================================================================
    # datasource 2
    #============================================================================
    #数据库连接
    jdbc2.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
    #数据库连接url
    jdbc2.jdbcUrl=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test2;
    #数据库用户名
    jdbc2.user=user
    #数据库密码
    jdbc2.password=test

###### 二、配置pom.xml ######

稍有删减，需要根据各自的项目进行一些调整。

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
       <modelVersion>4.0.0</modelVersion>
       <groupId>test</groupId>
       <artifactId>query</artifactId>
       <packaging>war</packaging>
       <version>0.0.1-SNAPSHOT</version>
       <properties>
       	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       	<springframework>4.0.6.RELEASE</springframework>
       	<servlet>3.1.0</servlet>
       	<mybatis-version>3.3.1</mybatis-version>
       </properties>
       <dependencies>
       	<dependency>
       		<groupId>commons-io</groupId>
       		<artifactId>commons-io</artifactId>
       		<version>2.5</version>
       	</dependency>
       	
       	
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-core</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-beans</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-web</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-jdbc</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-webmvc</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-aop</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	<dependency>
       		<groupId>org.springframework</groupId>
       		<artifactId>spring-aspects</artifactId>
       		<version>${springframework}</version>
       	</dependency>
       	
       	<dependency>
       		<groupId>com.zaxxer</groupId>
       		<artifactId>HikariCP-java7</artifactId>
       		<version>2.4.13</version>
       	</dependency>
       	
       	<dependency>
       		<groupId>mysql</groupId>
       		<artifactId>mysql-connector-java</artifactId>
       		<version>5.1.31</version>
       	</dependency>
       	
       	<dependency>
       		<groupId>com.microsoft.sqlserver</groupId>
       		<artifactId>sqljdbc4</artifactId>
       		<version>4.0</version>
       	</dependency>
       	<dependency>
       		<groupId>log4j</groupId>
       		<artifactId>log4j</artifactId>
       		<version>1.2.13</version>
       	</dependency>
       	
       	<dependency>
       		<groupId>org.mybatis</groupId>
       		<artifactId>mybatis</artifactId>
       		<version>${mybatis-version}</version>
       	</dependency>
       	
       	<dependency>
       		<groupId>org.mybatis</groupId>
       		<artifactId>mybatis-spring</artifactId>
       		<version>1.2.4</version>
       	</dependency>
       </dependencies>
       <build>
       	<plugins>
       		<plugin>
       			<artifactId>maven-compiler-plugin</artifactId>
       			<version>2.3.2</version>
       			<configuration>
       				<source>1.7</source>
       				<target>1.7</target>
       			</configuration>
       		</plugin>
       	</plugins>
       </build>
    </project>

###### 三、配置springMVC.xml ######

根据实际项目配置，略

###### 四、配置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:mvc="http://www.springframework.org/schema/mvc" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:task="http://www.springframework.org/schema/task"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
                http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
                http://www.springframework.org/schema/context 
                http://www.springframework.org/schema/context/spring-context-3.2.xsd
                http://www.springframework.org/schema/aop 
                http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
                http://www.springframework.org/schema/tx 
                http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
                http://www.springframework.org/schema/mvc 
                http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
                http://www.springframework.org/schema/context 
                http://www.springframework.org/schema/context/spring-context-3.2.xsd
                http://www.springframework.org/schema/task     
        		http://www.springframework.org/schema/task/spring-task-4.0.xsd ">
    	
    	<context:component-scan base-package="com.test" />
    
    	
    	<context:property-placeholder file-encoding="UTF-8" location="classpath:jdbc.properties" ignore-unresolvable="true" />
    
    	
    	<bean id="mySqlDataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="shutdown">
    		<property name="driverClassName" value="${jdbc.driverClass}" />
    		<property name="jdbcUrl" value="${jdbc.jdbcUrl}" />
    		<property name="username" value="${jdbc.user}" />
    		<property name="password" value="${jdbc.password}" />
    
    		
    		<property name="readOnly" value="false" />
    		
    		<property name="connectionTimeout" value="30000" />
    		
    		<property name="idleTimeout" value="600000" />
    		
    		<property name="maxLifetime" value="1800000" />
    		
    		<property name="maximumPoolSize" value="20" />
    	</bean>
    
    	<bean id="sqlServerDataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="shutdown">
    		<property name="driverClassName" value="${jdbc2.driverClass}" />
    		<property name="jdbcUrl" value="${jdbc2.jdbcUrl}" />
    		<property name="username" value="${jdbc2.user}" />
    		<property name="password" value="${jdbc2.password}" />
    
    		
    		<property name="readOnly" value="false" />
    		
    		<property name="connectionTimeout" value="30000" />
    		
    		<property name="idleTimeout" value="600000" />
    		
    		<property name="maxLifetime" value="1800000" />
    		
    		<property name="maximumPoolSize" value="20" />
    	</bean>
    
    	<bean id="dataSource" class="com.test.common.DynamicDataSource">
    		<property name="defaultTargetDataSource" ref="mySqlDataSource" />
    		<property name="targetDataSources">
    			<map>
    				<entry key="mySqlDataSource" value-ref="mySqlDataSource" />
    				<entry key="sqlServerDataSource" value-ref="sqlServerDataSource" />
    			</map>
    		</property>
    	</bean>
    
    	
    	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    
    	
    	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    		<property name="dataSource" ref="dataSource" />
    		<property name="configLocation" value="classpath:mybatis-config.xml"></property>
    	</bean>
    
    	
    	<tx:advice id="txAdvice" transaction-manager="transactionManager">
    		<tx:attributes>
    			
    			<tx:method name="*" propagation="REQUIRED" />
    		</tx:attributes>
    	</tx:advice>
    
    	<tx:annotation-driven transaction-manager="transactionManager" />
    
    	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
    		<constructor-arg index="0" ref="sqlSessionFactory" />
    	</bean>
    
    	...
    </beans>

###### 五、配置mybatis-config.xml ######

主要是配置mappers，略

###### 六、DynamicDataSource ######

package com.test.common;
    
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    public class DynamicDataSource extends AbstractRoutingDataSource {
        
    	public static final String mySqlDataSource = "mySqlDataSource";
    
    	public static final String sqlServerDataSource = "sqlServerDataSource";
    	// 本地线程，获取当前正在执行的currentThread
    	public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    
    	public static void setCustomerType(String customerType) {
        
    		contextHolder.set(customerType);
    	}
    
    	public static String getCustomerType() {
        
    		return contextHolder.get();
    	}
    
    	public static void clearCustomerType() {
        
    		contextHolder.remove();
    	}
    
    	@Override
    	protected Object determineCurrentLookupKey() {
        
    		return getCustomerType();
    	}
    }

###### 七，具体应用 ######

package com.test.service.impl;
    
    import java.util.Map;
    
    import javax.annotation.Resource;
    
    import org.springframework.stereotype.Service;
    
    import com.test.common.DynamicDataSource;
    import com.test.dao.TestDao;
    import com.test.entity.Test;
    import com.test.service.TestService;
    
    @Service
    public class TestServiceImpl implements TestService {
        
    	@Resource
    	TestDao testDao;
    
    	@Override
    	public Test getInfoByParam(Map<String, String> param) {
        
    		//重点： 实际操作证明，切换的时候最好清空一下
    		DynamicDataSource.clearCustomerType();
    		//切换数据源，设置后 就OK了。可以随时切换过来，可以在controller层切换，也可以在service层
            DynamicDataSource.setCustomerType(DynamicDataSource.sqlServerDataSource);
            //数据库操作
    		Test bean = gpiDao.getCasenoByParam(param);
    		DynamicDataSource.clearCustomerType();
    		//操作完之后切换回主数据源,这样不影响后面的操作
    		DynamicDataSource.setCustomerType(DynamicDataSource.mySqlDataSource);
    		return bean;
    	}
    }

当然这里需要考虑一下多线程的问题

--------------------

参考：[https://www.cnblogs.com/hero123/p/8945914.html][https_www.cnblogs.com_hero123_p_8945914.html]

[SpringMVC_Mybatis_]: https://blog.csdn.net/tiantang_1986/article/details/88989382
[https_www.cnblogs.com_hero123_p_8945914.html]: https://www.cnblogs.com/hero123/p/8945914.html