I am using hibernate 3.2.6, tomcat 5.5.

I tried both dbcp and c3p0 approaches.

I have a test servlet to list a content from a table. First 5 tries are ok. However, after 5th try, dbcp gives me error like "pool exhausted". c3po just make the testing servlet hang up.

Using JNDI is required.

1). Here is tomcat config file to use c3p0 approach.

server.xml

<Resource name="jdbc/myDS" auth="Container"
factory="org.apache.naming.factory.BeanFactory"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
maxPoolSize="10" minPoolSize="2" acquireIncrement="2"
user="test" password="test"
driverClass="oracle.jdbc.driver.OracleDriver"
jdbcUrl="jdbc:oracle:thin:@localhost:1521:sid"/>

context.xml

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

2). Here is tomcat config file to use dbcp approach.

context.xml

<Resource name="jdbc/myDS" auth="Container" type="javax.sql.DataSource"
maxActive="5" maxIdle="1" maxWait="10000"
username="test" password="test"
driverClassName="oracle.jdbc.driver.OracleDriver"
removeAbandoned="true" removeAbandonedTimeout="300"
url="jdbc:oracle:thin:@localhost:1521:sid"/>

3). Here is my app config file.

web.xml

<resource-ref>
<description>Test DS</description>
<res-ref-name>jdbc/myDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

spring configuration file

<bean id="companyDao" class="com.test.server.dao.hibernate.CompanyDaoImpl">
<property name="sessionFactory">
<ref bean="sessionFactory0"/>
</property>
</bean>

<bean id="sessionFactory0" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource"><ref bean="myDS"/></property>
<property name="hibernateProperties"><ref bean="hibernateProperties0"/></property>
<property name="mappingResources">
<list>
<value>com/test/server/domain/Company.hbm.xml</value>
</list>
</property>
</bean>

<bean id="hibernateProperties0"
class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="properties">
<props>
<prop key="hibernate.cache.use_second_level_cache">false</prop>
<prop key="hibernate.cache.use_query_cache">false</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
<prop key="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</prop>
<prop key="hibernate.bytecode.use_reflection_optimizer">false</prop>
<prop key="hibernate.use_outer_join">true</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.hbm2ddl.auto">false</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
<prop key="hibernate.connection.datasource">java:comp/env/jdbc/myDS</prop>
</props>
</property>
</bean>

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

4). In my code, I have a servet to call this DAO.

public List<Company> getAllCompanys() {
return getSession().createQuery("FROM Company").list();
}


Any idea why? Thanks much.

In your DAO

use something like this:



Obviously thats soething from my code, but I think you can adjust it to meet your needs easily. Make sure your DAO extends HibernateDaoSupport.

This will make sure to return connections to the pool. Your pool count is maxed out, which indicates that you're not closing connections which returns them to the pool.

In your DAO

use something like this:



Obviously thats soething from my code, but I think you can adjust it to meet your needs easily. Make sure your DAO extends HibernateDaoSupport.

This will make sure to return connections to the pool. Your pool count is maxed out, which indicates that you're not closing connections which returns them to the pool.

The question you should ask yourself about your architecture: how and when your Hibernate sessions and transactions are opened and closed.

The symptoms you give let me think your application doesn't release the database connections... Which in turn also means your Hibernate sessions are probably not closed... not saying about your transactions (if any).

As far as I can tell you are using the Spring framework... have a look to their OpenSessionInView filter + their chapter on transaction handling in their documentation. You will find many valuable information that will surely help you to trace your problem.

PS: asking questions on the forum costs credits... So please, grant me some if you found my post helpful. Thanks.

thanks much, both dbcp and c3p0 work now. Now I need to re-read "hibernate in action"