Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
Well, obviously you could do what Hibernate does and use inheritance for generating a dynamic proxy object, which allow you to retrieve the object graph. That's extremely hacky with SP though :D –  Max Mar 21 '12 at 10:52
    
So I would end up reinveinting half of hibernate, without the 10+ years of experience the hibernate team has :). –  Augusto Mar 21 '12 at 10:54
1  
Any DBA should prevent the dropping of particular tables by certain users. It shouldn't matter how you attempt to do it. –  JeffO Mar 21 '12 at 12:22
1  
You might take a look at Mybatis - it might provide the feature you need. It's less of an ORM than a mapping framework. You can write SQL however you like and tell Mybatis where to put it on your object model. It will handle large object graphs with multiple queries, which sounds like the situation you have (lots of thin stored procedures). –  Michael K Mar 21 '12 at 12:47
1  
@Augusto: I have been in a similar situation, not due to use of SPs, but due to use of a proprietary mapping framework that did not support object relationships. We spent days writing code that could be written in minutes using a proper ORM. I never did get that problem solved. –  kevin cline Mar 21 '12 at 16:58

5 Answers 5

up vote 15 down vote accepted

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.

share|improve this answer
    
Martijn, I agree that the app should be modelled using DDD principles, but the problem I'm facing (and please tell me if there's a solution!!) is that some stored procs return very little information to instantiante a DDD entity. Please see this comment where I explained a bit more about the information that the stored procedures return. I could circumvent this, by invoking more than one stored proc, and for example retrieving all the user details and then invoke another one to retrieve all the account info, but it feels wrong :). –  Augusto Mar 21 '12 at 12:17
1  
@Augusto Well... you're app developer, so you have to decide whether it makes sense for certain object to exists with certain fields set to NULL. If it makes sense (for certain task for example) then let it be. If not, ask the author of SP to provide more data, so you can create your objects. –  Jacek Prucia Mar 21 '12 at 16:25
    
And adding to Jacek's comment - it's actually perfectly acceptable to call 2+ stored procs, again think of them as two remote services that you have to call in order to create your domain model, nothing wrong with that :-). –  Martijn Verburg Mar 21 '12 at 16:39
    
@Martijn: In my experience, a thin layer isn't sufficient. The mapping code may be considerably longer than the underlying business logic. –  kevin cline Mar 21 '12 at 17:00
    
@Kevin cline - Good point, have put 'hopefully' in the answer :-) –  Martijn Verburg Mar 21 '12 at 22:21

Is there an actual benefit of using stored procedures?

In the financial world (and places where Sarbanes-Oxley compliance is required), you need to be able to audit systems to ensure that they do what they are supposed to do. In these cases, it is much easier to ensure compliance when all data access is through stored procedures. And when all ad-hoc SQL is removed, it is much harder to hide things. For an example of why this would be a "good thing", I refer you to Ken Thompson's classic paper Reflections on Trusting Trust.

share|improve this answer
    
yes a million times yes! You also need to make sure that users cannot do anythign they aren't supposed to do including not having direct rights to tables and stored procs help that tremendously. –  HLGEM Mar 21 '12 at 17:12
1  
I work for a public company and we are SOX sompliant. It might be my poor knowledge of auditing, but I don't see the difference between doing the auditing at the DB level (via stored procs) or at the application level. Each application should have its own DB schema and that schema is only accessible from the application, rather than shared between different applications. –  Augusto Mar 22 '12 at 10:38

Stored procedures are very much more efficient than and client-side SQL code. They pre-compile SQL in the DB which also allows it to perform some optimisations.

Architecturally, a SP will return the minimum data required for a task, which is good as it means less data is being transferred. If you've got such an architecture, you need to think of the DB as a service (think of it as a web service and each SP is a method to call). It shouldn't be a problem to work with it like this, whereas an ORM guides you into working with remote data as if it was local, thus tricking you into introducing performance issues if you're not careful.

I've been in situations where we used SPs completely, the DB provided a data API and we used it. That particular app was very large scale and performed amazingly well. I won't have anything bad said about SPs after that!

There's another advantage - the DBAs will write all your SQL queries for you, and will happily handle all the relational hierarchy in the DB, so you don't have to.

share|improve this answer
3  
gbjbaanb, most of what you said is true for older databases. Most newer databases recompile queries quite often to decide which new optimizations to use (even if they are in stored produced). I agree with what you said about using the DB as an external system, but I also see that as a lot of work, as the application owns the database and both should be in sync as much as possible. For example with the naming of table/classes and fields/columns. Also, the approach of letting the DBAs write the procedures smells like development silos, rather than a having a multidisciplinary team. –  Augusto Mar 21 '12 at 11:39
7  
SPs aren't necessarily always more efficient and I think handing off SQL to DBAs is a bad way to go. As a domain expert the developer should know what data they want to get and how to get it –  Martijn Verburg Mar 21 '12 at 11:40
1  
This is a good answer, but in my experience most clients don't actually need the performance gains for controlling data access through stored procedures versus the inconvenience of making full use of your ORM tools on the application layer. More often than not I see these architectural decisions made in software shops where they have a need to justify the bloated salaries of "grey beard" stored procedure programmers that have no other skills. –  maple_shaft Mar 21 '12 at 11:48
1  
@Augusto the approach of letting the DBAs write the procedures smells like development silos +100 internets to you for this gem of truth. I have always seen this as the case where data access was controlled through stored procedures. –  maple_shaft Mar 21 '12 at 11:50
    
@Augusto I think gbjaanb meant that in case of SP you have query plan precompiled which saves a bit of time. As for precompiling queries with never datases, I'm not sure if we refer to the same feature, but some RDBMS have optimisatiosn for badly written queries. They observer & analyze queries so that they can substitute more efficient query plans at runtime. This only pays off if you have app where you aren't able to finetune queries. The problem with SP speed gain is that usually it is quite small. You would have to deal with really long and hairy query to get any meaningfull speed gain. –  Jacek Prucia Mar 21 '12 at 16:15

Your domain objects can be populated however you please, its not neccesary to use Hibernate. I think the proper term is data-mapper. Its very possible that your persisted data will be completely different structure to your domain objects.

share|improve this answer
    
We're currently using data mappers, but the problem is that they stored procs return a minimal set of data, which sometimes is not enough to fill an object (maybe we should allow the stored procedures to return more information). For example, one store proc might return a user email, forename, surname; while another one adds userid and an address. Since the data is different we're using different objects to store the data, which means that we have different 'User' classes. I'm trying to avoid using inheritance here, because I think it's a wrong use of it. –  Augusto Mar 21 '12 at 11:45
    
@Augusto: Interfaces? –  Kramii Mar 21 '12 at 12:17
    
@Karmii, I don't think the interfaces help here, as we then would need to duplicate the logic in different classes. Or we could use interfaces and then delegate the processing to a helper class, but that's not really OO :(. –  Augusto Mar 21 '12 at 12:22
1  
@Augusto I don't understand the problem : "stored procs return a minimal set of data, which sometimes is not enough to fill an object" So you alter the sproc or create another one, and then let the data mapper do the mapping –  NimChimpsky Mar 21 '12 at 12:23

What often happens is that developers incorrectly use their ORM objects as their domain models.

This is incorrect, and ties your domain directly to your DB schema.

What should really have is seperate domain models as rich as you like and use the ORM layer seperately.

This means you will need mapping between each set of objects.

share|improve this answer
1  
This is a good idea but for smaller projects it really starts to feel like overkill. This approach also requires a translation layer between the ORM persistence layer and the domain model. –  maple_shaft Mar 21 '12 at 11:39
    
@maple_shaft agreed, and that's what I meant by "mapping" :-) –  jmo21 Mar 21 '12 at 11:42
    
@Ozz, the way I've worked is exactly that, the entity classes ARE the domain model (and I might add with quite lots of success). I agree that it ties the domain model to the schema, but that's exactly what I want, as I use convention over configuration, and the nice side effect is that if I see a field on an entity, I don't need to think hard about the name of the table and column where that information is stored. –  Augusto Mar 21 '12 at 11:54
    
@Augusto I've done it too! and as maple_shaft says, it's fine for small CRUD style apps, but there are many issues as the OP is finding out. One example might be where you have a many to many mapping table eg: StudentClasses, that maps Students to their classes and just contains StudentID and classID, you would not necessarily want to map this in your domain. That's just a quick off the top of my head example. –  jmo21 Mar 21 '12 at 12:07
2  
@Ozz: Your comment seems to contradict the very idea of an ORM. An ORM doesn't "tie your domain directly to your DB schema". The ORM maps your domain to a DB schema, without the need for a separate DAO layer. That's the whole point of an ORM. And most ORMs handle many-to-many mappings just fine, with no domain model needed for the mapping table. –  kevin cline Mar 21 '12 at 16:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.