SQL Zone is brought to you in partnership with:

I’m a software engineer. I got my first computer when I was 7 and have loved programming ever since. I’ve been developing corporate web applications since 1999, mostly using Java based technologies, but I like to try and explore a little bit of everything interesting going around the web/mobile technology scene. I like hard rock, RPG video games (all flavors), science fiction and fantasy books, and almost all movies (unless they star Sandra Bullock or Hugh Grant). Ricardo is a DZone MVB and is not an employee of DZone and has posted 16 posts at DZone. You can read more from them at their website. View Full User Profile

Sometimes It's Easier to Just Write Your SQL

  • submit to reddit

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.

Published at DZone with permission of Ricardo Zuasti, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)



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

  • only requires minimal configuration,
  • performs basic mapping of tables to objects,
  • abstracts a bit (id generation, paging) from the database (in these projects the database will more likely change than in large projects),
  • but otherwise let's me just write SQL statements.

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.


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.