Tuesday, August 23, 2005

I Need Another DB Framework!

I am currently facing a problem that neither Hibernate nor iBatis solves nicely. I also looked at other ORM or just DB framework, without success.

What I would need is a framework that generates PreparedStatements with a query by Criteria like API. I have many queries that are similar but varying according to different input parameters. iBatis can handle this, but for complex queries and scenarios, the XML becomes completely unreadable, and you therefore loose any advantage that iBatis was bringing with the externalization of SQL statements in XML. The other issue I have with using iBatis is that for another part of my project, the automatic generation of SQL statements a-la Hibernate is useful.

Hibernate has a very nice Query by Criteria API, but it lacks just a tiny bit of flexibility in customizing queries. For example, I could not find a way to specify a "USE INDEX(index_name)" in the generated SQL, after the SELECT FROM xxx and before the rest of the query. I did not find either a way to specify the use of a "STRAIGHT_JOIN" instead of an INNER JOIN. These are all MySQL specific issues, but those little things are extremely useful at improving some of my queries performances. Writing N sql queries hard coded is not a good option, since this N can be quite big, which is why I am using Query by Criteria in the first place.

So is there a need for yet another DB framework?

6 comments :

  1. Uh no, there is a need to request functionality be added to an existing framework.

    Starting from scratch is not the answer.

    Look at the good aweful number of webframeworks that accomplish the same thing, each adding just a tad more than the next. Nothing is accomplished by starting from scratch.

    ReplyDelete
  2. Use the Spring JDBC template. It will do the job.

    ReplyDelete
  3. The Spring JDBC template just allows to create a single SQL query, and you still write the SQL explicitely. You can't make it generate SQL, which is what I need because there are 100s of them just slightly different (with more or less parameters for example).

    ReplyDelete
  4. I patched Hibernate 3.1 for the USE INDEX feat. don't know if it will be approved.

    ReplyDelete
  5. I know it's been a while since this post but have you been successful in using STRAIGHT_JOIN with HQL? I really wouldn't like to use plain SQL. Best thing would be manipulating the select statement of the query created by Hibernate criteria.

    ReplyDelete
  6. Forget that ancient garbage. Look at Datanucleus! Supports both JPA *and* JDO, unlike Hibernate which barely supports the less-complete JPA standard only.

    ReplyDelete