When you are abstracting your database records and datasets into objects, what does your object model look like?

Question

I'm asking all of you who aren't using a library for this, but are constructing your own objects for managing data flowing to and from your database tables. Do I have a recordset object? one object per row of data? Both? Neither? Any suggestions or experiences welcome. Please don't tell me to use an ORM or other such toolkit. It's overkill for my project and besides, that's not the question now, is it?

Answer 1

No matter the size of your project I would say use an ORM :-P

but.....

Back in the days when there were no ORM libraries we used to manually pull all the fields out of a Java recordset object and plug them into a real Java class.

The inverse applied for insertions and deletion (with a flag to indicate which was to happen)

Multiple rows were usually stuffed into a List.

Answer 2

i'd strongly suggest picking up martin fowler's patterns of enterprise application architecture, it describes a number of database patterns that would be helpful to know, and give you an idea of the evolution of patterns to full on ORM libraries.

specific patterns you may be interested in:

• RecordSet
• Row Data Gateway
• Table Data Gateway

these basic patterns will give you an idea of how to structure your objects, and the more advanced patterns (like active record/data mapper) you'll see how those relate to problem domains beyond where your needs are at the moment.

Answer 3

To manage the actual data flowing in and out of the database (without an ORM), you should look at Jakarta Commons DbUtils.

It provides very light-weight helpers to run queries and updates, such as automatically turning ResultSets into Lists of beans and such.

Answer 4

This will depend on how you whether you want the database design to drive the design of your objects, or whether your domain model drives the design of your database.

In the first scenario you create one class per table. As you'll quickly find, there will be times when you need a object that is join of two tables, so you'll have to have a scenario to handle that exception.

In the second scenario will require that you create an Identity map of your objects and somehow map the relationships of the classes back to the tables. In this case there will be instances where you do not have a one-to-one relationship of object to table.

I know you didn't want a prescription for a tool, but SubSonic a very straight forward, stable tool kit that can help you generate code from your database structure, and is well suited for the scenario where you will have one class per table. You can install and start generating your code within a half hour. It's worth taking a look.

Answer 5

Your data model is dominated by the domain entities -- the real-world things. Real world things can sometimes be mapped to single rows in a database. One Entity is one Object is one relational row -- mostly.

Sometimes real-world entities are really complicated and span multiple database rows. This is the "Aggregate" problem. Objects can be aggregates. Relational rows can't easily be aggregates without breaking all the normal form rules.

Sometimes, because of class inheritance, you'll be stumped as to how to get the object mapped to a database row. Is it one row per layer of the inheritance hierarchy? Or are all of the layers flattened down to columns in each subclass table?

Additionally, you need to have collections of things (a database is a collection of collections of things).

These "collections" or "record sets" or "managers" or "data access objects" mediate between persistence (SQL?) and your domain entities. The record set builds your domain objects from whatever SQL stuff it has access to. Similarly, the record set unwinds your domain objects into SQL stuff.

ORM is one way to handle this; the ORM framework provides these class definitions. If an ORM is "overkill", borrow the design patterns. Read the iBatis API. [While you're at it, you may find that there's nothing too small for ORM.]

In short, both: "recordset object" plus "one object per row of data" -- approximately.

If you feel the need to roll your own record set collections, you can try and use simple serialization to persist your objects. You'll have complexities piled on top of complexities trying to serialize aggregates and subclass relationships. Why? Objects have direct references to each other. SQL database has to emulate this with primary keys and foreign keys.