Java 是否可以获取 Hibernate sqlRestriction 的连接表的 SQL 别名？

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

提问by Jason Novak

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".

我有一个 Person 类，它有一个 String 别名集合，表示该人可能会使用的其他名称。例如，克拉克肯特可能有别名“超人”和“钢铁侠”。德怀特霍华德还有一个别名“超人”。

@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

Hibernate 在我的数据库中创建了两个表，Person 和 Person_aliases。Person_aliases 是一个包含 Person_id 和 element 列的连接表。假设 Person_aliases 有以下数据

--------------------------------
| 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 查询，而不是 HQL 查询（除非可以在 Criteria 对象上添加 HQL 限制，在这种情况下，我全神贯注）或原始SQL 查询。因为根据如何使用 Hibernate Criteria 在 String 集合中查询具有值的对象？使用 CriteriaAPI 引用值类型集合的元素是不可能的，我认为我会在我的标准对象上添加一个 SqlRestriction 。

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

in the hopes that Hibernate will create an SQL statement like

希望 Hibernate 会创建一个 SQL 语句，如

    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.

但是，我需要用 SQL 查询中 Person_aliases 表的表别名填写 XXXXX，在本例中为“aliases2_”。我注意到如果我需要对 Person 表别名的引用，我可以使用 {alias}。但这行不通，因为 Person 是此 Criteria 的主表，而不是 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?

XXXXX 填什么？如果没有像 {alias} 这样好的替代令牌，那么有没有办法让我休眠来告诉我该别名将是什么？我注意到一个名为 generateAlias() org.hibernate.util.StringHelper 类的方法。这会帮助我预测别名是什么吗？

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

我真的非常想避免硬编码“aliases2_”。

Thanks for your time!

谢谢你的时间！

采纳答案by Pascal Thivent

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:

似乎 Criteria API 不允许查询元素集合，请参阅HHH-869（仍处于开放状态）。所以要么尝试建议的解决方法 - 我没有 - 要么切换到 HQL。以下 HQL 查询将起作用：

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

回答by xmedeko

May this linkhelp you? It advices:

可能这个环节帮你吗？它建议：

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

回答by xmedeko

See this Hibernate bugs and use attached files:

请参阅此 Hibernate 错误并使用附加文件：

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

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

回答by thangaraj

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

回答by pstanton

as xmedeko alludes to, when you want to do:

正如 xmedeko 所暗示的，当您想要执行以下操作时：

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

这为我解决了类似的问题，而无需去 HQL

回答by Marius K.

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 commentby Brett Meyer on HHH-6353, that this issues won't be fixed.

这个问题实际上已经很老了，但由于我今天遇到了同样的问题并且没有答案满足我的需求，我想出了以下解决方案，基于Brett Meyer 对HHH-6353的评论，这个问题不会得到解决.

Basically, I extended the SQLCriterionclass 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.

基本上，我扩展了SQLCriterion类以能够处理比基表别名更多的内容。为方便起见，我编写了一个小容器类，将用户给定的别名与匹配的子标准实例链接起来，以便能够用为子标准创建的别名 hibernate 替换用户给定的别名。

Here is the code of the MultipleAliasSQLCriterion class

这是 MultipleAliasSQLCriterion 类的代码

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.

但是不需要在创建条件时指定别名 - 您也可以在 SQL 限制中指定它并将其传递给容器。

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

回答by meirellesMS

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

回答by jarnoan

org.hibernate.criterion.CriteriaQueryhas a method getColumnsUsingProjectionwhich gives you the aliased column name.

org.hibernate.criterion.CriteriaQuery有一个方法getColumnsUsingProjection可以为您提供别名列名。

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

您可以实现自己的Criterion，org.hibernate.criterion.PropertyExpression例如使用。