是否可以获取Hibernate sqlRestriction的连接表的SQL别名？ [英] Is it possible to get the SQL alias of a join table for a Hibernate sqlRestriction?

问题描述

我有一个Person类，它有一个字符串集合的别名表示人可能去的附加名称。例如，Clark Kent可能有别名超人和钢铁人。 Dwight Howard也有一个别名超人。

  @Entity 
 class Person {
 
 @CollectionOfElements（fetch = FetchType.EAGER）
设置< String> aliases = new TreeSet< String>（）; 
  

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

：

  ------------------- ------------- 
 | Person_id |元素| 
 -------------------------------- 
 | Clark Kent |超人| 
 | Clark Kent |钢铁公司| 
 |霍华德超人| 
 | Bruce Wayne |蝙蝠侠| 
 -------------------------------- 
  

我想对所有通过超人别名的人进行一个休眠Criteria查询。

由于这里列出太长的原因，我真的希望这是一个Criteria查询，而不是一个HQL查询（除非有可能添加一个HQL限制Criteria对象，在这种情况下我都耳朵）或原始SQL查询。因为根据如何使用Hibernate Criteria在String集合中查询具有值的对象？使用CriteriaAPI引用值类型集合的元素是不可能的我认为我想借助于添加一个SqlRestriction我的条件对象。

  Criteria crit = session.createCriteria（Person.class）; 
 crit.add（Restrictions.sqlRestriction（XXXXX.element ='superman'）; 
  

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

  select * 
 from 
 Person this_ 
 left outer join 
 Person_aliases aliases2_ 
 on this_.id = aliases2_.Person_id 
其中
 XXXXX.element ='superman'
  
 
 
我为XXXXX填写什么？如果没有像{alias}这样的好的替换令牌，那么我有没有办法让hibernate告诉我别名是什么？我注意到一个方法称为generateAlias（）org.hibernate.util.StringHelper类。这会帮助我预测别名会是什么？ 
 
 
 
我真的很想避免硬编码'aliases2_'。 
 
 
 
感谢您的时间！
解决方案
 API不允许查询元素集合，请参见 HHH-869 （仍然打开）。所以，尝试建议的解决方法 - 我没有 - 或切换到HQL。以下HQL查询将工作：
  from Person p其中：元素中的别名（p.aliases）
  
 
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!
解决方案
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)


                        
这篇关于是否可以获取Hibernate sqlRestriction的连接表的SQL别名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！