CODEWITHSUNDEEP
hibernatehql
Ananth
Ananth

Reputation: 11

HQl equivalent of sql query

String SQL_QUERY = "SELECT count(*) FROM (SELECT * FROM Url as U where U.pageType=" + 1 + " group  by U.pageId having count(U.pageId) = 1)";
query = session.createQuery(SQL_QUERY);

I am getting an error

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 23 [ SELECT count() FROM (SELECT * FROM Url as U where U.pageType = 2 group by U.pageId having count(U.pageId) = 1)]

Upvotes: 0

Views: 2224

Answers (3)

axtavt
axtavt

Reputation: 242776

In HQL you cannot use subqueries in FROM clause.

In this particular case (with having count(U.pageId) = 1) the same result should be produced by the following query (though I'm not sure about performance):

SELECT count(u) FROM Url u WHERE u.pageType = 2 AND
    1 = (SELECT count(uu) FROM Url uu WHERE u.pageId = uu.pageId)

If it's not suitable, you can use session.createSQLQuery().

Upvotes: 0

bluish
bluish

Reputation: 27390

Your query seems to be ok as HQL code. Pay attention to the Capital letters for the entities: for example url in your bean class could be Url.

Anyway there's the method createSQLQuery(queryString) to run a query in SQL language.

Upvotes: 0

HamoriZ
HamoriZ

Reputation: 2438

You can use group by and having and subqueries in hql as well.

Some examples:

link text

Upvotes: 0

Related Questions