CODEWITHSUNDEEP
javamysqlspringhibernatec3p0
RBP
RBP

Reputation: 485

How to handle max database connections with Hibernate?

Below is Hibernate configuration from Hibernate.xml

    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource">
        <ref bean="dataSource"/>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.c3p0.timeout">300</prop>
        </props>
    </property>
    <property name="annotatedClasses">
        <list>
         .....
        </list>
    </property>
  </bean>

  <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
   </bean>

Below is code from GenricDaoImpl class (code 1)

@Override
public T save(T t) {
    Session session = getSessionFactory().openSession();
    Transaction tx=session.beginTransaction();
    session.saveOrUpdate(t);
    tx.commit();
    session.close();
    return t;
}

and other code from project (code 2)

   Query executeQuery = getSession().createQuery(hql);
   UserProfileuser  =  (UserProfile) executeQuery.uniqueResult();

Above both codes I am using in project. My question is which coding need to follow ? code 1 or code 2 to avoid max connections error.? I can connect max 1000 connections with database. But in some cases it is going more than 1000 . So i want to maintain database connection minimum. Please guide me.

Upvotes: 3

Views: 15991

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154090

Using 1000 database connections doesn't sound like a very good idea. Each extra database connection requires extra RAM and increases the likelihood of concurrency issues (deadlocks).

Since you use C3P0 you should have a max connection size:

<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>

If you run out of connections then it may be because:

  • you don't close the Hibernate Session, and the associated JDBC connection doesn't get released to the pool
  • your queries take too long to execute and so they don't release connections fast enough

I recommend using a connection pool size utility, such as FlexyPool to understand better the database connection usage patterns.

Regarding the two choices:

  • The 1st example contradicts the automatic session management support offered by Spring. When you use Spring you should not manage Hibernate Sessions yourself. You should let the transaction manager call the appropriate Hibernate initializing callbacks on a new transaction boundary.

    In your example, if the session throws an exception, the session will not be closed and the connection might be dangling around. That's because you haven't used a try/finally block for releasing the Session.

  • The 2nd example is better, but you need to wrap it into a @Transactional service.

Upvotes: 3

Related Questions