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 & setters
}
public class Card {
private int id;
private Client client;
// Getters & 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<Card> getCardList(){
List<Card> cards = new ArrayList<Card>();
// 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?