9.5. Filtering, Ordering, and Binding Parameters

When retrieving information from the database, developers almost always need to be able to filter and order the results that are returned. Therefore, DataQuery, DataCollection, and DataAssociationCursor objects allow for ordering and filtering. DataQuery and DataOperation also allow developers to set arbitrary bind variables within the queries and DML statements. This document discusses how these features are implemented and how using the Filter can be overridden to use any arbitrary filtering scheme.

9.5.1. Filtering

9.5.1.1. Overview

The filtering system is complex, in that it allows developers to create complex expressions by combining filters, yet simple in that it provides convenience methods for developers with simple needs.

It is important to realize that by default, Filters simply filter the resulting data from the query. For instance, if you have the following:

query myDataQuery {
    BigDecimal articleID;
    do {
       select max(article_id) from articles
    } map {
       articleID = articles.article_id;
    }
}

and then add the filter "lower(title) like 'b%'", the new query will be:

select * 
from (select max(article_id) from articles) results
where lower(title) like 'b%'

and not:

select max(article_id) from articles where lower(title) like 'b%'

This can clearly lead to different results.

9.5.1.2. Simple Filters

For simple filters, you should use the addEqualsFilter(String attributeName, Object value) or the addNotEqualsFilter(String attributeName, Object value) methods to filter a DataQuery, DataOperation, DataCollection, or a DataAssociationCursor object. These methods take the name of the attribute and its value, create the correct SQL fragment, and bind the variable. If the system is using Oracle or Postgres and the value is null, the system will create the correct is null or is not null syntax.

In order to specify the filter, you must use the name of the Java Attribute, not the name of the database column. The persistence layer automatically converts the property names to column names using the property mappings defined in the PDL. This layer of abstraction is one of the features that allows developers to change column names without having to update Java code. For example, see the following DataQuery defined in PDL (remove the "\" and make it all one line):

query UsersGroups {
String firstName;
String lastName;
String groupName;
do{
      select *
      from users, groups, membership 
      where users.user_id = membership.member_id and membership.group_id \
= groups.group_id
} map {
      firstName=users.first_name;
      lastName=users.last_name;
      groupName=groups.group_name;
}

To retrieve all users whose last name is "Smith", do the following:

DataQuery query = session.retrieveQuery("UsersGroups");
query.addEqualsFilter("lastName", "Smith")
while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

To get all users whose last name starts with "S", use the addFilter method:

DataQuery query = session.retrieveQuery("UsersGroups");
// FilterFactory is explained below
query.addFilter\
(query.getFilterFactory().startsWith("lastName", "S", false));
while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

9.5.1.3. Complex Filters

For more complex queries, it is helpful to understand the role of each interface that deals with Filters.

  • Filter — This class represents a single expression for part of a "where" clause. For instance, a Filter could be "foo = :bar" with a value associated with "bar" (e.g. "foo = 3").

  • CompoundFilter — This class extends Filter and provides the ability to add filters together using the AND and OR keywords.

  • FilterFactory — This class is responsible for handing out filters. It has methods such as "simple", "equals", "notEquals", "lessThan", "greaterThan", "startsWith", "contains", and "endsWith". If a user is using Oracle or Postgres, all these methods check whether the value is null, and if so, act correctly (e.g., use "foo is null" instead of "foo = null").

  • DataQuery — This class allows you to add filters as well as get a reference to the FilterFactory. If you need a FilterFactory but do not have a DataQuery, use Session.getFilterFactory().

If you want to filter the query based on certain conditions, you can incrementally build up your query as follows:

DataQuery query = session.retrieveQuery("UsersGroups");
FilterFactory factory = query.getFilterFactory();

if (beginLetter != null) {
    query.addFilter(factory.lessThan("firstLetter", beginLetter, true));
}

if (lastLetter != null) {
    query.addFilter(factory.greaterThan("firstLetter", beginLetter, true));
}

while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

Now suppose you want to get all users with a last name that is the same as the variable lName or is Smith, and with a first name that matches the variable fName or is John. You could do this as follows:

DataQuery query = session.retrieveQuery("UsersGroups");
FilterFactory factory = query.getFilterFactory();

Filter filter1 = factory.or().addFilter(factory.equals("lastName", lName))
                             .addFilter\
(factory.equals("lastName", "Smith"));
Filter filter2 = factory.or().addFilter(factory.equals("firstName", fName))
                             .addFilter\
(factory.equals("firstName", "John"));

query.addFilter(factory.and().addFitler(filter1).addFilter(filter2));
while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

These could also have been chained together in order to avoid creating any Filter variables, but this was not done here for clarity.

Finally, if you want to add a bunch of "foo = :foo" statements to a query, you can use the convenience methods provided by DataQuery. These methods delegate to FilterFactory and therefore handle the Oracle null problem. For instance:

DataQuery query = session.retrieveQuery("UsersGroups");
if (includeFirstName) {
    query.addEqualsFilter("firstName", fName);
} 

if (includeLastName) {
    query.addEqualsFilter("firstName", fName);
} 

while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

NoteNote
 

When setting dates, java.util.Date objects should be used instead of java.sql.Date objects because java.sql.Dates do not have hours, minutes, or seconds.

When filtering a query that returns data objects (as opposed to simple java types), you must prepend the name of the object attribute. For instance, if you want to retrieve all uses using a query, you would follow the example below. In practice, you want to retrieve a DataCollection from the Session but we are using a query here for demonstration purposes.

query retrieveAllUsers {
   User myUser;
   do {
      select user_id, first_name, last_name from users
   } map {
      user.id = users.user_id;
      user.firstName = users.first_name;
      user.lastName = users.last_name;
   }
}

DataQuery query = session.retrieveQuery("retrieveAllUsers");
// notice how the attribute name corresponds directly to what
// in the map" section of the query and is actully
// the <object type>.<attribute name>
query.addEqualsFilter("user.firstName", fName);

while (query.next()) {
   DataObject user = query.get("user");
   System.out.println("First name = " + user.get("firstName") + 
                      "; Last name = " + user.get("lastName") + 
                      "; Group = " + user.get("groupName"));
}

9.5.1.4. Restricting the number of rows returned

One common feature that is requested of queries, collections, and associations is to be able to restrict the number of rows that are returned. Specifically, in order to create any sort of pagination or to break up a large set of results in to a series of smaller sets it is necessary to restrict the number of rows returned.

Restricting the number of rows a query returns is easy. To do so, you can simple call setRange(Integer, Integer) on the data query is question. For instance, if I want results 10-19 for a qury, I can do the following:

DataQuery query = session.retrieveQuery("retrieveAllUsers");
query.setRange(new Integer(10), new Integer(20));
while (query.next()) {
   DataObject user = query.get("user");
   System.out.println("First name = " + user.get("firstName") + 
                      "; Last name = " + user.get("lastName") + 
                      "; Group = " + user.get("groupName"));
}

9.5.1.5. Filtering Using Subqueries

The filtering methods described so far handle most situations. However, sometimes developers need the ability to filter based on the results of a subquery. Therefore, the persistence layer provides a mechanism to allow developers to use named queries within filters. Specifically, this is useful within IN clauses and EXISTS clauses.

Suppose that you want to retrieve all articles written by authors whose last name begins with the letter "b". One easy way to avoid duplicates is to use an IN clause. To perform this operation, you can create the following two DataQueries:

query retrieveArticlesBasedOnAuthor {
      BigDecimal authorID;
      do {
          select article_id 
            from authors, author_article_map
           where authors.author_id = author_article_map.author_id
             and lower(last_name) like :lastName || '%'
      } map {
          authorID = authors.author_id;
      }
}

query retrieveSelectedArticles {
      BigDecimal articleID;
      String title;
      do {
          select article_id, title from articles
      } map {
          articleID = articles.article_id;
          title = articles.title;
      }
}

Next, simply retrieve one query and add the other query as part of the filter, as follows (remove the \ and make it all one line):

Session session = SessionManager.getSession();
DataQuery query = \
session.retrieveQuery("tutorial.retrieveSelectedArticles");
Filter filter = query.addInSubqueryFilter("articleID", \
"tutorial.retrieveAuthorsWithParam");
// we have to set the value for "lastName" since it is a bind variable
// in the subquery we added.
filter.set("lastName", "b");
System.out.println\
("The following articles have at least one author whose " +
                   "last name starts with 'b'");
while (query.next()) {
    System.out.println(query.get("title"));
}

The code above will actually execute the following SQL:

select article_id, title from articles
where article_id in (select article_id 
                       from authors, author_article_map
                      where authors.author_id = \
author_article_map.author_id
                        and lower(last_name) like ? || '%'

with ? = "b"

NoteNote
 

While there are other, possibly better ways to obtain the same result, this example is used to demonstrate how the feature works, not as an authoritative example of writing queries.

9.5.1.6. Filtering Using Functions

The filtering methods discussed so far work well when the developer only needs to filter directly off of columns or values. However, they do not work well if the developer wants a case-insensitive filter or needs to use some other function to manipulate the data in the columns.

To meet this need and to appropriately handle null values, the system provides a method within FilterFactory named compare that allows developers to pass in two expressions and have the system compare them to each other.

The most common use case for this is a case-insensitive comparison where the developer wants to know whether a string exists in a column, but does not care about the case. For instance, suppose a developer wants to retrieve all articles titled "Disaster Strikes," but does not care about the capitalization. He could use use the following code to achieve this:

DataCollection pub = SessionManager.getSession().retrieve("tutorial.Articles");
Filter filter = pub.addFilter(pub.getFilterFactory().compare("upper(title)",
                                                     FilterFactory.EQUALS,
                                                     ":title"));
// we set the title to all upper case so that we do not make oracle do
// it for us which would be slower
filter.set("title", "DISASTER STRIKES");

If the developer actually wants all articles with either the word "Disaster" or "Strikes," he can do the following:

DataCollection pub = \
SessionManager.getSession().retrieve("tutorial.Articles");
FilterFactory factory = pub.getFilterFactory();
Filter disasterFilter = \
factory.compare("upper(title)", FilterFactory.CONTAINS,
                                        ":disasterTitle"));
filter.set("disasterTitle", "DISASTER");
Filter strikesFilter = \
factory.compare("upper(title)", FilterFactory.CONTAINS,
                                       ":strikesTitle"));
filter.set("disasterTitle", "STRIKES");
pub.addFilter(factory.or()
                     .addFilter(disasterFilter)
                     .addFilter(strikesFitler));

The important thing to realize is that it this method will handle problem with null values faced by Oracle and Postgres, whereas using a standard simple filter will not.

9.5.2. Ordering

Use the addOrder(String order) method to order a DataQuery, DataCollection, and a DataAssociationCursor object. The addOrder method takes a String as its only parameter and the format of the string is the optional object type following by a dot and then then required attribute name ([<object type name>.]<attribute>) you wish to order by.

The string parameter passed to the addOrder(String order) method is used in an ORDER BY clause, which is appended to the SELECT statement. The order is specified by constructing a string representing the ORDER BY clause, but instead of specifying column names, you specify attribute names. The persistence layer automatically converts the attribute names to column names using the property mappings defined in the PDL. For example, see the following DataQuery defined in PDL (remove the "\" and make it all one line):

query UsersGroups {
   String firstName;
   String lastName;
   String groupName;
   do{
      select *
      from users, groups, membership 
      where users.user_id = membership.member_id and membership.group_id \
= groups.group_id
   } map {
      firstName=users.first_name;
      lastName=users.last_name;
      groupName=groups.group_name;
   }
}

You can order this query by the user's last name, as follows:

DataQuery query = session.retrieveQuery("UsersGroups");
query.addOrder("lastName asc");
while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

Finally, you can build up the ORDER BY string in the same way that you build up a filter. For instance:

DataQuery query = session.retrieveQuery("UsersGroups");
query.addOrder("lastName asc")
if {careAboutGroupName} {
    query.addOrder("groupName");
}
while (query.next()) {
   System.out.println("First name = " + query.get("firstName") + 
                      "; Last name = " + query.get("lastName") + 
                      "; Group = " + query.get("groupName"));
}

The ORDER BY string is any valid ORDER BY clause, except that you specify property names, not column names.

9.5.3. Binding Parameters

Use the setParameter(String parameterName, Object value) method to bind an arbitrary variable within a DataQuery, DataOperation, DataAssociationCursor, or DataCollection. The method getParameter(parameterName) allows you to retrieve the value of a set parameter. The setParameter takes in a string that should match the string within the defined SQL. The Object it takes should specify the value.

This functionality is useful for complicated queries that involve embedding parameters. For example, see the following DataQuery defined in PDL (bind variables are in bold):

query CategoryFamily {
  Integer level;
  BigDecimal categoryID;
  String name;
  String description;
  Boolean isEnabled;
  do {
     select l, c.category_id, c.name, c.description, c.enabled_p 
        from (select level l, related_category_id
                 from (select related_category_id, category_id
                          from cat_category_category_map 
                          where relation_type = :relationType)
                 connect by prior related_category_id = category_id 
                 start with category_id = :categoryID) m, 
            cat_categories c
        where c.category_id = m.related_category_id
  } map {
     level = m.l;
     categoryID = c.category_id;
     name = c.name;
     description = c.description;
     isEnabled = c.enabled_p;
  }
}

This query first retrieves all mappings that are of a particular type (e.g., "child" mappings as opposed to "related" mappings). It then does a "connect by" to get all the parents (or all the related categories). Finally, it joins this result with the original categories table so that it can have the name, categoryID, description, and isEnabled for each of the selected categories. Without being able to set the categoryID and relationType variables, you could not perform this query. Creating a separate query for each kind of relationType does work, but there is no way to account for every possible categoryID.

After the query is defined, it can be used as follows:

DataQuery query = session.retrieveQuery("CategoryFamily");
query.setParameter("relationType", "child");
query.setParameter("categoryID", "3");
while (query.next()) {
  System.out.println("We retrieved Category " + query.get("name")) +
                     "with ID = " + query.get("categoryID"));
}    

9.5.3.1. Binding Collection Parameters

Thus far, the document has discussed binding parameters that contain a single value, that is, parameters that are used in comparison clauses. While these types of parameters cover most cases, sometimes you will want to be able to have bind variables that represent many different values. Specifically, you may want to be able to get rows that meet specific criteria and are IN a given set of rows. To provide this functionality, the system has the ability to take a java.util.Collection as the value for a bind variable and then expand the Collection so that it works correctly.

For instance, if you want all Articles whose IDs were in a given collection, you can write a method such as the following (remove the "\" and make it all one line):

public DataCollection retrieveArticles(Collection articlesToRetrieve) {
    DataCollection articles = SessionManager.getSession().retrieve\
("tutorial.Articles");
    Filter filter = articles.addFilter("id in :articlesToRetrieve");
    filter.set("articlesToRetrieve", articlesToRetrieve);
    return articles;
}

9.5.4. How it Works

You may be wondering how to add filters and bind variables after retrieving the query. The persistence system does not actually execute the required SQL query until the first call to next(). Therefore, the DataQuery (or DataCollection or DataAssociationCursor) can be passed around and have Filters and ORDER BY statements added and variables bound before the first element is retrieved. Then, when the first element is retrieved, the query is executed and the code can no longer add or remove filters or ordering criteria.