Better ORQL

Towards a Better ORQL

The desire for an effective ORQL is obvious; the difficulties legendary. While the problem has long existed, there has been very little real progress. Even with the recent publication of the JDO2.0 and EJB3.0 specifications, there seems to be little significant improvement; the primary development -- and source of controversy -- is more drawn to annotations versus XML rather than any core improvement in the ORQL itself.

The fundamental difficulty, of course, is that Java and similar languages are object-oriented and procedural, while the query component of SQL is ideally declarative and semantically literal. The euphemism impedance-mismatch hardly does justice to the conceptual divide.

In essence, existing ORQLs lack the appearance of having been designed for productivity and maintainability. Typically, the existing ORQL grammars require metadata and SQL fragments to be embedded in unchecked string literals: each query code block is a mass of dependencies impervious to automatic refactoring. Moreover, these literals are scattered as parameters in complexes of query component classes and then further scattered throughout the modules of a project. The sacrifices of type-safety, clarity, and maintainability are beyond regrettable.

So, we propose a refined ORQL -- the Inductor IQL -- that is statically type-checked, uses well-defined established patterns, and has a syntax that is clear, readable, and familiar: literally a SQL query in Java.

Exemplary Query

Collection findProduct(Integer partno, String[] wareHouse) {
     Query q = new Query(new JdoDriver());
     Part part = new Part();
     q.from(part);
     q.where(part.partnumber.eq(partno), 
             part.quantity.gt(0), 
             part.warehouse.in(wareHouse));
     q.orderBy(part.warehouse.asc());
     return q.execute();
}

Field-Name Classes

The IQL implementation follows familiar object rules and implements a syntax that literally reads as a purely declarative SQL statement. No strings. No significant artifacts of the persistence manager implementation. The result is a concise, readable, and maintainable query statement.

The Inductor query API is essentially defined by just two classes, Query and Field. The Query class defines the familiar SQL select, from, where, orderBy, etc. constructs. The Field class defines an equally familiar set of field constraints.

An additional utility class is used to encapsulate all of the details of the persistence manager implementation: JdoDriver() and HibDriver() drivers are currently available.

public class Parts extends AbstractFieldClass {
     // the persistence-capable class reference
     public Class record = Inventory.class;
     // field-name Fields
     public Field partnumber = new Field(record, Inventory.PARTNUMBER);
     public Field quantity   = new Field(record, Inventory.QUANTITY);
     public Field warehouse  = new Field(record, Inventory.WAREHOUSE);
     ...
     // the view-name Field
     public Field qbe = new Field(record);
     // a cumulative list of the contained fields
     public Field[] fields = { partnumber, quantity, warehouse, ..., qbe };
}

Persistence Capable Classes

The preferred pattern is to define the field-name references in the corresponding persistence-capable class:

public class Inventory {
     // field-name references for use by the Field-Name class
     public static final String PARTNUMBER = "partnumber";
     public static final String QUANTITY = "quantity";
     public static final String WAREHOUSE = "warehouse";
     ...
     // standard persistence field declarations
     private String partnumber;
     private String quantity;
     private String warehouse;
     ...
     // standard getters and setters
     ...
}

The field name reference strings are defined in the persistence-capable classes. At the moment, this represents the only gap in end-to-end type-checking. Since the field name references are defined in the same file as the fields they name, the exposure is minimal. The use of annotations and/or the template generation of the persistence-capable and field-name classes will further reduce the exposure.

QBE and Views

A QBE operation using Inductor is straight forward.  Given a Person persistence-capable class object that is initialized with a QBE selection pattern to select all persons with B and R as first and last initials ...

Person myPerson = new Person();
myPerson.setFirstName("B");
myPerson.setLastName("R");

... and a corresponding Pers field-name class:

Pers pers = new Pers();

... the Inductor QBE where clause is then simply specified as:

q.where(pers.qbe.like(myPerson));

The non-null fields in the exemplar object – myPerson – are logically matched using .like selection criteria.

The scope of the qbefield of a field-name class is effectively defined by the fields.  Specifically, Inductor interprets the qbe field as representing all of the Fields within the field-name class.  Only the persistent fields represented by Fields within the field-name class participate in the QBE operation.  The field-name class thus defines a view onto the corresponding persistence class, particularly where the field-name class declares Fields for a subset of the persistence fields of its persistence class.  Multiple, differently composed field-name classes can be declared for a given persistence-capable class, each thus representing a different view.

Inheritance

Persistence packages typically support an inheritance hierarchy of persistence-capable classes.  The Inductor field-name classes can be directly structured in an equivalent inheritance hierarchy.

// field-name class extends a base field-name class
public class Empl extends Pers {

    // Given that Employee.class extends Person.class
    public Class record = Employee.class;

    // field-name Fields 
    public Field title = new Field(record, Employee.TITLE);
    public Field dept = new Field(record, Employee.DEPARTMENT);
    ...

    // the view-name Field 
    public Field qbe = new Field(record);

    public Field[] fields = { title, dept, ..., qbe };
}

Initialization supported through the AbstractFieldClass ensures that the visible instance in an inheritance hierarchy of field-name classes contains all of the Fields, etc., of its base classes.

Conclusion

This has been an altogether too brief introduction to Inductor.  Still, the benefits of Inductor should be quite apparent.  Gone is the need for detailed import, variable, and parameter specifications.  Gone is the need to manage a complex of criterion factories and a multitude of classes.

In contrast, Inductor leverages just two basic classes and a well-defined field-name class use pattern to provide an ORQL that embodies clarity, type-safety and all of the strength and flexibility of semantically literal SQL.