Java – HQL unexpected AST node: {vector}

grailshibernatehqljava

I'm trying to write an HQL query to grab a list of users that belong to a particular organisation, or any franchisee from a list of franchisees, however hibernate isn't able to parse it. I can't figure out why. Here is the HQL:

from User u where 
(u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in :franchisees) 
and u.parentOrganisation.deleted = false 
and u.active = true

This is the error that hibernate spits out:

unexpected AST node: {vector} [from com.myapp.User u where (u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in :franchisees0_, :franchisees
1_, :franchisees2_) and u.parentOrganisation.deleted = false and u.active = true]. Stacktrace follows:
Message: unexpected AST node: {vector} [from com.myapp.User u where (u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in :franchisees0_, :fr
anchisees1_, :franchisees2_) and u.parentOrganisation.deleted = false and u.active = true]

If I take out the or u.parentOrganisation in :franchisees bit, so my query looks like this:

from User u where 
(u.parentOrganisation = :topLevelOrganisation) 
and u.parentOrganisation.deleted = false 
and u.active = true

Then it works fine. What's wrong with my syntax? Why is hibernate complaining about that extra clause?

Best Answer

Oh, it turns out that I needed to enclose :franchisees in parentheses:

from User u where 
(u.parentOrganisation = :topLevelOrganisation or u.parentOrganisation in (:franchisees)) 
and u.parentOrganisation.deleted = false 
and u.active = true

Related Solutions

Hibernate – jpql order by subquery produces unexpected AST node exception

JPQL doesn't support subqueries in order by. If I correctly understand your query, you can try something like this:

select p 
from Piece p left join p.user.receivedVotes rv
group by p
order by count(rv) desc, p.index

Java – Hibernate Pagination using HQL

Your query tells database to sort all records that satisfy the WHERE clause. It potentially may sort millions of records before returning you top 50.

EDIT 1/26: Now that the specific questions were clarified, I'll try to respond more specifically.

Every time you execute query like that, Hibernate goes to the database. Even more, it would flush all new/updated data in the session to disk. If this is your situation, this behavior might contribute to the slowness.
Using Hibernate Query API usually performs quite well in most situations and is compatible with a broad variety of the database platforms. If you are really concerned about squeezing last drop of performance out of your data access layer, you can write your own native SQL query to select top 50 results. But as soon as you do that, you'll almost certainly loose database independence. So, evaluate your costs vs. benefits.

Your query run times appear to be in the single milliseconds range. This is usually as good as it gets with the relational databases that store data on disk. So you might want to evaluate whether you indeed have a performance problem.

EDIT 1/27: OK. It looks like the problem in the overall design of the page. I had been using AJAX for last 7 years or so, so I don't usually have to wait for the filtering UI controls to redraw when going through pages of a table. I guess, switching application UI frameworks is not an option in your case. You have to figure out how to optimize loading of the data for the dropdowns and such. Does this data change frequently? Can you cache it somewhere in the application? If you have to load them every time, can you get away with just getting the display strings instead of entire objects?

Related Topic