Pagination with Hibernate

Tags: Coldfusion, Hibernate

One of the things that has never really been easy is pagination within Coldfusion. And we all now know that Hibernate will be part of the core of Coldfusion now, but what we dont know is how much easier this will make our work.

With the addition to some extra coding by Barney he has written cfgroovy which also allows for the adoption of hibernate in Coldfusion now. Coldfusion 8 also added the ability to return data in paginated form, however that is not really a best world example as it returns more data than is really necessary each and every time before the function returns the data you specify.

That method was great for Ajax UI controls in Coldfusion, but could become a pain if there are millions of records within the table.

Another way to do it was to do a subselect within the select, but dpending on your database this became messy for each of the databases you supported as it was done differently each for each database.

So this is where hibernate comes into it.

Session sess = factory.openSession();
var results = sess.createQuery("from Account order by username");
Results.setFirstResult(FirstRow);
Results.setMaxResults(Count);
sess.close();
return Results.list();

Now the above code is directly a hibernate snippet, that one might use to get the session and when Coldfusion 9 is released the method of doing this might be different, or might be the same. We'll just have to wait and see.

The beauty about this code is that if you have a many to one relationship, or even a one to many relationship the query can be easily adopted with minimal fuss. And by setting the First Result and the Max Results, the data is fetch in the manner you want with very little code.

When compared to the way you would currently need to do it in Coldfusion, and remember that the more complex the table relationships the more complex the following code will get. And not to mention more dificult to debug as well.

<cffunction name="getArtists" access="remote">
<cfargument name="page" required="yes">
<cfargument name="pageSize" required="yes">
<cfargument name="gridsortcolumn" required="yes">
<cfargument name="gridsortdirection" required="yes">

<cfquery name="members" datasource="cfartgallery">
SELECT TOP #pagesize# ARTISTID, FIRSTNAME, LASTNAME, CITY, STATE
FROM ARTISTS
WHERE (ARTISTID NOT IN
(SELECT TOP #page# ARTISTID
FROM ARTISTS AS ARTISTS1 ORDER BY ARTISTID))
ORDER BY ARTISTID
</cfquery>
<cfreturn queryconvertforgrid(members,page,pagesize)/>
</cffunction>

I think it is safe to say that the hibernate way to do it, is also much neater and from a snapshot you also know exactly what is happening at first glance as well.

TweetBacks


Comments (Moderated) | Add Comment

There are no comments for this entry.