Library Version 12.1.3.0

Using MultiRowOptions to Retrieve Nested Tables
Prev | Chapter 7. Reading Table Rows | Next

## Using MultiRowOptions to Retrieve Nested Tables

When you are iterating over a table, or performing a multi-get operation, by default only rows are retrieved from the table on which you are operating. However, you can use `MultiRowOptions` to specify that parent and child tables are to be retrieved as well.

When you do this, parent tables are retrieved first, then the table you are operating on, then child tables. In other words, the tables' hierarchical order is observed.

The parent and child tables retrieved are identified by specifying a `List` of `Table` objects to the `ancestors` and `children` parameters on the class constructor. You can also specify these using the `MultiRowOptions.setIncludedChildTables()` or `MultiRowOptions.setIncludedParentTables()` methods.

When operating on rows retrieved from multiple tables, it is your responsibility to determine which table the row belongs to.

For example, suppose you create a table with a child and grandchild table like this:

    table create -name prodTable
    add-field -type STRING -name prodType
    add-field -type STRING -name typeDescription
    primary-key -field prodType
    exit
    plan add-table -name prodTable -wait

    table create -name prodTable.prodCategory
    add-field -type STRING -name categoryName
    add-field -type STRING -name categoryDescription
    primary-key -field categoryName
    exit
    plan add-table -name prodTable.prodCategory -wait

    table create -name prodTable.prodCategory.item
    add-field -type STRING -name itemSKU
    add-field -type STRING -name itemDescription
    add-field -type FLOAT -name itemPrice
    add-field -type STRING -name vendorUID
    add-field -type INTEGER -name inventoryCount
    primary-key -field itemSKU
    exit
    plan add-table -name prodTable.prodCategory.item -wait

With tables containing data like this:

- Row 1:

  prodType: Hardware
  typeDescription: Equipment, tools and parts

  - Row 1.1:

    categoryName: Bolts
    categoryDescription: Metric & US Sizes

    - Row 1.1.1:

      itemSKU: 1392610
      itemDescription: 1/4-20 x 1/2 Grade 8 Hex
      itemPrice: 11.99
      vendorUID: A8LN99
      inventoryCount: 1457

- Row 2:

  prodType: Tools
  typeDescription: Hand and power tools

  - Row 2.1:

    categoryName: Handtools
    categoryDescription: Hammers, screwdrivers, saws

    - Row 2.1.1:

      itemSKU: 1582178
      itemDescription: Acme 20 ounce claw hammer
      itemPrice: 24.98
      vendorUID: D6BQ27
      inventoryCount: 249

In this case, you can display all of the data contained in these tables in the following way.

Start by getting all our table handles:

    package kvstore.tableExample;

    import java.util.Arrays;

    import oracle.kv.KVStore;
    import oracle.kv.KVStoreConfig;
    import oracle.kv.KVStoreFactory;

    import oracle.kv.table.PrimaryKey;
    import oracle.kv.table.Row;
    import oracle.kv.table.Table;
    import oracle.kv.table.TableAPI;

    import oracle.kv.table.TableIterator;
    import oracle.kv.table.MultiRowOptions;

    ...

    private static Table prodTable;
    private static Table categoryTable;
    private static Table itemTable;

    private static TableAPI tableH;

    ...

    // KVStore handle creation is omitted for brevity

    ...

    tableH = kvstore.getTableAPI();
    prodTable = tableH.getTable("prodTable");
    categoryTable = tableH.getTable("prodTable.prodCategory");
    itemTable = tableH.getTable("prodTable.prodCategory.item");

Now we need the `PrimaryKey` and the `MultiRowOptions` that we will use to iterate over the top-level table. Because we want all the rows in the top-level table, we create an empty `PrimaryKey`.

The `MultiRowOptions` identifies the two child tables in the constructor's `child` parameter. This causes the iteration to return all the rows from the top-level table, as well as all the rows from the nested children tables.

    // Construct a primary key
    PrimaryKey key = prodTable.createPrimaryKey();

    // Get a MultiRowOptions and tell it to look at both the child
    // tables
    MultiRowOptions mro = new MultiRowOptions(null, null,
            Arrays.asList(categoryTable, itemTable));

Now we perform the iteration:

    // Get the table iterator
    // Exception handling is omitted, but in production code
    // ConsistencyException, RequestTimeException, and FaultException
    // would have to be handled.
    TableIterator<Row> iter = tableH.tableIterator(key, mro, null);
    try {
        while (iter.hasNext()) {
            Row row = iter.next();
            displayRow(row);
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

Our `displayRow()` method is used to determine which table a row belongs to, and then display it in the appropriate way.

    private static void displayRow(Row row) {
        // Display the row depending on which table it belongs to
        if (row.getTable().equals(prodTable)) {
            displayProdTableRow(row);
        } else if (row.getTable().equals(categoryTable)) {
            displayCategoryTableRow(row);
        } else {
            displayItemTableRow(row);
        }
    }

Finally, we just need the methods used to display each row. These are trivial, but in a more sophisticated application they could be used to do more complex things, such as construct HTML pages or write XSL-FO for the purposes of generating PDF copies of a report.

    private static void displayProdTableRow(Row row) {
        System.out.println("\nType: " +
            row.get("prodType").asString().get());
        System.out.println("Description: " +
            row.get("typeDescription").asString().get());
    }

    private static void displayCategoryTableRow(Row row) {
        System.out.println("\tCategory: " +
            row.get("categoryName").asString().get());
        System.out.println("\tDescription: " +
            row.get("categoryDescription").asString().get());
    }

    private static void displayItemTableRow(Row row) {
        System.out.println("\t\tSKU: " +
            row.get("itemSKU").asString().get());
        System.out.println("\t\tDescription: " +
            row.get("itemDescription").asString().get());
        System.out.println("\t\tPrice: " +
            row.get("itemPrice").asFloat().get());
        System.out.println("\t\tVendorUID: " +
            row.get("vendorUID").asString().get());
        System.out.println("\t\tInventory count: " +
            row.get("inventoryCount").asInteger().get());
        System.out.println("\n");
    }

Note that the retrieval order remains the top-most ancestor to the lowest child, even if you retrieve by lowest child. For example, you can retrieve all the Bolts, and all of their parent tables, like this:

    // Get all the table handles
    prodTable = tableH.getTable("prodTable");
    categoryTable = tableH.getTable("prodTable.prodCategory");
    itemTable = tableH.getTable("prodTable.prodCategory.item");

    // Construct a primary key
    PrimaryKey key = itemTable.createPrimaryKey();
    key.put("prodType", "Hardware");
    key.put("categoryName", "Bolts");

    // Get a MultiRowOptions and tell it to look at both the ancestor
    // tables
    MultiRowOptions mro = new MultiRowOptions(null,
            Arrays.asList(prodTable, categoryTable), null);

    // Get the table iterator
    // Exception handling is omitted, but in production code
    // ConsistencyException, RequestTimeException, and FaultException
    // would have to be handled.
    TableIterator<Row> iter = tableH.tableIterator(key, mro, null);
    try {
        while (iter.hasNext()) {
            Row row = iter.next();
            displayRow(row);
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

Prev | Up | Next
Specifying Field Ranges | Home | Reading Indexes