The Dreaded Search Function
I’ve worked at several jobs where the users have asked for a general-purpose search function in their application. It’s the sort of thing where people want the ability to search on, e.g., all customers with a last name that starts with SM, or all of the customers with a balance greater than 1000.00, etc.

If you can resist the request to build such a thing, then by all means, don’t implement it. End-users have a knack for creating queries which will bring your database to its knees, no matter how clever you think you are with indexing or UI design.

If you must do it, you’ll want to keep a close eye on how its used from the outset for when the inevitable nightmare query is run. One way to do this is by using Hibernate’s comments feature.

Use Comments to Hunt them Down
My colleague Matt Brock implemented something like this where we work. Suppose you have a general method for creating an HQL query in a DAO class, the method could look like this:

protected Query createQuery(String hql) {
return getSession().createQuery(hql).setComment(getUsername());
}

Next, make sure Hibernate’s SQL logging and comment logging is enabled in your hibernate.hbm.xml (warning – this log can get pretty huge):

<hibernate-configuration>
  <session-factory>
      <property name="show_sql">true</property>
      <property name="use_sql_comments">true</property>
.
.
.

Your log will now contain the users who are executing each query, like this:

Hibernate: /* johndoe */ select customer0_.customer_id as col_0_0_, customer0_.cust_company_name from CUSTOMER customer0_ where (customer0_.customer_id=12136 or customer0_.customer_id=16884 or customer0_.customer_id=11150 or customer0_.customer_id=155 or customer0_.customer_id
=27265 or customer0_.customer_id=697 or customer0_.customer_id=4133 or customer0_.customer_id=248 or customer0_.customer_id=2550 or customer0_.customer_id=8449)

One caveat: this may not work with stored procedures – SQL Server, in particular, will get nasty if you try to execute a stored procedure with Hibernate comments attached to it.

Happy Hibernating!

This entry was posted on Sunday, June 1st, 2008 at 6:32 am and is filed under portland, silly. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.