XApool连接池那些坑爹的问题

殷承恩

2023-12-01

最近项目需要使用到Oracle和Mysql操作一些数据库为了操作业务事务一致性，采用JTA事务处理跨数据库的事务，采用JOTM，但是出现一些列问题。

主要配置applicationContext-datasource.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:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	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/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">
	<!--指定Spring配置中用到的属性文件 -->
	<bean id="propertyConfig"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:jdbc.properties</value>
			</list>
		</property>
	</bean>
	<!-- 数据源A -->
	<bean id="mysqlJta" class="org.enhydra.jdbc.pool.StandardXAPoolDataSource"		destroy-method="shutdown">
		<property name="dataSource">
				<bean id="mysqlJtaDS" class="org.enhydra.jdbc.standard.StandardXADataSource"		destroy-method="shutdown">
				<property name="transactionManager">
					<ref bean="jotm" />
				</property>
				<property name="driverName" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="user" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
				<property name="minCon" value="1" />
			</bean>
		</property>
		<property name="transactionManager">
			<ref bean="jotm" />
		</property>
		<property name="maxSize">
			<value>10</value>
		</property>
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>



	<!-- 数据源B -->
	<bean id="oracleJta" class="org.enhydra.jdbc.pool.StandardXAPoolDataSource" destroy-method="shutdown">
		<property name="dataSource">
				<bean id="oracleJtaDS" class="org.enhydra.jdbc.standard.StandardXADataSource"		destroy-method="shutdown">
					<property name="transactionManager">
						<ref bean="jotm" />
					</property>
					<property name="driverName" value="${jdbc2.driver}" />
					<property name="url" value="${jdbc2.url}" />
					<property name="user" value="${jdbc2.username}" />
					<property name="password" value="${jdbc2.password}" />
					<property name="minCon" value="1" />
				</bean>
		</property>
		<property name="transactionManager">
			<ref bean="jotm" />
		</property>
		<property name="maxSize">
			<value>10</value>
		</property>
		<property name="user" value="${jdbc2.username}" />
		<property name="password" value="${jdbc2.password}" />
	</bean>
</beans>

采用jotm事务的applicationContext-jotm.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:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	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/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">
	<!-- JOTM本地实例 -->
	<bean id="jotm" class="com.easyway.ibatis.framework.jta.JotmFactoryBean" />

	<!-- JTA事务管理器 -->
	<bean id="txManager"
		class="org.springframework.transaction.jta.JtaTransactionManager">
		<property name="userTransaction" ref="jotm" />
	</bean>

	<!-- JTA事务传播特性 -->
	<tx:advice id="txAdviceJta" transaction-manager="txManager">
		<tx:attributes>
			<tx:method name="Jta*" isolation="DEFAULT" propagation="REQUIRED"
				rollback-for="Exception" />
			<tx:method name="*" read-only="true" />
		</tx:attributes>
	</tx:advice>

	<aop:config>
		<aop:advisor pointcut="execution(* com.easyway.ibatis.app.modules.service.impl..*(..))"
			advice-ref="txAdviceJta" />
	</aop:config>
</beans>

ibatis的配置如下：applicationContext-ibatis.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:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
    xmlns:p="http://www.springframework.org/schema/p"
	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/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">
    
    <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"  lazy-init="true" />  

	<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true">  
	    <property name="nativeJdbcExtractor">  
	        <ref local="nativeJdbcExtractor" />  
	    </property>  
	</bean>  
	
    
    <!-- Spring 提供给IBatis的SqlMap的配置 -->  
    <bean id="sqlMapClientForMySQL"
        class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
       <property name="configLocation"> 
           <value>classpath:/sql-map-config_A.xml</value>  
       </property>  
       <property name="dataSource">  
          <ref  bean="mysqlJta"/>  
       </property>  
    </bean>
	
	<!--创建SqlMapClientTemplate类  -->
	<bean id="sqlMapClientTemplateForMySQL" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
		<property name="sqlMapClient" ref="sqlMapClientForMySQL"/>
	</bean>
	
	
    	
    
    <!-- Spring 提供给IBatis的SqlMap的配置 -->  
    <bean id="sqlMapClientForOracle"
        class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
       <property name="configLocation">  
           <value>classpath:/sql-map-config_B.xml</value>  
       </property>  
       <property name="dataSource">  
          <ref  bean="oracleJta"/>  
       </property>  
    </bean>
    
    	<!--创建SqlMapClientTemplate类  -->
	<bean id="sqlMapClientTemplateForOracle" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
		<property name="sqlMapClient" ref="sqlMapClientForOracle"/>
	</bean>
	
</beans>

执行测试错误如下：

2013-03-25 15:50:07 - INFO org.springframework.context.support.ClassPathXmlApplicationContext - Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1a457b6: startup date [Mon Mar 25 15:50:07 CST 2013]; root of context hierarchy

2013-03-25 15:50:07 - INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [applicationContext-datasource.xml]

2013-03-25 15:50:07 - INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [applicationContext-jotm.xml]

2013-03-25 15:50:07 - INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [applicationContext-component.xml]

2013-03-25 15:50:07 - INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [applicationContext-ibatis.xml]

2013-03-25 15:50:08 - INFO org.springframework.beans.factory.config.PropertyPlaceholderConfigurer - Loading properties file from class path resource [jdbc.properties]

2013-03-25 15:50:08 - INFO org.springframework.beans.factory.support.DefaultListableBeanFactory - Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@172e449: defining beans [propertyConfig,mysqlJta,oracleJta,jotm,txManager,txAdviceJta,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.aop.support.DefaultBeanFactoryPointcutAdvisor#0,tabADAO,tabBDAO,stuSrv,nativeJdbcExtractor,oracleLobHandler,sqlMapClientForMySQL,sqlMapClientTemplateForMySQL,sqlMapClientForOracle,sqlMapClientTemplateForOracle]; root of factory hierarchy

2013-03-25 15:50:08 - INFO org.objectweb.jotm - CAROL initialization

2013-03-25 15:50:08 - INFO org.ow2.carol.util.configuration.ConfigurationRepository - No protocols were defined for property 'carol.protocols', trying with default protocol = 'jrmp'.

2013-03-25 15:50:08 - INFO org.objectweb.jotm - JOTM started with a local transaction factory

2013-03-25 15:50:08 - INFO org.springframework.transaction.jta.JtaTransactionManager - Using JTA UserTransaction: org.objectweb.jotm.Current@1e2befa

2013-03-25 15:50:08 - INFO org.springframework.transaction.jta.JtaTransactionManager - Using JTA TransactionManager: org.objectweb.jotm.Current@1e2befa

2013-03-25 15:50:08 - DEBUG java.sql.Connection - {conn-100000} Connection

2013-03-25 15:50:08 - DEBUG java.sql.Connection - {conn-100000} Preparing Statement: insert into tab_a( id, name, address ) values ( ?, ?, ? )

2013-03-25 15:50:08 - ERROR org.enhydra.jdbc.xapool - Error Exception in GenericPool:start java.sql.SQLException: Cannot get connection for URL jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8 : Access denied for user 'root '@'localhost' (using password: YES)

2013-03-25 15:50:08 - ERROR org.enhydra.jdbc.xapool - GenericPool:getFromPool Error Exception in GenericPool:getFromPool

java.sql.SQLException: Cannot get connection for URL jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8 : Access denied for user 'root '@'localhost' (using password: YES)

at org.enhydra.jdbc.standard.StandardDataSource.getConnection(StandardDataSource.java:216)

at org.enhydra.jdbc.standard.StandardPooledConnection.<init>(StandardPooledConnection.java:65)

at org.enhydra.jdbc.standard.StandardXAConnection.<init>(StandardXAConnection.java:81)

at org.enhydra.jdbc.standard.StandardXADataSource.getXAConnection(StandardXADataSource.java:110)

at org.enhydra.jdbc.pool.StandardXAPoolDataSource.create(StandardXAPoolDataSource.java:159)

at org.enhydra.jdbc.pool.GenericPool.getFromPool(GenericPool.java:247)

at org.enhydra.jdbc.pool.GenericPool.checkOut(GenericPool.java:351)

at org.enhydra.jdbc.pool.StandardPoolDataSource.getConnection(StandardPoolDataSource.java:194)

at org.enhydra.jdbc.pool.StandardPoolDataSource.getConnection(StandardPoolDataSource.java:164)

at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)

at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:224)

at $Proxy1.prepareStatement(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(ConnectionLogProxy.java:53)

at $Proxy2.prepareStatement(Unknown Source)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:497)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:75)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:393)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)

at org.springframework.orm.ibatis.SqlMapClientTemplate$8.doInSqlMapClient(SqlMapClientTemplate.java:366)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)

at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:364)

at com.easyway.ibatis.app.modules.dao.impl.TabADAOImpl.saveTabA(TabADAOImpl.java:27)

at com.easyway.test.SpringIbatisJotmTM.main(SpringIbatisJotmTM.java:106)

java.sql.SQLException: SQLException in StandardPoolDataSource:getConnection no connection available java.sql.SQLException: Cannot get connection for URL jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8 : Access denied for user 'root '@'localhost' (using password: YES)

at org.enhydra.jdbc.pool.StandardPoolDataSource.getConnection(StandardPoolDataSource.java:204)