Hi
I am trying to use Hibernate to create the following queries using Criteria and HQL. I had been able to do this via Critieria and Projections e.g. obtianing results and resetting Projection and reusing the Critieria to get the rowcount(). However looking at the generated sql this causes Hibernate to fire off 2 queries, which is not what we want.
Another solution has been to add a formula to a count field (which is transient) to get the count of the resultset.
I have been trying to do this in HQL, I have had some success, however but running into problems transforming the transient count field.
here are 2 queries that I have been playing around with and produce the same result:
1:
select v.*,count(*) over (partition by v.id) as count from myview v where v.id = upper('user');
2:
select v.*, (select count(*) from myview v1) from myview v WHERE id = upper('user1')
My Domain Object has a getter/setter for Count annotated Transient.
And here is the code which is fine without trying to bring the count field, when including count(*), I cannot get it to transform to the MyView.class.
I have been using ResultTransformer but having problems with it (not shown below) e.g. invalid column or object not castable to MyView:
Code:
String query = "select {v.*},count(*) over "+
"(partition by v.id) as count from myview v where v.id= :id";
Query q = session.createSQLQuery(query).addEntity("v",MyView.class);
//.addScalar("count", Hibernate.INTEGER);
q.setParameter("id", new String("user1"));
q.list();
System.out.println("q.list() :"+q.list().size());
System.out.println("q.list() :"+q.list());
for (Iterator iterator = q.list().iterator(); iterator.hasNext();) {
MyView view = (MyView) iterator.next();
System.out.println("id:"+view.getId());
}
Any advice appreciated.
Thanks
Nick
Code:
Code:
Login
FAQ
Search