How can I design a Java web application without an ORM and without embedded SQL

Prior to reading this I often wondered if any others really bothered to understand the impact an ORM tool has on a project. @Evgeny, @Marcelo Cantos, and @Jimmy B put this to rest.

In short, they are dead on with most of the issues that surround ORM tools. There is only a couple that they either haven't covered or haven't covered enough.

First, usage of an ORM does not mean less code. It might mean less SQL, but it certainly doesn't mean less code. Along these lines, tool generated code can be wrong. Worse, compensating for bad generated code is an exercise in frustration.

Second, ORM tools do NOT mean you don't have to understand SQL. To put this another way, you must know SQL to be an effective programmer (there are exceptions like the embedded guys). The queries (both type and number of) emitted by these tools just aren't good enough from a performance or resource perspective. If you already have to know SQL, why shackle yourself?

Third, ORM tools do NOT save time. You will spend as much (if not more) time beating your ORM tool of choice into submission to take care of any application of decent size. To add insult to injury, when you are done you will see that the quality of the queries are generally worse than if you had done it yourself. Point is ORM = more time spent on the project, worse outcome.

Fourth, DBMS independence is generally a waste of time. Most applications will use exactly one DBMS over it's life; whether it's Oracle, SQL server, MySql, whatever. The application will be much better off it is able to take advantage of the features the DBMS provides. Being DBMS agnostic means that you'll have to limit yourself to sub par queries.

Fifth, not everything has to be an object. This is an important thing to note. We are often asked to show a particular set of data on a page. More often than not this means joining data from two or more tables. Should your app have to create and instantiate all of those objects just to display some data? Or are you better off just executing a query and emitting the data straight to the screen/browser in the format of your choice?

ORM's add a LOT of overhead to even the simplest things. Most ORM's will generate multiple sql queries to perform even a simple UPDATE on a table.

Sixth, and the most important issue in my mind: ORM's decrease your security. Sure you can use ORM's with s'procs; but most people don't. Nearly every app I've seen that leveraged an ORM exposed the database in such a way that if the site is cracked the entire database can be easily killed or pilfered. ORM tools generate SQL on the fly. This means they need direct table access. This means that if your app is compromised, the hacker will have direct table access. This means that you have effectively removed at least one layer of security from your application.

I realize this question was posted a while ago, but I've wondered the same thing as johnny. I've seen Hibernate used on several large projects by intelligent people, and in every case, it was an unmitigated disaster. In my experience, the single most complicated and performance impacting area of most enterprise applications is between business layer and the database layer. This is part of why we add a data access layer to try to encapsulate the database interactions into manageable chunks.

The biggest arguments I've seen to use ORM are that they "reduce hand written code" and provide an abstraction layer that separates business logic from the data access. I assert that neither of these are true in practice except in very simple cases.

To make Hibernate (and most other ORM tools) work, we either create a hibernate mapping file which documents all the database and object interactions, or we use annotations to document the same relationships. In one case we moved our code to an xml configuration file that is harder to test, yet no less complex. In the other, we distribute the logic of how to interact with the database all over the domain model. The point is, while we've written less actual "code", moving code to configuration files or annotations != "less code". It simply moves the complexity from the code, where we can control it directly and mitigate it, to a third party tool with far less control.

The ORM abstraction layer that is supposed to separate the business/domain layer from the database layer tends to have more subtle effects that counteract this "separation". How many projects have you seen where the ORM layer affects the design of the object model and/or the database in a way that would be considered less than ideal? To use a poor physics analogy, assume your business layer, ORM layer, and database layer all have mass. The mere existence of the ORM layer between the other to tends exert a force that changes and warps the other layers. Have you had to introduce a primary key object that doesn't quite fit in your business model because the ORM layer needed it? Have you had to adjust your database structure to accommodate a particularly complex object graph model because the ORM tool can't handle it otherwise? Taken to extremes, the presence of the ORM layer can warp the entire perspective of how database interactions should work. Instead of a service layer that handles persisting object graphs, it can devolve into creating individual data access layer objects for each domain objects, as if they live in isolation. I've seen all these scenarios in varying degrees. Perhaps it's inexperience with the ORM toolset. Perhaps it's a bad database structure. I doubt it. Everything I've seen points to the ORM solution being inadequate.

If you accept my assertion that the data access layer is one of complexity, highly prone to performance bottlenecks, why should I consider adding in a tool that doesn't achieve it's objectives of less code and separation, and at the same time negatively affecting the structure of the other layers of my application?

The basic benefit of using ORM tools is to facilitate the principal of separating the business logic from the data access in a multi-layered application. If you can successfully build a data access layer that is responsive to changes in the database (by changing your mapping), there is less overall code for you to have to muck around with when changes are made.

Separation of the layers is typically the goal of 3 tier or n-tiered applications, and ORM is a good method of doing that.

Primary driver: less overall code to manage/maintain

1 advantage you get is that your data layer is separated from the object layer, so when you have multiple applications/projects dependent on that layer you change it once in the ORM and all applications/projects that reference the object layer don't have to change, where as without it you would have to make changes in all projects.