我想为以下本机SQL创建标准。
不幸的是,两次使用CreateCriteria时,我点击了重复的关联路径的错误。当我尝试使用限制时。它无法提供我想要的SQL。
尝试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:无效的关系运算符,因为" where"
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
感谢您的帮助。谢谢。
org.hibernate.QueryException: duplicate association path
的问题上有一个旧的Hibernate Bug HHH-879,仍在2005年打开并仍在打开...
其他问题没有解决方案HHH-7882
关闭所以选项1)不合适。
但是在上述错误的评论中,使用exists
因此,使用exists
使用两次sqlRestriction
,并使用相关的子查询过滤Propper类别。您只会获得连接到两个类别的 Companies
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 ) );
这导致以下查询,该查询提供了正确的结果
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 = ?)