SQL Queries That Can Be Mapped to JDOQL

If you are familiar with SQL, you may find it helpful to examine the JDOQL queries that correspond to a representative sample of SQL queries. The examples in this section use four application data classes that provide a simple model of a small town library.

The persistent object model for the small library is presented in Figure 2-3. The Library application in the JDO Learning Tools uses these classes. The Library application is designed to explore JDOQL. Chapter 8 discusses the JDO Learning Tools.

Figure 2-3: Object model for a simple library

Books and borrowers are two important classes in the library's persistent object model. A borrower can borrow from zero to any number of books. Each book is either borrowed or not borrowed. Books are categorized by subject. A book may fall into one or more categories, and each category may have any number of books. Finally, since this is a small town library, volunteers are important to the institution. A volunteer is always a borrower, but not all borrowers are volunteers. Although inheritance might model the relationship between volunteer and borrower, composition is a better approach because borrowers can both become and cease to be volunteers.

The outline of the application data classes is contained in Listing 2-3. All the listed fields are declared persistent in the JDO metadata. The examples of JDOQL query filters that follow use these persistent fields.

Listing 2-3: The Persistent Fields of the Library Classes

 public class Borrower    {    // each of the fields below is a persistent field    private String name;    private HashSet books; // borrowed books    private Volunteer volunteer;    ...    } public class Volunteer    {    // each of the fields below is a persistent field    private int hoursPerWeek;    private Borrower borrower;    ...    } public class Book    {    // each of the fields below is a persistent field    private Date checkOutDate;    private String title;    private Borrower borrower;    private HashSet categories; // book's categories    ...    } public class Category    {    // each of the fields below is a persistent field    private String name;    private HashSet books; // books in this category    ...    } 

Note

You might want to bookmark Listing 2-3, as the library classes are used for most of the remaining examples in this chapter.

The four classes are mapped to the relational tables BORROWER, VOLUNTEER, BOOK, and CATEGORY. The many-to-many relationship uses the cross-reference table CATEGORY_BOOK. Listing 2-4 describes the column structure of these tables. The proposed mapping of the application data classes to the relational tables is straightforward, but it is not the only possible mapping. Each JDO implementation for relational databases determines the mappings that it will use. The SQL queries that follow use the columns in these tables.

Listing 2-4: The Relational Tables for the Library Classes

 Table BORROWER    INTEGER BORROWER_ID (PK)    VARCHAR(80) NAME Table VOLUNTEER:    INTEGER VOLUNTEER_ID (PK)    INTEGER BORROWER_ID (FK)    INTEGER HOURS_PER_WEEK Table BOOK    INTEGER BOOK_ID (PK)    VARCHAR(80) TITLE    DATE CHECKOUT    INTEGER BORROWER_ID (FK) Table CATEGORY    INTEGER CATEGORY_ID (PK)    VARCHAR(80) NAME Table CATEGORY_BOOK    INTEGER CATEGORY_ID (FK)    INTEGER BOOK_ID (FK) 

Select on One Table

The ability to select on one table for some criteria is straightforward both in SQL and in JDOQL. For example, to select books with the title Gone Fishing, the following SQL query works:

 select book_id,checkout,title,borrower_id from book where title = 'Gone Fishing' 

Using Book as the candidate class, the corresponding JDOQL query is the following expression:

 "title == \"Gone Fishing\"" 

The results collection contains only books entitled Gone Fishing. If the library doesn't carry this title, the collection is empty.

Select on Join Using One-to-One Relationship

The ability in SQL to join on a one-to-one relationship has its counterpart in JDOQL. If the purpose of the query is to determine whether any volunteers are named Tom, then the following SQL query does the job:

 select A.volunteer_id, B.name from volunteer A, borrower B    where A.borrower_id = B.borrower_id and B.name = 'Tom' 

Using the candidate class Volunteer, the equivalent JDOQL query is the following expression:

 "borrower.name == \"Tom\"" 

As this example shows, navigating a field in JDOQL may translate to joining two tables in SQL.

Although SQL can bring back pieces of information from many tables, a JDO query brings back only objects that are assignment compatible with the candidate class. To get related objects, the application must navigate the field references within the returned objects. JDO provides transparently the mechanism to support the navigation, but this mechanism may generate more queries (in this case SQL queries) against the datastore service.

Select on Join Using One-to-Many Relationship

The ability of SQL to query based on a one-to-many relationship has its counterpart in JDOQL. If the query were to find all the books that Tom borrowed, then SQL could express it as follows:

 select A.book_id, A.title, A.checkout from book A, borrower B    where A.borrower_id = B.borrower_id and b.name = 'Tom' 

Using the candidate class Book, the corresponding query filter string in JDOQL is the following expression:

 "borrower.name = \"Tom\"" 

As in the previous example, JDOQL navigates where SQL typically joins.

Select on Join Using Many-to-Many Relationship

SQL provides the ability to join on a many-to-many relationship using the cross-reference table CATEGORY_BOOK. To determine the categories of books that interest Tom at the present time, the SQL query could be the following:

 select distinct A.category_id, A.name    from category A, book B, borrower C, book_category D    where A.category_id = D.category_id       and B.book_id = D.book_id       and B.borrower_id = C.borrower_id       and C.name = 'Tom' 

Using the candidate class Category and the variable declaration string:

 "Book someBook;" 

the corresponding query filter in JDOQL is the following expression:

 "books.contains(someBook) && someBook.borrower.name == \"Tom\"" 

As you can see, this query filter navigates a collection field using a constrained variable.

As this example shows, the JDO query string is usually simpler than its SQL counterpart. This simplicity applies only for the application programmer. The JDO implementation still runs the complex SQL query to pull the information from the relational database.

Select on Self-Join

Sometimes, to express a query in SQL, a table must be joined to itself. For example, to find books for which there are multiple copies, the SQL query uses a self-join.

 select distinct A.book_id, A.title from book A, book B where A.book_id <> B.book_id and A.title = B.title 

Using Book as the candidate class, the same query is expressed by using an unconstrained variable. After declaring the variable string

 "Book x;" 

the variable x is used in the following query filter:

 "title = x.title && this != x" 

The query variable x is unconstrained because the filter does not use the contains query method. When unconstrained, a variable ranges over the extent of its corresponding application data class. In this case, the query filter yields all the books where there are multiple copies.

As mentioned earlier, not all JDO implementations support unconstrained variables. As a result, this query filter is not portable. To achieve the same end when the JDO implementation does not support unconstrained variables, the application can query for all books and order the results by title. The application then iterates the ordered results collection to find duplicate copies. Usually this workaround does not have the efficiency of a self-join.

Select on Outer Join

Sometimes in SQL, there is a need to pull information from a join but not limit the rows selected to just those that have a match on the joined attribute. The following example finds all borrowers named Tom and the hours that they volunteer. Some borrowers named Tom are volunteers and others are not, but the result must list all borrowers who are named Tom. This situation calls for an outer join. In Oracle syntax, it is expressed as follows:

 select A.borrower_id, A.name, B.hours_per_week    from borrower A, volunteer B    where A.name = 'Tom' and B.borrower_id (+) = A.borrower_id 

In JDOQL, the corresponding query uses Borrower as the candidate class and uses the following query filter:

 "name == \"Tom\"" 

The results collection contains all borrowers who are named Tom. Of this lot, those who are volunteers have a reference in the volunteer field from which the hours can be obtained.

Select Using Subquery

Occasionally, the desired query would call for the use of a subquery in SQL. For example, to find all the borrowers who do not have any books outstanding requires selecting the borrowers whose id is not contained in the borrower_id foreign key field of the BOOK table. The following SQL query performs this work:

 select borrower_id, name from borrower    where borrower_id not in (       select distinct borrower_id from book where borrower_id is not null) 

Using Borrower as the candidate class, the corresponding JDOQL query uses the query method isEmpty on the collection of books that the borrower has borrowed, as shown in the following query filter:

 "books.isEmpty()" 

Simplicity Is the Power in JDOQL

As the preceding examples illustrate, JDOQL never leaves the persistent object model. The persistent fields of the application data classes provide the basic terms for the query expressions. Rather than a limitation, the simplicity of JDOQL is a strength. As the examples show, JDOQL can express in a simple manner many complex queries that are based on the relational data model.

The examples show the power of JDOQL and transparent navigation to simplify the application programmer's work. As the object model becomes more complex, SQL queries rapidly become more complex. It is simpler to express the relation-ships between objects in JDOQL than in SQL because the JDOQL query needs fewer terms. JDOQL queries are easier for a Java programmer to write and understand than SQL queries because the query filter uses the member fields of the application data classes.

At the same time, without transparent navigation, the Java code that executes the SQL query must execute additional queries to build up the object graph that flows from the initial objects of interest. With JDO, the additional queries to navigate the object graph are not coding tasks for the application programmer. The implementation provides these queries transparently. All in all, JDO greatly reduces the number and complexity of the queries that the application programmer writes.