Designing a DAO model for an existing project

Go To StackoverFlow.com

3

Most of the DAO examples I've seen consist of simple queries only involving one table.

I'm working on refactoring a project with no DAO that has lots of SQL queries where multiple tables are used. My question is how to best design the model for the DAO? In the examples below I could create a object that covers each specific query. However I'm uncertain as to whether this is good practise or not. e.g. is it better to have one object representing each db table?

CustomerPaymentDAO to cover this query:

select
    a.username,
    p.creation_date,
    p.amount,
    c.card_type
from
    account      a,
    payment      p,
    payment_type t,
    payment_card c
where
...

CustomerPurchaseDAO to cover this query:

select
    a.username,
    i.name,
    i.cost,
    c.name,
    v.value
from
    account      a,
    item         i,
    category     c,
    voucher      v
where
...
2012-04-03 20:09
by Andy Longwill
In OO, DAO usually returns an object or a collection of that object and the DAO is linked to it. Then you navigate the relationships to get values from related objects. But you are free to do what seems the most logical. As long as the DAO layer encapsulate the technical mean used to access the data (an excel file, a Database through Hibernate, a DB through JDBC - Guillaume Polet 2012-04-03 20:12


1

Generally speaking, there are two options:

  1. Create an entity corresponding to each table and specify necessary relationships (many-to-many, many-to-one, one-to-one).

  2. In the database create a view for each query, and create entities on per view basis (in your example - two views + two entities).

The second case is fine for read-only objects. If you need to create/update/delete entities then you need to create an entity corresponding to each single table.

2012-04-03 20:26
by Eugene Retunsky