Discussions

General J2EE: ORM framework for complex dynamic queries.

  1. ORM framework for complex dynamic queries. (5 messages)

    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.

    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?

    Thanks,
    Gerard

    Threaded Messages (5)

  2. 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.
  3. 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,

       Shaun

    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
  4. TopLink Great product[ Go to top ]

    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.
  5. TopLink Great product[ Go to top ]

    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 :-).
  6. ORM framework[ Go to top ]

    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.