Hibernate Create Criteria两次连接同一张表-尝试了2种方法,但有2个差异错误 [英] Hibernate Create Criteria to join the same table twice - tried 2 approach with 2 difference error
问题描述
我想为以下本地sql创建创建条件.
I would like to create create criteria for the following native sql.
不幸的是,我两次使用createCriteria时遇到重复的关联路径错误. 当我尝试使用Restrictions.sqlRestriction时.它无法提供我想要的SQL.
Unfortunately, I hit error of duplicate associate path when use createCriteria twice. When I try to use Restrictions.sqlRestriction. It unable provide the SQL that I want.
尝试1:创建条件-重复的关联路径
Criteria criteria = getSession().createCriteria( Company.class );
criteria.createAlias( "customerCategories", "c1" );
criteria.add( Restrictions.in( "c1.customerCategory.customerCategoryId",
company.getBaseCustomerCategoryId() ) );
criteria.createAlias( "customerCategories", "c2" );
criteria.add( Restrictions.in( "c2.customerCategory.customerCategoryId",
company.getPromoCustomerCategoryId() ) );
尝试2:创建SQL限制-ORA-00920:由于位置"而导致的关系运算符无效
Criteria criteria = getSession().createCriteria( Company.class );
criteria.add( Restrictions.sqlRestriction(
"INNER JOIN Company_Customercategory a on {alias}.companyId = a.companyId and a.CUSTOMERCATEGORYID = ?",
company.getBaseCustomerCategoryId(), LongType.INSTANCE ) );
criteria.add( Restrictions.sqlRestriction(
"1=1 INNER JOIN Company_Customercategory b on {alias}.companyId = b.companyId
and b.CUSTOMERCATEGORYID = ?",
company.getPromoCustomerCategoryId(), LongType.INSTANCE) );
错误的结果
select this_.* from Companies this_ where
INNER JOIN Company_Customercategory a
on this_.companyId = a.companyId
and a.CUSTOMERCATEGORYID = 1
and 1=1 INNER JOIN Company_Customercategory b
on this_.companyId = b.companyId
and b.CUSTOMERCATEGORYID = 6
期望的SQL
select * from companies c
inner join Company_Customercategory a
on c.companyId = a.companyId
and a.CUSTOMERCATEGORYID = 1
inner JOIN Company_Customercategory b
on a.companyId = b.companyId
and b.CUSTOMERCATEGORYID = 6
感谢您的帮助. 谢谢.
Appreciate your help. Thanks.
推荐答案
有一个旧的Hibernate错误 HHH-879 关于org.hibernate.QueryException: duplicate association path
的问题,该问题在2005年仍在打开...
There is an old Hibernate bug HHH-879 on the problem of org.hibernate.QueryException: duplicate association path
opened 2005 and still open...
其他问题已关闭,没有解决方法 HHH-7882
Other issue is closed without solution HHH-7882
所以选项1)不太合适.
So the option 1) is rather not suitable.
但是在上述错误的注释中,使用exists
But in the comments of the above bug an usefull workaround is mentioned using exists
因此,对exists
使用两次sqlRestriction
,并使用相关的子查询过滤propper类别.您只会获得同时连接到这两个类别的 companies .
So use twice sqlRestriction
with exists
and a correlated subquery filtering the propper category. You will get only companies connected to both categories.
crit.add( Restrictions.sqlRestriction(
"exists (select null from Company_Customercategory a where {alias}.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?)",
1, IntegerType.INSTANCE ) );
crit.add( Restrictions.sqlRestriction(
"exists (select null from Company_Customercategory a where {alias}.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?)",
6, IntegerType.INSTANCE ) );
这将导致以下查询提供正确的结果
This leads to the following query which provides the correct result
select this_.COMPANY_ID as COMPANY_ID1_2_0_, this_.COMPANY_NAME as COMPANY_NAME2_2_0_
from COMPANIES this_
where exists (select null from Company_Customercategory a
where this_.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?) and
exists (select null from Company_Customercategory a
where this_.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?)
这篇关于Hibernate Create Criteria两次连接同一张表-尝试了2种方法,但有2个差异错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!