之前写了一篇《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> <!-- Spring web mvc --> <!-- spring核心包 --> <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> <!-- HikariCP--> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP-java7</artifactId> <version>2.4.13</version> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.31</version> </dependency> <!-- sqlserver --> <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> <!-- mybatis核心包 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>${mybatis-version}</version> </dependency> <!-- mybatis-spring包 --> <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 "> <!-- 启动包扫描功能,以便注册带有@Controller、@Service、@repository、@Component等注解的类成为spring的bean --> <context:component-scan base-package="com.test" /> <!-- 读取jdbc,config配置文件,读取多个配置文件需要加上ignore-unresolvable="true",如果有相同的字段,以第一个扫描到的为准 --> <context:property-placeholder file-encoding="UTF-8" location="classpath:jdbc.properties" ignore-unresolvable="true" /> <!-- Hikari Datasource 1 --> <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}" /> <!-- 连接只读数据库时配置为true, 保证安全 --> <property name="readOnly" value="false" /> <!-- 等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQLException, 缺省:30秒 --> <property name="connectionTimeout" value="30000" /> <!-- 一个连接idle状态的最大时长(毫秒),超时则被释放(retired),缺省:10分钟 --> <property name="idleTimeout" value="600000" /> <!-- 一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired),缺省:30分钟,建议设置比数据库超时时长少30秒,参考MySQL wait_timeout参数(show variables like '%timeout%';) --> <property name="maxLifetime" value="1800000" /> <!-- 连接池中允许的最大连接数。缺省值:10;推荐的公式:((core_count * 2) + effective_spindle_count) --> <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}" /> <!-- 连接只读数据库时配置为true, 保证安全 --> <property name="readOnly" value="false" /> <!-- 等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQLException, 缺省:30秒 --> <property name="connectionTimeout" value="30000" /> <!-- 一个连接idle状态的最大时长(毫秒),超时则被释放(retired),缺省:10分钟 --> <property name="idleTimeout" value="600000" /> <!-- 一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired),缺省:30分钟,建议设置比数据库超时时长少30秒,参考MySQL wait_timeout参数(show variables like '%timeout%';) --> <property name="maxLifetime" value="1800000" /> <!-- 连接池中允许的最大连接数。缺省值:10;推荐的公式:((core_count * 2) + effective_spindle_count) --> <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> <!-- 使用spring的事务管理 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 创建sessionFactory --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:mybatis-config.xml"></property> </bean> <!--配置事务service层事务不需要每层service都标注解了 --> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <!-- *表示匹配任意方法名,add*:匹配add开头的方法 --> <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; } }
当然这里需要考虑一下多线程的问题