It took a lot of searching and experimenting, but I finally started working. Here are my results:
- enhydra xapool is a terrible connection pool. I will not list the problems that he caused, because it does not matter. The latest version of this pool has not been updated since December 2006. This is a dead project.
- I put c3p0 in my application context and got it working pretty easily. But for some reason, it just doesn't support rollbacks even within the same method. If I mark the method as @Transactional, then insert into the table and then throw a RuntimeException (which is definitely not declared in the throw list of the method, because the method does not have a throw list) it will still save the insert in this table. He will not roll back.
- I was going to try Apache DBCP, but there were a lot of complaints about it in my search, so I was not worried.
- I tried Bitronix and had a lot of problems to get it working properly under Tomcat, but as soon as I understood the magic configuration, it works great. What follows is all you need to do to properly configure it.
- I briefly talked about the Atomkos connection pool. It seems like this should be fine, but at first I started working with Bitronix, so I did not try to use it.
The configuration below works in standalone unit tests and under Tomcat. That was the main problem. Most of the examples I found on how to configure Spring using Bitronix suggest that I use JBoss or some other full container.
The first bit of configuration is the part that installs the Bitronix transaction manager.
<bean id="btmConfig" factory-method="getConfiguration" class="bitronix.tm.TransactionManagerServices"> <property name="disableJmx" value="true" /> </bean> <bean id="btmManager" factory-method="getTransactionManager" class="bitronix.tm.TransactionManagerServices" depends-on="btmConfig" destroy-method="shutdown"/> <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager"> <property name="transactionManager" ref="btmManager" /> <property name="userTransaction" ref="btmManager" /> <property name="allowCustomIsolationLevels" value="true" /> </bean> <tx:annotation-driven transaction-manager="transactionManager" />
The main difference between this code and the examples I found is the "disableJmx" property. It throws runtime exceptions if you are not using JMX but not including it.
The next bit of the configuration is the connection pool data source. Note that the connection pool name is not the normal oracle class "oracle.jdbc.driver.OracleDriver". This is an XA data source. I do not know what the equivalent class will be in other databases.
<bean id="dataSource" class="bitronix.tm.resource.jdbc.PoolingDataSource" init-method="init" destroy-method="close"> <property name="uniqueName" value="dataSource-BTM" /> <property name="minPoolSize" value="1" /> <property name="maxPoolSize" value="4" /> <property name="testQuery" value="SELECT 1 FROM dual" /> <property name="driverProperties"><props> <prop key="URL">${jdbc.url}</prop> <prop key="user">${jdbc.username}</prop> <prop key="password">${jdbc.password}</prop> </props></property> <property name="className" value="oracle.jdbc.xa.client.OracleXADataSource" /> <property name="allowLocalTransactions" value="true" /> </bean>
Please also note that the unique name must be different from any other data sources that you have configured.
Of course, testQuery should be specific to the database you are using. The driver properties are specific to the database class that I am using. OracleXADataSource for some stupid reason has different names for OracleDriver for the same value.
To allow allowLocalTransactions, true was set for me. I found recommendations to NOT install it online. But that seems impossible. It just won't work if it sets to false. I am not sufficiently aware of these things to know why this is so.
Finally, we need to configure the factory entity manager.
<util:map id="jpa_property_map"> <entry key="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup"/> <entry key="hibernate.current_session_context_class" value="jta"/> </util:map> <bean id="dataSource-emf" name="accessControlDb" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="persistenceXmlLocation" value="classpath*:META-INF/foo-persistence.xml" /> <property name="jpaVendorAdapter"> <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="showSql" value="true"/> <property name="databasePlatform" value="org.hibernate.dialect.Oracle10gDialect"/> </bean> </property> <property name="jpaPropertyMap" ref="jpa_property_map"/> <property name="jpaDialect"><bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/></property> </bean>
Note that the dataSource property refers to the identifier of the declared data source. PersistenceXmlLocation refers to a persistence XML file that exists somewhere in the classpath. Class path *: indicates that it can be in any bank. Without * he will not find him if he is in the bank for some reason.
I found util: map to conveniently use jpaPropertyMap values in one place, so I don't need to repeat them when I use multiple entity factory managers in the same application context.
Please note that the above utility: will not work unless you include the correct settings in the external beans element. Here is the header of the xml file I'm using:
<?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:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">
Finally, in order for Bitronix (or, apparently, any cpool that supports two-phase adoption) to work with Oracle, you need to run the following grants as a SYS user. (See http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.express.doc/info/exp/ae/rtrb_dsaccess2.html and http: //docs.codehaus.org/display/BTM/FAQ and http://docs.codehaus.org/display/BTM/JdbcXaSupportEvaluation#JdbcXaSupportEvaluation-Oracle )
grant select on pending_trans$ to <someUsername>; grant select on dba_2pc_pending to <someUsername>; grant select on dba_pending_transactions to <someUsername>; grant execute on dbms_system to <someUsername>;
These grants should be run for any user for whom the connection pool is configured, whether or not you really change anything. He seems to be looking for these tables when establishing a join.
A few other issues:
- You cannot query tables that are remote synonyms in Oracle, but inside the Spring @Transactional block when using Bitronix (you get ORA-24777). Use materialized views or a separate EntityManager that directly points to another database.
- For some reason, btmConfig in applicationContext.xml has problems setting configuration values. Instead, create the bitronix-default-config.properties file. The configuration values you can use are at http://docs.codehaus.org/display/BTM/Configuration13 . Some other configuration data for this file is located at http://docs.codehaus.org/display/BTM/JdbcConfiguration13 , but I have not used it.
- Bitronix uses some local files to store transactional materials. I don’t know why, but I know that if you have several web applications with local connection pools, you will have problems because they will both try to access the same files. To fix this, specify different values for bitronix.tm.journal.disk.logPart1Filename and bitronix.tm.journal.disk.logPart2Filename in the bitronix-default-config.properties file for each application.
- Bitronix javadocs is at http://www.bitronix.be/uploads/api/index.html .
This is pretty much the case. It is very inconvenient to make it work, but now it works, and I'm happy. I hope that all of this will help others who are experiencing the same problems as me to make it all work.