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
...

• java
• dao

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.