JBoss database connection pool auto-commit behavior

Question

We are using JBoss 4 and Oracle with a JNDI datasource configured via JBoss datasource XML file.

Recently realized that all connections acquired from the datasource by default has the auto-commit property set to true. However, we rely on Oracle stored procedures and want to control the commits within the stored procedures.

We use plain JDBC calls as well as Spring StoredProcedure wrapper to call stored procedures from JBoss. Trying to set the auto-commit from JBoss datasource XML did not really work.

I can only see that for each connection we get from datasource we can set the auto-commit property to false, but does someone know how we could configure this in one place?

Edit: I am adding the datasource configuration we use:

<local-tx-datasource>
    <jndi-name>some name</jndi-name>
    <connection-url>jdbc:oracle:thin:@(description=(address_list=(address=(protocol=tcp)(port=1521)(host=xxx))(address=(protocol=tcp)(port=1521)(host=xxx)))(load_balance = yes)(connect_data=(SERVICE_NAME=xxx)(SERVER=DEDICATED)))</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>xxxr</user-name>
    <password>xxx</password>
    <!-- Checks the Oracle error codes and messages for fatal errors -->
    <exception-sorter-class-name>
        org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
    </exception-sorter-class-name>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>25</max-pool-size>
    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
    <metadata>
        <type-mapping>Oracle10g</type-mapping>
    </metadata>
</local-tx-datasource>

We also used , but no change...

Answer 1

There are three main types of datasource:

• <no-tx-datasource>
• <local-tx-datasource>
• <xa-datasource>

JBoss Community ConfigDataSources

Your deploy/oracle-ds.xml file should use either <local-tx-datasource> or <xa-datasource> to get connections with auto-commit set to false.

See the reply by Scott Stark in this post: How to Declaratively set autoCommit to false or J2EE^TM Connector Architecture Specification Version 1.5 section 15.5.3.1 for the original source.

Answer 2

You could create your own DataSource, which is subclassed from the one you are using. Store that in JNDI. The only method you would need to override is getConnection(), which would do:

Connection public getConnection()
{
  Connection conn = super.getConnection();
  conn.setAutoCommit(true);
  return conn;
}