[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3. Custom Statement Builder

The DAOs have methods to query for an object that matches an id field (queryForId), query for all objects (queryForAll), iterating through all of the objects in a table (iterator), and some other simple martching methods. However, for more custom queries, there is the queryBuilder() method which returns a QueryBuilder object for the DAO with which you can construct custom queries to return a sub-set of your tables.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.1 Query Builder Basics

Here’s how you use the query builder to construct custom queries. First, it is a good pattern to set the column names of the fields with Java constants so you can use them in queries. For example:

 
@DatabaseTable(tableName = "accounts")
public class Account {
    public static final String PASSWORD_FIELD_NAME = "password"; 
    
    …
    @DatabaseField(canBeNull = false, columnName = PASSWORD_FIELD_NAME)
    private String password;
    …

This allows us to construct queries using the password field name without having the renaming of a field in the future break our queries. This should be done even if the name of the field and the column name are the same.

 
// get our query builder from the DAO
QueryBuilder<Account, String> queryBuilder =
  accountDao.queryBuilder();
// the 'password' field must be equal to "qwerty"
queryBuilder.where().eq(Account.PASSWORD_FIELD_NAME, "qwerty");
// prepare our sql statement
PreparedQuery<Account> preparedQuery = queryBuilder.prepare();
// query for all accounts that have "qwerty" as a password
List<Account> accountList = accountDao.query(preparedQuery);

You get a QueryBuilder object from the Dao.queryBuilder() method, call methods on it to build your custom query, call queryBuilder.prepare() which returns a PreparedQuery object, and then pass the PreparedQuery to the DAO’s query or iterator methods.

As a short cut, you can also call the prepare() method on the Where object to do something like the following:

 
// query for all accounts that have that password
List<Account> accountList =
   accountDao.query(
      accountDao.queryBuilder().where()
         .eq(Account.PASSWORD_FIELD_NAME, "qwerty")
         .prepare());

You can use another short cut to call query() or iterator() either on the QueryBuilder or Where objects.

 
// query for all accounts that have that password
List<Account> accountList =
      accountDao.queryBuilder().where()
         .eq(Account.PASSWORD_FIELD_NAME, "qwerty")
         .query();

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.2 Building Queries

There are a couple of different ways that you can build queries. The QueryBuilder has been written for ease of use as well for power users. Simple queries can be done linearly:

 
QueryBuilder<Account, String> queryBuilder =
  accountDao.queryBuilder();
// get the WHERE object to build our query
Where<Account, String> where = queryBuilder.where();
// the name field must be equal to "foo"
where.eq(Account.NAME_FIELD_NAME, "foo");
// and
where.and();
// the password field must be equal to "_secret"
where.eq(Account.PASSWORD_FIELD_NAME, "_secret");
PreparedQuery<Account> preparedQuery = queryBuilder.prepare();

The SQL query that will be generated from the above example will be approximately:

 
SELECT * FROM account
  WHERE (name = 'foo' AND password = '_secret')

If you’d rather chain the methods onto one line (like StringBuilder), this can also be written as:

 
queryBuilder.where()
  .eq(Account.NAME_FIELD_NAME, "foo")
  .and()
  .eq(Account.PASSWORD_FIELD_NAME, "_secret");

If you’d rather use parenthesis to group the comparisons properly then you can call:

 
Where<Account, String> where = queryBuilder.where();
where.and(where.eq(Account.NAME_FIELD_NAME, "foo"),
          where.eq(Account.PASSWORD_FIELD_NAME, "_secret"));

All three of the above call formats produce the same SQL. For complex queries that mix ANDs and ORs, the last format may be necessary to get the grouping correct. For example, here’s a complex query:

 
Where<Account, String> where = queryBuilder.where();
where.or(
  where.and(
    where.eq(Account.NAME_FIELD_NAME, "foo"),
    where.eq(Account.PASSWORD_FIELD_NAME, "_secret")),
  where.and(
    where.eq(Account.NAME_FIELD_NAME, "bar"),
    where.eq(Account.PASSWORD_FIELD_NAME, "qwerty")));

This produces the following approximate SQL:

 
SELECT * FROM account
  WHERE ((name = 'foo' AND password = '_secret')
         OR (name = 'bar' AND password = 'qwerty'))

If you want to do complex queries linearly, you can even use Reverse Polish Notation (of all things). There is a Where.or(int) and Where.and(int) methods which do the operation on the previous number of specified clauses.

 
where.eq(Account.NAME_FIELD_NAME, "foo");
where.eq(Account.PASSWORD_FIELD_NAME, "_secret");
// this does an AND between the previous 2 clauses
// it also puts a clause back on the stack
where.and(2);
where.eq(Account.NAME_FIELD_NAME, "bar"),
where.eq(Account.PASSWORD_FIELD_NAME, "qwerty")));
// this does an AND between the previous 2 clauses
// it also puts a clause back on the stack
where.and(2);
// this does an OR between the previous 2 AND clauses
where.or(2);

The QueryBuilder also allows you to set what specific select columns you want returned, specify the ’ORDER BY’ and ’GROUP BY’ fields, and various other SQL features (LIKE, IN, >, >=, <, <=, <>, IS NULL, DISTINCT, …). See section Where Capabilities. You can also see the javadocs on QueryBuilder and Where classes for more information. Here’s a good SQL reference site.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.3 Building Statements

The DAO can also be used to construct custom UPDATE and DELETE statements. Update statements are used to change certain fields in rows from the table that match the WHERE pattern – or update all rows if no where(). Delete statements are used to delete rows from the table that match the WHERE pattern – or delete all rows if no where().

For example, if you want to update the passwords for all of the Accounts in your table that are currently null to the string "none", then you might do something like the following:

 
UpdateBuilder<Account, String> updateBuilder =
  accountDao.updateBuilder();
// update the password to be "none"
updateBuilder.updateColumnValue("password", "none");
// only update the rows where password is null
updateBuilder.where().isNull(Account.PASSWORD_FIELD_NAME);
updateBuilder.update();

With update, you can also specify the update value to be an expression:

 
// update hasDog boolean to true if dogC > 0
updateBuilder.updateColumnExpression(
  "hasDog", "dogC > 0");

To help you construct your expressions, you can use the UpdateBuilder’s escape methods escapeColumnName and escapeValue can take a string or a StringBuilder. This will protect you if columns or values are reserved words.

If, instead, you wanted to delete the rows in the Accounts table whose password is currently null, then you might do something like the following:

 
DeleteBuilder<Account, String> deleteBuilder =
  accountDao.deleteBuilder();
// only delete the rows where password is null
deleteBuilder.where().isNull(Account.PASSWORD_FIELD_NAME);
deleteBuilder.delete();

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.4 QueryBuilder Capabilities

The following are some details about the various method calls on the QueryBuilder object which build custom select, delete, and update statements. See the Javadocs for the QueryBuilder class for the most up-to-date information about the available methods. Most of these methods return the QueryBuilder object so they can be chained.

Here’s a good tutorial of SQL commands.

distinct()

Add "DISTINCT" clause to the SQL query statement.

NOTE: Use of this means that the resulting objects may not have a valid ID column value so cannot be deleted or updated.

groupBy(String columnName)

This adds a "GROUP" clause to the SQL query statement for the specified column name. This can be called multiple times to group by multiple columns.

NOTE: Use of this means that the resulting objects may not have a valid ID column value so cannot be deleted or updated.

groupByRaw(String sql)

Add a raw SQL "GROUP BY" clause to the SQL query statement. This allows you to group by things that are not just column names. The SQL argument should not include the "GROUP BY" string.

having(String sql)

Add a raw SQL "HAVING" clause to the SQL query statement. This allows you to apply filters after the WHERE and other matching has been completed. This is usually done with aggregate functions. The SQL argument should not include the "HAVING" string.

join(QueryBuilder joinedQueryBuilder)

Join with another query builder. This will add into the SQL something close to "INNER JOIN other-table ...". Either the object associated with the current query builder or the argument query builder must have a foreign field of the other one. An exception will be thrown otherwise. This can be called multiple times to join with more than one table.

leftJoin(QueryBuilder joinedQueryBuilder)

Same as join(QueryBuilder) but it will use "LEFT JOIN" instead. See: LEFT JOIN SQL documentation.

NOTE: RIGHT and FULL JOIN SQL commands are not supported because we are only returning objects from the "left" table.

limit(Integer maxRows)

Limit the output to maxRows maximum number of rows. Set to null for no limit (the default).

offset(Integer startRow)

Start the output at this row number. Set to null for no offset (the default). If you are paging through a table, you should consider using the Dao.iterator() method instead which handles paging with a database cursor. Otherwise, if you are paging you probably want to specify a column to orderBy.

NOTE: This is not supported for all databases. Also, for some databases, the limit must also be specified since the offset is an argument of the limit.

orderBy(String columnName, boolean ascending)

Add "ORDER BY" clause to the SQL query statement to order the results by the specified column name. Use the ascending boolean to get a ascending or descending order. This can be called multiple times to group by multiple columns.

orderByRaw(String sql)

Add a raw SQL "ORDER BY" clause to the SQL query statement. This allows you to order by things that are not just columns but can include calculations. The SQL argument should not include the "ORDER BY" string.

prepare()

Build and return a prepared query that can be used by Dao.query(PreparedQuery) or Dao.iterator(PreparedQuery) methods. If you change the where or make other calls you will need to re-call this method to re-prepare the statement for execution.

selectColumns(String... columns)

Add columns to be returned by the SELECT query and set on any resulting objects. If no columns are selected then all columns are returned by default. For classes with id columns, the id column is added to the select list automagically. All fields not selected in the object with be their default values (null, 0, false, etc.).

This allows you to in effect have lazy loaded fields. You can specify exactly which fields to be set on the resulting objects. This is especially helpful if you have large fields in a table that you don’t always want to access. To get all of the fields on the object later, you can either do another query or call refresh() with the object. This can be called multiple times to add more columns to select.

WARNING: If you specify any columns to return, then any foreign-collection fields will be returned as null unless their ForeignCollectionField.columnName is also in the list. See ForeignCollectionField.

selectColumns(Iterable<String> columns)

Same as the above but with an iterable (such as a collection) instead of a variable list of column names. This can be called multiple times to add more columns to select.

selectRaw(String... columns)

Add raw columns or aggregate functions (COUNT, MAX, ...) to the query. This will turn the query into something only suitable for using as a raw query. This can be called multiple times to add more columns to select. See section Issuing Raw Queries.

where()

Build and return the Where object with which you can customize your WHERE SQL statements. See section Where Capabilities.

query()

Convenience method to perform the query. Same as dao.query(queryBuilder.prepare()).

queryForFirst()

Convenience method to perform the query and return the first result. Same as dao.queryForFirst(queryBuilder.prepare()).

queryRawFirst()

Convenience method to perform a raw query on the generated state and then return the first result as a String[]. Same as Dao.queryRaw(String) and GenericRawResults.getFirstResult().

iterator()

Convenience method to generate the iterator for the query. Same as dao.iterator(queryBuilder.prepare()).

reset()

Resets all of the settings in the QueryBuilder so it can be reused.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.5 Where Capabilities

The following are some details about the various method calls for adding WHERE SQL statements to your custom select, delete, and update statements. See the Javadocs for the Where class for the most up-to-date information about the available methods. All of the methods return the Where object so you can chain them together.

Here’s a good tutorial of SQL commands.

and()

Binary AND operation which takes the previous clause and the next clause and AND’s them together. This is when you are using inline query calls.

and(Where<T, ID> first, Where<T, ID> second, Where<T, ID>... others)

AND operation which takes 2 or more arguments and AND’s them together. This is when you are not using inline query calls but instead want to use standard arguments.

NOTE: There is no guarantee of the order of the clauses that are generated in the final query.

NOTE: There is an annoying code warning that I get with the usage of this method with more than 2 arguments that can be ignored.

and(int numClauses)

This method needs to be used carefully. This will absorb a number of clauses that were registered previously with calls to Where.eq() or other methods and will string them together with AND’s. There is no way to verify the number of previous clauses so the programmer has to count precisely.

NOTE: There is no guarantee of the order of the clauses that are generated in the final query.

between(String columnName, Object low, Object high)

Add a BETWEEN clause which makes sure the column is between the low and high parameters.

eq(String columnName, Object value)

Add a ’=’ clause which makes sure the column is equal to the value.

exists(QueryBuilder<?, ?> subQueryBuilder)

Add a EXISTS clause with a sub-query inside of parenthesis. This will return returns as long as the inner query returns results.

NOTE: The sub-query will be prepared at the same time that the outside query is.

ge(String columnName, Object value)

Add a ’>=’ clause which makes sure the column is greater-than or equals-to the value.

gt(String columnName, Object value)

Add a ’>’ clause which makes sure the column is greater-than the value.

idEq(ID id)

Add a clause where the ID is equals to the argument.

idEq(Dao<OD, ?> dataDao, OD data)

Add a clause where the ID is extracted from an existing object.

in(String columnName, Iterable<?> objects)

Add a IN clause which makes sure the column is equal-to one of the objects from the Iterable passed in. Most likely the Iterable should probably be on of the Collection classes such as a List or Set.

in(String columnName, Object... objects)

Add a IN clause which makes sure the column is equal-to one of the objects passed in.

in(String columnName, QueryBuilder<?, ?> subQueryBuilder)

Add a IN clause which makes sure the column is in one of the columns returned from a sub-query inside of parenthesis. The QueryBuilder must return 1 and only one column which can be set with the QueryBuilder.selectColumns(String...) method calls. That 1 argument must match the SQL type of the column-name passed to this method. See also Building Join Queries.

NOTE: The sub-query will be prepared at the same time that the outside query is.

isNull(String columnName)

Add a ’IS NULL’ clause which makes sure the column’s value is null. ’=’ NULL does not work.

isNotNull(String columnName)

Add a ’IS NOT NULL’ clause so the column must not be null. ’<>’ NULL does not work.

le(String columnName, Object value)

Add a ’<=’ clause which makes sure the column is less-than or equals-to the value.

lt(String columnName, Object value)

Add a ’<’ clause which makes sure the column is less-than the value.

like(String columnName, Object value)

Add a LIKE clause which makes sure the column match the value using ’%’ patterns.

ne(String columnName, Object value)

Add a ’<>’ clause which makes sure the column is not-equal-to the value.

not()

Used to NOT the next clause specified when using inline query calls.

not(Where<T, ID> comparison)

Used to NOT the argument clause specified. This is when you are not using inline query calls but instead want to use standard arguments.

notIn(String columnName, Iterable<?> objects)

Same as the IN clause but negated. Results are returned that are not in the objects from the iterator.

in(String columnName, Object... objects)

Same as IN clause but negated. Results are returned that are not in the variable array of objects.

notIn(String columnName, QueryBuilder<?, ?> subQueryBuilder)

Same as IN clause but negated. Results are returned that are not in the results from the sub-query.

or()

Binary OR operation which takes the previous clause and the next clause and OR’s them together. This is when you are using inline query calls.

or(Where<T, ID> first, Where<T, ID> second, Where<T, ID>... others)

OR operation which takes 2 or more arguments and OR’s them together. This is when you are not using inline query calls but instead want to use standard arguments.

NOTE: There is no guarantee of the order of the clauses that are generated in the final query.

NOTE: There is an annoying code warning that I get with the usage of this method with more than 2 arguments that can be ignored.

or(int numClauses)

This method needs to be used carefully. This will absorb a number of clauses that were registered previously with calls to Where.eq() or other methods and will string them together with OR’s. There is no way to verify the number of previous clauses so the programmer has to count precisely.

raw(String rawStatement)

Add a raw statement as part of the WHERE that can be anything that the database supports. Using the more structured methods above is recommended but this gives more control over the query and allows you to utilize database specific features.

prepare()

A short-cut for calling prepare() on the original QueryBuilder.prepare(). This method returns a PreparedQuery object.

reset()

Reset the where object so it can be reused in a new query but with the same QueryBuilder.

query()

Convenience method to perform the query. Same as queryBuilder.query() and dao.query(queryBuilder.prepare()).

queryRaw()

Convenience method to perform the query and return raw results. Same as queryBuilder.queryRaw() and dao.queryRaw(...).

queryForFirst()

Convenience method to perform the query and return the first result. Same as queryBuilder.queryForFirst() and dao.queryForFirst(queryBuilder.prepare()).

queryRawFirst()

Convenience method to perform the query and return the first raw result. Same as queryBuilder.queryRawFirst() and dao.queryRaw(...).

countOf()

Convenience method to perform the query and return the number of results. Same as queryBuilder.countOf() and dao.countOf(queryBuilder.prepare()).

iterator()

Convenience method to generate the iterator for the query. Same as queryBuilder.iterator() and dao.iterator(queryBuilder.prepare()).


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.6 Using Select Arguments

Select Arguments are arguments that are used in WHERE operations can be specified directly as value arguments (as in the above examples) or as a SelectArg object. SelectArgs are used to set the value of an argument at a later time – they generate a SQL ’?’.

For example:

 
QueryBuilder<Account, String> queryBuilder =
  accountDao.queryBuilder();
Where<Account, String> where = queryBuilder.where();
SelectArg selectArg = new SelectArg();
// define our query as 'name = ?'
where.eq(Account.NAME_FIELD_NAME, selectArg);
// prepare it so it is ready for later query or iterator calls
PreparedQuery<Account> preparedQuery = queryBuilder.prepare();

// later we can set the select argument and issue the query
selectArg.setValue("foo");
List<Account> accounts = accountDao.query(preparedQuery);
// then we can set the select argument to another
// value and re-run the query
selectArg.setValue("bar");
accounts = accountDao.query(preparedQuery);

It is a common problem for people to try to build queries or other statements with arguments that contain quote characters. These quotes can clash with the auto-generated SQL and result in syntax errors. In these cases a SelectArg is required. In addition, if you are passing in arguments from user-input, you can use SelectArg to protect yourself from SQL injection security problems. Lastly, certain data types use an internal SelectArg object because the string value of the object does not reliably match the database form of the object – java.util.Date is one example of such a type.

NOTE: SelectArg objects have protection against being used in more than one column name. You must instantiate a new object if you want to use a SelectArg with another column.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.7 Using Column Arguments

Typically when you use the QueryBuilder you ae comparing a column and a value. To compare one column against another you can use a ColumnArg.

For example:

 
QueryBuilder<Account, String> queryBuilder =
  accountDao.queryBuilder();
// list all of the accounts that have the same
// name and password field 
queryBuilder.where().eq(Account.NAME_FIELD_NAME,
    new ColumnArg(Account.PASSWORD_FIELD_NAME));
List<Account> results = queryBuilder.query();

The ColumnArg can also take a table-name which is useful with JOIN queries to compare a column from one table with one in another table. See section Building Join Queries.

For example:

 
QueryBuilder<Order, Integer> orderQb = orderDao.queryBuilder();
orderQb.where().ge("amount", 100.0F);
QueryBuilder<Account, Integer> accountQb = accountDao.queryBuilder();
// find all accounts who have an order larger than their limit
accountQb.where().gt(Account.MAX_LIMIT_AMOUNT_FIELD,
     new ColumnArg("orders", Order.AMOUNT_FIELD));
// join with the order query
List<Account> results = accountQb.join(orderQb).query();

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.8 Building Join Queries

ORMLite supports basic JOIN SQL queries. For more information about how JOIN queries work see JOIN documentation.

You setup a join query by building 2 QueryBuilder objects – one in the DAO from which you are returning objects, and another DAO which is related to the first. One of the objects must be a foreign field of the other object (either direction is supported) or the join methods will throw an exception.

For example, let’s say you want to return a set of Account objects what have an Order that is larger than $100. We would set it up something like the following:

 
QueryBuilder<Order, Integer> orderQb = orderDao.queryBuilder();
orderQb.where().ge("amount", 100.0F);
QueryBuilder<Account, Integer> accountQb = accountDao.queryBuilder();
// join with the order query
List<Account> results = accountQb.join(orderQb).query();

This will return all of the Account records that have a corresponding Order that has a value field that is more than 100.0. Notice that none of the Order information is returned by the query – just Account information is retrieved. You could also reverse the situation and, for example, return all of the orders that have an account whose first-name is equal to "Bob". To compare fields from different tables, you can use the ColumnArg class. See section Using Column Arguments.

ORMlite also supports the concept of "LEFT JOIN" which means, in the context of the above example, that accounts that have no orders will also be returned. Typically with a "normal" join, only accounts that have orders are candidates to be returned by the query. ORMLite does not support "RIGHT JOIN" or "FULL JOIN" since none of the "right" object’s information is being returned by the query.

Please note that other ORM libraries use JOIN statements to retrieve results from multiple tables to fill in foreign fields and foreign collections. ORMLite does not support this feature. You can only get results from one table using it.


[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

This document was generated by Gray Watson on December 16, 2013 using texi2html 1.82.