Geek Smithology

October 15, 2005

Not using an ORM tool? Leave the SQL in the code.

Filed under: Craft of Dev by Nathan @ 12:45 pm

Rick blogs about taking the property file Lysol to some repetitive code smells, and while I can dig on the HashSet idea (was he a Perl hacker in a previous life?), I’m not sold on putting the SQL into property files unless you’re using some kind of tool (or you are some kind of tool… just kidding. I haven’t been too cynical lately, so give me a break!)

Anyway, the reason I’m not sold is that in your classic DAO pattern, you will have two parts – a DAO interface and then an implementation of that interface. So let’s address Rick’s issues one by one with that in mind:

1) It is inflexible

It’s the exact opposite. By programming to the interface, you can reimplement it at any time. One could even think of the implementation as your properties file (I, of course, would not be that one.) There is nothing to gain by making the implementation “more” flexible by externalizing your SQL. How often do you think you’ll be able to meaningfully alter that query without changing at least a little bit of code?

2) The code is not abstract

I’m not exactly sure how externalizing the SQL makes the code more abstract. If you have a tool and you are making generic “persistence” calls, then I could (grudgingly) see the point. But if you need to know about bind vars or ResultSets or Statements, or hell, even that you are eventually calling SQL, the abstraction leaks through. On the other hand, if you program to a well defined contract (read: interface) it is very abstract.

3) It is not generic

Once again, I don’t think making your SQL calls go through a common wrapper makes your code generic in the way that the interface/implementation pair does. As long as all of your data access code uses interface references, you can switch from flat files to an Oracle database to an LDAP directory, all without changing anything other than your interface implemtation and a factory call (and that is something that would be worth externalizing) Also, having an interface makes your DAO layer a lot easier to mock for unit testing.

4) It obfuscates your code and it’s underlying logic

If you call a persistence method, you shouldn’t know anything about the underlying logic. You should just be calling a method like “getFoo(name)” without even knowing it’s persisted. On the other hand, if you’re in the implementation class, it’s obfuscation to not have the SQL in the class file. If you’re worried about performance, declare all of your PreparedStatement SQL as private static final and away you go.

And there is one dangerous piece of code in the blog entry that I want to make sure everyone steers away from, and that is this:

// Iterate through the Enumeration of keys.
while (preparedStatementsBundleKeys.hasMoreElements()) {
    // Each PreparedStatement has a name ...
    statementName = ((String)preparedStatementsBundleKeys.

    // The value associated with the key will
    // be used to create a PreparedStatement.
    statementSQL = (preparedStatementsBundle.

    // Prepare the PreparedStatement, and put
    // it into the HashMap along with its
    // statement name.

It’s that last bit that’s scary – that means you have to keep the connection you used to prepare that statement open for the life of the map, rather than properly scope it within the method (you know the old adage, you should be able to see the open and close statements for a resource on the same page.) What happens if somebody starts a transaction and doesn’t commit it. That is not a fun bug to track down. Trust me, I’ve had to.

In the end, if you’re using something like Hibernate or stored procedures, then go wild pretending that programmers don’t need to know SQL to be good web developers. But if your dev team is implementing the persistence layer, than please, please, leave the SQL in the code

5 Responses to “Not using an ORM tool? Leave the SQL in the code.”

  1. Rick Stabile says:

    Thanks for the thought-provoking post, dude.

    Sorry I wasn’t clear about it, but of course you would clear the map whenever you close the connection — the two actions should go hand-in-hand.

  2. Kirk says:

    Persistances and GUI component necessarily violate encapsulation and in doing so become tightly coupled to those things that they are working with. This is because in a vast majority of cases, they are working with detailed knowlege of how things are represented. Consequently there is little room for good abstrations. DOA and O/R mapping tools are about the best level of abstration you’re going to get in that they offer a much better way of injecting SQL into your application.

    FYI, you can measure the lower levels of abstration using a good metrics tool such as that offered int TJCC. Same issue applies with GUI code.

  3. cynicalman says:

    Kirk: I’m not familiar with TJCC – a quick Google search gives me some hits for Teen Jewish Community Centers and babbling about Arizona; I’m too lazy to go past the second page. By “measuring lower levels of abstraction” do you mean performance measurement? If not, then disregard what I’m about to type…

    <segue type=”performance”>
    I’ve used Borland’s Optimize it Pro, and I’ve found the level of detail to which it can analyze astounding. For example (to stay on topic), it will break out how much of your time is spent in JDBC calls. From here you can drill all the way down into execution vs. fetch timings for individual queries.

    I can remember at least twice when I asked people to guess which of 3 or 4 queries were slowing down a particular page in our application, and everyone (including me!) got it wrong both times. You just can’t optimize without a proper profiling toolkit.

    That said, DAO optimization can take a few forms – optimizing queries, optimizing the database (indexes, views, etc.) or upgrading the hardware.

    To try and bring this back, let me say that from what I understand (and I apologize for not knowing much about Hibernate, most of my tool experience is with CMP entity beans, which I hate as much as everyone else) you get the most flexibility performance wise from a custom DAO, but you get “ease of modelling” (with a good toolset, that is) from using a tool.

    And I agree that you should still have a DAO even if you use an ORM tool, I just think the SQL should be one with the code if you don’t use a tool.

    Thanks for the comments folks, this has been interesting.

  4. carfield says:

    My working company always put SQL inside the code, it work pretty good. In fact it is not hard to write portable SQL in most cases. It really easy for maintaining, because either SQL or code alone is meaningless. They have to exist togather. We’ve try to externalize SQL once, but turn out we always need to refer to SQL when modify coding.

  5. Timmehâ„¢ says:

    I don’t buy the “Externalizing SQL strings is worthful” comment. That assumes that not only you use the basic SQL standard (as Mr. Cynicalman sated above.) That part I could actually buy for a minute if you’re ok with the performance hit of not being able to use vendor specific SQL. The part I have a problem with is that you’re assuming that all databases you interact with are using the same table structure. I can assure you that this will most likely NOT be the case. Imagine some day in the future it’s decided to freshen up the backend with a nice new database. The company, from which I collect a paycheque, recently (well in the last 2 years) moved away from a single database concept and created a reporting database. This reporting database is a transformation of our transactional database. It uses an entirely different (de-normalized) structure to boost performance for faster reports. So all areas of the system that needed to be changed to read data from the reporting database had to deal with not only a different database vendor but also a completely different table structure.

Leave a Reply


Powered by WordPress