Two c3p0 pools from same database schema when using JPA/Hibernate

Question

My question is somewhat related to this; Can I use multiple C3P0 datasources for DB instance?

I have two Spring web services which accesses the same mySQL database schema. And I am using c3p0 to pool database connections. I am accessing the database using JPA/Hibernate

I have configured two data base pools, entitity manager factories and transaction managers similar to the above question.

In the first service

 <bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close" p:driverClass="com.mysql.jdbc.Driver" p:jdbcUrl="jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull" 
    p:user="test1" p:password="test1"
    p:acquireIncrement="1" p:idleConnectionTestPeriod="10" p:maxPoolSize="25"
    p:maxStatements="20" p:minPoolSize="5" >
 </bean>

<bean id="entityManagerFactory1"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceXmlLocation" value="classpath*:META-INF/persistence.xml"></property>
        <property name="persistenceUnitName" value="hibernatePersistenceUnit1" />
        <property name="dataSource" ref="dataSource1" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true" />
            </bean>
        </property>
    </bean>

<bean id="transactionManager1" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="dataSource" ref="dataSource1" />
    <property name="entityManagerFactory" ref="entityManagerFactory1" />
</bean>

In the second service

 <bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close" p:driverClass="com.mysql.jdbc.Driver" p:jdbcUrl="jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull" 
    p:user="test1" p:password="test1"
    p:acquireIncrement="1" p:idleConnectionTestPeriod="10" p:maxPoolSize="25"
    p:maxStatements="20" p:minPoolSize="5" >
 </bean>

<bean id="entityManagerFactory2"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceXmlLocation" value="classpath*:META-INF/persistence.xml"></property>
        <property name="persistenceUnitName" value="hibernatePersistenceUnit2" />
        <property name="dataSource" ref="dataSource2" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true" />
            </bean>
        </property>
    </bean>

<bean id="transactionManager2" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="dataSource" ref="dataSource2" />
    <property name="entityManagerFactory" ref="entityManagerFactory2" />
</bean>

Also I have annotated the two data access classes with relevant transaction manager

@Transactional("transactionManager1")
public class JPADataAccessObject1<T, ID extends Serializable> implements DataAccessObject<T, ID>

@Transactional("transactionManager2")
    public class JPADataAccessObject2<T, ID extends Serializable> implements DataAccessObject<T, ID>

One of my services has a spring message channel which uses a jpa-inbound-channel-adapter

<int:channel id="testChannel">
        <int:priority-queue capacity="20" />
    </int:channel>

<int-jpa:inbound-channel-adapter
        channel="testChannel" entity-class="com.test.TestObject"
        entity-manager-factory="entityManagerFactory1" auto-startup="true"
        jpa-query="SELECT x FROM TestObject AS x WHERE x.testColumns LIKE '1'"
        delete-after-poll="true">
        <int:poller fixed-rate="5000">
            <int:transactional propagation="REQUIRED"
                transaction-manager="transactionManager1" />
        </int:poller>
    </int-jpa:inbound-channel-adapter>

Now the problem is when I deploy both services in tomcat 7 they become not responding. Any idea on this?

score 0 · Accepted Answer · answered May 27 '14 at 10:31

I went on a different approach and setup a container managed database pool in tomcat and referred as a JNDI resource.

In Server.xml

<Resource type="javax.sql.DataSource"
       name="jdbc/TEST"
       factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
       driverClassName="com.mysql.jdbc.Driver"
       url="jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull"
       username="test1"
       password="test1" 
       initialSize="10"
       maxActive="100"
       maxIdle="50"
       minIdle="10"/>

In Context.xml

<ResourceLink type="javax.sql.DataSource"
                name="jdbc/LocalTEST"
                global="jdbc/TEST"/>

And the bean configuration for the datasource is,

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:comp/env/jdbc/LocalTEST"/>
    </bean>

Two c3p0 pools from same database schema when using JPA/Hibernate

1 Answers1