We develop a RAD form designer tool. Currently the forms it creates require at least the table names for generating SQL SELECT statements, but many forms require complex SQL to be specified.

The current tool results in a 2-tier architecture, which we would like to get away from. We've decided to create separate domain and persistence layers, using NHibernate for the persistence.

My question is, since this is a development tool (both for use in-house to develop our product's standard UI, and for customers to use to modify the base product, which is a major selling point and happens more often than not), many of the queries to populate forms are complicated. Yet, some members of our design team are very reluctant to allow HQL to be used in a form's request for data. As an alternative to "proprietary" HQL, a "generic" XML format for requests (including criteria) would be invented. I see no value in this, since we have to maintain it, it essentially must duplicate HQL but in some "generic" way, etc. It ends up being more proprietary than NHibernate, not less, and none of our developers or customers could leverage existing NHibernate knowledge.

Does anyone else need rapid application development where most forms have arbitrary, complex queries? How can this be dealt with, while keeping the UI "pure" and free of any notion of NHibernate? One possibility appears to be using something like the "named queries" of (Java's) Hibernate 3.0 -- each form would simply send a query ID/alias to a data access layer, and the data access layer would map that to a specific HQL query. That doesn't seem to really improve the situation very much, since something that's usually unique to one form now lives in the data access layer. Seems like just one more dislocated piece to worry about when creating or modifying a UI. Instead of all your UIs being tied to NHibernate, they're tied to your proprietary DAL.

Any recommendations?

What you may do is create a layer to formulate your requests: for each form, you create a class which can take all input from the form and build a HQL/ICriteria object for NHibernate (or directly the result).
You can see this as a step forward of the DAO pattern.

So your UI will depend of these Query classes (you may even use them in Windows and Web forms).
And you can change the implementation of these classes whenever you have to change your persistence framework (just make sure that the input and output stay compatibles).

IMO, creating a new format is definitively not a good idea.

Hmmm... The scenario you mention sounds like Query By Example, which would be straightforward. My concern is how can we possibly come up with our own "generic" query and criteria classes for a typical form to do issue a query equivalent to



I pulled this example out of the Hibernate docs; our grids will have at least 2 joins each, more typically around 5, and will usually need to select "primary" or other "chosen" children out of the driving entity's many collection properties.

I've looked at the criteria API, and it appears very awkward for anything except Query By Example with forms that simply select a list of a single entity type. There's no way our typical tech-unsavvy customer (or even our own developers) will accept specifying complex queries for their forms with some "generic" equivalent of the NHibernate criteria API :(

I have a hard time seeing anything but a "named queries" approach as scalable/universal. We have enough problems already with past attempts to create "simplified" interfaces that are supposedly sufficient for 90% of cases -- we always end up with a mess of different approaches and workarounds because the simplified interface simply fails to do the job way too often. :?

Yes, it sounds like Query By Example but far more complex and maintained by you (not NHibernate).

I applied this "pattern" to provide a very complex search engine in a software I developped. And the generated HQL can be as complex as the one you posted...

You just need to define all the criteria and create a class which can hold them and convert them to HQL/ICriteria/IQuery. It is not easy, but it is feasable.

Any chance this search engine layer you've developed to generate HQL will be made open source :D Assuming no, what about paying you for a licensed use of the DLL (source access not necessary) :) We REALLY don't want to invent this ourselves (or at least I don't), every "generic" layer invented in-house inevitably turns out lame compared to what eventually comes along written by someone else :o

Actually, nix that request -- we were assuming that we would go to the bother of doing that ourselves, but came to the conclusion that it would be too unwieldly to make people programatically write complex queries this way. It doesn't matter if someone wrote it for us, it just won't be accepted for RAD form building.

Only an object query language will be acceptable, and that looks like we support HQL queries but simply define them outside the UI and invoke them by name. If the customer wants to switch from NHibernate to some other O/R mapper, then they'll just have to convert their queries. A one time hit, no UI changes, so big deal. The big worry about how painful that would be overblown in comparison to the the day-in-day-out pain of programatically constructing every query with some proprietary "generic" API ...

We took the "generic" approach.... As part of a new .net framework for our company, we developed a generic criteria expression class, that can accommodate any type of conditional expression, for SQL or otherwise. The gui populates a filter object and passes to an business layer service, which translates the filter into criteria expression objects, and passes to the framework persistence composite (which is composed of any number of configurable persistence components, one being nhibernate), and the nhibernate component translates criteria expression into HQL, and passes to underlying nhibernate methods. The trick was creating the criteria expression generic enough to completely hide the nhibernate implementation, but still provide all the functionality, and still be user friendly.