I am searching an ORM framework that allows me to create complex queries. At the beginning of the project the functional requirements (about queries) weren't complex, so I decided to create a custom 'framework' for persistence. I use plain JDBC and create dynamic SQLs.
- Posted by: Gerard Vidal
- Posted on: June 15 2005 05:42 EDT
Currently the functional requirements have increased in complexity and I have to do very complex queries, i.e. any combination of 'distincts', 'order by', 'WHERE ... AND ... OR ... < ...>', 'UPPERCASE(field) = UPPERCASE(value)', wildcards, etc. This is done by creating the SQL dynamically (using StringBuffers) and as consequence the complexity of the code and the possibility of bugs has increased, and of course I feel that I am 'reinventing the wheel'.
I have some knowledge in Hibernate but the database design is so poor that it doesn't seem to fit. I would like to know if there are any other ORM solutions that allow to create complex dynamic queries. I have heard about OJB and Ibatis, any suggestions?
- ORM framework for complex dynamic queries. by Aaron Craven on June 15 2005 08:29 EDT
- TopLink support for Complex Dynamic Queries by Shaun Smith on June 15 2005 13:41 EDT
iBATIS isn't technically an ORM solution -- it's more of a result mapping solution. Knowing that, however, it may very well be what you're looking for. Some of the newest stuff includes the ability to generates some fairly complex dynamic queries, including the ability to create WHERE clauses with usually-complex constructs like FIELD IN ([value-list]). I would definitely look at it and see if it meets your needs. Start with the tutorial and developer guide on the iBATIS site.
If you are looking for a powerful ORM framework with advanced querying you should consider TopLink.
In TopLink there are 5 ways to specify a query: Expressions, Custom SQL, Stored Procedure Calls, EJB QL, and Query by Example.
I think what you are looking for is support for very complicated queries expressed in terms of the object model which is what is supported by the Expression framework. You can use TopLink Expressions with either object or data queries (ReportQueries in TopLink) and all the features you mentioned (e.g., distinct, order by, etc.) and many more are supported.
Take a look,
Query manual page: http://download-west.oracle.com/docs/cd/B14099_06/web.1012/b15901/queries002.htm#i1128959
TopLink OTN Page: http://www.oracle.com/technology/products/ias/toplink/index.html
It is the best OR mapping tool i have worked on.
You can create complex to complex queries although I faced scenarios which toplink failed to fire.
Be careful using this product. Do you have any stored procedures cruising in your databse then turn off the caching. In case you are in a clustered environment then synchronize the cache. You need to go through a learning curve to implement this product.
You need to go through a learning curve to implement this product.
I picked up the basics of iBATIS in less than a week. However, I have never looked at TopLink (although I have seen it mentioned several times), so I can't really offer any comparison from my point of view -- I hope to check it out soon, though :-).
Thanks for all your answers, I will take a look to iBATIs and TopLink, the Expressions stuff seems to be what I need.
However, I'd rather an open-source framework (if I am not wrong TopLink is not). Hibernate offers the Criteria API which might be similar to the TopLink's Expressions. Maybe I'll give another chance to Hibernate despite it is a 'too heavy' framework for my purposes.