9.5. Filtering, Ordering, and Binding Parameters

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"));
}

	Note
	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"

	Note
	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.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;
}