How to configure C3P0 to use datasource URL for connection at the oracle database in jboss AS7

Question

We are having trouble to configure c3p0 in jboss. There are two configurations files, and we don't know which we have to change:

Jboss datasource app-ds.xml:

<datasources>
    <datasource jta="false" jndi-name="java:/TestJNDI" pool-name="TestPool" use-ccm="false">
        <connection-url>jdbc:oracle:thin:@server_test:port:database</connection-url>
        <connection-property name="schema">schema</connection-property>
        <driver-class>oracle.jdbc.OracleDriver</driver-class>
        <driver>ojdbc6.jar</driver>
        <pool>
                <min-pool-size>20</min-pool-size>
                <max-pool-size>400</max-pool-size>
        </pool>
        <security>
            <user-name>user</user-name>
            <password>pass</password>
        </security>
        <validation>
            <validate-on-match>false</validate-on-match>
            <background-validation>false</background-validation>
        </validation>
        <statement>
            <share-prepared-statements>false</share-prepared-statements>
        </statement>
    </datasource>   
</datasources>

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0">
    <persistence-unit name="Oracle">
        <description>Oracle database connection</description>
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>entity1</class>
        <!-- entities -->
        <class>entityN</class>
        <properties>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.connection.datasource" value="java:/TestJNDI"/>
            <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" /> 
            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
            <property name="hibernate.generate_statistics" value="true"/>  
            
            <!-- Important -->
            <property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" /> 
            <property name="hibernate.c3p0.max_size" value="100" />
            <property name="hibernate.c3p0.min_size" value="0" />
            <property name="hibernate.c3p0.acquire_increment" value="1" />
            <property name="hibernate.c3p0.idle_test_period" value="300" />
            <property name="hibernate.c3p0.max_statements" value="0" />
            <property name="hibernate.c3p0.timeout" value="100" />
        </properties>
    </persistence-unit>
</persistence>

And the log:

INFO  [org.jboss.as.jpa] (MSC service thread 1-4) JBAS011401: Read persistence.xml for Oracle
WARN  [org.jboss.jaxrs] (MSC service thread 1-15) JBAS011204: resteasy.scan found and ignored in web.xml. This is not necessary, as Resteasy will use the container integration in the JAX-RS 1.1 specification in section 2.3.2
INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-15) JBAS010403: Deploying JDBC-compliant driver class oracle.jdbc.OracleDriver (version 11.2)
INFO  [org.jboss.as.jpa] (MSC service thread 1-8) JBAS011402: Starting Persistence Unit Service 'AppTestEAR.ear/AppTest.war#Oracle'
INFO  [org.hibernate.ejb.Ejb3Configuration] (MSC service thread 1-8) HHH000204: Processing PersistenceUnitInfo [
    name: Oracle
    ...]
INFO  [org.hibernate.service.jdbc.connections.internal.ConnectionProviderInitiator] (MSC service thread 1-8) HHH000130: Instantiating explicit connection provider: org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
INFO  [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider] (MSC service thread 1-8) HHH010002: C3P0 using driver: oracle.jdbc.driver.OracleDriver at URL: null
INFO  [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider] (MSC service thread 1-8) HHH000046: Connection properties: {autocommit=true, release_mode=auto}
INFO  [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider] (MSC service thread 1-8) HHH000006: Autocommit mode: true
INFO  [com.mchange.v2.log.MLog] (MSC service thread 1-8) MLog clients using log4j logging.
INFO  [com.mchange.v2.c3p0.C3P0Registry] (MSC service thread 1-8) Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
INFO  [com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource] (MSC service thread 1-8) Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@20860e3b [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@8b71134e [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2zkodt9o11wh4uo1mijhxn|327556d1, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 180, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 200, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 20, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@d6b12b99 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 2zkodt9o11wh4uo1mijhxn|2c53cfe3, jdbcUrl -> null, properties -> {autocommit=true, release_mode=auto} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 2zkodt9o11wh4uo1mijhxn|4fcb6c, numHelperThreads -> 3 ]
WARN  [com.mchange.v2.async.ThreadPoolAsynchronousRunner] (Timer-3) com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1b6cdb87 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
WARN  [com.mchange.v2.async.ThreadPoolAsynchronousRunner] (Timer-3) com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1b6cdb87 -- APPARENT DEADLOCK!!! Complete Status: 

The problem is at:

HHH010002: C3P0 using driver: oracle.jdbc.driver.OracleDriver at URL: null

We want use the credentials (the URL) provided in the datasource in the jboss (business requirement),

So the question is: How to configure c3p0 to use datasource URL for connection at the oracle database in jboss AS7?

Answer 1

Can you touch hibernate.cfg.xml? You could add something like

<hibernate-configuration>
<session-factory> name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>
<property name="hibernate.connection.url">jdbc:oracle:thin:@xyz:1521:sid</property>
<property name="hibernate.connection.username">u</property>
<property name="hibernate.connection.password">p</property>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">30</property>
<property name="hibernate.c3p0.timeout">20</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>      
</session-factory>
</hibernate-configuration>

Answer 2

Just to add in hibernate config you can connect to datasource as below

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.datasource">java:comp/env/jdbc/MySQLDS</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="javax.persistence.validation.mode">none</property>
        <!-- Mapping with model class containing annotations -->
    <mapping class="com.study.vo.User"/>
    </session-factory>
</hibernate-configuration>

Answer 3

It looks like what's going on is that the DataSource is being constructed according to the config in persistence.xml and some of the JDBC-standard properties (e.g. minPoolSize, maxPoolSize) are being overridden by settings in app-ds.xml.

I think the main issue is that you do not configure the JDBC url in persistence.xml. (c3p0's jdbcUrl is not is JDBC-standard property, so app-ds.xmlis not setting it.) You should add to persistence.xml something like:

<property name="hibernate.connection.url">jdbc:oracle:thin:@server_test:port:database</property>

user and password also appear not to be set. Add to persistence.xml something like:

<property name="hibernate.connection.username">user</property>
<property name="hibernate.connection.password">pass</property>

(To see what settings have and have not taken, scroll right to see the config information on the log line beginning Initializing c3p0 pool...)

I'd try removing as much as possible from the app-ds.xml, so you don't confusingly have config in two places. You might be able to get away with...

<datasources>
    <datasource jta="false" jndi-name="java:/TestJNDI" pool-name="TestPool" use-ccm="false" />
</datasources>

...but maybe not.