Raw SQL vs. ORM: Who is the winner?

Preface

Until today, using an ORM framework instead of raw SQL was still a big question when it came to storing application data in a database. In general, we actually have 3 options to choose from, but I will let the Query Builder out of the game. This article will not pick side or list the pros and cons of these methods, but instead I will give you all a little bit of information on the origin of ORM by telling?2 stories.

First thing first: what's ORM (object–relational mapping)?

Before having ORM?

An application connects directly to a database via a database driver (JDBC, ODBC). To get a quick idea of how it works, look at the image and code below.

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database-name","username","password");

Statement statement = conn.createStatement();

ResultSet resultSet = statement.executeQuery("SELECT * FROM employees WHERE employeeID=10");

resultSet.next();

String name = resultSet.getString("name");

statement.close();

conn.close();        

ORM - Hibernate

With ORM, your application interacts with the database driver via a wrapper/API

SessionFactory sessionFactory = HibernateUtils.getSessionFactory();

Session session = sessionFactory.getCurrentSession();

session.getTransaction().begin();

Employee emp = getEmployee(session,10);

String name = emp.getEmployeeName();

session.getTransaction().commit();        

The origin

Story 1: Java Performance Tuning conducted an interview with Gavin King (the creator of Hibernate) https://www.javaperformancetuning.com/news/interview041.shtml

Host: What brought you to build Hibernate.

Gavin: Hibernate was designed as a fix for the well known problems of Entity Beans. I was developing J2EE applications with an Australian company called Cirrus Technologies and was very frustrated by my lack of productivity, and by my inability to apply object modeling techniques to the business problem. I ended up spending more time thinking about persistence than I spent thinking about the user's problem. That's always wrong.

Host: Why, where and when should someone use Hibernate?

Gavin: You should use Hibernate if you have a nontrivial application (definition of nontrivial varies, but I usually think of Hibernate being less applicable to applications with only ten tables or so) that use an object-oriented domain model. Not every application needs a domain model, so not every application needs ORM. But if your application does a lot of business logic - rather than just displaying tabular data on a webpage - then a domain model is usually a good thing. Hibernate really starts to shine in applications with very complex data models, with hundreds of tables and complex interrelationships. For this kind of application, Hibernate will take away a huge amount of coding effort (perhaps up to 25%, for some applications) and will result in an application that performs better than the alternative handcrafted JDBC. This is possible because some kinds of performance optimizations are very difficult to handcode: caching, outer-join fetching, transactional write-behind, etc.

Host: Another aspect of performance is how the product it to be used? Is Hibernate being used as you expected it to be? Are there any usage models that have pleasantly surprised? Can you talk about any usage models that you would discourage?

Gavin: There are two usage models that are problematic. The first is where people try to apply ORM where it is not really suitable. The main example of where ORM - and indeed Java - is not suitable is the case of processing data in bulk. It is simply never going to be efficient to fetch millions of rows from the database, into your JVM, and then update them one at a time. Don't use Java for this. Use a stored procedure. The second problem is sort of cultural. Some developers come to using a tool like Hibernate because they are uncomfortable with SQL and with relational databases. We would say that this is exactly the wrong reason to use Hibernate. You should be very comfortable with SQL and JDBC before you start using Hibernate - Hibernate builds on JDBC, it does not replace it. So, when developing business logic that calls Hibernate, you should be monitoring exactly what database requests ends up being generated. It is otherwise very easy to build an application that performs badly because you simply have no idea what is happening underneath. That is the cost of extra abstraction. On the other hand, it is usually very easy to come along later and fix the kinds of performance bugs that result from this kind of approach. That is the advantage of the extra abstraction, and it more than mitigates the disadvantage. But you will save yourself effort if you pay attention to the database at all stages of development.

Story 2: An honest opinion from William Shields (owner of the cforcoding blog) when he had a chance to work with both approaches?https://web.archive.org/web/20090528082618/https://www.cforcoding.com/2009/05/orm-or-sql.html

Hibernate became the poster-child for post-EJB OO fanatics. I was late to this particular party. I'd gone back to doing plain SQL and was happy. Less than two years ago I was forced to learn?JPA?and I gave it a fair shake of the stick, I really did.

The?relational-object divide?has been a divisive issue for many years. Jeff Atwood went as far as saying?Object-Relational Mapping is the Vietnam of Computer Science.

I am a steadfast believer that?abstractions are leaky. And an object model on top of a relational model?is?an abstraction. To jam this into the Java/J2EE world, apparently mechanisms like?load-time weaving?were required.

Now I believe that those behind such changes were (and are) well-intentioned but, as the quote goes,?the road to hell is paved with good intentions. I see the problem like this: imagine a Cartesian graph with one axis representing complexity and the other representing the completeness of the abstraction. The more "complete" it is, the less leaky it is. I picture such a graph would produce an?hyperbola. JDBC is simple because it's not much of an abstraction. Hibernate and other JPA providers are?incredibly?complex because they are attempting to be complete. An important observation from this analogy is that to achieve a perfect abstraction the solution would be infinitely complex (ie impossible).

I also believe that every developer should be educated in relational algebra, comfortable with databases and proficient in SQL. Just like any Java Web developer should understand the servlets API before they can truly truly appreciate and properly leverage a higher-order MVC framework like Spring MVC or Struts 2.?Ultimately, you still need to know something about how HTTP works. The same applies to SQL.

This is why I believe the effort to create the "perfect" object model for relational databases is futile and ten plus years later we're still not there. And you know what? I don't think we'll be there in another ten years either. If anything, the issue will become a non-issue as the traditional relational database will be replaced by?"slacker" databases, persistent caches or whatever comes after that.

Conclusion

Although these two opinions at high levels are quite opposite, you can notice that these two geniuses have the same view on the below points:

1. The original goal of the first ORM was to solve Entity Bean problems.
2. Raw SQL or using an ORM is just a choice that fits your purpose. ORM is not a replacement for raw SQL.
3. Lastly, and I think most people underestimate this point, If you are using an ORM because your team is uncomfortable with SQL, this is definitely a wrong decision.

The fun fact is that sometimes it actually does not matter at all :)

Further reading

Hibernate in Action by Gavin King and Christian Bauer

Thanks for reading!