Hi,

I’m using Hibernate 3.x, Oracle 10g in our application. I’m using hibernate criteria with pagination option for fetching results and using projections for getting result count. The following are the queries generated by the hibernate.

Total Result Count Criteria Code
criteria.setResultTransformer(Criteria.PROJECTION);
totalCount=(Integer) criteria.setProjection(Projections.rowCount()).uniqueResult();

Query generated by hibernate
select count(*) as y0_ from table1 this_ where this_.GroupId in (?) and this_.status in (?, ?, ?, ?) and this_.UserId=?

Pagination Results Criteria
criteria.setFirstResult(startCount);
criteria.setMaxResults(pageSize);
result=crit.list();

Query generated by hibernate
select * from ( select this_.column1 as column1_0_, this_.column2 as column2_0_, this_.column3 as column3_6_0_ from table1 this_
where this_.GroupId in (?) and this_.status in (?, ?, ?, ?) and this_.UserId=?) where rownum <= ?

I want to combine this two queries together using oracle 10g analytic function

select * from ( select this_.column1 as column1_0_, this_.column2 as column2_0_, this_.column3 as column3_6_0_, count(*) over (partition by 1) as totalcount from table1 this_ where this_.GroupId in (?) and this_.status in (?, ?, ?, ?) and this_.UserId=?) where rownum <= ?

I cannot use named query since these are all dynamically generated by criteria so Please suggest how to add this feature to hibernate criteria (suggest options like extending dialect, using formula mapping)

Thanks
Jaya Murugan

Hi All,

I'm able to achieve this by using sqlProjection

projectionsList.add(Property.forName("column1"));
projectionsList.add(Property.forName("column2"));
projectionsList.add(Property.forName("column3"));
projectionsList.add(Property.forName("column4"));
projectionsList.add(Projections.sqlProjection("count(*) over (partition by 1) as totalCount",
new String[]{"totalCount"},new Type[]{Hibernate.STRING}));
crit.setProjection(projectionsList);
List lst=crit.list();

Hibernate Generated SQL Query
select * from ( select this_.column1 as y0_, this_.column2 as y1_, this_.column3 as y2_,this_.column4 as y3, count(*) over (partition by 1) as totalCount from table1 this_ where this_.GroupId in (?) and this_.status in (?, ?, ?, ?) and this_.UserId=? order by this_.UserId desc ) where rownum <= ?

There are two issues with this approach
1. Setting the all the properties in projection (Even though I require all the properties of class I need to specify all the property names in the projection list)

2. Need to have result transformer to return list of vo (This is required to map totalcount to vo. Also,The existing transformers (AliastoBean) requires alias name for all the projection but having alias for projection is creating issue in query "where clause" the actual column names in the "where clause" are generated as alias causing Oracle query to fail)

Please suggest better approach or solution to any of these issues.

Thanks
Jaya Murugan

Hi All,

I tried another approach and its working

In the HBM Mapping (Use formula for the total count property)

<property name="totalCount" type="string" formula="(count(*) over (partition by 1))"/>

Extend the Oracle10g Dialect and register over and partition as new functions

@Override
protected void registerFunctions() {
registerFunction( "over", new StandardSQLFunction("over"));
registerFunction( "partition",new StandardSQLFunction("partition")); super.registerFunctions();
}

List lst=crit.list();

Hibernate Generated SQL Query
select * from ( select this_.column1 as y0_, this_.column2 as y1_, this_.column3 as y2_,this_.column4 as y3, count(*) over (partition by 1) as totalCount from table1 this_ where this_.GroupId in (?) and this_.status in (?, ?, ?, ?) and this_.UserId=? order by this_.UserId desc ) where rownum <= ?

Now you can use formula for analytic related functions without changing the criteria code.

Note:Since I'm using read only view so I didn't test update or insert operations which might have implications.

Thanks
Jaya Murugan

Hi,
I got sqlGrammerException when i use the formula in hbm.xml.
I am getting alias to partition "this_.partition".and oracle giving the exception
java.sql.SQLException: ORA-00907: missing right parenthesis
select * from ( select this_.column1 as y0_, this_.column2 as y1_, this_.column3 as y2_,this_.column4 as y3, count(*) over (this_.partition by 1) as totalCount from table1 this_ where this_.GroupId in (?) and this_.status in (?, ?, ?, ?) and this_.UserId=? order by this_.UserId desc ) where rownum <= ?
Thanks
Raj