RDBMS : Views

DataNucleus supports persisting objects to RDBMS datastores, persisting to Tables. The majority of RDBMS also provide support for Views, providing the equivalent of a read-only SELECT across various tables. DataNucleus also provides support for querying such Views. This provides more flexibility to the user where they have data and need to display it in their application. Support for Views is described below.

When you want to access data according to a View, you are required to provide a class that will accept the values from the View when queried, and Meta-Data for the class that defines the View and how it maps onto the provided class. Let's take an example. We have a View SALEABLE_PRODUCT in our database as follows, defined based on data in a PRODUCT table.

CREATE VIEW SALEABLE_PRODUCT (ID, NAME, PRICE, CURRENCY) AS
    SELECT ID, NAME, CURRENT_PRICE AS PRICE, CURRENCY FROM PRODUCT
    WHERE PRODUCT.STATUS_ID = 1

So we define a class to receive the values from this View.

package org.datanucleus.samples.views;
public class SaleableProduct
{
    String id;
    String name;
    double price;
    String currency;

    public String getId()
    {
        return id;
    }

    public String getName()
    {
        return name;
    }

    public double getPrice()
    {
        return price;
    }

    public String getCurrency()
    {
        return currency;
    }
}

and then we define how this class is mapped to the View

<?xml version="1.0"?>
<!DOCTYPE jdo SYSTEM "file:/javax/jdo/jdo.dtd">
<jdo>
    <package name="org.datanucleus.samples.views">
        <class name="SaleableProduct" identity-type="nondurable" table="SALEABLE_PRODUCT">
            <field name="id"/>
            <field name="name"/>
            <field name="price"/>
            <field name="currency"/>

            <!-- This is the "generic" SQL92 version of the view. -->
            <extension vendor-name="datanucleus" key="view-definition" value="
CREATE VIEW SALEABLE_PRODUCT
(
    {this.id},
    {this.name},
    {this.price},
    {this.currency}
) AS
SELECT ID, NAME, CURRENT_PRICE AS PRICE, CURRENCY FROM PRODUCT
WHERE PRODUCT.STATUS_ID = 1"/>
        </class>
    </package>
</jdo>

Please note the following

  • We've defined our class as using "nondurable" identity. This is an important step since rows of the View typically don't operate in the same way as rows of a Table, not mapping onto a persisted updateable object as such
  • We've specified the "table", which in this case is the view name - otherwise DataNucleus would create a name for the view based on the class name.
  • We've defined a DataNucleus extension view-definition that defines the view for this class. If the view doesn't already exist it doesn't matter since DataNucleus (when used with autoCreateSchema) will execute this construction definition.
  • The view-definition can contain macros utilising the names of the fields in the class, and hence borrowing their column names (if we had defined column names for the fields of the class).
  • You can also utilise other classes in the macros, and include them via a DataNucleus MetaData extension view-imports (not shown here)
  • If your View already exists you are still required to provide a view-definition even though DataNucleus will not be utilising it, since it also uses this attribute as the flag for whether it is a View or a Table - just make sure that you specify the "table" also in the MetaData.

We can now utilise this class within normal DataNucleus querying operation.

Extent e = pm.getExtent(SaleableProduct.class);
Iterator iter = e.iterator();
while (iter.hasNext())
{
    SaleableProduct product = (SaleableProduct)iter.next();
}

Hopefully that has given enough detail on how to create and access views from with a DataNucleus-enabled application.