How do I handle max connections with Hibernate?
Below is the Hibernate config 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 the 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 the project (code 2)
Query executeQuery = getSession().createQuery(hql);
UserProfileuser = (UserProfile) executeQuery.uniqueResult();
Above both codes I am using in a project. My question is which encodings should I follow? code 1 or code 2 to avoid max connections error.? I can connect up to 1000 database connections. But in some cases it is more than 1000. So I want to keep the database connection minimal. Please guide me.
source to share
Using 1000 database connections doesn't seem like a very good idea. Each additional connection to the database requires additional RAM and increases the likelihood of concurrency problems (deadlocks).
Since you are using C3P0 you should have a maximum connection size:
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
If you run out of connections, it may be because:
- you do not close Hibernate
Session
and the associated JDBC connection will not be released to the pool - your requests are taking too long to complete and therefore not freeing connections fast enough.
I recommend using a connection pool sizing utility like FlexyPool to better understand the usage patterns of a database connection.
Regarding two options:
-
The first example conflicts with Spring's automatic session management support. When you use Spring, you don't have to manage Hibernate sessions yourself. You must let the transaction manager call the appropriate Hibernate initialization callbacks at the new transaction boundary.
In your example, if the session throws an exception, the session will not be closed and the connection may be dropped. This is because you did not use a try / finally block to release the session.
-
The second example is better, but you need to wrap it in a service
@Transactional
.
source to share