Home » ORM

Category Archives: ORM

Using an ORM or plain SQL?

Posted on by | Leave a comment
Speaking as someone who spent quite a bit of time working with JPA (Java Persistence API, basically the standardized ORM API for Java/J2EE/EJB), which includes Hibernate, EclipseLink, Toplink, OpenJPA and others, I’ll share some of my observations.
  1. ORMs are not fast. They can be adequate and most of the time adequate is OK but in a high-volume low-latency environment they’re a no-no;
  2. In general purpose programming languages like Java and C# you need an awful lot of magic to make them work (eg load-time weaving in Java, instrumentation, etc);
  3. When using an ORM, rather than getting further from SQL (which seems to be the intent), you’ll be amazed how much time you spend tweaking XML and/or annotations/attributes to get your ORM to generate performant SQL;
  4. For complex queries, there really is no substitute. Like in JPA there are some queries that simply aren’t possible that are in raw SQL and when you have to use raw SQL in JPA it’s not pretty (C#/.Net at least has dynamic types–var–which is a lot nicer than an Object array);
  5. There are an awful lot of “gotchas” when using ORMs. This includes unintended or unexpected behavior, the fact that you have to build in the capability to do SQL updates to your database (by using refresh() in JPA or similar methods because JPA by default caches everything so it won’t catch a direct database update–running direct SQL updates is a common production support activity);
  6. The object-relational mismatch is always going to cause problems. With any such problem there is a tradeoff between complexity and completeness of the abstraction. At times I felt JPA went too far and hit a real law of diminishing returns where the complexity hit wasn’t justified by the abstraction.
There’s another problem which takes a bit more explanation.
The traditional model for a Web application is to have a persistence layer and a presentation layer (possibly with a services or other layers in between but these are the important two for this discussion). ORMs force a rigid view from your persistence layer up to the presentation layer (ie your entities).
One of the criticisms of more raw SQL methods is that you end up with all these VOs (value objects) or DTOs (data transfer objects) that are used by simply one query. This is touted as an advantage of ORMs because you get rid of that.
Thing is those problems don’t go away with ORMs, they simply move up to the presentation layer. Instead of creating VOs/DTOs for queries, you create custom presentation objects, typically one for every view. How is this better? IMHO it isn’t.
I’ve written about this in ORM or SQL: Are we there yet?.
My persistence technology of choice (in Java) these days is ibatis. It’s a pretty thin wrapper around SQL that does 90%+ of what JPA can do (it can even do lazy-loading of relationships although its not well-documented) but with far less overhead (in terms of complexity and actual code).
This came up last year in a GWT application I was writing. Lots of translation from EclipseLink to presentation objects in the service implementation. If we were using ibatis it would’ve been far simpler to create the appropriate objects with ibatis and then pass them all the way up and down the stack. Some purists might argue this is Bad™. Maybe so (in theory) but I tell you what: it would’ve led to simpler code, a simpler stack and more productivity.

Hibernate, iBatis, Java EE or other Java ORM tool

Posted on by | Leave a comment
Let me take a crack at this. First of, I’ve written some on this subject in Using an ORM or plain SQL?. Specifically to address your points:
Learning Curve/Ease of Use
Ibatis is about SQL. If you know SQL the learning curve for ibatis is trivial. Ibatis does some things on top of SQL such as:
  • group by;
  • discriminated types; and
  • dynamic SQL.
that you’ll still need to learn but the biggest hurdle is SQL.
JPA (which includes Hibernate) on the other hand tries to distance itself from SQL and present things in an object rather than a relational way. As Joel points out however, abstractions are leaky and JPA is no exception. To do JPA you’ll still need to know about relational models, SQL, performance tuning of queries and so forth.
Whereas Ibatis will simply having you apply the SQL you know or are learning, JPA will require you to know something else: how to configure it (either XML or annotations). By this I mean figuring out that foreign key relationships are a relationship (one-to-one, one-to-many or many-to-many) of some kind, the type mapping, etc.
If you know SQL I would say the barrier to learning JPA is actually higher. If you don’t, it’s more of a mixed result with JPA allowing you to effectively defer learning SQL for a time (but it doesn’t put it off indefinitely).
With JPA once you setup your entities and their relationships then other developers can simply use them and don’t need to learn everything about configuring JPA. This could be an advantage but a developer will still need to know about entity managers, transaction management, managed vs unmanaged objects and so on.
It’s worth noting that JPA also has its own query language (JPA-SQL), which you will need to learn whether or not you know SQL. You will find situations where JPA-SQL just can’t do things that SQL can.
Productivity
This is a hard one to judge. Personally I think I’m more productive in ibatis but I’m also really comfortable with SQL. Some will argue they’re way more productive with Hibernate but this is possibly due–at least in part–to unfamiliarity with SQL.
Also the productivity with JPA is deceptive because you will occasionally come across a problem with your data model or queries that takes you a half a day to a day to solve as you turn up logging and watch what SQL your JPA provider is producing and then working out the combination of settings and calls to get it to produce something that’s both correct and performant.
You just don’t have this kind of problem with Ibatis because you’ve written the SQL yourself. You test it by running the SQL inside PL/SQL Developer, SQL Server Management Studio, Navicat for MySQL or whatever. After the query is right, all you’re doing is mapping inputs and outputs.
Also I found JPA-QL to be more awkward than pure SQL. You need separate tools to just run a JPA-QL query to see the results and it’s something more you have to learn. I actually found this whole part of JPA rather awkward and unwieldy although some people love it.
Maintainability/Stability
The danger with Ibatis here is proliferation meaning your dev team may just keep adding value objects and queries as they need them rather than looking for reuse whereas JPA has one entitty per table and once you have that entity, that’s it. Named queries tend to go on that entity so are hard to miss. Ad-hoc queries can still be repeated but I think it’s less of a potential problem.
That comes at the cost of rigidity however. Often in an application you will need bits and pieces of data from different tables. With SQL it’s easy because you can write a single query (or a small number of queries) to get all that data in one hit and put it in a custom value object just for that purpose.
With JPA you are moving up that logic into your business layer. Entities are basically all or nothing. Now that’s not strictly true. Various JPA providers will allow you to partially load entities and so forth but even there you’re talking about the same discrete entitites. If you need data from 4 tables you either need 4 entities or you need to combine the data you want into some kind of custom value object in the business or presentation layer.
One other thing I like about ibatis is that all your SQL is external (in XML files). Some will cite this is as a disadvantage but not me. You can then find uses of a table and/or column relatively easy by searching your XML files. With SQL embedded in code (or where there is no SQL at all) it can be a lot harder to find. You can also cut and paste SQL into a database tool and run it. I can’t overstate enough how many times this has been useful to me over the years.
Performance/Scalability
Here I think ibatis wins hands down. It’s straight SQL and low cost. By its nature JPA simply won’t be able to manage the same level of latency or throughput. Now what JPA has going for it is that latency and throughput are only rarely problems. High performance systems however do exist and will tend to disfavour more heavyweight solutions like JPA.
Plus with ibatis you can write a query that returns exactly the data you want with the exact columns that you need. Fundamentally there’s no way JPA can beat (or even match) that when it’s returning discrete entities.
Ease of Troubleshooting
I think this one is a win for Ibatis too. Like I mentioned above, with JPA you will sometimes spend half a day getting a query or entity produce the SQL you want or diagnosing a problem where a transaction fails because the entity manager tried to persist an unmanaged object (which could be part of a batch job where you’ve committed a lot of work so it might be nontrivial to find).
Both of them will fail if you try to use a table or column that doesn’t exist, which is good.
Other criteria
Now you didn’t mention portability as one of your requirements (meaning moving between database vendors). It’s worth noting that here JPA has the advantage. The annotations are less portable than, say, Hibernate XML (eg standard JPA annotations don’t have an equivalent for Hibernate’s “native” ID type) but both of them are more portable than ibatis / SQL.
Also I’ve seen JPA / Hibernate used as a form of portable DDL, meaning you run a small Java program that creates the database schema from JPA configuration. With ibatis you’ll need a script for each supported database.
The downside of portability is that JPA is, in some ways, lowest common denominator, meaning the supported behaviour is largely the common supported behaviour across a wide range of database vendors. If you want to use Oracle Analytics in ibatis, no problem. In JPA? Well, that’s a problem.