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 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_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!

Sartre

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)

Gaylagayle

Thanks for your response - I actually did know about the unfortunate Criteria API limitation thanks to a previous Stack Overflow question but had hopes that using a native SQL snippet via sqlRestriction might provide a 'hack' that would let us still use Criteria. But I can't figure out a way to get a reference to the name of the join table. If there is no way, I'll resort to HQL like you suggested. However, there are reasons I don't want to which would take more space than I have here to explain (this question is a simplified representation of our actual query) – Sartre

@Jason Maybe it's possible but I don't see how without hard coding the table name. – Gaylagayle

Answer 2

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

Vulpine

Am I supposed to use the actual string {alias} or replace that with the object name like accountUser? – Tripalmitin

use the string {alias} it is a hibernate keyword and unfortunately it is the only way to use aliases in criteria world. – Vulpine

This works fine but I cannot find a document related to this. Do you know one? – Medicine

docs.jboss.org/hibernate/orm/3.5/reference/en/html/… -depends on your version. – Vulpine

@Vulpine what if i have another alias?? what is him name – Uhf

{alias} is a keyword in my eg it takes the place of "joinedEntity" (RTFM) – Vulpine

How to use two aliases ? I want where this and that, where this and that are two different aliases .... sqlRestriction is allowing only for one – Stator

Answer 3

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

Ames

Answer 4

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

Photocompose

Calling createCriteria() method for join object on main class solved my issue. I added all the join fields in sub criteria of join object's class and my problem was solved. – Lucillelucina

Answer 5

See this Hibernate bugs and use attached files:

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

Photocompose

Answer 6

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

Lobelia

I wonder if there aren't two typos in your example. Aren't the bankAccountSubAliasCon objects supposed to be passed to the MultipleAliasSQLCriterion constructor ? – Monohydroxy

Here is how I used your utility: ` String hibernatePersonAlias = "p"; Criteria personCriteria = mainCriteria.createCriteria("person", hibernatePersonAlias); String personSalarySqlCriterion = hibernatePersonAlias + ".salary > 1500"; MultipleAliasSQLCriterion.SubCriteriaAliasContainer personAliasContainer = new MultipleAliasSQLCriterion.SubCriteriaAliasContainer(hibernatePersonAlias, personCriteria); mainCriteria.add(new MultipleAliasSQLCriterion(personSalarySqlCriterion, personAliasContainer));` – Monohydroxy

Yes, I think you might be right @Stephane. I'll edit it to represent the correct use – Lobelia

Hi Marius, I tried to do a similar utility #36173303 but it is not working as expected. Knowing you might have a better understanding of this, I reach out to you, if you have a moment to look at it. Thanks! – Monohydroxy

Answer 7

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.

Silverweed

Answer 8

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

Whiteheaded