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):
<pre class="xml" style="font-family:monospace;"> 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!
