Contents
Queries return entities matching certain criteria. In greenDAO you can formulate queries with raw SQL, or more easily with the QueryBuilder API.
Also, queries support lazy-loading results, which may save memory and performance when operating on large result sets.
QueryBuilder
Writing SQL can be difficult and is prone to errors which are only noticed at run time. The QueryBuilder class lets you build custom queries for your entities without SQL and helps detect errors already at compile time.
Simple condition example: Query for all users with the first name “Joe”, ordered by last name:
| 1 2 3 4 | List<User> joes = userDao.queryBuilder() .where(Properties.FirstName.eq("Joe")) .orderAsc(Properties.LastName) .list(); |
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 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)) (the 10th month, October).
| 1 2 3 4 5 | QueryBuilder<User> qb = userDao.queryBuilder(); qb.where(Properties.FirstName.eq("Joe"), qb.or(Properties.YearOfBirth.gt(1970), qb.and(Properties.YearOfBirth.eq(1970), Properties.MonthOfBirth.ge(10)))); List<User> youngJoes = qb.list(); |
Order
You can order the results of a query. Based on the example of people with last names and year of birth:
| 1 2 3 4 5 6 7 8 | // order by last name queryBuilder.orderAsc(Properties.LastName); // in reverse queryBuilder.orderDesc(Properties.LastName); // order by last name and year of birth queryBuilder.orderAsc(Properties.LastName).orderDesc(Properties.YearOfBirth); |
The default collation used by greenDAO is COLLATE NOCASE , though it can be customized with stringOrderCollation(). See the QueryBuilder class documentation for additional methods that affect the order of results.
Limit, Offset, and Pagination
Sometimes you only need a subset of a query, for example the first 10 elements to display in your user interface. This is especially helpful (and resourceful) when you have a high number of entities and you cannot limit the result using “where” statements only. QueryBuilder<T> has methods to define a limit and an offset:
limit(int): Limits the number of results returned by the query.
offset(int): Sets the offset for query results in combination with limit(int). The first offset results are skipped and the total number of results will be limited by limit(int). You cannot use offset without limit(int).
Custom Types as Parameters
Usually, greenDAO maps the types used in queries transparently. For example, boolean is mapped to INTEGER with 0 or 1 values, and Date is mapped to (long) INTEGER values.
Custom types are one exception to this: you always have to use the database value type when building a query. For example, if you have an enum type mapped to an int value using a converter, you should use the int value in the query.
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<T> internally uses the Query<T> class. If you want to run the same query more than once, you should call build() on the QueryBuilder<T> 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<T> (or QueryBuilder<T>), 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, use one of the following 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 you 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.
However, parameters may be changed: call the setParameter method for each changed parameter. Currently, individual parameters are addressed by a zero-based parameter index. The index is based on the order you pass the parameters to QueryBuilder. For example:
| 1 2 3 4 5 6 7 8 9 10 11 | // fetch users with Joe as a first name born in 1970 Query<User> query = userDao.queryBuilder().where( Properties.FirstName.eq("Joe"), Properties.YearOfBirth.eq(1970) ).build(); List<User> joesOf1970 = query.list(); // using the same Query object, we can change the parameters // to search for Marias born in 1977 later: query.setParameter(0, "Maria"); query.setParameter(1, 1977); List<User> mariasOf1977 = query.list(); |
Executing queries in multiple threads
If you use queries in multiple threads, you must call forCurrentThread() to get a Query instance for the current thread. Object instances of Query are bound to their owning thread that built 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.
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
In case QueryBuilder does not provide what you need, there are two approaches to execute raw SQL that still return entity objects. The first, preferred way is to use QueryBuilder and WhereCondition.StringCondition. With this you can pass any SQL fragments as the WHERE clause to the query builder.
The following code is a theoretical example how you could run a sub select (using a join would be the better solution):
| 1 2 3 4 | Query<User> query = userDao.queryBuilder().where( new StringCondition("_ID IN " + "(SELECT USER_ID FROM USER_MESSAGE WHERE READ_FLAG = 0)") ).build(); |
The second approach outside of QueryBuilder is to use 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 to using the alias T.
The following example shows how to create a query that retrieves users of the group called “admin” using a join (again, greenDAO supports joins natively, this is just to demonstrate):
| 1 2 3 | Query<User> query = userDao.queryRawCreate( ", GROUP G WHERE G.NAME=? AND T.GROUP_ID=G._ID", "admin" ); |
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, for example convenience methods may be added.
Keep in mind, that bulk deletes currently do not affect entities in the identity scope, for example 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 expected result? There are two static flags to enable SQL and parameter logging on QueryBuilder:
| 1 2 | QueryBuilder.LOG_SQL = true; QueryBuilder.LOG_VALUES = true; |
Those will log the resulting SQL command and the passed values when calling one of the build methods, compare them against what you actually expected. Furthermore, it might help to copy the generated SQL into some SQLite database explorer and see how it executes.
