Sometimes It's Easier to Just Write Your SQL
Object-relational mapping (in the JPA compliant sense) is so ubiquitous nowadays in the Java development scene, that we rarely question ourselves if we really need it, and if not using it may be a better option.
In my experience, some systems benefit from using JPA technology
(most systems actually), but some do not, in fact I think some systems
become unnecessarily complicated and bigger because of it.
Imagine a mission critical system, in which you handle very big tables
(be it in number of rows or sheer data size) and/or complicated queries.
Usually in this scenario, the optimization and proper usage of the
database is a bigger issue than how long it takes a developer to code a
specific query or update sentence.
The scene I see repeating itself in a lot of projects is a code base that starts with very beautiful and elegant ORM based queries (be it API based, HQL or similar) and maybe a few native SQL queries for very specific features, but once the system is deployed to a production environment and faces reality you start to migrate to a code base where most (or all) of the database related interactions are expressed as native SQL queries so you can squeeze every ounce of performance your DBMS is capable of, and use all those vendor-specific features that make your use case 10 times faster than solving them on the Java side.
Wouldn’t it be better then, in cases similar to this one, to use a framework or design pattern lighter than a JPA compliant framework, and include the design and coding of SQL sentences from the beginning of the project?
If you think so, I encourage you to re-visit the DAO pattern and consider coding your data access layer yourself, or better yet, use a lightweight framework like MyBatis that provides a lot of useful features but is still based on writing your own SQL sentences.
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)
Comments
Jilles Van Gurp replied on Mon, 2012/04/23 - 1:55am
Object relational is a bit of a fallacy, unless you know what you are doing. If you don't you will likely be tricked into believing you can have your object model mapped to the database, which almost certainly results in overly complicated database schemas that are hard to query and scale. This illusion is actually fine since most applications don't need the scale to begin with. The problem is that little applications sometimes become big applications and that's when things can start falling apart. All those indexes hibernate created for you, all those extra mapping tables you really didn't need, and all those joins hibernate is doing for you across them will start hurting at some point.
If you do know what you are doing, writing the sql natively isn't that hard or that big of a deal: either you know your shit and it is easy or you don't in which case you can't be trusted with hibernate either. Doing things manually can still a bit tedious but if you are aiming for simple, stable schema designs, it is actually worth your time sitting down and just getting that over with. There are several frameworks that will save you from dealing with raw jdbc (e.g. Spring's JDBCTemplate is pretty nice) while still giving you the option to when you need to.
Lance Semmens replied on Mon, 2012/04/23 - 3:59am
I couldn't agree more... I have been totally turned off of Hibernate and ORM frameworks in general.
I was working on a Hibernate project with a complex schema with complex relationships. When I turned on Hibernate's SQL logging, I saw many unnecessary joins and selects being performed to retrieve data that was never used. I also witnessed a few instances of the N+1 selects problem. I have often said that "Hibernate makes intelligent people do stupid things".
In most systems, the Database is the slowest component and I would never want to wrap it with an abstraction layer that often performs poorly, slowing it down even further.
+1 for MyBatis, it embraces SQL rather than abstracting me from it. I prefer the XML configuration. We were able to hand the MyBatis config files to our DBA for signoff without exposing him to the Java code.
Der Meister replied on Mon, 2012/04/23 - 8:12am
In lots of my projects the main most of the code is not performance critical, e.g. data administration - search for data, view it, change it, save it. In this case it is nice if the abstraction layer (lazily) loads all related data.
But there will always be parts where you can't avoid writing custom SQL, e.g. for reports or real time data display. But you can still do this with Hibernate or other JPA compliant frameworks, so I wouldn't abandon these frameworks for large projects.
On the other hand, for small projects (e.g. web application with only few users, small data model or desktop software) I'd rather use a small framework like jEasyORM, which
P.S. I personally don't like XML configuration files, I'd rather define the mapping in the entity classes themselves (and only if there is a non-trivial mapping!). Signoff with DBAs was no problem so far.
Greg Brown replied on Mon, 2012/04/23 - 11:04am
+1. Never been a big fan of ORMs myself. They always seemed like overkill to me.