User's Manual

84 Chapter 9. Persistence Tutorial
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 demon-
strate how the feature works, not as an authoritative example of writing queries.