HQL for querying a CollectionOfElements

olel · **Joined:** Fri Nov 14, 2008 7:11 am **Posts:** 31

Hi,

I have some problems in finding the correct HQL statement for an actual easy query. I have an object "User" with some attributes like username, password and with a Set of aliases that are mapped as as CollectionOfElements. The mapping looks like

Code:

public class User {
   private String username;
   private String password;

   private Set<String> aliases = new HashSet<String>();

   @CollectionOfElements(fetch = FetchType.EAGER)
   @JoinTable(name = "USER_ALIAS", joinColumns = @JoinColumn(name = "user_id"))
   public Set<String> getAliases() {
      return aliases;
   }

Now I want to write a HQL query that returns the user with a given alias name (all aliases are unique). I tried this one

Code:

final String queryString = "from User u where :alias in elements(u.aliases)";
final List<User> result = getHibernateTemplate().findByNamedParam(queryString,"aliases", "myAliasesToLookFor");

but the result list is empty. If I let Hibernate generate the sql statement it is

Code:

select
        user.id,
        user.password,
        user.username 
    from
        USERTABLE user 
    where
        ? in (
            select
                aliases.alias 
            from
                USER_ALIAS aliases 
            where
                user.id=aliases.user_id
        )

and if I run that against my database directly I get the correct user.

Can someone explain to me what is wrong with my HQL?

Thanks in advance,
Ole

mmerder · **Posted:** Wed Nov 11, 2009 5:38 am

Well, if the query works in your editor it can only depend on the parameter you pass. I have no experience with hibernate template, but do you pass the parameters correctly? Shouldn't it be:

Code:

final List<User> result = getHibernateTemplate().findByNamedParam(queryString, "alias", "yourSingleAliasToLookFor");

olel · **Joined:** Fri Nov 14, 2008 7:11 am **Posts:** 31

mmerder wrote:

Well, if the query works in your editor it can only depend on the parameter you pass. I have no experience with hibernate template, but do you pass the parameters correctly? Shouldn't it be:

Code:

final List<User> result = getHibernateTemplate().findByNamedParam(queryString, "alias", "yourSingleAliasToLookFor");

Sorry, that was my fault. Indeed my HQL query looks exactly as yours. It have been just typos.
The fact that the SQL query is working as expected is what is irritating me the most. Moreover the HQL query

Code:

select u, elements(u.aliases) from User u where :alias in elements(u.aliases)

returns a list of object arrays with the correct combination of users and aliases.

I don't see what might be wrong with the other HQL statement. Is it possible to express the same with another HQL statement without using "in elements"?

Ole

mmerder · **Posted:** Wed Nov 11, 2009 8:54 am

olel wrote:

Moreover the HQL query

Code:

select u, elements(u.aliases) from User u where :alias in elements(u.aliases)

returns a list of object arrays with the correct combination of users and aliases.

Try to change it to

Code:

select u from User u join fetch u.aliases where :alias in elements(u.aliases)

olel · **Joined:** Fri Nov 14, 2008 7:11 am **Posts:** 31

mmerder wrote:

Try to change it to

Code:

select u from User u join fetch u.aliases where :alias in elements(u.aliases)

Unfortunately that doesn't work either. The list is also empty.

mmerder · **Posted:** Wed Nov 11, 2009 10:58 am

Weird...

Well, as the generated query works, the only possibilities are that you still pass the wrong parameter or something in the HibernateTemplate goes wrong.
Try to log also the values, hibernate puts into the prepared statement (set loglevel of org.hibernate.type to debug or trace) so that you can check whether the parameter you pass is the same as the one you use in your pure sql.

olel · **Joined:** Fri Nov 14, 2008 7:11 am **Posts:** 31

At last I changed the query to

Code:

select u from User u join u.aliases alias where alias = :alias

as it is told to be the secound valid query syntax at the Hibernate FAQ (https://www.hibernate.org/116.html#A31).

Thanks for your help!