Query API Walkthrough

The Weborders example is located in the root folder of the repository. We will use an HgDB instance built from the weborders package from this example project to demonstrate every aspect of the API.

#Single Table Queries#

Every query on an HgDB instance is done via static methods in the HgDB class in the org.mercurydb.queryutils package. For single table queries, we use the following method:

public static <T> HgStream<T>
query(AbstractFieldExtractablePredicate<T,?>... fieldPredicates)

As an example, we can query the Customer table like so to retrieve all Customer instances where the name (cname) is equal to “Bartholomew Jameson”:

HgStream<Customer> stream =
HgDB.query(CustomerTable.eq.cname(“Bartholomew Jameson”);

for (Customer c : stream) {
    // do something with c
}

By default 7 relations are statically defined in org.mercurydb.HgRelation: EQ : a==b NE : a!=b LT : a<b LE : a<=b GT : a>b GE : a>=b IN : a∈b

Every public field observed in the database has each of these relations defined for use in queries. Inequality relations require that the field type implements the Comparator interface, and the IN relation requires that the type of the element on the right hand side of the relation is a Java Collection.

Users are also free to define their own predicates. Note that in the following code segments we will mostly adhere to the Java 8 lambda syntax for simplicity. However, all HgDB code is backwards compatible down to Java 6. Observe the following stream declarations that result in equivalent streams as above:

// Java 7 syntax
HgPredicate<Customer> = new HgPredicate<Customer> {
    public boolean test(Customer value) {
        return value.cname.equals(“Bartholomew Jameson”);
    }
}
// Java 8 Syntax
// Using Customer predicate, same as above
HgPredicate<Customer> cPred = c -> c.cname.equals(“Bartholomew Jameson”);
HgStream<Customer> stream = HgDB.query(CustomerTable.predicate(cPred));

// Using String predicate on field cname
HgPredicate<String> nPred = cname -> cname.equals(“Bartholomew Jameson”);
stream = HgDB.query(CustomerTable.predicates.cname(nPred));

The above code demonstrates that we may define a predicate over a field specifically via the predicates class member of the table, or we may define a more general predicate over the containing class of the field via the predicate method.

Looking back at the prototype of the query method, we see that the method accepts a variable number of predicates. There is an implicit and relation between all of the arguments, such that an entry in the table must pass each of the predicates passed as arguments to be included in the resulting stream. The query method will seed the result with the smallest indexed argument, if there is one, and then perform a filter operation for the rest of the arguments. In order to or the results of two queries, we can either define a custom predicate or concatenate query results using the HgStream’s concat method. Both methods are shown below:

HgStream<Order> stream =
HgDB.query(OrderTable.predicates.ono(o -> o == 1020 || o == 1021));

stream =
HgDB.query(OrderTable.eq.ono(1020))
.concat(HgDB.query(OrderTable.eq.ono(1020));

One other way of further filtering results manually is to use HgStream’s filter method. As an example we will retrieve an unfiltered stream of Order instances using the Order table’s stream() method explicitly and filter on records in which the order number is equal to 1020.

HgStream<Order> stream = 
OrderTable.stream().filter(OrderTable.eq.ono(1020);

#Multi-Table Queries (Joins)#

Multi-Table Queries, or table “joins”, are performed using a set of statically defined methods in the org.mercurydb.HgDB class. We will start by observing a simple join of the Order table and Order Detail table on the order number field present in both classes:

HgTupleStream stream =
HgDB.join(OrderTable.on.ono(), OdetailTable.on.ono());

For those accustomed to relational database queries in SQL, this is functionally equivalent to:

SELECT * FROM ORDERS INNER JOIN ODETAILS ON ORDERS.ONO=ODETAILS.ONO

All join operations in HgDB are inner joins. That is, joins return all records where the results from table 1 intersect with table 2. The default relation for the join is equality, and in the above query the relation was omitted. As a third argument to the join, we can use any of the 7 defined relations in HgRelation, or we can also define our own custom predicate. We will rewrite the above query in a couple of ways:

// With the explicit equality relation as the third argument
HgTupleStream stream =
HgDB.join(OrderTable.on.ono(), OdetailTable.on.ono(), HgRelation.EQ);

// With a custom predicate
HgBiPredicate<Integer, Integer> eqPred = (o, od) -> o == od;
stream = HgDB.join(OrderTable.on.ono(), OdetailTable.on.ono(), eqPred);

Like the HgPredicate class for predicates involving records from single table queries, we define an HgBiPredicate class for defining custom predicates involving records from two tables.

Joins involving more than two tables must be performed by wrapping the join arguments in JoinPredicate instances or by feeding the resulting HgTupleStream of one join result into another join. In the first case HgDB will try and perform the joins in an optimal order. In the second case the order is defined by the user. The first case is shown below:

// Using the HgDB optimizer
HgTupleStream stream =
HgDB.join(
    new JoinPredicate(
        OrderTable.on.ono(), 
        OdetailTable.on.ono()),
    new JoinPredicate(
        CustomerTable.on.zipcode(), 
        OdetailTable.on.storezip()),
    new JoinPredicate(
        ZipcodeTable.on.zipcode(),
        CustomerTable.on.zipcode()));

This is functionally equivalent to the following SQL:

SELECT * FROM ORDERS, ODETAILS, CUSTOMERS, ZIPCODES
  WHERE ORDERS.ONO=ODETAILS.ONO
    AND CUSTOMERS.ZIPCODE=ODETAILS.STOREZIP
    AND ZIPCODES.ZIPCODE=CUSTOMERS.ZIPCODE

Notice that in the three JoinPredicate instances in the query, the third implicit argument to specify the relation or predicate is omitted. Other relations and predicates could be specified as well.

Also notice that the JoinPredicates must unify and be compatible with one another for the method to work correctly, otherwise the method will throw an exception to the user.

Now we must discuss how elements are retrieved from the HgTupleStream instances returned by join operations in HgDB. An HgTupleStream is essentially an HgStream<HgTuple> with additional functionality for defining join metadata, like the field we are joining on. There is an ID system in place for retrieving elements from HgTuple instances. By default every table has a publicly defined TableID instance named ID that is globally unique. Using these id’s we can retrieve elements from a tuple like so:

HgTupleStream stream =
HgDB.join(OrderTable.on.ono(), OdetailTable.on.ono());

for (HgTuple t : stream) {
    Order o = t.get(OrderTable.ID);
    Odetail od = t.get(OdetailTable.ID);
}

There is also an aliasing system in place that is necessary for performing self joins. An example is shown below:

TableID<Part> partAlias = PartTable.createAlias();
HgTupleStream stream =
HgDB.join(
    PartTable.on.price(), 
    PartTable.on.price(partAlias), 
    HgRelation.LT);

for (HgTuple t : stream) {
    Part p1 = t.get(PartTable.ID);
    Part p2 = t.get(partAlias);
    assert(p1.price < p2.price);
}

We allow the client to continue to make permanent identifiers until the first alias is created. In this way it is encouraged that if an alias is to be used often another permanent identifier be created instead.