Im running hibernate in a spring web-app and for some reason the function that I call keeps its connection open(sleeping) after I do anything possible to close it. But running it in Eclipse works. What's going on?

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="co.za.easypay.reportconfig.Email"
table="Email_tbl"
>
<id
name="Email_ID"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="native">
</generator>
</id>
<property
name="Email_Address"
type="java.lang.String"
update="true"
insert="true"
/>
<property
name="Name"
type="java.lang.String"
update="true"
insert="true"
/>
<set
name="reports"
table="Report_email__tbl"
lazy="false"
fetch="join"
cascade="all"
sort="unsorted"
>
<key
column="Email_ID"
>
</key>
<many-to-many
class="co.za.easypay.reportconfig.Report"
column="Report_ID"
/>
</set>
</class>
</hibernate-mapping>


public List getList(HttpServletRequest request, String hql){
String realpath = request.getSession().getServletContext().getRealPath("");
File fl = new File(realpath + "/WEB-INF/classes/hibernate.cfg.xml");
System.out.println(fl.getAbsolutePath());
// building a session factory with the given configuration
SessionFactory sf = new Configuration().configure(fl).buildSessionFactory();
// Open a session
Session sess = sf.openSession();
// Initialize a Transaction
Transaction tx = null;
List list = null;
try {
// Preparing the transaction
tx = sess.beginTransaction();
list = sess.createQuery(hql).list();
tx.commit();
sess.connection().close();
sess.clear();
sess.flush();
sess.close();

} catch (Exception e) {
// If exceptions occur and there is an unfinished transaction, rollback before exception is thrown
if (tx != null)
tx.rollback();

} finally {
// Always close the session

}
return list;
}


MySQL v4.1

If you are using Spring and coding a web app why worry about a connection sleeping after you have used it ?

Isn't the idea that:

You have a connection pool of connections that are started by the app server, and stay dormant until they are needed, so the connection pool will have many open connections, that can be used by any thread that wants them ?

Isn't opening and closing a connection an expensive thing to do, so by having a connection pool you re-use an exisitng connection, eliminating mulitple opens and closes ?

Have a read of this

http://www.javaworld.com/javaworld/jw-1 ... -pool.html

in a connection pool scenario, all closing a connection does is return it to the pool of (open) connections.

PS if you are using SPring why not make your DB query class extend HibernateDaoSupport then you can remove all your sessiona nd transaction code from your class and let spring handle it, like this

public class ApplicationDAOHibernate extends HibernateDaoSupport ......

and then this:

public List getApplications() {
return getHibernateTemplate().find("from Application");
}

somewhat easier :-)

I understand the concept of connection pooling but at this moment my pool is an infinity-pool. It just creates new connections until I get a "too many connections" error. I narrowed this problem down now but I dont like my solution. It is because I call that getList() function and then return the List to another function. If I just do the sess.open() and sess.close() in the same function that I use the List in, the connection behaves itself. I will look into your Idea but it is still a bit vague to me, could you please explain a bit more?

Ah, OK, so it looks like you are creating a new connection each time rather than re-using old ones or something.

For a starter are you using connection pools at all ? How is hibernate obtaining the JDBC connection ? Can you post a copy of either hibernate.cfg.xml or applicationContext.xml (the spring config file)

Here's a good article that expands on how to get Spring and Hibernate working together

http://www-128.ibm.com/developerworks/w ... a-spring2/

Your code does look a bit odd in that it seems to be loading hte hibernate.cfg.xml everytime you do a query. This should be a one off task for the duration of the application. Perhaps the fact that you are doing this is contributing to you using up your connections.

If you want to continue to use Hibernate without Spring (which is what your code seems to be doing), then I suggest you look into building your session factory once rather than every time as well (does your code run slowly by any chance ?). Here's some example code:

public class HibernateUtil {

static {
try {
new Configuration().configure().buildSessionFactory();
} catch (Throwable ex) {
ex.printStackTrace();
throw new ExceptionInInitializerError(ex);
}
}

/**
* Get session factory
* @param sessionFactoryJndiName factory name
* @return the Session Factory
* @throws NamingException cannot find a factory with the name
*/
public static SessionFactory getSessionFactory(String sessionFactoryJndiName)
throws NamingException {
SessionFactory sessionFactory = null;

Context ctx = new InitialContext();

sessionFactory = (SessionFactory) ctx.lookup(sessionFactoryJndiName);
return sessionFactory;

}

}