19 Feb
2008
19 Feb
'08
11:33 a.m.
A fourth option (someway similar to the second one) is to use the Native
SQL Query feature of Hibernate. Instead of calling
session.createQuery(hql) you can call session.createSQLQuery(sql). It
works, using aliases. However, I tested it only on MySql and HSQLDB.
WDYT? Is this a convenient solution. I would have to switch many of the
hql queries from the Statistics Application to sql queries.
Marius
Right.. but the error persists with the correction
made. Not at the same
level though.
WARN util.JDBCExceptionReporter - SQL Error: 1054, SQLState: 42S22
ERROR util.JDBCExceptionReporter - Unknown column 'pageViewsCount' in
'order clause'
The problem is with Hibernate. There is a bug ("missing feature", for
them) in the translation from HQL to SQL. The aliases from the Select
clause are not resolved correctly in other clauses, including Order By. In
this case they "recommend" us to duplicate the expressions from the Select
clause, hoping that the RDBMS will take care of the optimization.
See for yourself:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-892
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1902
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2390
http://forum.hibernate.org/viewtopic.php?t=966075&postdays=0&postor…
So, using aliases breaks Hibernate and using expressions in the Order By
clause is not supported by MySql 4.1.20. I don't see any clean solution
for this. Some options are:
* drop the Order By clause and sort on the Java side - No way, I don't
think this is feasible.
* skip Hibernate layer and use JDBC directly - Doable, the SQL query being
not too complicated, but with cross DB issues.
* tweak the HQL query - How?
Any other options Sergiu?
Marius
Marius Dumitru Florea wrote:
_______________________________________________
users mailing list
users(a)xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Hi Jeremie,
The sql query you suggested is cleaner and at least as efficient as the
current one. It was my first option but unfortunately it's not
supported
by the newer versions of mysql. I've no idea why..
mysql> select xws_name, sum(xws_page_views) as pageviewcount from
xwikistatsdoc where xws_action='view' and xws_name like '%.%' group by
xws_name order by pageviewscount desc;
ERROR 1054 (42S22): Unknown column 'pageviewscount' in 'order clause'
Note that there's a typo: pageviewcount <> pageview-S-count
Anyway, hibernate should be responsible for the
correct translation
from
hql to a specific sql dialect. A solution would be to use the (idea
from
the) above sql but configure the dialect to MySQL5Dialect (in my case).
I
have to test this. I'll give you feedback soon.
Best Regards,
Marius.
--
Sergiu Dumitriu
http://purl.org/net/sergiu/
_______________________________________________
users mailing list
users(a)xwiki.org
http://lists.xwiki.org/mailman/listinfo/users