JBoss database connection pool for auto commit

Question

We use JBoss 4 and Oracle with a JNDI data source configured using a JBoss XML data file.

They recently realized that all connections received from the default data source have the auto-commit property set to true. However, we rely on Oracle stored procedures and want to control commits in stored procedures.

We use simple JDBC calls as well as the Spring StoredProcedure wrapper to call stored procedures from JBoss. Trying to set auto-commit from Jaboss datasource XML doesn't actually work.

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

Edit: I am adding a data source configuration that 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 data sources:

• <none-TX data source>
• <locally-TX data source>
• <XA data source>

JBoss Community ConfigDataSources

deploy/oracle-ds.xml : < local-tx-datasource > < xa-datasource > , auto-commit false.

. : autoCommit false J2EE ^TM 1.5 15.5.3.1 .

Answer 2

DataSource, , . JNDI. , , getConnection(), :

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

Answer 3

In my case, I used Jboss EAP 6 (AS7), and the solution for me created a wrapper for the WrapperDataSource class and added an instruction:

wc.setAutoCommit(false);

in getConnection methods to set to automatically join false when my application receives a connection from the JDBC pool.

This link explains how to do it.

http://antuansoft.blogspot.com.es/2017/01/jboss-datasources-set-autocommit-example.html

Hope to help someone