I am using Hibernate in my web application which is developed with pure Servlet and JSP. I am facing to a big trouble "sometimes" when I execute the code. What happens is I am getting Too many Connections error from Hibernate.
I went through lot of Stackoverflow questions seeking for an answer and I found different solutions. Some suggested to use a third party pooling system, some suggested to be thread safe, some suggested to be using one SessionFactory etc, therefor I am not sure which one is applicable to mine.
Below is a portion of my database layer.
package dao;
import java.util.List;
import model.main.Familyvisa;
import model.main.Familyvisa;
import model.main.Familyvisa;
import model.main.Pensionhistory;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
/**
*
* @author user
*/
public class FamilyVisaImpl implements FamilyVisaInterface
{
private Session currentSession;
private Transaction currentTransaction;
public Session openCurrentSession() {
currentSession = getSessionFactory().openSession();
return currentSession;
}
public Session openCurrentSessionwithTransaction() {
currentSession = getSessionFactory().openSession();
currentTransaction = currentSession.beginTransaction();
return currentSession;
}
public void closeCurrentSession() {
currentSession.close();
}
public void closeCurrentSessionwithTransaction() {
currentTransaction.commit();
currentSession.close();
}
private static SessionFactory getSessionFactory() {
Configuration configuration = new Configuration().configure();
StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
.applySettings(configuration.getProperties());
SessionFactory sessionFactory = configuration.buildSessionFactory(builder.build());
return sessionFactory;
}
public Session getCurrentSession() {
return currentSession;
}
public void setCurrentSession(Session currentSession) {
this.currentSession = currentSession;
}
public Transaction getCurrentTransaction() {
return currentTransaction;
}
public void setCurrentTransaction(Transaction currentTransaction) {
this.currentTransaction = currentTransaction;
}
@Override
public void save(Familyvisa entity) {
getCurrentSession().save(entity);
}
@Override
public void update(Familyvisa entity) {
getCurrentSession().update(entity);
}
@Override
public Familyvisa findById(int id) {
Familyvisa book = (Familyvisa) getCurrentSession().get(Familyvisa.class, id);
return book;
}
@Override
public void delete(Familyvisa entity) {
getCurrentSession().delete(entity);
}
@Override
public List<Familyvisa> findAll() {
List<Familyvisa> remDur = (List<Familyvisa>) getCurrentSession().createQuery("from Familyvisa").list();
return remDur;
}
public Familyvisa findByForiegnKey_Family(int idFamily)
{
String hql = "FROM Familyvisa WHERE idFamily = :famId";
//String hql = "FROM Visa WHERE idFamily = :famId";
Query q = getCurrentSession().createQuery(hql);
q.setParameter("famId", idFamily);
Familyvisa v = new Familyvisa();
if(!q.list().isEmpty())
{
v = (Familyvisa)q.list().get(0);
}
return v;
}
@Override
public void saveOrUpdate(Familyvisa p)
{
getCurrentSession().saveOrUpdate(p);
}
@Override
public List<Object[]> findReminderActiveVisaWithFamilyAndEmployee()
{
String sql = "";
SQLQuery createSQLQuery = getCurrentSession().createSQLQuery(sql);
return createSQLQuery.list();
}
@Override
public void batchUpdate(List<Familyvisa> list)
{
for(int i=0;i<list.size();i++)
{
getCurrentSession().update(list.get(i));
}
}
}
Below is my service layer, related to the above code.
package service;
import dao.FamilyVisaImpl;
import java.util.List;
import model.main.Familyvisa;
/**
*
* @author user
*/
public class FamilyVisaService
{
private FamilyVisaImpl familyVisaImpl;
public FamilyVisaService()
{
familyVisaImpl = new FamilyVisaImpl();
}
public Familyvisa findByForiegnKey_Family(int idFamily)
{
familyVisaImpl.openCurrentSession();
Familyvisa findByForiegnKey_Family = familyVisaImpl.findByForiegnKey_Family(idFamily);
familyVisaImpl.closeCurrentSession();
return findByForiegnKey_Family;
}
public List<Object[]> findReminderActiveVisaWithFamilyAndEmployee()
{
familyVisaImpl.openCurrentSession();
List<Object[]> visa = familyVisaImpl.findReminderActiveVisaWithFamilyAndEmployee();
familyVisaImpl.closeCurrentSession();
return visa;
}
public void batchUpdate(List<Familyvisa> list)
{
familyVisaImpl.openCurrentSessionwithTransaction();
familyVisaImpl.batchUpdate(list);
familyVisaImpl.closeCurrentSessionwithTransaction();
}
}
Below is a code from Servlet, which explains how I execute the code.
private void updateDatabase(List<VisaWithFamilyAndEmployeeBean> reminderSentList)
{
FamilyVisaService service = new FamilyVisaService();
List<Familyvisa> visa = new ArrayList<Familyvisa>();
for(int i=0;i<reminderSentList.size();i++)
{
Familyvisa familyVisa = service.findByForiegnKey_Family(reminderSentList.get(i).getIdFamily());
familyVisa.setNumberOfReminders(familyVisa.getNumberOfReminders()+1);
familyVisa.setLastReminderSent(Common.getCurrentDateSQL());
visa.add(familyVisa);
}
service.batchUpdate(visa);
}
I have lot of classes in three layers (servlet, DAO, Service) and all follow the exact same structure, serving different purposes, but methods looks almost same (like update, insert etc).
Please pay some decent attention to the code, to the key words, usage of access specifiers etc. In some other classes, in service layer, I define it's IMPL as static as well eg: private static EmployeeImpl employeeimpl;
Can you find what is happening wrong here? Since it occures only "sometimes" and in any of the code (not only in here, but the other classes also same, only difference is they call to different tables) so I can figure it out.
UPDATE
Considering the comments and answers, I changed the code to below. Please let me know whether it is in quality level.
FamilyVisaService service = new FamilyVisaService();
Session session = service.openCurrentSession(); //This method will call openCurrentSession() in Impl class
try {
for(int i=0;i<reminderSentList.size();i++)
{
/* findByForiegnKey_Family() has Session argument now! */
Familyvisa familyVisa = service.findByForiegnKey_Family(session, reminderSentList.get(i).getIdFamily());
familyVisa.setNumberOfReminders(familyVisa.getNumberOfReminders()+1);
familyVisa.setLastReminderSent(Common.getCurrentDateSQL());
visa.add(familyVisa);
}
} catch (Exception ex) {
System.out.println("ERROR:"+ex);
} finally {
session.close();
}
Your snippet of code:
for(int i=0;i<reminderSentList.size();i++)
{
Familyvisa familyVisa = service.findByForiegnKey_Family(reminderSentList.get(i).getIdFamily());
familyVisa.setNumberOfReminders(familyVisa.getNumberOfReminders()+1);
familyVisa.setLastReminderSent(Common.getCurrentDateSQL());
visa.add(familyVisa);
}
opens and closes the session inside the loop multiple times during it's execution using service.findByForeignKey_Family() function.
Session openning and closing could take some time but the loop is fast enough. That's why could multiple sessions to be open: it's just need the time to be closed. And in your code it's actual. That's why "Too Many Connections" error occurs.
In other words, pass the session to the service.findByForiegnKey_Family() as parameter instead of openning and closing this inside function.
Like this:
Session session = ...
try {
for(int i=0;i<reminderSentList.size();i++)
{
/* findByForiegnKey_Family() has Session argument now! */
Familyvisa familyVisa = service.findByForiegnKey_Family(session, reminderSentList.get(i).getIdFamily());
familyVisa.setNumberOfReminders(familyVisa.getNumberOfReminders()+1);
familyVisa.setLastReminderSent(Common.getCurrentDateSQL());
visa.add(familyVisa);
}
} catch (Exception ex) {
System.out.println("ERROR:"+ex);
} finally {
session.close();
}
The above example is Thread-safe. Because you open, operate and close the session inside single function.
Hibernate requires transaction block even for read operations. So you have to fix your code like this:
Session session = ...
try {
session.beginTransaction();
...
Your Loop
...
session.getTransaction.commit();
...