4. Issuing Queries
Contents
4.1. Introduction
4.2.1. Query Parameters and Sort Orders
4.2.2. Class Joins
4.3. Using the AggregateOQuery Class
4.4. In-Memory Queries
4.4.1. Defining Indexed Attributes
4.4.2. Limitations
4.1. Introduction
One of the main strengths of a relational database resides in its powerful querying
capabilities. Object models based on VBSF can take advantage of sophisticated relational
query engines without leaving the object model. Properly designed and indexed relational
models can provide fast ad-hoc access to objects. VBSF provides the OQuery (Object Query) and AggregateOQuery
(Aggregate Object Query) classes which are used to formulate and issue queries to
the database.
The OQuery class provides an easy way to construct complex queries without using SQL or OQL. To issue a query, an OQuery object is created, then query parameter values and sort orders are added to the OQuery object, and the query is executed. The OQuery class also provides support for retrieving distinct objects and for restricting the maximum number of objects returned using the setDistinct() and setMaxCount() methods respectively. An OQuery object can be executed by invoking its execute() or list() methods, or by passing it as an argument to the Database or the OCollection classes get() and list() methods. Executing a query using the execute() method is identical to executing it using a get() method (i.e. the query is executed from the cache if possible). The following sections describe in more detail how query parameters and sort orders are specified, and provide examples.
4.2.1. Query Parameters and Sort Orders
A query parameter consists of the attribute name being queried, a search value, and an optional comparison type, logical operator, and grouping type. Query parameters are added to a query using the add and addObject methods.
The comparison type specifies how the value stored in the database table (or in the cache for in-memory queries) is to be compared with the search value supplied. Valid types are:
EQUAL, LIKE, GREATER, LESS, GREATER_OR_EQUAL, LESS_OR_EQUAL, NOT_EQUAL, NOT_LIKE, IN and NOT_IN ( defaults to EQUAL if not specified )
The logical operator describes the type of logical operation to be performed between any two comparisons. Valid operators are:
AND, OR, EQV, and XOR. ( defaults to AND if not specified )
Grouping types are used to specify a specific order of evaluation for attribute comparison expressions. Valid types are:
A sort order specifies an attribute name and an optional sort order by which the query results are to be ordered. The sort order can be either ascending or descending. If no sort order is specified, an ascending sort is performed. Sort orders are specified using the addOrder method.
The code below is taken from the sample vcontactdemo application. It issues a query
that retrieves the first ten contacts referencing a Category
with object ID = 3 and a fFirstName >= 'ROGER'
sorted by last name ascending.
OQuery qry = new OQuery(Contact.class);
qry.add(3L, "myCategory");
qry.add("ROGER", "fFirstName", OQuery.GREATER_OR_EQUAL, OQuery.AND);
qry.addOrder("fLastName");
qry.setMaxCount(10);
Contact[] allContacts = (Contact[])qry.execute(db);
Queries executed by invoking the query's execute()
method must be associated with a persistent class in their constructor. Query parameters
and sort orders are added to the query using the add
and addOrder methods. The setMaxCount
method is used to specify the maximum number of objects that will be returned by the
query. The query is then executed by invoking its execute()
method, passing it as argument the Database object
where the query is to be executed.
A query can also be used just to sort objects, or to restrict the maximum number of
objects returned (or both). Multiple sort orders may be specified. The query below is used only to limit the number of contacts
returned to the first four in the database:
OQuery qry = new OQuery(Contact.class);
qry.setMaxCount(4);
Contact[] allContacts = (Contact[])qry.execute(db);
The one below simply sorts all contacts in the database by last name:
OQuery qry = new OQuery(Contact.class);
qry.addOrder("fLastName");
Contact[] allContacts = (Contact[])qry.execute(db);
The LIKE and NOT_LIKE comparison types allow performing pattern searches on string and
CLOB type columns. If the query is to be executed directly from the database you can
specify wildcard and pattern match characters directly in the search value. For example,
the query below retrieves all contacts with a last name starting with 'MOO':
OQuery qry = new OQuery(Contact.class);
qry.add("MOO%", "fLastName", OQuery.LIKE);
Contact[] allContacts = (Contact[])qry.execute(db);
Most, if not all, databases interpret the %
character as pattern to match any number of zero or more characters. Another character
that should be recognized by many databases is the _
(underscore) character as pattern to match any single character. Consult your database
documentation for any additionally supported search patterns.
The single quote character is reserved as a delimiter for string values in LIKE queries. If single quotes are part of the search string, VBSF automatically substitutes each single quote with two single quotes before sending the string to the database. When a LIKE comparison is executed from the cache, VBSF only recognizes the% character as a search pattern. Any other pattern match characters are taken literally, so they should not be used as pattern match characters. See the Limitations section below for other details on LIKE queries executed from the cache.
4.2.2. Class Joins
The queries presented above involve only one business class. The OQuery
class also provides the ability to specify queries that join multiple business classes.
For example, the query below retrieves all contacts with first name >=' ROGER' that
belong to the category with description = 'BUSINESS CONTACTS' by joining the Contact and Category
classes:
OQuery qry = new OQuery(Contact.class);
qry.add("ROGER", "fFirstName", OQuery.GREATER_OR_EQUAL);
qry.add("BUSINESS CONTACTS",
"vcontactdemo.engine.Category.fDescription");
qry.addJoin("vcontactdemo.engine.Contact.myCategory",
"vcontactdemo.engine.Category.fID");
Contact[] allContacts = (Contact[])qry.execute(db);
Since the fFirstName attribute belongs to the Contact class and the fDescription
attribute to the Category class, both classes must
be joined in order to formulate the query. A join is defined using the addJoin method, which accepts the two class qualified
attribute names (using a standard period punctuation mark as separator) to be joined. This
method may be invoked multiple times to define multiple joins. In the example above, the myCategory reference attribute of the Contact class is joined with the object ID attribute of
the Category class.
Attribute names for query parameters that specify attributes not belonging to the class being queried must be qualified by their class name. In our example, since the fDescription attribute is in the Category class, it must be qualified by the Category class name. Notice also that the full package name of the Category class must also be specified.
In the example above, the Category and Contact classes have a one-to-many relationship. The relationship can be traversed because there is a reference attribute in one class (Contact.myCategory) that points to the other class (Category). This makes the class join possible. However, when classes have a many-to-many relationship that is mapped via a join table, there is no specific reference attribute in one class that can be used to join with the other. In this case, you can use the addTableJoin() method to specify a join to be performed across a join table. For example, the vpersondemo2 sample application depicts a many-to-many relationship between the Store and Customer classes. The Store class has a referenced collection attribute named customers that is mapped to a join table. This collection allows a Store to reference its Customer objects. The query shown below retrieves all customers that have bought in the 'Fish-R-US' store by performing a join across the join table mapped to the customers collection in the Customer class:
OQuery qry = new OQuery(Customer.class);
qry.add( "Fish-R-US", "vpersondemo2.Store.fStoreName" ) ;
qry.addTableJoin( "vpersondemo2.Store.customers" ) ;
Customer[] allCustomers = (Customer[])qry.execute(db) ;
4.2.3. Ad-hoc & Constrained Queries of Owned Classes
There are two types of queries that can be performed on owned classes: constrained and ad-hoc. If an OQuery object is passed as an argument to a get() or list() method of an owned collection, then the query is constrained to the set of owned objects (i.e. the query is used to further limit which objects are returned from the set of objects that are owned by a particular object). If a query is executed via its execute() method or passed as argument to a get() or list() method of a Database object, then an ad-hoc query is performed. Ad-hoc queries performed on owned classes are not restricted by ownership constraints. In other words, it is possible to formulate queries that return objects owned by different owner objects.
Below is an example of a constrained query that retrieves all component parts of an
assembly having the word 'drive' in its fDescription
attribute. The results are sorted by fPartNo.
Assembly myAssembly = (Assembly)db.lookup(Assembly.class,
100);
OQuery qry = new OQuery();
qry.add("drive", "fDescription", OQuery.LIKE);
qry.addOrder("fPartNo");
Component[] components = (Component[])myAssembly.getComponentParts(qry);
The OQuery object is constructed using its no
arguments constructor. After query parameters and sort orders are added, the query is
passed as an argument to the getComponentParts
method which executes the query by passing the query object to the get(qry)
method of its OCollection attribute of component
parts and returning the results. Only component part objects owned by the myAssembly object having the word 'drive' in its fDescription attribute are returned.
To return ALL component parts having the word 'drive' in its fDescription
attribute, regardless of which assembly owns them, we can formulate the ad-hoc query
below:
OQuery qry = new OQuery(ComponentPart.class);
qry.add("drive", "fDescription", OQuery.LIKE);
qry.addOrder("fPartNo");
Component[] components = (Component[])qry.execute(db);
When an ad-hoc query is performed on an owned class, it is possible to get a reference
back to each of the owners of the returned objects by invoking the get()
method of the OReference attribute that links each
owned object to its owner. This method can be used to walk up an ownership graph.
Class joins can also be performed on owned classes. Below is an ad-hoc query of an
owned class (Locator) that joins three classes: Locator, Contact,
and Category. The query retrieves all alpha locators
that belong to contacts with category description = BUSINESS CONTACTS :
OQuery qry = new OQuery(Locator.class);
qry.add("A", "fLocatorNo", OQuery.GREATER_OR_EQUAL);
qry.add("BUSINESS CONTACTS",
"vcontactdemo.engine.Category.fDescription");
qry.addJoin("vcontactdemo.engine.Locator.myOwner",
"vcontactdemo.engine.Contact.fID");
qry.addJoin("vcontactdemo.engine.Contact.myCategory",
"vcontactdemo.engine.Category.fID");
qry.addOrder("fLocatorNo"); //sort results by locator number
Locator[] locators = (Locator[])qry.execute(db);
The example below is the constrained version of the above query:
Contact currContact = (Contact)db.lookup(Contact.class, 7);
OQuery qry = new OQuery();
qry.add("A", "fLocatorNo", OQuery.GREATER_OR_EQUAL);
qry.add("BUSINESS CONTACTS",
"vcontactdemo.engine.Category.fDescription");
qry.addJoin("vcontactdemo.engine.Locator.myOwner",
"vcontactdemo.engine.Contact.fID") ;
qry.addJoin("vcontactdemo.engine.Contact.myCategory",
"vcontactdemo.engine.Category.fID") ;
qry.addOrder("fLocatorNo"); //sort results by locator number
Locator[] locators = (Locator[])currContact.getLocators(qry);
The sample applications and the OQuery API
documentation include many additional examples on the usage of the OQuery
class.
4.3. Using the AggregateOQuery Class
The AggregateOQuery class is a
subclass of OQuery
that supports database aggregate functions. This class allows performing RDBMS queries of the form:
SELECT Sum(a), Average(b), count(*), count(c), d, e,
Func(f)
FROM X
WHERE ....
GROUP BY d, e, Func(f);
The aggregate functions in the SELECT clause can be chosen from among the standard SQL aggregate functions constants defined in
the AggregateOQuery class. Functions in the GROUP BY clause are user definable and
all group by attributes are automatically added to the SELECT clause. Aggregate queries are always executed from the database directly and results are never cached.
An AggregateOQuery is formulated by adding
aggregate and/or group by functions via the addAggregate() and
addGroupBy() methods. Query conditions are added via the
add() method inherited from OQuery.
Query execution is performed via the executeAggregateQuery() method, but if an
AggregateOQuery is to be executed against an owned or referenced collection, then it must be executed by passing it as argument to either the
OCollection.executeAggregateQuery(AggregateOQuery) or to the
Database.executeAggregateQuery(Object holder, String collName, AggregateOQuery query) methods.
Execution of an AggregateOQuery returns a
DBSReader object that can be used to read any value from the result set. Multiple rows may be returned by the query.
An AggregateOQuery for which no aggregate functions or group by functions are
added automatically performs an object count (i.e. SELECT COUNT(*)) of all the objects that match the query criteria. Below is a simple example that returns the number of contacts with last name equal to
"Picard":
AggregateOQuery qry = new
AggregateOQuery(Contact.class);
qry.add("Picard", "fLastName");
DBSReader rdr = qry.executeAggregateQuery(db);
if (rdr.nextRow()) {
int noRows = rdr.getInt(1);
}
rdr.close(); //** must close reader to release db resources
A
readRow() method that reads a complete row at a time is also provided for convenience.
Below is an example of a query against a referenced collection that is executed by passing it
as an argument to the OCollection.executeAggregateQuery(AggregateOQuery) method. The query calculates the
average number of "drive" components in an assembly and uses the readRow()
method to read the result.
AggregateOQuery qry = new AggregateOQuery();
qry.addAggregate("qty", AggregateOQuery.AVERAGE);
qry.add( "drive", "fDescription", OQuery.LIKE );
//componentParts is an OCollection attribute of Assembly
DBSReader rdr = myAssembly.componentParts.executeAggregateQuery(qry);
Double avrg = (Double)qry.readRow(rdr).get(0);
qry.close(rdr); //**must close reader to release db resources
The following example query computes the total amount of all sales for each salesperson during the month of April 2002:
AggregateOQuery qry = new
AggregateOQuery(SalesTransaction.class);
qry.add((new GregorianCalendar(2002,5,1)).getTime(),"fSaleDate",OQuery.GREATER_OR_EQUAL);
qry.add((new GregorianCalendar(2002,5,30)).getTime(),"fSaleDate",OQuery.LESS_OR_EQUAL);
qry.addAggregate("fSaleAmount", AggregateOQuery.SUM, false);
qry.addGroupBy("fSalesPersonID");
DBSReader rdr = qry.executeAggregateQuery(db);
while (rdr.nextRow()) { //query returns multiple rows, one for each salesrep
double totalSalesAmt = rdr.getDouble(1);
long salesRepID = rdr.getLong(2);
}
rdr.close(); //**must close reader to release db resources
If an AggregateOQuery returns a single value you can use one of the
getCount(), getLong(),
getDouble(), getFloat(),
getBigDecimal(), getString(),
getDate(), or getBoolean()
convenience methods to retrieve the value. For example, the first query above can be re-written as:
AggregateOQuery qry = new
AggregateOQuery(Contact.class);
qry.add("Picard", "fLastName");
DBSReader rdr = qry.executeAggregateQuery(db);
int count = qry.getCount(rdr); //getCount automatically closes reader
The vcontactdemo2 sample application provides additional examples on the usage of the
AggregateOQuery
class. See the API
documentation for additional details.
4.4. In-Memory Queries
In-memory queries are queries that are processed directly from internal caches without accessing the database. Queries processed in-memory are executed many times faster than equivalent database queries. In-memory queries are automatically enabled when retrieving objects from the Database class and from the OCollection class. See section 6. Object Caching for details on caching.
VBSF contains a powerful query engine used to process in-memory queries. The in-memory query engine can execute queries in one of two ways:
Unless a collection contains a small number of objects, an indexed search executes faster than a collection scan. To enable indexed searches you must first define indexed attributes.
The query engine always attempts an indexed search before resorting to a collection scan. In order for the query engine to be able to perform an indexed search all query parameters in the query must have EQUAL comparisons. In addition, one of the following conditions must also be met:
For example, both query conditions below can be processed via an indexed search:
//either fLastName or fFirstName or both must be indexed
fLastName = "MOORE" AND fFirstName = "DEMI"
//both fLastName and fFirstName must be indexed
fLastName = "MOORE" OR fFirstName = "DEMI"
However, the query conditions below force a collection scan:
//use of '>' inhibits indexed search
fLastName > "MOORE" AND fFirstName = "DEMI"
//fSecondName is not indexed
fLastName = "MOORE" OR fSecondName = "P."
//even if all three query fields were indexed,
// the mixture of AND and OR operators inhibits indexed search
fLastName = "MOORE" AND fFirstName = "DEMI" OR fTitle = "MS"
4.4.1. Defining Indexed Attributes
Persistent object indexes are equivalent in functionality to database table indexes. As a rule, indexes should be defined for all attributes whose corresponding table fields are indexed. However, since maintaining in-memory indexes does require some system overhead, you should make sure that the indexes you create will actually be used. Specifically, create indexes only for attributes that are used in query conditions often and are likely to be used in queries meeting the conditions specified in the previous section. Collection size is also an important factor. In order for indexes to be useful, there should be a significant number of objects in a collection such that an index lookup is considerably faster than a collection scan.
Attribute indexes are defined by marking the attributes as indexed in their Attribute Mapping Form using the mapping tool. VBSF automatically creates an object ID index so you do not have to specify object ID attributes as indexed.
4.4.2. Limitations
The in-memory query engine has the following limitations:
Aside from the above limitations, in-memory queries can be expected to return result sets identical to the ones a database engine would return.