springMVC+Ibatis 同时访问多数据库

最新推荐文章于 2021-01-19 17:50:08 发布

t67345

最新推荐文章于 2021-01-19 17:50:08 发布

阅读量1.5k

点赞数

分类专栏： java 笔记文章标签： ibatis spring mvc 数据库

本文链接：https://blog.csdn.net/t67345/article/details/72473280

版权

java 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

笔记

2 篇文章 0 订阅

订阅专栏

最近由于业务需要同一个项目里面需访问多个数据库获取数据，因此做了一个实例并对所做实例进行一个总结。

1、DAO类

ITestADao.java

public interface ITestADao{

	public List<Map<String, Object>> queryForListA();

}

ITestBDao.java

public interface ITestBDao{

	public List<Map<String, Object>> queryForListB();

}

2、DAO类实现

TestADaoImpl.java

//@Repository //使用该注解相当于声明了一个bean，多数据源时在spring配置文件中声明以便区分

public class TestADaoImpl extends SqlMapClientDaoSupport implements ITestADao {
	@Override
	public List<Map<String, Object>> queryForListA() {
		return getSqlMapClientTemplate().queryForList("tablea.getList");//读取数据源A中tablea的查询
	}
}

TestBDaoImpl.java

public class TestBDaoImpl extends SqlMapClientDaoSupport implements ITestBDao {
	@Override
	public List<Map<String, Object>> queryForListB() {
		return getSqlMapClientTemplate().queryForList("tableb.getList");//读取数据源B中tableb的查询

	}
}

3、Service类

ITestService.java

public interface ITestService{
	
	public List<Map<String, Object>> queryForListA();
	
	public List<Map<String, Object>> queryForListB();
	
}

4、Service类实现

TestServiceImpl.java

@Service
public class TestServiceImpl implements ITestService{
	@Autowired
	private ITestADao aDao;

	@Autowired
	private ITestBDao bDao;

	public List<Map<String, Object>> queryForListA(){
		return aDao.queryForListA();
	}
	
	public List<Map<String, Object>> queryForListB(){
		return bDao.queryForListB();
	}
}

5、spring配置文件

<?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:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
	http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd 
	http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd 
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd 
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"
	default-lazy-init="true">


	<description>Spring公共配置</description>
<context:annotation-config />
    <context:component-scan base-package="xx.xx.test" >
    </context:component-scan>
	<!-- 定义受环境影响易变的变量 -->
	<bean
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
		<property name="ignoreResourceNotFound" value="true" />
		<property name="ignoreUnresolvablePlaceholders" value="true" />
		<property name="locations">
			<list>
				<!-- 数据库配置 -->
				<value>classpath*:config/jdbc.properties</value>
				<!-- 其它基础配置 -->
				<value>classpath*:config/common.properties</value>
			</list>
		</property>
	</bean>

	<!-- 激活利用注解进行装配 -->
	<context:component-scan base-package="xx.xx.test">
		<context:exclude-filter type="annotation"
			expression="org.springframework.stereotype.Controller" />
	</context:component-scan>

	<!-- 数据源配置A -->
	<bean id="dataSource"
		class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close" >
		<property name="driverClass" value="${JDBC.Driver}"/>
		<property name="jdbcUrl" value="${JDBC.ConnectionURL}"/>
		<property name="user" value="${JDBC.Username}"/>
		<property name="password" value="${JDBC.Password}"/>
		<property name="autoCommitOnClose" value="true" />
		<property name="checkoutTimeout" value="${JDBC.checkoutTimeout}" />
		<property name="initialPoolSize" value="${JDBC.minPoolSize}" />
		<property name="minPoolSize" value="${JDBC.minPoolSize}" />
		<property name="maxPoolSize" value="${JDBC.maxPoolSize}" />
		<property name="maxIdleTime" value="${JDBC.maxIdleTime}" />
		<property name="acquireIncrement" value="${JDBC.acquireIncrement}" />
		<property name="numHelperThreads" value="${JDBC.numHelperThreads}"/>
	</bean>

	<bean id="exporter" class="org.springframework.jmx.export.MBeanExporter"
		lazy-init="false">
		<property name="beans">
			<map>
				<entry key="bean:name=adataSource" value-ref="dataSource" />
			</map>
		</property>
	</bean>


	<!-- 数据源配置B Mssql-->
	<bean id="dataSourceMssql"
		class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close" >
		<property name="driverClass" value="${MSSQL.JDBC.Driver}"/>
		<property name="jdbcUrl" value="${MSSQL.JDBC.ConnectionURL}"/>
		<property name="user" value="${MSSQL.JDBC.Username}"/>
		<property name="password" value="${MSSQL.JDBC.Password}"/>
		<property name="autoCommitOnClose" value="true" />
		<property name="checkoutTimeout" value="${MSSQL.checkoutTimeout}" />
		<property name="initialPoolSize" value="${MSSQL.minPoolSize}" />
		<property name="minPoolSize" value="${MSSQL.minPoolSize}" />
		<property name="maxPoolSize" value="${MSSQL.maxPoolSize}" />
		<property name="maxIdleTime" value="${MSSQL.maxIdleTime}" />
		<property name="acquireIncrement" value="${MSSQL.acquireIncrement}" />
		<property name="numHelperThreads" value="${MSSQL.numHelperThreads}"/>
	</bean>

	<bean id="exporterMssql" class="org.springframework.jmx.export.MBeanExporter"
		lazy-init="false">
		<property name="beans">
			<map>
				<entry key="bean:name=bdataSourceMssql" value-ref="dataSourceMssql" />
			</map>
		</property>
	</bean>

	<!-- 事务配置A -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<aop:config>
		<aop:pointcut expression="execution(* *..*ServiceImpl.*(..))"
			id="serviceMethods" />
		<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceMethods" />
	</aop:config>
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="save*" propagation="REQUIRED" />
			<tx:method name="cancel*" propagation="REQUIRED" />
			<tx:method name="insert*" propagation="REQUIRED" />
			<tx:method name="remove*" propagation="REQUIRED" />
			<tx:method name="delete*" propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
			<tx:method name="set*" propagation="REQUIRED" />
			<tx:method name="*" read-only="true" />
		</tx:attributes>
	</tx:advice>


	<!-- 事务配置B Mssql -->
	<bean id="transactionManagerMssql"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSourceMssql" />
	</bean>
	<aop:config>
		<aop:pointcut expression="execution(* *..*ServiceImpl.*(..))"
			id="serviceMethodsMssql" />
		<aop:advisor advice-ref="txAdviceMssql" pointcut-ref="serviceMethodsMssql" />
	</aop:config>
	<tx:advice id="txAdviceMssql" transaction-manager="transactionManagerMssql">
		<tx:attributes>
			<tx:method name="save*" propagation="REQUIRED" />
			<tx:method name="cancel*" propagation="REQUIRED" />
			<tx:method name="insert*" propagation="REQUIRED" />
			<tx:method name="remove*" propagation="REQUIRED" />
			<tx:method name="delete*" propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
			<tx:method name="set*" propagation="REQUIRED" />
			<tx:method name="*" read-only="true" />
		</tx:attributes>
	</tx:advice>


	<!-- iBatis配置 -->
	<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:sqlmap/sqlmap-config.xml" />
	</bean>

	<bean id="testADaoImpl" class="xx.xx.test.dao.impl.TestADaoImpl"> 
	      <property name="sqlMapClient" ref="sqlMapClient" />  
	</bean>
	
	<!-- iBatis配置Mssql -->
	<bean id="sqlMapClientMssql" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="dataSource" ref="dataSourceMssql" />
		<property name="configLocation" value="classpath:sqlmap/sqlmap-mssql-config.xml" />
	</bean>

	<bean id="testBDaoImpl" class="xx.xx.test.dao.impl.TestBDaoImpl">  
	      <property name="sqlMapClient" ref="sqlMapClientMssql" />  
	</bean>

    

</beans>

6、sqlmap文件里面分别引入两个不同数据源的文件

sqlmap-config.xm

如：<sqlMap resource="sqlmap/a.xml" />

sqlmap-mssql-config.xml

如：<sqlMap resource="sqlmap/b.xml" />

7、数据配置

jdbc.properties

#SqlServer
MSSQL.JDBC.Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
MSSQL.JDBC.ConnectionURL=jdbc:sqlserver://IP:1433;DatabaseName=数据库名
MSSQL.JDBC.Username=TEST
MSSQL.JDBC.Password=******

MSSQL.minPoolSize=1
MSSQL.maxPoolSize=10
MSSQL.maxIdleTime=30
MSSQL.maxIdleTimeExcessConnections=180
MSSQL.checkoutTimeout=60000
MSSQL.acquireIncrement=1
MSSQL.numHelperThreads=1 


#MySql
JDBC.Driver=com.mysql.jdbc.Driver
JDBC.ConnectionURL=  jdbc:mysql://IP:3306/数据库名?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
JDBC.Username=TEST
JDBC.Password=******