HQL with a collection in the WHERE clause

Question

Hello good people!

I've been trying for the this whole a query who is officially giving me nightmares. The system is a user and contact management. So I have UserAccount, Contact and Phone.

UserAccount has a bidirectional one-to-many relationship with Contact and an unidirectional one on phone all mapped by a Set:

//UserAccount mapping 
@OneToMany(targetEntity=PhoneImpl.class, cascade= {CascadeType.ALL})
@org.hibernate.annotations.Cascade(value=org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
private Set<Phone> phones = new HashSet<Phone>();

@OneToMany(targetEntity=ContactImpl.class, cascade={CascadeType.ALL}, mappedBy="userAccount")
@org.hibernate.annotations.Cascade(value=org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
private Set<Contact> contacts = new HashSet<Contact>();

Contact now has a one-to-many unidirectional with phones

@OneToMany(targetEntity=PhoneImpl.class, cascade={CascadeType.ALL})
private Set<Phone> phones = new HashSet<Phone>();

I'm writing a method to check the existence of the same number for the same contact of a particular user by the email unique field.

I know I could have overridden the equals and hashcode for that but since phone in a entity mapped by set I don't know at this moment how to do that. So I wanted to provide a method to rather check for that uniqueness for me before each entry on the contact page

public boolean checkForExistingPhone(String userEmail, String formatedNumber) {
    List<Contact> result = null;
    Session sess = getDBSession().getSession();

    String query = "select Contact ,cphones.formatedNumber from Contact c inner join    Contact.phones cphones where c.UserAccount.email = :email and cphones.formatedNumber= :number";
//        try {
        result = (List<Contact>) sess.createQuery(query)
                .setParameter("email", userEmail)
                .setParameter("number", formatedNumber).list();
//        } catch (HibernateException hibernateException) {
//            logger.error("Error while fetching contacts of email " + userEmail + " Details:"     + hibernateException.getMessage());
//        }
        if(result == null)
            return false;
        else
            return true;
}

I keep on having this error:

org.hibernate.hql.ast.QuerySyntaxException: Contact is not mapped [select
cphones.formatedNumber from Contact c inner join Contact.phones cphones where
c.UserAccount.email = :email and cphones.formatedNumber= :number].

I can't really figure out what happens and first i don't know how to treat collections in HSQ.thanks for reading

Answer 1

HQL query would be probably something along these lines:

select c
from Contact c
join c.phones cphones
where c.userAccount.email = :email
  and cphones.formatedNumber = :number

Also you may want to handle results of query like this. The list() method returns always a list, never a null.

 return !result.isEmpty();