Objects in SQL Databases

As a very long-time Java developer, and someone who has worked with many different databases (from embedded SmallSQL, to >600 table legacy Informix database, to a brand spanking new Oracle DB), I’ve also worked with lots of ways to get data in and out of the database. I’ve used JDO (in various forms), Hibernate, TopLink, my own home-grown mapping layer, direct SQL through JDBC.

I feel I can now say I’m fluent in SQL, since I’ve recently been writing SQL statements that span 20 or more lines. What i’ve come to realize is that when you put a major Object/Relational mapping layer between you and your database, you may as well be storing your data in XML files on the filesystem (having played around with both, I find that the performance is much the same). Rather use a true Object database where possible (for actually storing objects, the performance is much better than a SQL database).

The problem is that you are loosing all the power that sits in SQL. SQL is an incredibly powerful language, designed entirely for fetching and manipulating data. It’s not the performance of the mapping layer I’m concerned with, but the fact that you can no longer craft efficient SQL.

Summary: Use the right tools in the right places!

Advertisements

6 Responses to “Objects in SQL Databases”

  1. erka Says:

    SQL is a great language, but it has many problem. If you would need change Oracle to DB2 or another database you may have some problems( same sql could return different result sets). SQL is a cross database as java is a cross platform :(.

  2. Jason Says:

    I’ve mostly found that when coding for a client, there is one target database. If the database ever changes, they hire teams to manage the porting of all their systems to the new database anyway. In cases where you are having to be database neutral, I would say go with an interceptor pattern. I’ve seen amazing things done with facade JDBC drivers as well.

  3. erka Says:

    You are right. In anyway it’s great that we have alternatives for implementation data access layer.

  4. Jason Says:

    I agree absolutely. My personal favorites of the data mapping layers is most certainly the EoD RI from Java 6 beta, followed by JDO, and in particular JDO Genie. Though as for the EoD RI, I find my own EoD SQL project to have the little bit more power that I need 😉

  5. Hardeep Singh Says:

    I agree with you on the fact that SQL potential is underutilised. Please read my related post: /computer/20080731/using-sql-potential/

    (append this to my blog address)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: