我有一个下面的查询,我必须从临时表中选择由子查询创建的行。
从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 descI 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中的嵌套查询
发布评论