Raible Designs

Dynamic Queries with Hibernate

I have a client that wants the ability to search on all columns in all tables in their database. So far, I've been able to get all the columns, and their friendly labels by getting all the *Form.* keys in my ApplicationResources.properties file. I still need to sort them alphabetically, but that's another issue. Now I have the following UI pretty much done:

I'm happy with the UI, but I'm struggling with getting Hibernate to return my desired results. I'm able to dynamically select the table/column to search on because of values in my "tables/columns" drop-down. However, many of these tables have Id fields - for status fields, type fields, etc. (basically drop-downs). When searching, this is a pain because users are likely to search on "Active" rather than "1". Because of this, my first issue is how do I make the status field's friendly name a part of my POJO (with Hibernate)?

My 2nd issue is regarding Hibernate's Expression API. I want to be able to pass in the criteria (=, contains, <, >), propertyName and value and get my results. However, it seems that the second half of the comparison must be the same datatype as the field. This means if you're searching on "contract amount", and its a Double, I need to do a Double.valueOf(searchTerm). Basically, I'm looking for an easy way to do this:

  Criteria filter = ses.createCriteria(clazz); 
  
  // determine type of expression - I'd love to figure 
  // out a cleaner way to do this but there's only 
  // 5 possible types, so I don't mind typing the 5
  // if statements
  if (expression.equals("=")) { 
    // How do I convert these to the fields' type at run-time?
    // Sure I could do a bunch of if statements again, but it seems ugly.
    filter.add(Expression.eq(field, Double.valueOf(value))); 
  }
 
  List results = filter.list();

Any advice is appreciated - especially considering I estimated this task to take 4-5 hours and I'm going on 8.

Posted in Java at Oct 05 2003, 10:39:29 AM MDT 11 Comments

Comments:

Can you just leave the field as a string instead of trying to convert it to its target type? This way, your database will just do whatever conversion is appropriate, won't it? Granted, I'm not experienced with Hibernate, but this works with Torque on Oracle.

Posted by Ryan Campbell on October 05, 2003 at 04:41 PM MDT #

SessionFactory.getClassMetadata(Class) then on the net.sf.hibernate.metadata.Metadata, use getPropertyNames() and getPropertyTypes().

Posted by Damien on October 05, 2003 at 04:55 PM MDT #

Not sufficient... With the CVS version (or just get net.sf.hibernate.type.*), you can call fromXML on your Type. The documentation is a bit misleading, citing XML, but if you look at the source, you'll see it does just what you want (except for the String perhaps, it adds single quotes). Not that useful in the end... I will think more before I post the next time :-)

Posted by Damien on October 05, 2003 at 06:53 PM MDT #

This is common, and coverd in my "book". Your main select statment must select codes (not display values), get it to work like that first. I am assumig you are using struts optionsCollection tag (that takes a collection as argument). (I wrote a method that can give options name value pairs generic given an argument) Still "getter/setter" gets the actual value, not displayed value. Then you just "decode" using a *client side join * (options/select) hth, If you want to talk voice, I am home tmrw, e-mail me.

Posted by Vic Cekvenich on October 05, 2003 at 07:47 PM MDT #

My main issue is populating a POJOs statusDesc property with Hibernate. I'm sure it's fairly simple - I tried a one-to-one mapping between project and project_status - but Hibernate seems to want to use the projectId as the foreign key, rather than the statusId. I might be help back by my desire to use XDoclet - I just want to know if this is possible with Hibernate - so far, the answer seems to be no (but it's GOT to be possible).

Posted by Matt Raible on October 05, 2003 at 08:26 PM MDT #

But.. I do not think you should populate desc! Just populate codes, and get codes to work from Hibreante. Now stop for a second.
Now, using a diferent "connection" populate the options collection. It "joins" on the browser, what the users sees. .V

Posted by Unknown on October 06, 2003 at 09:07 AM MDT #

This all works fine Vic - and I'm doing what you suggest. The problem is that when I want to Hibernate's Expression API to allow the user to search on the "statusDesc" field of a "Project" object. Here's an example of how easy this would be if I could populate the statuDesc property.

Criteria filter = ses.createCriteria(Project.class); 

if (expression.equals("=")) { 
    filter.add(Expression.eq("statusDesc", searchValue)); 
} 

List results = filter.list();

So my question is - <strong>How do I search on the text value of a code property <em>with Hibernate.</em></strong>

Posted by Matt Raible on October 06, 2003 at 09:46 AM MDT #

I can't help on the second part of the question, but on the first I'm a big fan of using a Map.

Map map = new HashMap();  // String -> Method

Method m = map.get(expression);
if (m == null) {
  // something nasty is going on, bail
}

m->invoke(this, new Object[] { filter, field, value });

where you have previously stuffed the map with goodies like

void exprEq(Filter filter, String field, String value) {
   filter.add(Expression.eq(field, (magic) value);
}

It sounds stupid, but I've found it easier to solve problems with Maps pointing to very small methods that do one thing well than a long if/else if ladder that tries to handle everything.

Posted by Bear Giles on October 06, 2003 at 11:33 AM MDT #

Matt, here are my two cents worth. First off for the question of how to search text value (the description) of a code property I have used a "Lookup" class/table. The Lookup class has a type, code, and description. I then use a many-to-one relationship from my "main" class that references the lookup. For example in an issue tracking application the Issue class has a status property of type Lookup (and a valid status might be "O" with a description of "Open"). The current beta of Hibernate is supposed to allow you to traverse these relationships to let you search by the description in this scenario. As to the class conversion issue I am using the ConvertUtils.convert method from the Commons BeanUtils library to perform the actual conversion. The way I'm currently finding out what class to convert to is kind of a hack. I'm using PropertyUtils.getPropertyType again from the BeanUtils package, but this requires me to instantiate an instance of the persistent class that I don't really need. I am going to go check into the SessionFactory.getClassMetadata mentioned above to see if that might be helpful. By the way, since you are experimenting with the Criteria API, have you tried using the orderBy functionality? I've recently run into issues using orderBy in conjunction with outer-join fetching. The generated SQL is invalid, the outer joins are added to the statement after the order by clause. I haven't had a chance to ask in the Hibernate forum yet, but just thought I'd ask...

Posted by Scott Mitchell on October 06, 2003 at 07:02 PM MDT #

why don't you build your own sql and do a simple join between the id and what its description is as you bring it back. it is rather simple and Hibernate 2.1 lets you do your own sql statements. just curious.

Posted by just a suggestion on October 08, 2003 at 01:22 AM MDT #

Posted by 200.75.13.148 on March 29, 2006 at 02:29 PM MST #

10+ YEARS

TWITTER

RECENT ENTRIES

Tag Cloud

Dynamic Queries with Hibernate