Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Christoph H.
Please Help! Error with CustomPersistenceImpl and Hibernate queries
February 15, 2008 12:51 AM
Answer

Christoph H.

Rank: Regular Member

Posts: 147

Join Date: July 31, 2007

Recent Posts

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:
 1    public List findByRootIdWithLevel(long rootId) throws SystemException {
 2
 3        _log.debug("FindByRootIdWithLevel start...");
 4
 5        // Hibernate Session and Transaction, sql-string and query object
 6        Session session = null;
 7        Transaction trans = null;
 8        String sql = null;
 9        SQLQuery q = null;
10        List<Nestedset> res = new ArrayList<Nestedset>();
11
12        try {
13            // open a new session
14            SessionFactoryImplementor lSF = HibernateUtil.getSessionFactory();
15            session = lSF.openSession();
16
17            _log.debug("Session opened");
18            
19            // Begin Transaction
20            trans = session.beginTransaction();
21
22            _log.debug("Transaction started");
23            
24            // pull out our query
25            sql = CustomSQLUtil.get(FIND_BY_ROOTID_WITH_LEVEL);
26
27            _log.debug("SQL Query (RAW) is: " + sql);
28            _log.debug("Corresponding Values are: " + rootId + " twice");
29
30            // create a SQLQuery object
31            q = session.createSQLQuery(sql);
32            q.setLong(0, rootId);
33            q.setLong(1, rootId);
34
35            // q.addEntity("ns", NestedsetImpl.class);
36
37            _log.debug("Set all Parameters, preparing for list()");
38            
39            // execute the query and return a list from the db
40            List rows = q.list();
41
42            _log.debug("Got list, should be fine now");
43                   
44            Object[] row = null;
45            Nestedset node = null;
46            for (int i = 0; i < rows.size(); i++) {
47                row = (Object[]) rows.get(i);
48//                _log.debug("Row " + i);
49//                for (int j = 0; j < row.length; j++) {
50//                    _log.debug("Element j: " + row[j]);
51//                }
52                node = new NestedsetImpl(Long.parseLong("" + row[0]), Long
53                        .parseLong("" + row[1]), Long.parseLong("" + row[3]),
54                        Long.parseLong("" + row[2]), new String("" + row[4]),
55                        Long.parseLong("" + row[5]));
56                res.add(node);
57                // TODO: Eventually remove this, bc the cache could be good
58                if (session.contains(node)) {
59                    session.evict(node);
60                }
61                // _log.debug("Got Node: " + node.getNestedsetId() + " " +
62                // node.getTitle() + " " + node.getLevel());
63            }
64
65            // flush session
66            session.flush();
67            
68            // commit
69            trans.commit();
70
71        } catch (Exception e) {
72            if (trans != null) {
73                try {
74                    // rollback the changes
75                    trans.rollback();
76                } catch (HibernateException exRollback) {
77                    throw new SystemException(
78                            "Hibernate Exception when rolling back", exRollback);
79                }
80            }
81            throw new SystemException(e);
82        } finally {
83            // TODO: Eventually remove this, bc the cache could be good
84            // clear session (cache)
85            session.clear();
86            
87            // close the hibernate session (we don't want many open sessions!)
88            HibernateUtil.closeSession(session);
89        }
90        // return result
91        return res;
92    }


I have a Service Nestedset and this queries the DB for the tree.
my Database looks like this for example:
 1+-------------+---------------+--------+--------+---------+
 2| nestedsetId | title         | rootId | leftId | rightId |
 3+-------------+---------------+--------+--------+---------+
 4|           1 | Root: Foo Bar |      1 |      1 |      12 |
 5|           2 | SubNode 1     |      1 |      2 |       7 |
 6|           3 | SubNode 2     |      1 |      8 |       9 |
 7|           4 | SubNode 3     |      1 |     10 |      11 |
 8|           5 | SubNode 1.2   |      1 |      3 |       4 |
 9|           6 | SubNode 1.1   |      1 |      5 |       6 |
10+-------------+---------------+--------+--------+---------+


After calling
1List tree = CustomNestedsetUtil.findByRootIdWithLevel(1);
like 18 times, Liferay just hangs:

 114:40:37,944 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:36] ProcessAction
 214:40:37,945 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1853] FindByRootIdWithLevel start...
 314:40:37,945 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1866] Session opened
 414:40:37,946 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1871] Transaction started
 514: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
 614:40:37,947 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1877] Corresponding Values are: 1 twice
 714:40:37,947 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1886] Set all Parameters, preparing for list()
 814:40:37,963 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1891] Got list, should be fine now
 914:40:37,965 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:75] Got Nestedset List WithLevel
1014:40:37,966 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 1: 0: Root: Foo Bar
1114:40:37,966 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 2: 1: SubNode 1
1214:40:37,967 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 5: 2: SubNode 1.2
1314:40:37,967 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 6: 2: SubNode 1.1
1414:40:37,968 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 3: 1: SubNode 2
1514:40:37,968 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:81] 4: 1: SubNode 3
1614:40:37,969 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:87] Done
1714:40:37,969 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:88] Last Line of ProcessAction
1814:40:38,091 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:94] Rendering, set title
1914:40:38,095 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:96] Forwarding...
2014:40:41,566 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:46] Client InetAdress: 127.0.0.1
2114:40:41,567 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065301504 endIP: -2065235969
2214:40:41,567 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
2314:40:41,568 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 175814144 endIP: 175814399
2414:40:41,568 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
2514:40:41,568 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 181207040 endIP: 181272575
2614:40:41,568 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
2714:40:41,569 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065245696 endIP: -2065245441
2814:40:41,569 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
2914:40:41,639 DEBUG [de.freiburg.iif.electures.portal.util.struts.StrutsActionURL:40] Creating StrutsActionURL
3014:40:45,511 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:46] Client InetAdress: 127.0.0.1
3114:40:45,512 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065301504 endIP: -2065235969
3214:40:45,512 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
3314:40:45,512 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 175814144 endIP: 175814399
3414:40:45,513 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
3514:40:45,513 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: 181207040 endIP: 181272575
3614:40:45,513 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
3714:40:45,513 DEBUG [de.freiburg.iif.electures.portal.util.InetRange:42] IP after Conversion is: 2130706433 StartIP: -2065245696 endIP: -2065245441
3814:40:45,514 DEBUG [de.freiburg.iif.electures.portal.security.auth.GuestAutoLogin:56] Range null or IP not contained: /127.0.0.1
3914:40:45,522 DEBUG [com.ext.portlet.helloworldportlet.action.TestNestedsetAction:36] ProcessAction
4014:40:45,522 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1853] FindByRootIdWithLevel start...
4114:40:45,523 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1866] Session opened
4214:40:45,524 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1871] Transaction started
4314: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
4414:40:45,525 DEBUG [de.freiburg.iif.electures.portal.library.service.persistence.CustomNestedsetPersistenceImpl:1877] Corresponding Values are: 1 twice
4514: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.
[solved] Please Help! Error with CustomPersistenceImpl and Hibernate query
February 15, 2008 12:58 AM
Answer

Christoph H.

Rank: Regular Member

Posts: 147

Join Date: July 31, 2007

Recent Posts

Ok, here comes the Solution - another annoying Liferay show stopper:
In HibernateUtil the Method closeSession simply does nothing.
 1public static void closeSession(Session session) {
 2        try {
 3            if (session != null) {
 4
 5                // Let Spring manage sessions
 6
 7                //session.close();
 8            }
 9        }
10        catch (HibernateException he) {
11            _log.error(he.getMessage());
12        }
13    }


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.