Filtering data with Hibernate

Tags: Coldfusion, Hibernate

In a previous post I touched on how you can use hibernate to return paginated data, today I am going to show how to use hibernate to filter the results returned from a query. Again I would like to say and endorse that currently Barney has a written CFGroovy and is a good example how to use hibernate within ColdFusion right now.

Anyway we all know that when dealing with queries within coldfusion, we have a very easy way to achieve what we need to do. It is flexiable and easy to write a query and get what we need back in a resultset or query as it is known in ColdFusion.

The following code is an example of a basic query within Coldfusion, that returns records from the database and is filtered based on the criteria that we wish to filter the data with.

<cffunction name="getArtists" access="remote">
<cfargument name="keyword" required="yes">

<cfquery name="members" datasource="cfartgallery">
SELECT TOP #pagesize# ARTISTID, FIRSTNAME, LASTNAME, CITY, STATE
FROM ARTISTS
WHERE Firstname like '%keyword%' or Lastname like '%keyword%'
ORDER BY ARTISTID
</cfquery>
<cfreturn members/>
</cffunction>

And yes as most of you know this is an example ColdFusion code snippet, and one should adopt and adhere to reducing SQL Injection by taking the appropriate steps to stop that. In almost all cases the best method is using the cfqueryparam, and that discussion is beyond the scope of this article.

So what would that look like in hibernate. Well to be honest it looks ugly to begin with, and if you are used to using a simple approach the hibernate way is a little daunting if you're not familiar with it.

So lets write the above query in hibernate.

session = sessionFactory.openSession();
Criteria crit = session.createCriteria("from Artists");
crit.add(Restrictions.like("Firstname", "%keyword%"));
List Artists = crit.list();
session.close();

Now although I didnt add the second or criteria to the above code, I just wanted to give an quick example on how to use the underlying methods to add a condition to the query. The above code can also be written differently, to make it a bit more easier to read as well.

session = sessionFactory.openSession();
Criteria crit = session.createCriteria("from Artists where FirstName like :keyword or lastname like :keyword", {keyword='%#keyword#%'});
List Artists = crit.list();
session.close();

Most ColdFusion users would maybe be more comfortable with the above syntax, as it is simple and to the point of what the query is doing. However there will come a time when you want to throw conditional code around the creation of the criteria and build it up on the fly. So the first example, would be more beneficial to adopting to code that needs to do this.

TweetBacks


Comments (Moderated) | Add Comment

  • Mark Mandel's Gravatar I think you've mixed up your createQuery() and your createCriteria()
    # Posted By Mark Mandel | 3/5/09 12:30 PM
  • Andrew Scott's Gravatar Thanks for picking that up Mark, that was a big oversight and has been changed.
    # Posted By Andrew Scott | 3/5/09 3:41 PM

Comments (Moderated) | Add Comment