XApool连接池那些坑爹的有关问题

2013-03-29

XApool连接池那些坑爹的问题? ? ? ? 最近项目需要使用到Oracle和Mysql操作一些数据库为了操作业务事务一致

XApool连接池那些坑爹的问题

? ? ? ? 最近项目需要使用到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.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsdhttp://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"/></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" destroy-method="shutdown"><property name="dataSource"><bean id="oracleJtaDS" /></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.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsdhttp://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" /><!-- JTA事务管理器 --><bean id="txManager"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.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsdhttp://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"  lazy-init="true" />  <bean id="oracleLobHandler" lazy-init="true">      <property name="nativeJdbcExtractor">          <ref local="nativeJdbcExtractor" />      </property>  </bean>          <!-- Spring 提供给IBatis的SqlMap的配置 -->      <bean id="sqlMapClientForMySQL"        ref="sqlMapClientForMySQL"/></bean>            <!-- Spring 提供给IBatis的SqlMap的配置 -->      <bean id="sqlMapClientForOracle"        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)