User's Manual

Chapter 9. Persistence Tutorial 87
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;
}