DAO involving a JOIN query

Question

I am new to the world of DAO. I have 3 tables.

• Consumer
• Bill
• Receipt

Consumer table contains following fields

• consumer_id
• consumer_name
• address

Bill table contains

• bill_number
• bill_date
• consumer_id
• bill_amount

Receipt table contains

• receipt_no
• pay_date
• paid_amount
• consumer_id

Bill table has a many to one relationship with Consumer table, Receipt table also has a many to one relationship with Consumer table.

Currently I have created three classes

1. Consumer
2. Bill
3. Receipt

and created DAOs for them like ConsumerDAO, BillDAO, ReceiptDAO. They contains basic CRUD operations.

Now I want a listing of a consumer involving data from these three tables. I am using a JOIN SQL query for the same. It is as follows:

SELECT c.consumer_id,
       c.consumer_name,
       c.address,
       r.receipt_no,
       r.pay_date,
       r.paid_amount,
       b.bill_date,
       b.bill_number,
       b.bill_amount
FROM consumer c
LEFT OUTER JOIN
  (SELECT r.consumer_id,
          r.pay_date,
          r.receipt_number,
          r.paid_amount,
          ROW_NUMBER() OVER (PARTITION BY r.consumer_id
                             ORDER BY r.pay_date) AS rank
   FROM receipt r) r ON (c.consumer_id = r.consumer_id
                         AND r.rank = 1)
LEFT OUTER JOIN
  (SELECT b.consumer_id,
          b.bill_number,
          b.bill_date,
          b.bill_amount,
          ROW_NUMBER() OVER (PARTITION BY b.consumer_id
                             ORDER BY b.bill_date) AS rank
   FROM bill b) b ON (c.consumer_id = b.consumer_id
                      AND b.rank = 1)";

I was wondering in which DAO I should place this query ? I thought to add a Bill field and a Receipt field in the Consumer class and add a getCurrentBillAndReceipt() method in the ConsumerDAO. Is it the right way to do ?

I read the following question:

how to create a DAO for join tables

and some more, but I was not able to get the idea.

Answer 1

Instead of trying to make a query like this every time you need access to the most recent Bill/Receipt of each of your consumers, what if your Consumer POJO became the following:

public class Consumer {
    private Integer consumerId;
    private String consumerName;
    private String address;
    // join data into these variables
    private Set<Bill> bills = new Set<Bill>(0);
    private Set<Receipt> recipts = new Set<Receipt>(0);

    // add constructor and getter/setter methods here
}

Then, in your ConsumerDAO, your find methods could have the following logic:

public class ConsumerDAO {
    /*
     * Find a consumer without any transaction data JOINed in
     */
    public Consumer find(Integer consumerId){
        // create a new Consumer object
        // run SQL query to populate consumerId, consumerName, and address fields of new Consumer object
        // return new Consumer object
    }

    /*
     * Find a consumer with all transaction data JOINed in
     */
    public Consumer findWithTransactionData(Integer consumerId){
        // create new consumer object
        // run a query to get the the basic fields of the Consumer POJO, that also uses
        // JOINs to get all of the consumer's Bills and Receipts; store all of the latter
        // in the Consumer object's Sets
        // return consumer object
    }

    /*** Note that the following two methods could even go in the Consumer class at this point ***/
    public Bill getMostRecentBill(Consumer consumer){
        // iterate through the consumer's bills, and return the most recent one
    }

    public Receipt getMostRecentReceipt(Consumer consumer){
       // iterate through the consumer's receipts, and return the most recent one
    }
}

Is this what you're trying to do? I think it would make the most sense to put the "find most recent" methods in the Consumer class itself, because you then wouldn't have to pass anything to the method as a parameter; you could just call getMostRecentBill() on the Consumer object that you get from findWithTransactionData(Integer) if you wanted to. This is generally considered to be a poor practice, though, because the POJO is supposed to be as "dumb" as possible. Thus, how you want to handle that is up to you.