How do I map a resultset to a nested structure of objects?

Question

I have a result set like this…

+--------------+--------------+----------+--------+
| LocationCode | MaterialCode | ItemCode | Vendor |
+--------------+--------------+----------+--------+
|            1 |           11 |      111 |   1111 |
|            1 |           11 |      111 |   1112 |
|            1 |           11 |      112 |   1121 |
|            1 |           12 |      121 |   1211 |
+--------------+--------------+----------+--------+

And so on for LocationCode 2,3,4 etc. I need an object (to be converted to json, eventually) as : List<Location> Where the the hierarchy of nested objects in Location Class are..

Location.class
    LocationCode
    List<Material>

Material.class
    MaterialCode
    List<Item>

Item.class
    ItemCode
    Vendor

This corresponds to the resultset, where 1 location has 2 materials, 1 material(11) has 2 Items, 1 item(111) has 2 vendors.

How do i achieve this? I have used AliasToBeanResultTransformer before, but i doubt it will be of help in this case.

I don't think those are "nested classes" in the sense that Java uses that term. Or at least, they don't *need* to be nested classes. — Stephen C, Jun 17 '13 at 13:25
@Marco : I am stuck at a level where i don't know how to proceed from here, or what approach to use. So far, i only have the code to create a Query object and query.list(). — insanity, Jun 17 '13 at 13:27

Stephen C · Accepted Answer · 2013-06-17T13:41:32.443

I don't think there is a neat way to do that mapping. I'd just do it with nested loops, and custom logic to decide when to when to start building the next Location, Material, Item, whatever.

Something like this pseudo-code:

while (row = resultSet.next()) {
    if (row.locationCode != currentLocation.locationCode) {
        currentLocation = new Location(row.locationCode)
        list.add(currentLocation)
        currentMaterial = null
    } else if (currentMaterial == null ||
               row.materialCode != currentMaterial.materialCode) {
        currentMaterial = new Material(row.materialCode)
        currentLocation.add(currentMaterial)
    } else {
        currentMaterial.add(new Item(row.itemCode, row.vendorCode))
    }
}

How do I map a resultset to a nested structure of objects?

1 Answers1