9 Replies Latest reply: Feb 2, 2011 1:35 PM by jschellSomeoneStoleMyAlias RSS

    How to use DAO pattern

    828129
      Hi

      Recently I came across the DAO design pattern and I'm a bit confused how to properly use it.

      I have read somewhere that there should be one DAO per table and it should perform CRUD operation on that table only. DAO should be dumb object that just maps object properties with database fields and business layer uses different DAOs to complete the objects. A DAO should not call another DAO. Is this correct? I have an example where this doesn't work.

      Suppose I have a three entities in my general store application: Product, Customer, and Bill.

      In database I have three tables:
      product(id, name, price),
      customer(id, name, address),
      bill(id, product_id, customer_id, date)

      If I create DAOs that can access only one table and perform only CRUD operations on it, it will be simple to create ProductDAO and CustomerDAO but I can not think of how BillDAO will work. Will it only return me the productId and customerId and from business layer I again call ProductDAO to get the details of each product and the customer's name. Lets take another example, say i want to get the details of all the products bought in a particular month and name of customers who bought them. This requires join of all the tables. Which DAO should answer this query, should there be another DAO for it?

      Please help me in understanding the best practices for using a DAO pattern in multi tier applications. What goes in DAO and what goes in Business layer.

      Thanks
      Amit Khanna
        • 1. Re: How to use DAO pattern
          jschellSomeoneStoleMyAlias
          user11345138 wrote:
          Hi

          Recently I came across the DAO design pattern and I'm a bit confused how to properly use it.

          I have read somewhere that there should be one DAO per table and it should perform CRUD operation on that table only.
          Both parts of that are wrong.

          Normally there will be one per table. However other DAOs might exist for convenience. DAOs will normally more closely model the data model rather than the database schema. But if there is no explicit data model, or if it exactly matches the schema, then it works out as about the same. One does a one to one mapping (exluding link tables) because the business functionality often needs access to it anyways.

          Normally CRUD will exist. Other methods might exist. Very rarely some CRUD methods might be inappropriate (for example when a proc is use to control deletion of a tree.)

          I might note that myself I do unit testing agains the database and do not attempt mocks (not would I consider doing that to be a good idea either.) Consequently for unit test reasons I just do all of the CRUD methods even if business functionality does not directly support it.
          DAO should be dumb object that just maps object properties with database fields and business layer uses different DAOs to complete the
          objects. A DAO should not call another DAO. Is this correct? I have an example where this doesn't work.

          It says the business layer should have its own DAOs? That definitely sounds odd.
          I put all the DAOs in the same layer. Only time that isn't the case is when the functional need of the enterprise suggests obvious different groupings of database functionality. So instead of one DAO package I might have three.

          >
          Suppose I have a three entities in my general store application: Product, Customer, and Bill.

          In database I have three tables:
          product(id, name, price),
          customer(id, name, address),
          bill(id, product_id, customer_id, date)

          If I create DAOs that can access only one table and perform only CRUD operations on it, it will be simple to create ProductDAO and CustomerDAO but I can not think of how BillDAO will work. Will it only return me the productId and customerId and from business layer I again call ProductDAO to get the details of each product and the customer's name. Lets take another example, say i want to get the details of all the products bought in a particular month and name of customers who bought them. This requires join of all the tables. Which DAO should answer this query, should there be another DAO for it?
          Unless the project is very complex or has some extreme constraints (such as performance) don't obsess to much about the the 'correct' place to put object association code (object A references/contains object B.) Either object or even a new object just for the association works as well. But keep it relatively consistent for all objects.
          • 2. Re: How to use DAO pattern
            forumKid2
            I might note that myself I do unit testing agains the database and do not attempt mocks (not would I consider doing that to be a good idea either.) Consequently for unit test reasons I just do all of the CRUD methods even if business functionality does not directly support it.
            I disagree that unit testing without mocks is a good idea as it goes against the basic definition of a unit test. Maybe for some small application it would be ok, but for a medium to large application, it's a must to mock the database out and it's considered a good practice.

            I just wanted to point that out. I don't mean for this thread to diverge into a unit test best practice thread.

            Edited by: forumKid2 on Jan 6, 2011 6:32 AM
            • 3. Re: How to use DAO pattern
              gimbal2
              Unit testing and databases work well together.

              I have created two situations so far:

              1. Oracle database is 'mocked' using an in-memory HSQL database (setting up a testset is a big pile of work this way...). Hibernate schema generation from JPA entities is a key requirement here.
              2. An actual separate Oracle schema is used for the unit tests (test set is scripted and the same as the set of data used for development purposes)

              In the second case the code was of course prepared to be unit tested; using TestNG features a single transaction could be instantiated during the runtime of a unit test that the application code will then adopt (the unit test suite creates the EntityManager which is then injected in all EJB services). The TestNG logic then triggers that at the start of a test (@BeforeMethod) a transaction is created and and the end of it it (@AfterMethod) is rolled back whatever happens. Thus a unit test does no permanent mutations to the data in the database.

              I prefer not to mock out the database as the data state changes in the database itself are of vital importance to create valid unit test conditions.
              • 4. Re: How to use DAO pattern
                forumKid2
                My unit tests are then used as automated unit tests via a test suite. I can't even begin to think how much longer it would take for the tests to run if they were making trips to the database.

                Again, I dont want to hijack and turn this into a unit testing discussion :-)
                • 5. Re: How to use DAO pattern
                  823400
                  Well, I'm trying to put in simple terms as to how do we generally use DAO layer and business layer:

                  DAO delgates the responsibilities and accepts the results after acting on Database. By this, I mean - Say, I would want to fetch the list of Patients available in the hospital. All I would pass to DAO is the hospital information which it uses to construct the queries dynamically. As mentioned earlier, once the query is built dynamically, it delegates the execution responsibilites to a helper class which inturn manages the connection and execution part. Helper class would ideally pick up the available connection from the pool and would execute the query. Once the query is executed, it would ideally construct the Data transfer object of the pertaining query and gives it back to DAO layer which validates the results(such as whether the query successfully fetched results or not or any similar validation) returns these results to subsequent layers.

                  Coming to Business layer, as the name says, all the business validations should be carried out. Say for ex: In a company, there can be only one CEO. Say the user attempts to register one more CEO, this validation before calling the register method of DAO should be done in Business layer.

                  I've tried to put it in lay-man terms so that it is easier to catch up. Let me know, if you need more technically.

                  Edited by: user13466561 on Feb 1, 2011 1:37 AM
                  • 6. Re: How to use DAO pattern
                    jschellSomeoneStoleMyAlias
                    forumKid2 wrote:
                    I might note that myself I do unit testing agains the database and do not attempt mocks (not would I consider doing that to be a good idea either.) Consequently for unit test reasons I just do all of the CRUD methods even if business functionality does not directly support it.
                    I disagree that unit testing without mocks is a good idea as it goes against the basic definition of a unit test. Maybe for some small application it would be ok, but for a medium to large application, it's a must to mock the database out and it's considered a good practice.
                    Sorry just saw this....

                    Exactly what mock method do you use to test the functionality of the database itself? You do test the database right? All of the stored procedures? All of the constraints?

                    How exactly do you mock the connection itself? That is something that my code tests. My code also tests database failures.

                    As for size I believe I am dealing with hundreds of thousands of lines of code and certainly thousands of classes. I have about 1,500 files in one code tree of many that I now manage. The managed code at the company I am at was over 3 million lines several years ago.

                    And I was writing unit tests against database layers (to the database) on a project with a team of 20-30 developers.

                    So exactly what do you mean by "medium to large application"?
                    • 7. Re: How to use DAO pattern
                      jschellSomeoneStoleMyAlias
                      forumKid2 wrote:
                      My unit tests are then used as automated unit tests via a test suite. I can't even begin to think how much longer it would take for the tests to run if they were making trips to the database.
                      That has nothing to do with the discussion.

                      If your code has a problem with that then perhaps you should look to the code rather than the tests.
                      • 8. Re: How to use DAO pattern
                        jwenting
                        jschell wrote:
                        forumKid2 wrote:
                        My unit tests are then used as automated unit tests via a test suite. I can't even begin to think how much longer it would take for the tests to run if they were making trips to the database.
                        That has nothing to do with the discussion.

                        If your code has a problem with that then perhaps you should look to the code rather than the tests.
                        not necessarilly.
                        I've worked in environments where the development and test databases were running on seriously underpowered machines on seriously bandwidth restricted network segments.
                        For example a query running in 10 milliseconds on production to yield a 1000 rows might take 5 minutes on test to yield 10 rows.

                        Also, an automated test doing hundreds of thousands of database requests in a very short time might cause a flood of network traffic you don't want, when the application during normal operations might cause only a few hundred requests in the same period from any specific client.

                        Mocking a database operation can well be a good thing, and also allows you to test your application independent of its database, thus independent of data changes, changes in triggers and stored procedures, etc.
                        Of course it will need to be tested against the actual database as well, testing it exclusively against a mock isn't going to give you any guarantees that it's functional (for example, you're calling a stored procedure but get the order or names of some of the arguments wrong, or of some of the fields in the result).
                        • 9. Re: How to use DAO pattern
                          jschellSomeoneStoleMyAlias
                          jwenting wrote:
                          jschell wrote:
                          forumKid2 wrote:
                          My unit tests are then used as automated unit tests via a test suite. I can't even begin to think how much longer it would take for the tests to run if they were making trips to the database.
                          That has nothing to do with the discussion.

                          If your code has a problem with that then perhaps you should look to the code rather than the tests.
                          not necessarilly.
                          I've worked in environments where the development and test databases were running on seriously underpowered machines on seriously bandwidth restricted network segments.
                          That is certainly a relevant concern.

                          However it is not the basis of a best practice that one should apply to all testing.
                          For example a query running in 10 milliseconds on production to yield a 1000 rows might take 5 minutes on test to yield 10 rows.

                          Also, an automated test doing hundreds of thousands of database requests in a very short time might cause a flood of network traffic you don't want, when the application during normal operations might cause only a few hundred requests in the same period from any specific client.
                          If doing volume testing then one should manage the entire infrastructure such that it won't impact other operations significantly.

                          That is however a test management decision and applies to any volume tests of any type. For example if you run tests automatically on check in and you have a test that maxes the CPU for an hour that probably isn't a good thing either.
                          Mocking a database operation can well be a good thing, and also allows you to test your application independent of its database, thus independent of data changes, changes in triggers and stored procedures, etc.
                          Huh?

                          I see two possibilities.
                          - Those changes have no impact on the tests, and thus what is your point?
                          - Those changes do have impacts on the tests, and you are claiming that the tests are still valid? If still valid exactly what do you think is going to happen in production? And if they will impact the tests but you don't know it and your tests won't reveal it either - so how is that a good thing?
                          Of course it will need to be tested against the actual database as well, testing it exclusively against a mock isn't going to give you any guarantees that it's functional (for example, you're calling a stored procedure but get the order or names of some of the arguments wrong, or of some of the fields in the result).
                          Certainly if one tests the database layer against the database in the first place it meets all of those goals.