Fórumok
Please Help! Error with CustomPersistenceImpl and Hibernate queries
Christoph H., módosítva 16 év-val korábban
Please Help! Error with CustomPersistenceImpl and Hibernate queries
Regular Member Bejegyzések: 147 Csatlakozás dátuma: 2007.07.31. Legújabb bejegyzések
Hi,
i have a big problem with some methods i wrote accessing the Database.
The interesting point is: I can run my code once - it works fine. If i run it more often, at some point Liferay will just hang.
Heres one of the methods causing the Problem:
I have a Service Nestedset and this queries the DB for the tree.
my Database looks like this for example:
After calling
The place where it stops is very random, but always when executing a query.
I would be very happy if someone could help me with this.
i have a big problem with some methods i wrote accessing the Database.
The interesting point is: I can run my code once - it works fine. If i run it more often, at some point Liferay will just hang.
Heres one of the methods causing the Problem:
public List findByRootIdWithLevel(long rootId) throws SystemException {
_log.debug("FindByRootIdWithLevel start...");
// Hibernate Session and Transaction, sql-string and query object
Session session = null;
Transaction trans = null;
String sql = null;
SQLQuery q = null;
List<nestedset> res = new ArrayList<nestedset>();
try {
// open a new session
SessionFactoryImplementor lSF = HibernateUtil.getSessionFactory();
session = lSF.openSession();
_log.debug("Session opened");
// Begin Transaction
trans = session.beginTransaction();
_log.debug("Transaction started");
// pull out our query
sql = CustomSQLUtil.get(FIND_BY_ROOTID_WITH_LEVEL);
_log.debug("SQL Query (RAW) is: " + sql);
_log.debug("Corresponding Values are: " + rootId + " twice");
// create a SQLQuery object
q = session.createSQLQuery(sql);
q.setLong(0, rootId);
q.setLong(1, rootId);
// q.addEntity("ns", NestedsetImpl.class);
_log.debug("Set all Parameters, preparing for list()");
// execute the query and return a list from the db
List rows = q.list();
_log.debug("Got list, should be fine now");
Object[] row = null;
Nestedset node = null;
for (int i = 0; i < rows.size(); i++) {
row = (Object[]) rows.get(i);
// _log.debug("Row " + i);
// for (int j = 0; j < row.length; j++) {
// _log.debug("Element j: " + row[j]);
// }
node = new NestedsetImpl(Long.parseLong("" + row[0]), Long
.parseLong("" + row[1]), Long.parseLong("" + row[3]),
Long.parseLong("" + row[2]), new String("" + row[4]),
Long.parseLong("" + row[5]));
res.add(node);
// TODO: Eventually remove this, bc the cache could be good
if (session.contains(node)) {
session.evict(node);
}
// _log.debug("Got Node: " + node.getNestedsetId() + " " +
// node.getTitle() + " " + node.getLevel());
}
// flush session
session.flush();
// commit
trans.commit();
} catch (Exception e) {
if (trans != null) {
try {
// rollback the changes
trans.rollback();
} catch (HibernateException exRollback) {
throw new SystemException(
"Hibernate Exception when rolling back", exRollback);
}
}
throw new SystemException(e);
} finally {
// TODO: Eventually remove this, bc the cache could be good
// clear session (cache)
session.clear();
// close the hibernate session (we don't want many open sessions!)
HibernateUtil.closeSession(session);
}
// return result
return res;
}</nestedset></nestedset>I have a Service Nestedset and this queries the DB for the tree.
my Database looks like this for example:
+-------------+---------------+--------+--------+---------+
| nestedsetId | title | rootId | leftId | rightId |
+-------------+---------------+--------+--------+---------+
| 1 | Root: Foo Bar | 1 | 1 | 12 |
| 2 | SubNode 1 | 1 | 2 | 7 |
| 3 | SubNode 2 | 1 | 8 | 9 |
| 4 | SubNode 3 | 1 | 10 | 11 |
| 5 | SubNode 1.2 | 1 | 3 | 4 |
| 6 | SubNode 1.1 | 1 | 5 | 6 |
+-------------+---------------+--------+--------+---------+After calling
List tree = CustomNestedsetUtil.findByRootIdWithLevel(1);14:40:37,944 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:36] ProcessAction
14:40:37,945 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1853] FindByRootIdWithLevel start...
14:40:37,945 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1866] Session opened
14:40:37,946 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1871] Transaction started
14:40:37,946 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1876] SQL Query (RAW) is: SELECT ns.nestedsetId, ns.rootId, ns.rightId, ns.leftId, ns.title, COUNT(*)-1 AS level FROM Nestedset ns, Nestedset ns2 WHERE ns.rootId = ? AND ns2.rootId = ? AND ns.leftId >= ns2.leftId AND ns.leftId <= ns2.rightId GROUP BY ns.leftId ORDER BY ns.leftId
14:40:37,947 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1877] Corresponding Values are: 1 twice
14:40:37,947 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1886] Set all Parameters, preparing for list()
14:40:37,963 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1891] Got list, should be fine now
14:40:37,965 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:75] Got Nestedset List WithLevel
14:40:37,966 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 1: 0: Root: Foo Bar
14:40:37,966 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 2: 1: SubNode 1
14:40:37,967 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 5: 2: SubNode 1.2
14:40:37,967 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 6: 2: SubNode 1.1
14:40:37,968 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 3: 1: SubNode 2
14:40:37,968 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 4: 1: SubNode 3
14:40:37,969 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:87] Done
14:40:37,969 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:88] Last Line of ProcessAction
14:40:38,091 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:94] Rendering, set title
14:40:38,095 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:96] Forwarding...
14:40:41,566 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:46] Client InetAdress: 127.0.0.1
14:40:41,567 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065301504 endIP: -2065235969
14:40:41,567 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:41,568 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 175814144 endIP: 175814399
14:40:41,568 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:41,568 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 181207040 endIP: 181272575
14:40:41,568 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:41,569 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065245696 endIP: -2065245441
14:40:41,569 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:41,639 DEBUG [de.freiburg.iif.electures.portal.util.struts.StrutsActionURL:40] Creating StrutsActionURL
14:40:45,511 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:46] Client InetAdress: 127.0.0.1
14:40:45,512 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065301504 endIP: -2065235969
14:40:45,512 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:45,512 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 175814144 endIP: 175814399
14:40:45,513 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:45,513 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 181207040 endIP: 181272575
14:40:45,513 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:45,513 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065245696 endIP: -2065245441
14:40:45,514 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
14:40:45,522 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:36] ProcessAction
14:40:45,522 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1853] FindByRootIdWithLevel start...
14:40:45,523 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1866] Session opened
14:40:45,524 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1871] Transaction started
14:40:45,524 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1876] SQL Query (RAW) is: SELECT ns.nestedsetId, ns.rootId, ns.rightId, ns.leftId, ns.title, COUNT(*)-1 AS level FROM Nestedset ns, Nestedset ns2 WHERE ns.rootId = ? AND ns2.rootId = ? AND ns.leftId >= ns2.leftId AND ns.leftId <= ns2.rightId GROUP BY ns.leftId ORDER BY ns.leftId
14:40:45,525 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1877] Corresponding Values are: 1 twice
14:40:45,525 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1886] Set all Parameters, preparing for list()The place where it stops is very random, but always when executing a query.
I would be very happy if someone could help me with this.
Christoph H., módosítva 16 év-val korábban
[solved] Please Help! Error with CustomPersistenceImpl and Hibernate query
Regular Member Bejegyzések: 147 Csatlakozás dátuma: 2007.07.31. Legújabb bejegyzések
Ok, here comes the Solution - another annoying Liferay show stopper:
In HibernateUtil the Method closeSession simply does nothing.
The solution is of course to call session.close() instead of HibernateUtil.close(session);
If the Connection is not closed, the DB Connection si not returned to the pool and very fast Liferay locks up. What I'm really wondering is that there is *no exception* in the logs, that the connection pool is empty.
Hope this helps someone else.
In HibernateUtil the Method closeSession simply does nothing.
public static void closeSession(Session session) {
try {
if (session != null) {
// Let Spring manage sessions
//session.close();
}
}
catch (HibernateException he) {
_log.error(he.getMessage());
}
}The solution is of course to call session.close() instead of HibernateUtil.close(session);
If the Connection is not closed, the DB Connection si not returned to the pool and very fast Liferay locks up. What I'm really wondering is that there is *no exception* in the logs, that the connection pool is empty.
Hope this helps someone else.