PriDE Design Patterns: Joins

A key feature of SQL databases is the ability to extract data of related tables in a single call (this is actually what makes them 'relational'). The SQL term for this feature is the so-called join. As a difference to most other O/R mapping toolkits, PriDE simply treats a join as an entity type being assembled from the data of multiple tables. It just has to be considered that join entities are not suitable for updates and that the relationship rule must somehow be provided by the application. PriDE provides several different ways to build joins., depending on the application needs.

Dedicated entity types

The following example demonstrates an entity class for a join from a customer and an order table, providing an individual selection of fields from both tables and making use of a foreign-key relationship:
 
// Joined entity type providing customer ID, customer name,
// order ID and order volume from the tables 'customer' and
// 'orders' in one query
public class CustomerOrder extends MappedObject
{
    // The table relation to consider in all queries
    private static final String JOIN = " AND customer.id=orders.customer_id";

    protected static RecordDescriptor red = new RecordDescriptor
        (CustomerOrder.class, "customer,orders", null, new String[][] {
            { "customer.id",   "getId",      "setId"      },
            { "customer.name", "getName",    "setName"    },
            { "orders.id",     "getOrderId", "setOrderId" },
            { "orders.volume", "getVolume",  "setVolume"  },
        });
    protected RecordDescriptor getDescriptor() { return red; }

    //...

    // Returns the list of all orders for the specified customer
    public ResultIterator query(int customerId) throws SQLException {
        id = customerId;
        return super.query(constraint() + JOIN);
    }
}

If the relationship rule becomes more complex, it is recommended to use SQLExpression for a safer expression assembly. Of course this would often require field identifiers as right-hand side operators and thus a suppression of the standard SQL formatting. Using the helper class SQLRaw ommits any formatting of the provided value. The following example shows how to assemble the relationship rule above using an SQLExpression:
 
// Empty SQLExpression
SQLExpression join = new SQLExpression(DatabaseFactory.getDatabase());
// Join criteria using SQLRaw to suppress SQL formatting of "orders.customer_id"
join = join.and("customer.id", new SQLRaw("orders.customer_id"));
// join.and above didn't actually produce an "AND" as the expression
// was formerly empty. So we add it explicitely here
String JOIN = "AND " + join.toString();

Distinct

An important variant for join queries is to remove duplicates in the result set by adding the qualifier "DISTINCT" to the query expression. As join types can only be used for read operations, a distinct join in PriDE could simply be expressed by adding the keyword to the very first field identfier in the corresponding record descriptor, e.g.

    protected static RecordDescriptor red = new RecordDescriptor
        (CustomerOrder.class, "customer,orders", null, new String[][] {
            { "DISTINCT customer.id",   "getId",      "setId"      },
            { "customer.name", "getName",    "setName"    },
            { "orders.id",     "getOrderId", "setOrderId" },
            { "orders.volume", "getVolume",  "setVolume"  },
        });
    protected RecordDescriptor getDescriptor() { return red; }


Allthough this solution actually works, it is of course conceptionally questionable (it would cause troubles e.g. when having a base descriptor specified). A safer way is e.g. to use a specialized record descriptor as demonstrated in the following example.

public class DistinctRecordDescriptor extends RecordDescriptor {
    public DistinctRecordDescriptor(
        Class objectType, String dbtable,
        RecordDescriptor baseDescriptor, String[][] attributeMap)
        throws IllegalDescriptorException {
        super(objectType, dbtable, baseDescriptor, attributeMap);
    }

    protected String getResultFields() {
        return "DISTINCT " + super.getResultFields();
    }
}

The code above is suitable for any distinct join definition and will probably become a core PriDE class in the future. It is simply used instead of the standard RecordDescriptor type, performing a distinct selection, e.g.

    protected static DistinctRecordDescriptor red = new DistinctRecordDescriptor
        (CustomerOrder.class, "customer,orders", null, new String[][] {
            { "customer.id",   "getId",      "setId"      },
            { "customer.name", "getName",    "setName"    },
            { "orders.id",     "getOrderId", "setOrderId" },
            { "orders.volume", "getVolume",  "setVolume"  },
        });
    protected RecordDescriptor getDescriptor() { return red; }

Entity classes for the data from multiple tables can automatically be created by the entity generator mentioned in the introduction. The tables of interest are passed as a comma-separated list instead of just passing a single table name. Another way to work with joins is the configuration of views in the database which can be accessed within PriDE like an ordinary table.

Extended queries

In many cases, a join simply queries a table which is already mapped but has to take this table's relationship to other tables into account. For these cases, PriDE provides an appropriate copy constructor for record descriptors. The following example shows a derivation of the customer entity from the introduction chapter, making use of this feature. It retrieves all customers having ordered something in the past which requires to take a look on the related order table without actually returning any data from it:

// Extended customer type to retrieve "active" customers
public class ActiveCustomer extends Customer {
    // An inner join to use for retrieval
    private static final String JOIN = "customer c inner join orders o on o.customer_id = c.id";

    // An extended record descriptor specifying the alias "c"
    // and the alternate table expression JOIN
    protected static RecordDescriptor red = new RecordDescriptor(Customer.red, "c", JOIN);

    protected RecordDescriptor getDescriptor() { return red; }
}

Complex entity types

Another reason for the use of joins is the reduction of database interactions by retrieving the data of multiple tables in a single step. In this case, the query results can often be represented by an aggregation of already mapped types. Using the copy constructor for records descriptors as shown above, also allows to build specialized descriptors for complex entity types. The following example shows a descriptor retrieving customers and their potential last order, using an outer join with the order table:

public class JoinedCustomer extends Customer {

    private static final String JOIN =
        "customer c left outer join orders o on o.customer_id = c.id";
     
    private Order lastOrder;
    public Order getLastOrder() { return lastOrder; }
    public void setLastOrder(Order lastOrder) { this.lastOrder = lastOrder; }

    protected static RecordDescriptor red = new JoinedDescriptor();
    public RecordDescriptor getDescriptor() { return red; }

    /**
     * Specialized record descriptor for selecting customers and their
     * potential last order by a single SQL command using an outer join.
     */
    protected static class JoinedDescriptor extends RecordDescriptor {
        private RecordDescriptor orderDesc = new RecordDescriptor(Order.red, "o");
        public JoinedDescriptor() { super(Customer.red, "c", JOIN); }
        
        protected String getResultFields() {
            return super.getResultFields() + ", " + orderDesc.getFieldNames(null);
        }
        
        public int record2object(Object obj, ResultSet results, int position)
            throws SQLException, IllegalAccessException, InvocationTargetException {
            JoinedCustomer jc = (JoinedCustomer)obj;
            position = super.record2object(obj, results, position);

            // Check for existence of an order part in the result record
            results.getObject(position);
            if (!results.wasNull()) {
                jc.lastOrder = new Order();
                orderDesc.record2object(jc.lastOrder, results, position);
            }
            else
                jc.lastOrder = null;
            position += orderDesc.totalAttributes();
            return position;
        }
    }
    
}

The source code of the examples above can be found in examples/join.

Home Introduction Javadoc