使用Criterion的Hibernate中的嵌套查询 [英] Nested Query in Hibernate using Criterion
问题描述
我有一个下面的查询,我必须从临时表中选择由子查询创建的行。
从x中选择x,y,x
从some_table中选择x,y,z,其中x在x1和x2之间)
其中y喜欢'y1'
按z排序by desc
我必须使用Criteria从数据库中获取结果
我已经经历了使用标准和分离标准处理子查询的几个示例和文档。我已经使用分离查询,但它不服务的目的或我缺少的东西。
我使用下面的代码
DetachedCriteria subCriteria =
DetachedCriteria.forClass(SomeClass.class)
.add(Restrictions.between(x,x1,x2) )
.setProjection(Projections.projectionList()
.add(Projections.property(x))
.add(Projections.property(y))
。 add(Projections.property(z));
List< Object []> results = session
.createCriteria(Program.class)
。 exists(subCriteria))
.add(Restrictions.like(y,y1))
.addOrder(Order.desc(z))list();
HQL或JPQL都不支持派生表表达式。你可以使用sub - 选择或选择,但这是很多。 / p>
这次你需要使用本地查询,这实际上是正确的。当你想获取实体而不是投影时,HQL / JPQL最有用。
I have a following query where I have to select rows from temporary table created by subquery.
select x, y, x
from (select x, y, z from some_table where x between x1 and x2)
where y like 'y1'
order by z by desc
I have to use Criteria for fetching result from database
I have gone through several examples and documentation for handling subqueries using criteria and detached criteria. I have used Detached query but it is not serving the purpose or I am missing something.
I have used following code
DetachedCriteria subCriteria =
DetachedCriteria.forClass(SomeClass.class)
.add(Restrictions.between("x","x1","x2"))
.setProjection(Projections.projectionList()
.add(Projections.property("x"))
.add(Projections.property("y"))
.add(Projections.property("z"));
List<Object[]> results = session
.createCriteria(Program.class)
.add(Subqueries.exists(subCriteria))
.add(Restrictions.like("y", "y1"))
.addOrder(Order.desc("z")).list();
解决方案 Neither HQL or JPQL support "Derived Table Expressions". You can use sub-selects or in-selects but that's much it.
You need to use a native query this time and that's actually the right thing to do. HQL/JPQL are mostly useful when you want to fetch Entities not Projections.
这篇关于使用Criterion的Hibernate中的嵌套查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!