Table join in DAO and delegating ResultSet reading I'm wondering if (and how) this idea could come to bite me in the future.

I was thinking that I should take away the responsability from any given DAO to know how to load a different object from a joined resultset, besides from not needing to know the names used in the database itself.

(Here is a very simplified - working - )

Example

Let's say I have Cards and Clients. Not really important but, each client will only be able to use one card at a time.

The join example is in CardDAO -> getCardList() method.

Tables

Card Table Data


+---------+----------------+
| card_id | card_client_id |
+---------+----------------+
|       1 | 1              | // Card 1 used by client 1
|       2 | NULL           | // Card 2 available
+---------+----------------+


Client Table Data


+-----------+-------------+
| client_id | client_name |
+-----------+-------------+
|         1 | Client X    |
|         2 | Client Y   |
+-----------+-------------+


Codes

Simplified and reduced (Exceptions, Connections, closes(), etc). Also, Card and Client classes are just POJOs, like so:

public class Client {
    private int id;
    private String name;
    // Getters &amp; setters
}

public class Card {
    private int id;
    private Client client;
    // Getters &amp; setters
}


ClientDAO

public class ClientDAO{
    public static final String table = "tb_client";
    // Column names as defined in the table
    public static final String id = "client_id";
    public static final String name = "client_name";

    // Other methods    

    public static Client loadClient(ResultSet rs){
        Client client = new Client();
        client.setId(rs.getInt(ClientDAO.id));
        client.setName(rs.getString(ClientDAO.name));
        return client;
    }
}


CardDAO

public class CardDAO{
    public static final String table = "tb_card";
    // Column names as defined in the table
    public static final String id = "card_id";
    public static final String client = "card_client_id";

    // Other methods    

    public List&lt;Card&gt; getCardList(){
        List&lt;Card&gt; cards = new ArrayList&lt;Card&gt;();
        // Sql could be just concat too
        String sql = String.format("SELECT ca.*, cl.* FROM %s AS ca INNER JOIN %s AS cl ON ca.%s = cl.%s WHERE ca.%s is not null",
                CardDAO.table,
                ClientDAO.table,
                CardDAO.id,
                ClientDAO.id,
                CardDAO.client);

        PreparedStatement pstn = this.con.prepareStatement(sql);
        ResultSet rs = pstn.executeQuery();
        while (rs.next()) {
            cards.add(CardDAO.loadCard(rs));
        }
        return cards;
    }

    public static Card loadCard(ResultSet rs){
        Card card = new Card();
        card.setId(rs.getInt(CardDAO.id)); // Don't know the column name, it doesn't matter here
        // CardDAO doesn't need to know how to load Client, just 'SELECT' everything and delegate a loader
        card.setClient( ClientDAO.loadClient(rs) );
        return card;
    }
}


On a side note, this would be the generated query:

SELECT ca.*, cl.* FROM tb_card AS ca INNER JOIN tb_client AS cl ON ca.card_id = cl.client_id WHERE ca.card_client_id is not null



Some things I already thought:


Increased complexity when writing queries;
Need to always load all the columns from a given table to make it possible for the loadXClass method to be called (can't select specific fields, only everything);
What if there are more levels to it? Example: the client has an address that is linked to an Address table, so I'd have to include it in the query and join every time so the code doesn't fail, even when not using it.


What are your thoughts?
I was thinking that I should take away the responsability from any given DAO to know how to load a different object from a joined resultset


Well, I would have made a view which does the selection and the dao would then do the mapping, or rather the disassembly and re-assembly of the resultSet (get all Cards, extract Clients, set the Client to the corresponding Card). So at least it's only one select.

Or you could introduce a new type in a layer 'above' both dao's, which does the mapping.

To your thoughts:


  Increased complexity when writing queries


Yeaaah, that's why people tend to use existing ormappers ;) - you might want to introduce some static methods in your base dao, e.g. all() and join(String table, String condition), so the writing is more fluent and it's better to read... you might want to check the Criterion API/impl of hibernate, it's quite nice to write and read, but that's quite the work, if you really want to implement your own ormapper with a certain amount of sql features.

And: Most important: It's your job to make a class or a method readable. In test cases, I even write a static tomorrow() method, which creates a new Date and adds a day. Of course, you have to write a lot in java, but that must not be the excuse, to make your code not readable.


  Need to always load all the columns from a given table to make it possible for the loadXClass method to be called (can't select specific fields, only everything);


I really think the 'only select data you need' paradigma is quite out dated. The management of the 'choose what columns' thingy, the 'be careful because its not null in the backend but someone might not have loaded that column' and the inconvenient increase of execution plans on your backend - which has to be managed, too - is in my opinion much more expensive compared to just select all (= upgrade the switch or add another cpu to the backend server)


  What if there are more levels to it? Example: the client has an address that is linked to an Address table, so I'd have to include it in the query and join every time so the code doesn't fail, even when not using it.


... I think a view with a dao responsible for that case is the way to go ;-)

To be honest, in ormappers, I do not like the represent the complete erd in java objects approach. Maybe I just didn't get it, but sometimes it gets quite horrible. I mean, you have a Customer, the Customer has a List of Addresses, a List of Orders, a List of PhoneNumbers, a List of Items in the Shopping Card, a List of open bills and and and. And everything is present in the type of hell. And it gets worse, because all those dependencies have other dependencies, too. I found, that an app, at least above a certain size and complexity, is much more maintainable, if "those" problems are taken care of with a certain degree of isolation, even though that means, there is some redundancy, maybe a ClientAddress type and a ClientOrders type, instead of a "one size fits all" approach.

Hope that helps,...