Retrieving database data - General retrieve method?

Question

Hello,

My data access classes often have a method called loadDataToEntity or something similar. This method takes a resultset for instance and populates an entity class which is passed on to be used by the client.

This is nice because all methods which use a stored procedure to retrieve data can just pass the resultset to the loadDataToEntity method and get back an entity.

So it's a good way to reduce code but I often hear that you should retrieve as few columns as possible. To use my general method all stored procedures needs to fetch all columns needed for loadDataToEntity(). Also, when a column is added to the table and is retrieved by a stored procedure all other stored procedures needs to be altered and also retrieve that column.

I'm wondering if there's a better way to both achieve a general method and also retrieve different number of columns sometimes depending on the stored procedure?

 
public Entity getById(int id)  
{    
    ResultSet rs;
    //call stored procedure A and stuff  
    return loadDataToEntity(rs);  
}  
public Entity getByName(String name)
{
    ResultSet rs;
    //call stored procedure B and stuff
    return loadDataToEntity(rs);
}
public Entity loadDataToEntity(ResultSet rs)
{
    // fill and return entity here
}
 

Answer 1

It really depends on how you've organised the different categories of entity within your database and how you're retrieving them. For example, if you have one large denormalised table that can contain many entity types with each type requiring a different combination of columns being populated then you will have to retrieve all columns if you wish to retrieve all entity types in a single query.

Conversely if you've organised your database schema in a normalised form you might well have a parent entity table (which might simply contain entity ID) and multiple sub-tables, one per entity type. In this case you could run multiple bespoke queries to retrieve the various entity types; e.g.

select E.EntityId, S.EntityName, S.EntityAge
from Entity E
inner join SubEntity S on E.EntityId = S.EntityId

or

select E.EntityId, A.EntityAddress, A.EntityGender
from Entity E
inner join AnotherSubEntity A on E.EntityId = S.EntityId

The trade-off with this second approach is the need to run multiple queries, which could in fact be slower.