exedio persistence

Persistence Framework for Java.

Searching Trail

This trail shows you how to query your data with exedio persistence. If you are familiar with SQL, you will recognize it's features within the API provided by the framework. However, you will never directly write SQL, nor any other text based query language. This will show you most common mistakes as compile-time errors, and it will completely remove any chance of SQL injection.

Simple Query

First we define a simple example model consisting of one persistent class only:

class Customer extends Item
{
   static final StringField email =
       new StringField();
   static final IntegerField loginCounter =
       new IntegerField();
}

Lets start with a simple query searching for all instances of this class:

List<Customer> allCustomers =
   Customer.TYPE.search();

Note, that the static final field Customer.TYPE is generated by the source instrumentor. Compiling this statement does not cause an "unchecked warning", the return type of search is really a List<Customer>

If you put the code above into class Customer, you can write even shorter:

List<Customer> allCustomers = TYPE.search();

For brevity we will assume, that all the examples below are located in class Customer.

Conditions

Conditions restrict the number of items found by a query. This is equivalent to the "where clause" in SQL. Lets search for the customer with a certain email address:

TYPE.search(email.equal("search@exedio.com"));

For a "like condition" write:

TYPE.search(email.like("search@exedio.%"));

There is a convenience method, that lets you write equivalently but shorter:

TYPE.search(email.startsWith("search@exedio."));

Of course searching works for integer fields as well. Here a query, that searches for customers, that have logged in at least 8 times:

TYPE.search(loginCounter.greaterOrEqual(8));

To demonstrate composite conditions, here another query, that searches for customers, that have logged in at least 8 times, but at most 12 times:

TYPE.search(loginCounter.greaterOrEqual(8).and(loginCounter.lessOrEqual(12)));

which is equivalent to:

TYPE.search(loginCounter.between(8, 12));

Of course there is an OR-condition as well:

TYPE.search(email.equal("search@exedio.com").or(email.equal("search@exedio.de")));

which is equivalent to:

TYPE.search(email.in("search@exedio.com", "search@exedio.de"));

In contrast to the queries above, all the following queries will not compile:

TYPE.search(email.equal(8));
TYPE.search(loginCounter.greaterOrEqual("search@exedio.com"));
TYPE.search(loginCounter.like("search@exedio.%"));

The first one tries to compare a string field to an integer literal. The second query compares an integer field to a string field. The third uses the operator "like" on an integer field instead of a string field.

Sorting

Here again a query, that searches for customers, that have logged in more than 8 times, but now sorted by their email adresses:

TYPE.search(loginCounter.greaterOrEqual(8), email, true);

The third parameter specifies accending order. Use false to order descendingly.

If you want to order by multiple fields, you have to use an explicit query for the first time. This looks like this:

Query<Customer> q =
   TYPE.newQuery(loginCounter.greaterOrEqual(8));
q.addOrderBy(loginCounter);
q.addOrderByDescending(email);
List<Customer> customers = q.search();

This query finds all customers, which have logged in at least 8 times, sorted by the number of their logins ascendingly and their email adress descendingly.

Note, that all the methods Type.search(...) in the examples before use an instance of Query in their implementation as well. They are convenience methods for the most common and simple queries.

Limits

Sometimes you want to fetch just a part of the result of a query. This is commonly needed for paging. The following query gets the third page of a list of all customers, which have logged in at least 8 times. Each page contains 10 customers:

Query<Customer> q =
   TYPE.newQuery(loginCounter.greaterOrEqual(8));
q.setLimit(20, 10);
List<Customer> customers = q.search();

Sometimes you may just want to have the first element of the result. Then you would probably write something like this:

Query<Customer> q =
   TYPE.newQuery(loginCounter.greaterOrEqual(8));
q.setLimit(0, 1);
List<Customer> customers = q.search();

After such a one-line-only query, there often some code like this:

if(customers.size()==0)
   return null;
else if(customers.size()==1)
   return customers.get(0);
else
   throw new RuntimeException("oops");

There is a convenience method searchSingleton, which avoids writing such code over and over again:

Query<Customer> q =
   TYPE.newQuery(loginCounter.greaterOrEqual(8));
q.setLimit(0, 1);
return q.searchSingleton();

This method is useful, whenever you are sure, that the result set can have at most one element. This happens if you use limits as above or you restrict your query to match a unique field or when using aggregators as explained below.

Selecting and Aggregating

Sometimes you just want to deal with values of fields, not items. For instance you might want to have a list of the email addresses of all customers:

Query<String> q = new Query<String>(email);
List<String> emails = q.search();

Of course you can combine this with sorting and limits. To get a list of the greatest 10 login counters you could write:

Query<Integer> q = new Query<Integer>(loginCounter);
q.setOrderBy(loginCounter, true);
q.setLimit(0, 10);
List<Integer> loginCounters = q.search();

Or you might want to compute the sum of the login counters of all customers. This is called aggregating:

Query<Integer> q =
   new Query<Integer>(loginCounter.sum());
Integer totalLoginCounters = q.searchSingleton();

Aggregates can be used for getting minimum and maximum as well. The following example queries the greatest login counter:

Query<Integer> q =
   new Query<Integer>(loginCounter.max());
Integer maximumLoginCounter = q.searchSingleton();

Joins

To demonstrate joins with exedio persistence, we have to define another persistent class:

class Order extends Item
{
   static final ItemField<Customer> buyer =
       ItemField.create(Customer.class);
   static final DoubleField total =
       new DoubleField();
}

Now we can use a join to search for all orders, where the customer has a email address from the .com top level domain:

Query<Order> q = Order.TYPE.newQuery(Customer.email.endsWith(".com"));
q.join(Customer.TYPE, Order.buyer.equal(Customer.TYPE.getThis()));
List<Order> orders = q.search();

Note the join condition in the second line, it specifies a natural join on the item field buyer. The join condition can also be specified in a short form, using a convenience method:

q.join(Customer.TYPE, Order.buyer.equalTarget());