Creating a Java Bean from master Detail Table - Perfomance issue

Question

I am creating a java bean from a master detail table. Lets call the master table A and Detail table B. When I make this in to a java bean I have BeanA and BeanB. BeanA will have all the relavant rows in the A table and will also have a list collection of BeanB (detail table B) based on the primarykey. So BeanA will look like

class BeanA {
    String property1;
    String Property2;
    List<BeanB> lstpropery; //This is the data of detail table based on the primary key
}

In this scenerio I am using Spring JDBC to query the table. So when I query the table A and loop through the resultset and set the BeanA properties, I will also set the lstproperty by doing the query to B table.

so the code will be

String qry = "select * from A';
result =  this.queryTemplate.query(qry, obj, new RowMapper(){
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
       BeanA bean1= new BeanA();                         
       bean1.setProperty1(rs.getString(1));
       bean1.setProperty2(rs.getString(2));
       bean1.setLstpropery(callTableB(String primaryKey)); // I see a performance issue here
       return bean1;
    }
});

Is there a better way to do this?

Answer 1

If you have a chance to use Hibernate, then lazy-fetching concept might be useful here.

Answer 2

Use a left join :

select a.*, b.* from A a left outer join B b on a.id = b.aId

(You should in fact explicitely list all the columns and assign aliases to each of them).

This query will retrieve a row per couple A-B, and will also retrieve all the As which have no B.

For each row, see if you already have an A with the returned A ID. If not, construct the A and put it in a map (indexed by A ID). Then construct a B instance with the columns from the B table (unless the returned B ID is null, which means that the A has no B), and add it to the list of Bs of the A you got from the map or just constructed.

Note that ORMs like JPA make this tedious work much easier: they populate an object tree from a single request for you.