After using Hibernate on most of my projects for about 8 years, I've landed on a company that discourages its use and wants applications to only interact with the DB through stored procedures.
After doing this for a couple of weeks, I haven't been able to create a rich domain model of the application I'm starting to build, and the application just looks like a (horrible) transactional script.
Some of the issues I've found are:
- Cannot navigate object graph as the stored procedures just load the minimum amount of data, which means that sometimes we have similar objects with different fields. One example is: we have a stored procedure to retrieve all the data from a customer, and another to retrieve account information plus a few fields from the customer.
- Lots of the logic ends up in helper classes, so the code becomes more structured (with entities used as old C structs).
- More boring scaffolding code, as there's no framework that extracts result sets from a stored procedure and puts it in an entity.
My questions are:
- has anyone been in a similar situation and didn't agree with the store procedure approach? what did you do?
- Is there an actual benefit of using stored procedures? apart from the silly point of "no one can issue a drop table".
- Is there a way to create a rich domain using stored procedures? I know that there's the possibility of using AOP to inject DAOs/Repositories into entities to be able to navigate the object graph. I don't like this option as it's very close to voodoo.
Conclusion
First, thank you all for your answers. The conclusion that I've arrived is that ORMs don't enable the creation of Rich Domain models (as some people mentioned), but it does simplify the the amount of (often repetitive) work. The following is a more detailed explanation of the conclusion, but is not based on any hard data.
Most applications request and send information to other systems. To do this, we create an abstraction in the model terms (e.g. a business event) and the domain model sends or receives the event. The event usually needs a small subset of information from the model, but not the whole model. For example in a online shop, a payment gateway requests some user information and the total to charge a user, but doesn't require the purchase history, available products, and all the customer base. So the event has a small and specific set of data.
If we take the database of an application as an external system, then we need to create an abstraction that allows us to map the Domain Model entities to the database (as NimChimpsky mentioned, using a data-mapper). The obvious difference, is that now we need to handcraft a mapping for each model entity to the database (either a legacy schema or stored procedures), with the extra pain that, since the two are not in sync, one domain entity might map partially to a database entity (e.g a UserCredentials class that only contains username and password is mapped to a Users table that has other columns), or one domain model entity might map to more than one database entity (for example if there's a one-to-one mapping on the table, but we want all the data in just one class).
In an application with a few entities, the amount of extra work might be small if there's no need to transverse the entities, but it increases when there's a conditional need to transverse the entities (and thus we might want to implement some kind of 'lazy loading'). As an application grows to have more entities, this work just increases (and I have the feeling that it increases non-linearly). My assumption here, is that we don't try to reinvent an ORM.
One benefit of treating the DB as an external system, is that we can code around situations in which we want 2 different versions of an application running, in which each application has a different mapping. This becomes more interesting in the scenario of continuous deliveries to production… but I think this is also possible with ORMs to a lesser extent.
I'm going to dismiss the security aspect, on the basis that a developer, even if he doesn't have access to the database, can obtain most if not all the information stored in a system, just by injecting malicious code (eg. I can't believe I forgot to remove the line that logs the credit card details of the customers, dear lord!).
Small update (6/6/2012)
Stored procedures (at least in Oracle) prevent doing anything like continuous delivery with Zero downtime, as any change to the structure of the tables will invalidate the procedures and triggers. So during the time that the DB is being updated, the application will be down too.
Oracle provides a solution for this called Edition-Based Redefinition, but the few DBAs I've asked about this feature mentioned that it was poorly implmented and they wouldn't put it in a production DB.
Best Answer
Your application should still be modelled from domain driven design principles. Whether you use an ORM, straight JDBC, calling SPs (or whatever) should not matter. Hopefully a thin layer abstracting your model from the SPs should do the trick in this case. As another poster stated, you should view the SPs and their results as a service and map the results to your domain model.