8. Queries

8.1 Making The Query

The query service of the library is accessed through one of the four finder methods of the Store object:

• find(String statement)
• find(String statement, Object[] parameters)
• findSingle(String statement)
• findSingle(String statement, Object[] parameters)

These methods all receive a statement string first. This statement's full syntax is described in Appendix A, and this chapter will also deal with all of the features of this finder statement. Additionally parameters may be passed in the optional object array. As in SQL, the statements may contain placeholder question marks ('?'), which will be substituted in-order by the parameters given. The findSingle methods are convenience methods, which return exactly one object, or null if no objects match the query. If multiple objects match, an arbitrary one is returned.

Note on SQL injection: The statement given will be compiled into an SQL statement, all primitive values in the statement will be inserted into the sql statement with the use of PreparedStatement.set* methods, and none of them will be literally included in the sql statement string. This is true for all primitive values such as strings, numbers, dates and parameters given in the parameters object array. Also, the query statement string, can be hijacked under no circumstances to update/delete or in any way alter the database, it will always compile into a select statement. This means, you can freely create such a statement with the concatenation of conditions and input from the user, without fear of altering the data in the database. Be aware however, that depending on your algorithm, the user might still change your statment in the following ways: add conditions, messing up paranthesis balance, supplying junk characters into the statement. So a bit of attention is still required, although no destructive attacks are possible.

Some other libraries prefer, or at least offer a programmatical way of setting up a query. This is used mostly to avoid SQL injection, and some purists prefer it because it blends well with Java itself. We think however, that a readable query is maintanable and more easy to read than multiple rows of Java code, so the library does not offer programmatic interface to the query engine. You may however implement one in your application if you desire so.

Note on case sensitivity: All queries are case insensitive, you can mix uppercase and lowercase letters any way you want. All identifiers and operators are case-insensitive, even classnames and attributenames. The only case sensitive part of a query statment, are strings which are enclosed in apostrophes.

All queries are also distinct. That means no two objects are contained in the result list that are of the same identity, so from the same phisical row, or to put it another way, have the same persistence_id. There are a few exceptions however. When the query returns an object, which has an attribute of type byte[], or any of it's subclasses or superclasses have an attribute of type byte[], then the result will not be distinct. This is because not all databases support comparing BLOBs. Also, distinctivity is modified, when the statement contains an order by term, which references an attribute not in the returned class. This namely causes the library to include that attribute into the selected attributes, in which case this may make the distinctivity more strict.

8.2 Lazy Result Lists

Before diving into the syntactical depths of the query language itself, a word is needed about the return value of the finder methods. All finders return a List object. This object implements all methods of the List interface, exactly as a normal List would do, such as a Vector or ArrayList. However, this is a special LazyList, which does not load all result objects all at once, but just a handfull of those which are likely to be used by the caller. Despite this, calling the size() method will return the full size of the result, so all paging and memory handling is totally transparent to the caller.

When a select is issued to the library, it will at first select a few dozens objects to the LazyList it will return. If it senses that more objects are available, it will also issue a count statement with the same conditions as the original statment to determine the full size of the possible result list. This way, the LazyList will known the exact size of the resultset it represents, but it does not need to contain all resulting objects.

Also, rule number one of resultlists is, you do not talk about.... err.. I mean result lists do not change. Ever. This is a very important rule, because this way, the size of the resultlist also never changes. It is enough to determine the full size at the very first query. The list can leave the transaction it was created in, and can be maintained indefinitely without using unnecessary resources, without ever changing it's contents, even if all objects are long deleted from the database (keep in mind, the library keeps object versions).

8.3 Finding Objects

The query statement syntax is object oriented, so do not think about tables, indexes, primary keys and such. Just express the query in the form the most natural for objects. The most simple query statement is of the form:

find book

The first word of the query is always find (or view as described in the next section). After find, you must specify which class to select. The example will select all Book classes available in the database. Note, that the language is case-insensitive, even with classes, so book also means Book. The full Java code to execute this statement would be something like this:

List allBooks = getStore().find("find book");

In the following examples we assume, you know how to execute the statements, so we will only write the statement itself, and not the Java code.

Traditional joins, such as cross joins, left joins and other things like these are not directly supported. The classes that need to be included in the statement will be dynamically calculated when the statement is analized. Let's continue with another example, selecting a book based on it's author (which is an attribute of Book and of the Author):

find book where book.author=author and author.name='Neal Stephenson'

The library itself will figure out that the table for the Author class will have to be joined to the Book class, you do not have to worry about that. Of course, this is an object oriented language (tries to be anyway), so the above can be expressed much simpler:

find book where book.author.name='Neal Stephenson'

Sometimes it's necessary to assign aliases to classes, to keep apart instances of the same class inside the statement:

find book where book.author=snow(book).author and snow.title='Snow Crash'

The above statement selects all books, which are written by the same person as the book titled Snow Crash. As you see, the word snow is an alias, and of type Book. The first time, you refer to the alias, you must define it's type, and all subsequent references can just consist of the alias itself. Until now, all class references were an abbreviated name of the classnames themselves, which is probably the most used form, but if you want, you can also specify the fully qualified class, but then, you must use the alias construct to give the class a shorter, more readable name:

find book(com.acme.bookstore.Book) where book.title='Snow Crash'

This is useful, if you have a classname that is not unique within your application. For example, if there is a Book class in the com.acme.bookstore package, but also in the com.acme.bookrental package, you have to specify the package name also, so the library can determine the class unambigously. You don't have to write all package names, just until the point it becomes unambigous, so this is also valid:

find book(bookstore.Book)

Expressions after the where keyword can be any expressions known from SQL, which consist of the supported operators and constructs. Most operators like =, <>, >=, not null, null, etc. are supported, also the most common logical operators such as or, and and not are also supported. See Appendix B for a complete list of operators. Important to note, that string operations are case-sensitive by default (see the like operator), only the ilike operator offers case-insensitive string search.

The result can be ordered using the order by keywork. This works exactly like in SQL, except that it may reference attributes through multiple levels.

find book order by book.title asc

The ascending or descending keyword is optional, ascending is default. Order by attributes may not necessarily be a part of Book:

find book order by book.author.name

And, multiple ordering statements can be specified, just like in SQL:

find book order by book.author.name, book.title desc

8.4 Polymorphism

BeanKeeper uses polymorphism when selecting objects. If you have a Writing and Book class such as:

public class Writing
{
   private String title;
   private Author author;

   ...getters, setters...
}

public class Book extends Writing
{
   private String isbn;

   ...getter, setter...
}

And you have a few inserted Books, and Writings, the following query will potentially return both Book and Writing instances:

find writing where publishyear > 2000

BeanKeeper keeps track of all classes it ever encountered, so you are free to select for any superclasses, interface classes you like. In an extreme case, you can even issue:

List objects = store.find("find object");

Which will return all objects currently inside the database. Of course this particular query is rarely needed, if ever.

8.5 View Selects

Sometimes it is necessary to return multiple attributes which are not part of the same class. These types of queries are often used for reports, or other forms of aggregated data retrieval.

For example, in our bookstore application, we want to generate a report with potential ISBN number conflicts, because these indicate that something has been mistyped, or worse. To do this report with the find keyword, we have to do the following:

find book1(book) where book1.isbn=book2(book).isbn

But this only lists one of the conflicting books, so to display both of the conflicting books, we must make another select for all rows.

find book2(book) where book2.isbn=book1(book).isbn and book1=?

Where the question mark holds the place of the current Book object in the row, which will be passed as an item in the parameters array. Obviously this generates a lot of selects, one select for each row.

Instead, the view keyword offers to select multiple attributes into one result. The above can be written as:

view book1(book).title t1, book2(book).title t2 where book1.isbn=book2.isbn

All view selects will be also return LazyLists, but they will not be populated by instances of the selected class, but Maps. These maps will contain every attribute the statement specified to select. The maps' keys will be either the alias names of the attributes, like in this case t1 and t2, or when no alias is given, the attribute names themselves will be used.

This document was generated by Robert Brautigam on November, 21 2009 using texi2html 1.78.