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

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 How do I query for objects with a value in a String collection using Hibernate Criteria? 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_开发者_Go百科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!

as xmedeko alludes to, when you want to do:

crit.add(Restrictions.sqlRestriction(
    "{alias}.joinedEntity.property='something'"));

you need to instead do:

crit.createCriteria("joinedEntity").add(Restrictions.sqlRestriction(
    "{alias}.property='something'"));

This has solved similar problems for me without going to HQL

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)

try to create another Criteria like

Criteria crit = session.createCriteria(Person.class, "person");
Criteria subC = crit.createCriteria("Person_aliases", "Person_aliases");
subC.add(Restrictions.sqlRestriction("{alias}.element='superman'");

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(); 

See this Hibernate bugs and use attached files:

• https://hibernate.atlassian.net/browse/HHH-2952
• https://hibernate.atlassian.net/browse/HHH-2381

The question is actually quite old, but since I encountered the same problem today and no answer satisfied my needs, I came up with the following solution, based on the comment by Brett Meyer on HHH-6353, that this issues won't be fixed.

Basically, I extended the SQLCriterion class to be able to handle more than the base table alias. For convenience reasons I wrote a small container class that links the user given alias with the matching subcriteria instance to be able to replace the user given alias with the alias hibernate created for the subcriteria.

Here is the code of the MultipleAliasSQLCriterion class

public class MultipleAliasSQLCriterion extends SQLCriterion
{
    /**
     * Convenience container class to pack the info necessary to replace the alias      generated at construction time
     * with the alias generated by hibernate
     */
    public static final class SubCriteriaAliasContainer
    {
        /** The alias assigned at construction time */
        private String alias;

        /** The criteria constructed with the specified alias */
        private Criteria subCriteria;

        /**
         * @param aAlias
         *            - the alias assigned by criteria construction time
         * @param aSubCriteria
         *            - the criteria
         */
        public SubCriteriaAliasContainer(final String aAlias, final Criteria aSubCriteria)
        {
            this.alias = aAlias;
            this.subCriteria = aSubCriteria;
        }

        /**
         * @return String - the alias
         */
        public String getAlias()
        {
            return this.alias;
        }

        /**
         * @return Criteria - the criteria
         */
        public Criteria getSubCriteria()
        {
            return this.subCriteria;
        }
    }

    private final SubCriteriaAliasContainer[] subCriteriaAliases;

    /**
     * This method constructs a new native SQL restriction with support for multiple aliases
     * 
     * @param sql
     *            - the native SQL restriction
     * @param aSubCriteriaAliases
     *            - the aliases
     */
    public MultipleAliasSQLCriterion(final String sql, final SubCriteriaAliasContainer... aSubCriteriaAliases)
    {
        super(sql, ArrayHelper.EMPTY_OBJECT_ARRAY, ArrayHelper.EMPTY_TYPE_ARRAY);

        this.subCriteriaAliases = aSubCriteriaAliases;
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException
    {
        // First replace the alias of the base table {alias}
        String sql = super.toSqlString(criteria, criteriaQuery);

        if (!ArrayUtils.isEmpty(this.subCriteriaAliases))
        {
            for (final SubCriteriaAliasContainer subCriteriaAlias : this.subCriteriaAliases)
            {
                sql = StringHelper.replace(sql, subCriteriaAlias.getAlias(), criteriaQuery.getSQLAlias(subCriteriaAlias.getSubCriteria()));
            }
        }

        return sql;
    }
}

I use it like this

final String sqlRestriction = "...";
final String bankAccountAlias = "ba";
final Criteria bankAccountCriteria = customerCriteria.createCriteria("bankAccount", bankAccountAlias);

SubCriteriaAliasContainer bankAccountSubAliasCon = new SubCriteriaAliasContainer(bankAccountAlias, bankAccountCriteria);        

customerCriteria.add(new MultipleAliasSQLCriterion(sqlRestriction, bankAccountSubAliasCon));

But there is no need to specify the alias at criteria creation - you can also specify it at the SQL restriciton and pass it to a container.

final String sqlRestriction = "... VALUES(ba.status_date), (ba.account_number) ...";
final Criteria bankAccountCriteria = customerCriteria.createCriteria("bankAccount");

SubCriteriaAliasContainer bankAccountSubAliasCon = new SubCriteriaAliasContainer("ba", bankAccountCriteria);        

customerCriteria.add(new MultipleAliasSQLCriterion(sqlRestriction, bankAccountSubAliasCon));

org.hibernate.criterion.CriteriaQuery has a method getColumnsUsingProjection which gives you the aliased column name.

You could implement your own Criterion, using org.hibernate.criterion.PropertyExpression as an example.

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")));