Object Mapping + Stored Procedures arch suggestions 2

[dbleyl]

Question:

If you need to have the power of stored procedures and the cleanliness of persistence abstraction (Hibernate, CMP, JDO),
how should you design the architecture?

Example:
class BookDataAccessObject {
public void save(Book book){
//small and simple pojo great for
//persistence framework.
persister.save(book);
}
public double getCrossRefGMROIByPublisher(Book book) {
//complex set logic, much better left at the db.
callableGMROI.executeQuery();
...
}
}
It seems ugly to mix framework and sp's, but overly simplistic to pick one or the other and live with it.

Any ideas about partitioning this type of object?

I'm thinking that you could have the DAO framework and a separate command interface for the sp's, but then you can't
share connection information as easily, for example
persister.connection().prepareCall();
???
How to make this clean?

 

[idarke]

For my money, I don't like the user of Stored Procedures because you end up with business logic in two separate places.

Mind you, this isn't taking performance issues into consideration. I haven't really come across a problem where the performance was so bad that I had to consider stored procedures as an antidote.

I like all the business logic to be contained in beans on the server. It's easier to maintain and it's easier for someone new to figure the system out.

 

[dbleyl]

Thank you. I agree with your point. SP's split logic where it should be kept in one place. Perhaps there is just an elegant sql solution, or a pre-existing stored proc.

I have a follow-up question: if you had to use them, how would you organize them? Currently, I have a DAO class that delegates JDBC calls to a JDBCImpl class with default scope.

 

[sedj]

I have to disagree with idarke - depending on the size of your company (this site is meant for proffesionals.

In our company, there is a clear definition between data manipulation/handling (ie db team) and data entry/disply (ie java/web team).

I think by no means the java/web side should control how data is manipulated on the db - there should be no insert/delete/update SQL statements at all executed by the java/web team - this is the realm of the db team. The only interface java/web should have with a db is via controlled methods such as stored procs. It keeps the db expertise within the db, and data display/entry within the web team.

Freelance "SQL" statements are dangerous, and if schemas change within the database, the web/java stuff needs to be changed also - this is dangerous, and unecessary .

>>>>>For my money, I don't like the user of Stored Procedures because you end up with business logic in two separate places.

This is entirely false - the usuage of stored procedures keeps business logic in one place - not two ...


Just my two pennies ...

 

[siberian]

I gotta go with sedj on this one, its really dependant on your goals.

In our case we have 3 different teams using 3 different sets of technologies (ASP/Perl/C++) on different technology platforms with different goals. Centralizing all database access through stored procs has really led to some serious efficiencies and much higher quality of service across the entire product. Like sedj says, standardizing the db access layer in one place instead of multiples.

When developers were doing freelance SQL things broke constantly, with it all centralized any changes no longer have a ripple effect since the interfaces stay constant.

That said, for a smaller project stored procs can be overkill, its really dependant on your goals.

But these days a 'website' is not just a 'website', applications grow out of their homes and are crossing barriers, going to different clients, embedded platforms, web systems etc and all of these need in many cases to operate off of a single base of knowledge. This is where stored procedures are great.

 

[dbleyl]

Yes, there is some debate in this area. I agree with everyone. This is my solution, and I'm asking for anyone
to elaborate on theirs:

Given a solution that requires a.) an o/r mapping layer, and b.) jdbc-specific code (stored procs), how do you mix the
two nicely?

My current approach: Use a DAO that relies on the o/r mapping and delegates the JDBC-specific code to a separate class with default scope.

My Second Question really is:

If you aren't using stored procs, then how do you perform complex aggregate functions (weighted gross profit of all items not meeting conditions x,y and z) efficiently?
In a stored proc, the db can do the calculations and return
one result, in Java you must iterate over a resultset, and create the aggregate. Is there another way? Is it really not that bad?

 

[sedj]

http://java.sun.com/products/ejb/

 

[dbleyl]

sedj, I'm having trouble interpreting this one. For a second, I thought you were suggesting I rely on entity beans and that everything from then on would be beautiful!

 

[sedj]

I meant that EJB might suit your needs if you aren't using stored procedures ...

 

[dbleyl]

sedj,

When you use EJBs to perform a blanket operation or aggregate,
how do you structure them? By blanket, I mean, for example, a n inventory system, where you'd like the user to, in one click, flag all items in the system which have a particular number of inventory turns, and then present some stats once that action is completed, such as % of items that fell within the setting, broken down by geography. At the same time, the user may be viewing the items individually in order to view specifics and make fine-grained decisions.

To my way of thinking, you have two things going on - one which is very well-suited to SQL, and one which lends itself nicely to an o/r mapping-iterative process. I'm just looking for the best way to partition this...or, is it simply better to have an EJB that does this type of aggregation through iteration?

Thanks!

 

[sedj]

To be honest I'd call a stored proc ... what have you got against them so much ?

DAO's are OK, but it sounds like you are trying to perform a fairly complex and custom action on your database, which does not really lend itself to the "Object Orientedness" of EJB or DAO.

Don't always belive the hype about the benifits of Object Orientated design - sometimes it makes no sense at all, and doing somehting in an OO way just means you are writing code for no good reason. Just have a custom proc and custom JSP/Servlet/swing client that calls it.

 

[dbleyl]

sedj,

I actually like stored procedures. Your advice comes as a big relief - I've struggled with this for a few days now. Some actions lend themselves to set-oriented approaches, while others fit the OO model nicely. In the same application, within the same object, I've just separated the two types into different impl classes, and then exposed the functionality through a common facade.

There is a bit of synchronization to deal with, which I suppose is natural with this type of what amounts to an optimization. Thanks for helping me think this through.

 

[sedj]

Great ... you should program as it feels natural - which is some times in structured bespoke manner, and sometimes in an OO way - just do whatever feels right for the application/component - and have fun !