Queries

Queries return entities matching certain criteria. You can formulate queries with raw SQL, or, the preferred way, using the QueryBuilder API of greenDAO. Queries also support lazy-loading results, which may save memory and performance when operating on large result sets.

QueryBuilder

The QueryBuilder class lets you build custom queries for your entities without touching SQL. Writing SQL is not everyone’s favorite and it is also prone to errors, which you will only notice at run time. QueryBuilder is easier to use and saves you from writing SQL. Also, getting bugs in your query code gets much harder, as the syntax is checked at compile time. The compile time checks include even references to properties, which is made possible by the code generator approach behind greenDAO.

Example: Query for all users with the first name “Joe” ordered by their last name.

Nested conditions example: Get users with the first name “Joe” that are born in October 1970 or later.
Let’s say we have a user’s birthday as separate properties for year, month, and day. Then, we could express the the condition in a more formal way: First name is “Joe” AND (year of birth is greater than 1970 OR (year of birth is 1970 AND month of birth is equal to or greater than 10 (October).

Query and LazyList

The Query class represents a query that can be executed multiple times. When you use one of the methods in QueryBuilder to fetch a result (like list()), QueryBuilder internally uses the Query class. If you want to run the same query more than one, you should call build() on the QueryBuilder to create the Query without executing it.

greenDAO supports unique results (0 or 1 result) and result lists. If you expect a unique result call unique() on the Query (or QueryBuilder), which will give you a single result or null, if no matching entity was found. If your use case prohibits null as an outcome, call uniqueOrThrow() which will guarantee to return an non-null entity (otherwise it will throw a DaoException).

If you expect multiple entities as a query result, you use one of the list… methods:

list()	All entities are loaded into memory. The result is typically an ArrayList with no magic involved. Easiest to use.
listLazy()	Entities are loaded into memory on-demand. Once an element in the list is accessed for the first time, it is loaded and cached for future use. Must be closed.
listLazyUncached()	A “virtual” list of entities: any access to a list element results in loading its data from the database. Must be closed.
listIterator()	Let’s you iterate through results by loading the data on-demand (lazily). Data is not cached. Must be closed.

The methods listLazy, listLazyUncached, and listIterator make use of greenDAO’s LazyList class. To load data on-demand, it holds a reference to a database cursor. This is the reason must ensure to close the lazy lists and iterators (typically in a try/finally block). The cached lazy list from listLazy() and the lazy iterator from listIterator() closes the cursor automatically, once all elements are accessed or traversed. However, it’s your job to call close() if the list processing stops prematurely.

Executing Queries multiple times

Once you built a query using the QueryBuilder, the Query object can be reused to execute the query later. This is more efficient than always creating new Query objects. If the query parameters don’t change you can just call one of the list/unique methods again. If there may be changed parameters, you have to call the setParameter method for each changed parameter. Currently, individual parameters are addressed by the zero-based parameter index. The index is based on the order you pass the parameter to QueryBuilder.

The following example uses a Query object to fetch users with Joe as a first name born in 1970:

Using this Query object, we can search for Marias born in 1977 later:

Executing queries in multiple threads

If you use queries in multiple threads, you must call forCurrentThread() on the query to get a Query instance for the current thread. Starting with greenDAO 1.3, object instances of Query are bound to their owning thread that build the query. This lets you safely set parameters on the Query object while other threads cannot interfere. If other threads try to set parameters on the query or execute the query bound to another thread, an exception will be thrown. Like this, you don’t need a synchronized statement. In fact you should avoid locking because this may lead to deadlocks if concurrent transactions use the same Query object.

To avoid those potential deadlocks completely, greenDAO 1.3 introduced the method forCurrentThread(). This will return a thread-local instance of the Query, which is safe to use in the current thread. Every time, forCurrentThread() is called, the parameters are set to the initial parameters at the time the query was built using its builder.

Raw queries

There are two approaches to execute raw SQL resulting in entities. The preferred way is using QueryBuilder and WhereCondition.StringCondition. Using this you can pass any SQL fragments for the WHERE clause to the query builder. The following code is a (clumsy) example how you could use this with a sub select faking a join:

In case QueryBuilder does not provide the feature you need (e.g. joins), you can fall back to one of the queryRaw or queryRawCreate methods. They allow you to pass a raw SQL string, which is appended after the SELECT and the entities columns. This way, you can have any WHERE and ORDER BY clause you want to select entities. The entity table can be referred using the alias “T”:

The following example shows how to create a query that retrieves users of the group called “admin” using a join:

Note: You can refer to table and column names using generated constants. This is recommended to avoid typos because the compiler will check the names. In an entity’s DAO, you will find TABLENAME holding the name of the database table, and an inner class Properties with constants for all properties (field columnName).

Delete queries

Bulk deletes do not delete individual entities, but all entities matching some criteria. To perform bulk deletes, create a QueryBuilder, call its buildDelete method, and execute the returned DeleteQuery. This part of the API may change in the future, e.g. convenience methods may be added etc. Keep in mind, that bulk deletes currently do not affect entities in the identity scope, e.g. you could “resurrect” deleted entities if they have been cached before and are accessed by their ID (load method). Consider clearing the identity scope for now, if that may cause issues for your use case.

Troubleshooting queries

Your query does not return the result you expected? There are two static flags to enable SQL and parameter logging on the QueryBuilder:

Those will log the resulting SQL command and the passed values when calling one of the build methods. Like this you can compare if you actually get what you expected. It might also help to copy generated SQL into some SQLite database explorer and execute it with the values.