配置第一个数据源
<bean id="tmsDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="driverUrl" value="jdbc:oracle:thin:@127.0.0.1:1521:tms" />
<property name="user" value="mgs" />
<property name="password" value="o123" />
配置第二个数据源
<bean id="hisDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="driverUrl" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL" />
<property name="user" value="system" />
<property name="password" value="o123" />
整合两个数据源
<!-- 整合两个数据源 -->
<bean id="dynamicDataSource" class="com.pap.datasource.DynamicDataSource">
<!-- 通过key-value的形式来关联数据源 -->
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="tmsDataSource" key="tmsDataSource"></entry>
<entry value-ref="hisDataSource" key="hisDataSource"></entry>
</map>
</property>
<!-- 设置默认数据源 -->
<property name="defaultTargetDataSource" ref="tmsDataSource">
</property>
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource" />
<property name="packagesToScan">
<list>
<value>com.pap.entity</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<!-- <prop key="hibernate.hbm2ddl.auto">update</prop> -->
</props>
</property>
</bean>
DynamicDataSource类代码：
public class DynamicDataSource extends AbstractRoutingDataSource {
public static final Logger logger = Logger.getLogger(DynamicDataSource.class.toString());
@Override
protected Object determineCurrentLookupKey() {
logger.info("当前数据源 :" + DataSourceContextHolder.getDataSourceType());
return DataSourceContextHolder.getDataSourceType();
}
}
DataSourceContextHolder类代码：
public class DataSourceContextHolder {
public static final String DATA_SOURCE_TMS = "tmsDataSource";
public static final String DATA_SOURCE_HIS = "hisDataSource";
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setDataSourceType(String customerType) {
contextHolder.set(customerType);
}
public static String getDataSourceType() {
return contextHolder.get();
}
public static void clearDataSourceType() {
contextHolder.remove();
}
}
用DataSourceContextHolder.setDataSourceType("hisDataSource");去切换数据源执行查询时，控制台报错：
2014-2-17 16:36:36 com.pap.datasource.DynamicDataSource determineCurrentLookupKey
信息: 当前数据源 :null
[PAPService-INFO] 2014-02-17 16:36:37 - org.springframework.orm.hibernate3.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:416) Using DataSource [com.pap.datasource.DynamicDataSource@5878d2] of Hibernate SessionFactory for HibernateTransactionManager
2014-2-17 16:36:38 com.pap.datasource.DynamicDataSource determineCurrentLookupKey
信息: 当前数据源 :null
Hibernate:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select this_.CLINIC_DATE as CLINIC1_2_2_, this_.CLINIC_LABEL as CLINIC4_2_2_, this_.TIME_DESC as TIME2_2_2_, this_.REGIST_PRICE as REGIST3_2_2_, clinicinde2_.CLINIC_LABEL as CLINIC1_3_0_, clinicinde2_.CLINIC_TYPE as CLINIC2_3_0_, clinicinde2_.CLINIC_DEPT as CLINIC4_3_0_, clinicinde2_.DOCTOR as DOCTOR3_0_, deptdict3_.DEPT_CODE as DEPT1_4_1_, deptdict3_.DEPT_ALIAS as DEPT2_4_1_, deptdict3_.DEPT_NAME as DEPT3_4_1_ from OUTPADM.CLINIC_FOR_REGIST this_ inner join OUTPADM.CLINIC_INDEX clinicinde2_ on this_.CLINIC_LABEL=clinicinde2_.CLINIC_LABEL left outer join COMM.DEPT_DICT deptdict3_ on clinicinde2_.CLINIC_DEPT=deptdict3_.DEPT_CODE where this_.CLINIC_DATE=? and this_.TIME_DESC in (?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1046)
at com.pap.dao.impl.BaseDaoImpl.find(BaseDaoImpl.java:45)
at com.pap.service.impl.BaseServiceImpl.queryClinicForRegist(BaseServiceImpl.java:120)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy26.queryClinicForRegist(Unknown Source)
at com.pap.ws.PAPWebService.queryClinicRegist(PAPWebService.java:167)
at com.pap.test.TestService.testDataSource(TestService.java:23)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(Par
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2545)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
at org.springframework.orm.hibernate3.HibernateTemplate$36.doInHibernate(HibernateTemplate.java:1056)
at org.springframework.orm.hibernate3.HibernateTemplate$36.doInHibernate(HibernateTemplate.java:1046)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
... 43 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490)
... 51 more
在线等答案。
