Criteria queries with user defined select list

cameronbraid · **Joined:** Thu Aug 26, 2004 5:53 am **Posts:** 37

I make heavy use of the Criteria API for pagination.

Currently, I am using MySQL 4, with a custom hacked PagenatedCriteria object that provides me extra functionality.

I am able to instruct mysql, when using limit and offset, to provide a count of the number of records found before the limit and offset was found. This requires a select hint within the sql query, and a second query to fetch the total number.

This means that I can do very efficient pagination. I can also know the total number of records matching the where clause.

I wish to change over to PostgreSQL, and stop using this hacked PagenatedCriteria object.

I am happy to run two queries - a count query, and the list query. However, currently the Criteria API can only return a list of entities.

I would love to see a modification to the criteria API to support returning a custom select list.

Something like:

Code:

Criteria.uniqueResult(String selectList) // hibernate 3
Criteria.list(String selectList) // hibernate 2.x

// or even 

session.createCriteria(Class entityClass, String selectList)

Then I could use this API like this :

Code:

Criteria criteria = session.createCriteria(MyClass.class);

addExpressions(criteria);

// get the count list (executes a sql query)
Integer count = (Integer)criteria.uniqueResult("select count(*)");

// select page
criteria.setMaxResults(10);
criteria.setFirstResult(100);

// get the paged list
List pageOfItems = criteria.list();

I can see that these concepts could be implemented in many ways.

1) criteria cloning with a mutable selectList

Code:

Criteria criteria = session.buildCriteria(MyClass.class);

addExpressions(criteria);

Criteria countCriteria = criteria.clone();
countCriteria.setSelectList("select count(*)");
Integer count = (Integer)countCriteria.list().iterator().next();

List itemsForPage = criteria.list();

2) user creates a criteria instance per query, with immutable selectList

Code:

Criteria countCriteria = session.createCriteria(MyClass.class, "select count(*)");
addExpressions(countCriteria);
Integer count = (Integer)countCriteria.list().iterator().next();

Criteria criteria = session.createCriteria(MyClass.class);
addExpressions(criteria);
criteria.setMaxResults(10);
criteria.setFirstResult(100);
List itemsForPage = criteria.list();

I guess that the minimum addition is the ability to specify the 'select list' for criteria based queries. This will allow use of code that generates expressions to be used for both the count query as well as the list query. In fact it can even be used to generate other aggregates.

I know that there was discussion about (not)introducing a .count() method to the criteria API, could this be a way round it ?

This will also allow returning Object[]s or even a list of a single property of an entity.

Any thoughts / ideas ?

christian · **Posted:** Thu Aug 26, 2004 6:26 am

http://opensource.atlassian.com/project ... wse/HB-474

cameronbraid · **Joined:** Thu Aug 26, 2004 5:53 am **Posts:** 37

Is that patch ever likely to be integrated into hibernate ?

Is there any likelyhood that the criteria api may support a custom 'selectList' ?

christian · **Posted:** Thu Aug 26, 2004 6:42 am

Maybe 3.1, read the comments in JIRA.

gavin · **Posted:** Thu Aug 26, 2004 7:03 am

We don't plan to spend any time improving Criteria API until the 3.1 timeframe, unless someone wants to really step up and take on the responsibility to "own" the Criteria stuff and put a lot of work into it. So far no-one has offered to do that.

We have to prioritize things, and this got pushed back.

gavin · **Posted:** Thu Aug 26, 2004 7:05 am

But to answer your question: yes, eventually we will support projection and aggregation in the Criteria API.

cameronbraid · **Joined:** Thu Aug 26, 2004 5:53 am **Posts:** 37

thankyou both.

I have downloaded the patched 2.1.6 jar with the Criteria.count() patch.

This will suit my purposes until 3.1 :)

Thanks to both of you for your prompt replies.

gavin · **Posted:** Thu Aug 26, 2004 7:25 am

Thank you Cameron.

(Wow, I'm sure you've been using Hibernate since about 0.9.x or something....)

cameronbraid · **Joined:** Thu Aug 26, 2004 5:53 am **Posts:** 37

yeah, its been a while...

though I dunno if it was that long ago !

Fedor · **Joined:** Thu Nov 04, 2004 4:56 am **Posts:** 2

Sorry? may be stupid question/ But how to download patched hibernate2.1.6? Just now downloaded from http://prdownloads.sourceforge.net/hibe ... 1.6.tar.gz
There is no count() method in Criteria.

Thank you, Fedor

cameronbraid · **Joined:** Thu Aug 26, 2004 5:53 am **Posts:** 37

you have to download the patched JAR from the jira task at

http://opensource.atlassian.com/project ... wse/HB-474

direct link :

http://opensource.atlassian.com/project ... atched.jar

Fedor · **Joined:** Thu Nov 04, 2004 4:56 am **Posts:** 2

thank's a lot

jdelfosse · **Posted:** Wed Nov 17, 2004 3:00 pm

I don't know whether implementing projection or aggregation support in Criteria is an heavy task, but I see an immediate and very important benefit to it.

In the web application I'm currently developing, almost all queries require a record count for pagination. Most of my DAO methods should generate different queries based on the (non) nullity of input parameters and Criteria queries are perfect for that since they allow you build your query incrementally. However, without count support, I'm stuck to write 2^n hql queries to return objects, plus 2^n queries to return object count.

It's quite a pity to loose object orientation of the query api in a tool that aims to provide an object orientation view of tabular data. But please, don't get me wrong, Hibernate is a truly wonderfull product, could not live without it.