November 25, 2004
Database Independence
Thinking of developing your application to be database independent? Don't. The long answer is in this post on Mark Rittman's blog the short answer is in a quote from it;
"Database-independent code doesn't use features, it uses vanilla-flavored, lowest common denominator elements - so you pay every penalty, but never pick up the benefits."
It's another fine piece of writing from the always excellent Mark. I'm referencing it here as an aide memoire for the next time a wet behind the ears Java programmer tells me that the database is just somewhere to persist object instances.
Posted by Andy Todd at November 25, 2004 12:03 PM
It's important to distinguish between database-independent SQL, and database-independent code. Your SQL can be database-specific, because it pretty much *has* to be, for all the practical reasons discussed in the article.
However, your *code* should not be database specific; it should call functions that are named according to the domain-specific things you need to do, like "findCustomerByName()". Better yet, put those functions as methods on a "CustomerDatabase" class. Then, your application *code* is not tied to the database, even though you have a class that's tightly coupled to a specific database.
And, even if you don't change databases, you'll *still* be glad you did it, when you discover that the new version of your database's optimizer doesn't handle that query correctly without a "force" or "hint", and you have only one place that you have to look for or change it. Not only that, but your code will be more comprehensible to somebody reading it and seeing 'findCustomerByName()' instead of a bunch of SQL.
Speaking as someone who's migrated a significant SQL-based application from Sybase to Oracle, I wish I had taken this approach when the app was first being written. But because SQL was embedded directly in the code, I had to define a bunch of low-level functions (similar to JDBC escapes, in effect) and go around sticking them inside the SQL in a lot more places than I would have with the approach above.
Also, when the Oracle admins asked for sample queries, we could've just said, "here's a copy of the class with *all* the SQL in it."
Pre-2000 I spent a lot of time trying to convince
C++ and Java programmers that an RDBMS was anything more than an inconvenience. I distinctly remember the way my eyebrows arched when a group of "Java Architects" decided to jettison Oracle in favour of storing all the data in an LDAP server instead.
These days it's a much better - Java programmers "get" databases and C++ programmers have retired :)
Posted by: Alan Green on November 26, 2004 03:38 PM