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.