populating bean objects from the resultset

Question

I am facing a problem regarding populating bean objects from the resultset.

Description:Resultset contains the result of a stored procedure which is join of 3 tables B, BO, and BOV.

I have 3 POJO's corresponding to tables. The relation between tables is: B can have 0 or more BO's, BO can have 0 or more BOV's. So totally in resultset I have 162 records, which contain duplicates for B.

For example:

B  BO   BOV
1  1     1
1  1     2
1  2     1
2  1     1 

and so on.

Actually there are 10 distinct B's. So II want only 10 B's from the resultset not 162 records. Also II should be able to get corresponding BO and BOV's like for B=1 all values of BO and BOV's.

How can I do this? This is pure java logic and cannot change anything for the stored procedure. Just have to process the resultset.

Answer 1

Changing the query to include a GROUP BY is much the best option.

Answer 2

Have a running Map of the resultset here is a possible pseudocode

[pseudo code only... not guaranteed to compile]

Map mapofBs = new HashMap();
while(rs.hashNext()) {
    rs.next();
    String bId = rs.getString("columnname for id of b");
    B objectB = mapofBs.get(bId);
    if(objectB == null) {
        objectB = new B();
        //read relevant columns from result set and put into objectB
        mapOfBs.put(bId, objectB)
    }   
    //now onto the boId
    String boId = rs.getString("columnname for id of BO");
    BO objectBO = objectB.getBOForId(boId);
    if(objectBO == null) {
        objectBO = new BO();
        //read relevat columns from result set for objectBO
        objectB.addObjectBO(objectBO);
    }
    String bovID = s.getString("columnname for id of BOV");
    BOV objectBOV = objectBO.getBOVForId(bovId);
    if(objectBOV == null) {
        objectBOV = new BOV();
        //read relevat columns from result set for objectBOV
        objectBO.addObjectBOV(objectBOV);
    }
}
//mapOfBs.keySet() gives you a Set<B> which you are interested in