Mapping objects to a relational database...

[Joe Black]

Something I end up doing a lot (like most developers no doubt) is designing/trying to design a framework which is efficient at instantiating objects from a DB which may have children of the same type, e.g. instantiate object X, object X's properties are retrieved from database, object X has children of the same class, which in turn may/may not also have children...a recursive join in DB speak.

Over the years I've developed various (for want of a better word) hacks to do this, but am still not happy and am wondering if there's a better way than for example a) joining the same table multiple times b) having a chunk a code which does recursive calls or c) relying on a proprietary DB function such as Oracle's 'connect'?

[Spacecadet]

Personally i would just use your option b

1 call to get the info for the parent
1 call to get the info for all the children - which you then work your way through creating the objects as you go.

[HankWangford]

hankwangford

or mapper's?? llblgen / entitybroker

[Joe Black]

A couple of interesting suggestions Hank, which I hadn't heard of.

"Everything is a hack to some extent in this horrible area of OO design."

Indeed, given that most large companies still use relational databases, it's still an ongoing problem of connecting something that seems simple as an OO design or XML schema into the real world of what you are allowed to use or what is available...

PS: "It's business logic - stick it where it's supposed to be", but it's not, it's the basic stuff on which the system is built, BL is just what goes on top of it...

[ASB]

Have you ever looked at this, it might help:-

http://neo.codehaus.org/

Or it might not.

[ASB]

Yeah but I don't trust anything like that. It's another piece of third party crap to maintain and work around bugs in.

If you write it yourself, you know how it works!

Fair comment, but you do get the source with NEO. There are quite a lot of contributors.

[HankWangford]

Not True

Yeah but I don't trust anything like that. It's another piece of third party crap to maintain and work around bugs in.

If you write it yourself, you know how it works!

These OR mappers are mature and have been tested / used by many people giving more test coveridge than a test team in one company could ever hope to achieve.
Why re-invent the boring wheel of creating business entities and crud methods to map objects to your rdbms when you can have them generated. The scope for bugs is much less than a bored developer cutting and pasting. Saves me hours and companies are well impressed with the time saved to concentrate on 'visible' functionality

[f_eriksen]

Orm

I feel the pain whenever I see discussions on this topic.

there are a few options, Hibernate being quite popular in his area.

But, most 3rd party tools, such as hibernate/nhibernate, neo, etc, etc does add an additional layer, which may or may not be a good thing (cache..), and abstraction is of course good, but...

and this is a but...

have you seen the actual access code being spitted out of these type of frameworks?

as someone normally hired to get most juice possible of out database(s), and whos primary skill is database engineering/modelling/coding etc, etc.. I cry when i see that. Mind you, it all falls back to the actual requirements of whatever you are building, granted, and the benefits of abstraction, etc can outweigh "simple" matters such as efficient sql, shuffling large resultsets across the wire between the db and the middle tier, but me, i prefer fast performing sql using in/out params and return values where I can

Cache is king, but alas, a pain to maintain, and not all data is possible to cache (contact center, presence, and other highly transient data)...

a quick look on google with ORM will give you plenty of good pointers to research though.

just my 2c...

Fridthjof-G

[f_eriksen]

Agree the generated code does suck.

Glad to hear Although I can, as said earlier, see the points and benefits, I've yet to meet an OO/OR freak to understand that efficient/good sql and data access code is beneficial/crucial, but hey, I shouldnt complain. when I get hired to fix up a systems poor performance it is not too hard to point a finger

Because I am paid by the day to do that!

a good point indeed.

As always, the holy grail is somewhere in the middle.

[HankWangford]

Inline vs SP's

I feel the pain whenever I see discussions on this topic.

there are a few options, Hibernate being quite popular in his area.

But, most 3rd party tools, such as hibernate/nhibernate, neo, etc, etc does add an additional layer, which may or may not be a good thing (cache..), and abstraction is of course good, but...

and this is a but...

have you seen the actual access code being spitted out of these type of frameworks?

as someone normally hired to get most juice possible of out database(s), and whos primary skill is database engineering/modelling/coding etc, etc.. I cry when i see that. Mind you, it all falls back to the actual requirements of whatever you are building, granted, and the benefits of abstraction, etc can outweigh "simple" matters such as efficient sql, shuffling large resultsets across the wire between the db and the middle tier, but me, i prefer fast performing sql using in/out params and return values where I can

Cache is king, but alas, a pain to maintain, and not all data is possible to cache (contact center, presence, and other highly transient data)...

a quick look on google with ORM will give you plenty of good pointers to research though.

just my 2c...

Fridthjof-G

Got o disagree, there is no major gain by using sp's over inline sql. There was a great debate on asp.net forums and over many weblogs about this. The performance diff is negligable especially in crud operations and certainly not worth the pain of maintaining a large number of procs in a large DB. 4 procs * 300 tables, 1200 SP's.....YAK. SP's are usefull for complex reporting and IMHO thats all


check out this thread http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx