Is it possible to get the SQL alias of a join table for a Hibernate sqlRestriction?

Question

I have a Person class which has a String collection of aliases representing additional names that person may go by. For example, Clark Kent may have aliases "Superman" and "Man of Steel". Dwight Howard also has an alias of "Superman".

@Entity
class Person {

  @CollectionOfElements(fetch=FetchType.EAGER)
  Set<String> aliases = new TreeSet<String>();

Hibernate creates two tables in my database, Person and Person_aliases. Person_aliases is a join table with the columns Person_id and element. Let's say Person_aliases has the following data

--------------------------------
| Person_id     | element      |
--------------------------------
| Clark Kent    | Superman     |
| Clark Kent    | Man of Steel |
| Dwight Howard | Superman     |
| Bruce Wayne   | Batman       |
--------------------------------

I want to make a hibernate Criteria query for all persons who go by the alias of "Superman".

For reasons too long to list here, I'd really like to make this a Criteria query, not an HQL query (unless it's possible to add an HQL restriction on a Criteria object, in which case I'm all ears) or a raw SQL query. Since according to http://stackoverflow.com/questions/2321214/how-do-i-query-for-objects-with-a-value-in-a-string-collection-using-hibernate-cr it is impossible to refer to elements of value-type collections using the CriteriaAPI I thought I'd resort to adding an SqlRestriction on my criteria object.

Criteria crit = session.createCriteria(Person.class);
crit.add(Restrictions.sqlRestriction("XXXXX.element='superman'");

in the hopes that Hibernate will create an SQL statement like

    select *
from
    Person this_ 
left outer join
    Person_aliases aliases2_ 
        on this_.id=aliases2_.Person_id 
where
    XXXXX.element='superman' 

However, I need to fill in the XXXXX with the table alias for the Person_aliases table in the SQL query, which in this case would be 'aliases2_'. I noticed that if I needed the reference to the Person table alias I could use {alias}. But this won't work because Person is the primary table for this Criteria, not Person_aliases.

What do I fill in for the XXXXX? If there is no nice substition token like {alias} then is there a way I could get hibernate to tell me what that alias is going to be? I noticed a method called generateAlias() org.hibernate.util.StringHelper class. Would this help me predict what the alias would be?

I'd really, really like to avoid hard coding 'aliases2_'.

Thanks for your time!

Answer 1

It seems that the Criteria API doesn't allow to query collections of elements, see HHH-869 (which is still open). So either try the suggested workaround - I didn't - or switch to HQL. The following HQL query would work:

from Person p where :alias in elements(p.aliases)

Answer 2

May this link help you? It advices:

List persons = sess.createCriteria(Person.class)
       .createCriteria("company")
       .add(Restrictions.sqlRestriction("companyName || name like (?)",  "%Fritz%", Hibernate.STRING))
       .list(); 

Answer 3

See this Hibernate bugs and use attached files:

• http://opensource.atlassian.com/projects/hibernate/browse/HHH-2952
• http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381

Answer 4

public class Products { private Brands brand; ... } public class Brands { private long id; ... } ...

DetachedCriteria dc=DetachedCriteria.forClass(Products.class, "prod");

dc.add(Restrictions.ge("prod.brand.id", Long.parseLong("12345")));