This discussion is archived
9 Replies Latest reply: Nov 20, 2013 9:19 AM by rp0428 RSS

DAO patterns in relation to Stored Procs

saum Newbie
Currently Being Moderated

Hi All.

 

The DAO pattern suggests that all data access happen through the DAO object, which in turn will encapsulate the actual Data Source, which is a table in the simplest of example.

 

Can the DAO pattern be applied to Stored Procs as well..? Or is there a different pattern that can be applied for multiple table inserts/updates.

 

 

-Saum

  • 1. Re: DAO patterns in relation to Stored Procs
    rp0428 Guru
    Currently Being Moderated
    The DAO pattern suggests that all data access happen through the DAO object, which in turn will encapsulate the actual Data Source, which is a table in the simplest of example.

     

    Can the DAO pattern be applied to Stored Procs as well..?

    The DAO pattern is opaque as regards the physical data source. It makes no difference if the data source is a table, view, procedure, queue, etc.

     

    Or is there a different pattern that can be applied for multiple table inserts/updates.

    I don't understand what you mean by this. What does 'multiple table inserts/updates' have to do with what you ask?

  • 2. Re: DAO patterns in relation to Stored Procs
    saum Newbie
    Currently Being Moderated

    Well the easiest way I find to Insert and update multiple tables in a DB is with a single SP - and you can have an execute statement to do that in a single call to the DB.

    The other way would be to have a DAO encapsulate each table and make multiple call to the DB to update all the tables.

     

    Is there any other around way for that situation.

     

    -Saum

  • 3. Re: DAO patterns in relation to Stored Procs
    rp0428 Guru
    Currently Being Moderated
    Well the easiest way I find to Insert and update multiple tables in a DB is with a single SP - and you can have an executestatement to do that in a single call to the DB.

    The other way would be to have a DAO encapsulate each table and make multiple call to the DB to update all the tables.

    Well I still don't understand the connection not do I see how this has anything to do with JDBC.

     

    Multiple tables implies multiple data sources which implies multiple DAOs. As I said above the actual 'physical' manifestation of the 'remote' data doesn't makes any difference: table, view, procedure, queue, etc.

     

    I also don't understand why a single SP would be the 'easiest' way for anything that has to deal with 'multiple tables'. That SP has to obtain the source data for the insert/update from somewhere and that 'somewhere' would seldom be a source that has data for multiple tables. If it was that would suggest a very flawed data model.

     

    Forgetting DAOs for the moment it is rarely good practice to encapsulate multiple operations (e.g. DML on multiple tables) in a single SP anyway. Standard practice would be an SP for each data source/target. There might be a 'control' SP that controls the process of executing the datasource SPs in the proper order. So, again, I'm not sure why you would have one SP for multiple tables especially if the data is being passed into/out of that SP.

     

    Architecturally speaking each table would have its own DML process tailored to its own unique data projection. For normalized data multiple tables would NOT be used for data relating to the SAME entity at the same level. Multiple tables for related data would typically be hierarchical (e.g. Parent/Child).

     

    For a Parent/Child set of data you would have one DAO for the parent and another DAO set for the child data. The DML for those DAOs would either be handled individually or both sets passed to the SP so it could iterate through the parent/child data to deal with surrogate key generation or issues like that.

     

    Maybe others can offer a different viewpoint.

  • 4. Re: DAO patterns in relation to Stored Procs
    saum Newbie
    Currently Being Moderated

    For a Parent/Child set of data you would have one DAO for the parent and another DAO set for the child data. The DML for those DAOs would either be handled individually or both sets passed to the SP so it could iterate through the parent/child data to deal with surrogate key generation or issues like that.

    To illustrate an example .. Lets say a Person{pk_person, fname, lname, fk_address} table and an Address{pk_addr, Street,city, zip} table. Now they are not quite parent/child, they are to separate and independent entities altogether, related by the FK_ADDRESS.

    Now one way to insert a data set would be to Insert to the Address table and use the pk_addr in the Person table, where both pk_person & pk_addr can be a Sequence from the DB.

     

    In that case it would be simpler to pass the fname, lname, Street,city, zip from the Business Component to a DAO - calling an SP.

     

    Now what I meant to ask was - is that the right approach OR is it recommended to have the Business Component call the PersonDAO and an AddresDAO seperately (and I've no clue if that can be made to work).

  • 5. Re: DAO patterns in relation to Stored Procs
    rp0428 Guru
    Currently Being Moderated

    To illustrate an example .. Lets say a Person{pk_person, fname, lname, fk_address} table and an Address{pk_addr, Street,city, zip} table. Now they are not quite parent/child, they are to separate and independent entities altogether, related by the FK_ADDRESS.

    Now one way to insert a data set would be to Insert to the Address table and use the pk_addr in the Person table, where both pk_person & pk_addr can be a Sequence from the DB.

     

    In that case it would be simpler to pass the fname, lname, Street,city, zip from the Business Component to a DAO - calling an SP.

     

    Now what I meant to ask was - is that the right approach OR is it recommended to have the Business Component call the PersonDAO and an AddresDAO seperately (and I've no clue if that can be made to work).

    I don't consider that 'simpler' at all. It also isn't scaleable or object-oriented.

     

    There are two 'standard' approaches used depending on how the keys are generated. Both of those use separate DAOs.

     

    One method generates the keys using methods in the persistence layer; that might call a sequence generator.

     

    The Address DAO would be used to create an address row. If the keys are generated by a method in the persistence layer that method is called to obtain a new key, the key used to update the DAO and then the new address row created.

     

    That same key is then used to update the personDAO and then that DAO creates a new person row.

     

    In the other method a DB trigger generates the key on INSERT. So the address DAO creates a new address row and the new key is returned (e.g. Oracle procedure does an insert with a RETURNING clause). That new key is used to update the addressDAO and also the personDAO. Then the personDAO can create it's new row.

     

    Person addresses can be easily changed; all you need is the new address key you want to use. That key updates the personDAO value and then an update is performed.

     

    Many frameworks (hibernate, toplink) can handle all of that easily because when you define the data model all of those foreign key relationships are specified as is the key generation method to use.

     

    Which is 'best'? There is no 'best'. And both of those techniques, although commonly used, are severely non-performant for bulk loading.

     

    It all hinges on how the source data is obtained to begin with. That is, if there are two data files one for person and one for address how is the 'address' for a person determined for that file data? A person might even have multiple addresses (e.g. work, home, vacation).

     

    So the key for NEW data is where that data comes from, what format it is in and how those links are encoded in the actual source data. For one-at-a-time data entry GUIs it it rather trivial. It is that bulk stuff that can get complicated.

     

    But I have never seen a stored procedure such as what you suggest used. That is not scaleable and also non-performant.

    And even passing custom Oracle datatypes involves packing/unpacking the data into those types when simple DML can get the job done much quicker.

     

    There is always a trade-off between generality and performance. Small volume processing such as between a GUI and the backend works fine using all of those layers and DAOs. High volume performs very poorly. You generally need a mix to handle the different major use cases.

  • 6. Re: DAO patterns in relation to Stored Procs
    saum Newbie
    Currently Being Moderated

    Thanks rp0428 for the detailed explanation. I gather that a PROC would not be a recommended approach for such a scenario and simple DML is more efficient. That was what I was looking for.

    Is there some link/document that you would suggest where I can read in more detail about it. Something about High Volume transactions as well preferably.

  • 7. Re: DAO patterns in relation to Stored Procs
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated


    >  Something about High Volume transactions

     

    Does your business model actually need that?

     

    For example it would be highly unlikely that you need to insert 1000 new customers a second.

  • 8. Re: DAO patterns in relation to Stored Procs
    saum Newbie
    Currently Being Moderated

    Hi jSchell,

     

    No I do not have a requirement for a high volume transaction, it was a general query for my knowledge.

     

    As for the original question, it crept up in when i was trying to figure out how to apply the DAO pattern as described here Core J2EE Patterns - Data Access Object

     

    -Saum

  • 9. Re: DAO patterns in relation to Stored Procs
    rp0428 Guru
    Currently Being Moderated

    Hopefully you noticed the sections in that article that talked about the different types of persistence: CMP, BMP, etc.

     

    Typically, applications use shared distributed components such as entity beans to represent persistent data. An application is considered to employ bean-managed persistence (BMP) for its entity beans when these entity beans explicitly access the persistent storage-the entity bean includes code to directly access the persistent storage. An application with simpler requirements may forego using entity beans and instead use session beans or servlets to directly access the persistent storage to retrieve and modify the data. Or, the application could use entity beans with container-managed persistence, and thus let the container handle the transaction and persistent details.

    . . .

    •   Not Useful for Container-Managed Persistence
      Because the EJB container manages entity beans with container-managed persistence (CMP), the container automatically services all persistent storage access. Applications using container-managed entity beans do not need a DAO layer, since the application server transparently provides this functionality. However, DAOs are still useful when a combination of CMP (for entity beans) and BMP (for session beans, servlets) is required.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points