SQL语句转换为hibernate标准（具有count子句）

问题描述

我有一个SQL查询，我想将它转换为一个标准（Hibernate 3.3.2），将其用于我的持久层。
这是查询：

  select last_name 
 from member 
 where 
 member .end_date> SYSDATE 
 group by member.last_name 
具有count（member.last_name）> 1; 
  

我已经尝试了很多组合，但没有结果对我有好处。 。这是我的代码：

  Criteria criteria = getSession（false）.createCriteria（Member.ENTITY_NAME）; 
 criteria.setProjection（Projections.projectionList（）
 .add（Projections.groupProperty（lastName））
 .add（Projections.count（lastName）。as（cptLastName ））
）; 
 criteria.add（Restrictions.ge（endDate，now））
 .add（Restrictions.le（startDate，now））
 //。add（Restrictions.gt cptLastName，new Integer（1）））
 .addOrder（Order.asc（lastName））
; 
  

通过使用注释行编写它，我有一个包含名称和数量的对象列表该名称出现在数据库中的时间。但是当我想分解它时，这个错误：
java.sql.SQLException：ORA-00904：Y1_：无效标识符

开发这段代码，我受到了以下不同文章的启发：

我认为我并不遥远解决方案，但有没有人来帮助我？
如果您有任何问题，请不要犹豫，问我。
如果您发现另一个替代方法可以粘贴到此查询中，请毫不犹豫地提出它...

如果我是对的，你现在不能直接在hibernate标准中使用having子句计数，请在下面找到修改后的查询。如果它不起作用，请发布整个堆栈跟踪

  DetachedCriteria innerQuery = DetachedCriteria.forClass（Member.ENTITY_NAME，inner） ; 
 innerQuery.setProjection（Projections.rowCount（））; 
 innerQuery.add（Restrictions.eqProperty（lastName，outer.lastName））; 
 
标准c = s.createCriteria（Member.ENTITY_NAME，outer）; 
 c.setProjection（Projections.property（lastName））; 
 c.add（Restrictions.gt（endDate，now））
 c.add（Subqueries.eq（new Integer（1），innerQuery））; 
  




I have an SQL query that I would like to convert into a criteria (Hibernate 3.3.2) to use it into my persistancy layer.That's the query :

select last_name
  from member
  where
    member.end_date > SYSDATE
  group by member.last_name
  having count(member.last_name) >1;

I already try a lot a combinations, but there is no result that would be good for me ... This is my code :

Criteria criteria = getSession(false).createCriteria(Member.ENTITY_NAME);
criteria.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("lastName"))
            .add(Projections.count("lastName").as("cptLastName"))
            );
criteria.add(Restrictions.ge("endDate", now))
            .add(Restrictions.le("startDate", now))
            //.add(Restrictions.gt("cptLastName", new Integer(1)))
            .addOrder(Order.asc("lastName"))
;

With writing it with the comment line, I have an objects list that contains the name and the number of times that the name appears in the database. But when i would like to decomment it, this the error :java.sql.SQLException: ORA-00904: "Y1_": invalid identifier

To develop this code, I am inspired by different post below:

• Hibernate Criteria: Projecting Count with group by clause

• Hibernate count rows with some criterias

• How do we count rows using Hibernate?

• HOW to use HAVING COUNT(*) with hibernate

• Hibernate Criteria API - HAVING clause work arounds

I think I'm not far of the solution but is there anybody to help me?If you have questions, don't hesitate to ask me it.And if you found another workaround for me to paste to this query, don't hesitate to propose it...

解决方案

Hi if i am right you cant use the having clause count directly in hibernate criteria as of now, Please find below the modified query. If it doesnt works, please post the entire stack trace

DetachedCriteria innerQuery = DetachedCriteria.forClass(Member.ENTITY_NAME, "inner");
  innerQuery.setProjection(Projections.rowCount());
  innerQuery.add(Restrictions.eqProperty("lastName", "outer.lastName"));

  Criteria c = s.createCriteria(Member.ENTITY_NAME, "outer");
  c.setProjection(Projections.property("lastName"));
  c.add(Restrictions.gt("endDate", now))   
  c.add(Subqueries.eq(new Integer(1), innerQuery));

这篇关于SQL语句转换为hibernate标准（具有count子句）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！