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!

score 15 · Answer 1 · answered Aug 03 '11 at 02:19

15

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

answered Aug 03 '11 at 02:19

pstanton

35,033
24
126
168

Am I supposed to use the actual string `{alias}` or replace that with the object name like `accountUser`? – NobleUplift Jul 13 '16 at 20:05
1

use the string {alias} it is a hibernate keyword and unfortunately it is the only way to use aliases in criteria world. – pstanton Jul 14 '16 at 04:28
This works fine but I cannot find a document related to this. Do you know one? – Sanghyun Lee Jul 27 '16 at 05:32
https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/querycriteria.html -depends on your version. – pstanton Jul 28 '16 at 05:50
@pstanton what if i have another alias?? what is him name – Alberto Acuña Jul 12 '18 at 08:14
`{alias}` is a keyword in my eg it takes the place of "joinedEntity" (RTFM) – pstanton Jul 12 '18 at 19:47
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 – user1735921 Dec 14 '18 at 12:31

Pascal Thivent · Accepted Answer · 2010-02-27T15:58:49.317

4

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)

edited Feb 27 '10 at 15:58

answered Feb 27 '10 at 03:57

Pascal Thivent

562,542
136
1,062
1,124

1

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) – Jason Novak Mar 01 '10 at 17:14
@Jason Maybe it's possible but *I* don't see how without hard coding the table name. – Pascal Thivent Mar 01 '10 at 17:20

score 3 · Answer 3 · answered Jun 30 '14 at 16:10

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

answered Jun 30 '14 at 16:10

meirellesMS

346
3
3

Marius K. · Answer 4 · 2016-03-22T10:35:28.673

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

I wonder if there aren't two typos in your example. Aren't the bankAccountSubAliasCon objects supposed to be passed to the MultipleAliasSQLCriterion constructor ? — Stephane, Mar 22 '16 at 07:39
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));` — Stephane, Mar 22 '16 at 09:35
Yes, I think you might be right @Stephane. I'll edit it to represent the correct use — Marius K., Mar 22 '16 at 10:35
Hi Marius, I tried to do a similar utility http://stackoverflow.com/questions/36172803/sql-projection-utility-matching-multiple-hibernate-alias-names-to-sql-alias-ones 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! — Stephane, Mar 23 '16 at 08:13

score 2 · Answer 5 · answered Aug 20 '10 at 08:50

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

answered Aug 20 '10 at 08:50

xmedeko

7,336
6
55
85

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. – Mital Pritmani Jul 16 '14 at 12:33

xmedeko · Answer 6 · 2021-03-25T06:42:05.610

2

See this Hibernate bugs and use attached files:

edited Mar 25 '21 at 06:42

answered Aug 20 '10 at 14:56

xmedeko

7,336
6
55
85

score 0 · Answer 7 · answered Jan 11 '17 at 10:06

0

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.

answered Jan 11 '17 at 10:06

jarnoan

4,309
1
19
17

score -4 · Answer 8 · edited Jan 06 '12 at 22:42

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

edited Jan 06 '12 at 22:42

thkala

84,049
23
157
201

answered Oct 30 '10 at 08:32

thangaraj

1

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

8 Answers8

Linked