Hierarchical Navigation

Nesting Retrieval Loops
EntityClass Helper Methods
The SQL Equivalent

Nesting Retrieval Loops

We have shown a simple query in AndSpaces and OrSpaces, but the results of it was a single series of Items. We can also easily navigate a database directly, in a hierarchical way, and we can even combine AndOrSpace query capabilities with hierarchical navigation. Let's visit a certain supplier using EntityClass SUPPLIER, and for each value of the SUPP_ORDER Attribute, visit the ORDER's LINE_ITEM's. The report we need to print has some info about an order at the top of the page, then some rows about each line item.

We make use of a next(ItemSpace db, Cu entity, Attribute attribute, Cu value) helper method on EntityClass to simplify things, although all of this can be done with just the lowest-level operations in a somewhat more verbose way.

package com.infinitydb.manual;

import java.io.IOException;

import com.infinitydb.Attribute;
import com.infinitydb.Cu;
import com.infinitydb.EntityClass;
import com.infinitydb.ItemSpace;

public class HierarchicalAccess {
	static final EntityClass SUPPLIER = new EntityClass(0);
	static final EntityClass ORDER = new EntityClass(1);
	static final EntityClass LINE_ITEM = new EntityClass(2);
	static final Attribute SUPP_ORDER = new Attribute(0);
	static final Attribute ORDER_LINE_ITEM = new Attribute(1);
	static final Attribute QUANTITY = new Attribute(2);

	public static void printOrdersOfSupplier(ItemSpace db, long supplierId) throws IOException {
		Cu cuSupplier = Cu.alloc().append(supplierId); // we start with given supplierId
		Cu cuOrder = Cu.alloc(); // Will contain Items with one, long component
		while (SUPPLIER.next(db, cuSupplier, SUPP_ORDER, cuOrder)) {
			// Now cuOrder contains a particular order's id,
			// no matter what its primitive type or whether it is composite and so on.
			long orderId = cuOrder.longAt(0); // we choose to treat order id as just a long
			// Use orderId to print facts about the order at the top of the page
			//..
			Cu cuLineItem = Cu.alloc();
			while (ORDER.next(db, cuOrder, ORDER_LINE_ITEM, cuLineItem)) {
				long lineItemId = cuLineItem.longAt(0);
				// Use lineItemId to print facts about line item in rows
				// This getLong() helper gets the value of the single-valued attribute QUANTITY
				long quantity = LINE_ITEM.getLong(db, cuLineItem, QUANTITY, 0/*default*/);
			}
			cuLineItem.dispose();
			// do other nested loops
			//..
		}
		cuSupplier.dispose(); // for speed
		cuOrder.dispose();
	}
	// ..
}

EntityClass Helper Methods

The next(ItemSpace db, Cu entity, Attribute attribute, Cu value) helper method for an EntityClass builds an Item in a temporary Cu and accesses the db with it, handling the concatenation of the EntityClass, Entity, Attribute, and current Value and so on. It changes the value Cu to be the next Value in sequence, based on the other parameters. As usual, check the javadoc for more - in this case the effective implementation code itself.

Because the entity and value parameters are Cu's, they can contain any components at all, so they will work for any component type, and for composite, heterogenous, or variable-component-count entities and values.

The EntityClass.getLong(ItemSpace db, Cu entity, Attribute attribute, long defaultValue) helper gets a single long value for us, again hiding most of the work.

The SQL Equivalent

To do the above in SQL we can:

Retrieve one table having one row per line item, ordering by order and line item, and then read the table while watching for the order number to change to tell us when to starting printing a new order. This method does not work when the nesting is tree-structured. It is also cumbersome and can be unclear.
Query to get a table of orders and then loop, doing multiple queries, one query per order to get its line items. This incurs the SQL round-trip time of several milliseconds for each order, and requires considerable JDBC code.
Query each row in a separate SELECT.
Use Object-Relational tools to transform tables into and back from Java Objects and collections. The O/R tools can be burdensome (using XML descriptors and so on) and all operations require transferring entire collections as a unit whether all of the data is required or not. Collections are limited to memory size. Also, under the hood, the O/R system must still choose one of the preceding options.

Previous Next