com.j256.ormlite.stmt
Class QueryBuilder<T,ID>

java.lang.Object
  extended by com.j256.ormlite.stmt.StatementBuilder<T,ID>
      extended by com.j256.ormlite.stmt.QueryBuilder<T,ID>
Type Parameters:
T - The class that the code will be operating on.
ID - The class of the ID column associated with the class. The T class does not require an ID field. The class needs an ID parameter however so you can use Void or Object to satisfy the compiler.

public class QueryBuilder<T,ID>
extends StatementBuilder<T,ID>

Assists in building sql query (SELECT) statements for a particular table in a particular database.

Here is a good tutorial of SQL commands.

Author:
graywatson

Nested Class Summary
static class QueryBuilder.InternalQueryBuilderWrapper
          Internal class used to expose methods to internal classes but through a wrapper instead of a builder.
 
Nested classes/interfaces inherited from class com.j256.ormlite.stmt.StatementBuilder
StatementBuilder.StatementInfo, StatementBuilder.StatementType, StatementBuilder.WhereOperation
 
Field Summary
 
Fields inherited from class com.j256.ormlite.stmt.StatementBuilder
addTableName, dao, databaseType, tableInfo, tableName, type, where
 
Constructor Summary
QueryBuilder(DatabaseType databaseType, TableInfo<T,ID> tableInfo, Dao<T,ID> dao)
           
 
Method Summary
protected  void appendStatementEnd(StringBuilder sb, List<ArgumentHolder> argList)
          Append the end of our statement string to the StringBuilder.
protected  void appendStatementStart(StringBuilder sb, List<ArgumentHolder> argList)
          Append the start of our statement string to the StringBuilder.
protected  boolean appendWhereStatement(StringBuilder sb, List<ArgumentHolder> argList, StatementBuilder.WhereOperation operation)
          Append the WHERE part of the statement to the StringBuilder.
 void clear()
          Deprecated. Renamed to be reset().
 long countOf()
          Sets the count-of query flag using setCountOf(boolean) to true and then calls Dao.countOf(PreparedQuery).
 QueryBuilder<T,ID> distinct()
          Add "DISTINCT" clause to the SQL query statement.
protected  FieldType[] getResultFieldTypes()
          Get the result array from our statement after the StatementBuilder.appendStatementStart(StringBuilder, List) was called.
 QueryBuilder<T,ID> groupBy(String columnName)
          Add "GROUP BY" clause to the SQL query statement.
 QueryBuilder<T,ID> groupByRaw(String rawSql)
          Add a raw SQL "GROUP BY" clause to the SQL query statement.
 QueryBuilder<T,ID> having(String having)
          Add raw SQL "HAVING" clause to the SQL query statement.
 CloseableIterator<T> iterator()
          A short cut to Dao.iterator(PreparedQuery).
 QueryBuilder<T,ID> join(QueryBuilder<?,?> joinedQueryBuilder)
          Join with another query builder.
 QueryBuilder<T,ID> joinOr(QueryBuilder<?,?> joinedQueryBuilder)
          Like join(QueryBuilder) but this combines the WHERE statements of two query builders with a SQL "OR".
 QueryBuilder<T,ID> leftJoin(QueryBuilder<?,?> joinedQueryBuilder)
          Similar to join(QueryBuilder) but it will use "LEFT JOIN" instead.
 QueryBuilder<T,ID> leftJoinOr(QueryBuilder<?,?> joinedQueryBuilder)
          Like leftJoin(QueryBuilder) but this combines the WHERE statements of two query builders with a SQL "OR".
 QueryBuilder<T,ID> limit(int maxRows)
          Deprecated. Should use limit(Long)
 QueryBuilder<T,ID> limit(Long maxRows)
          Limit the output to maxRows maximum number of rows.
 QueryBuilder<T,ID> offset(int startRow)
          Deprecated. Should use offset(Long)
 QueryBuilder<T,ID> offset(Long startRow)
          Start the output at this row number.
 QueryBuilder<T,ID> orderBy(String columnName, boolean ascending)
          Add "ORDER BY" clause to the SQL query statement.
 QueryBuilder<T,ID> orderByRaw(String rawSql)
          Add raw SQL "ORDER BY" clause to the SQL query statement.
 QueryBuilder<T,ID> orderByRaw(String rawSql, ArgumentHolder... args)
          Add raw SQL "ORDER BY" clause to the SQL query statement.
 PreparedQuery<T> prepare()
          Build and return a prepared query that can be used by Dao.query(PreparedQuery) or Dao.iterator(PreparedQuery) methods.
 List<T> query()
          A short cut to Dao.query(PreparedQuery).
 T queryForFirst()
          A short cut to Dao.queryForFirst(PreparedQuery).
 GenericRawResults<String[]> queryRaw()
          A short cut to Dao.queryRaw(String, String...).
 String[] queryRawFirst()
          A short cut to Dao.queryRaw(String, String...) and GenericRawResults.getFirstResult().
 void reset()
          Clear out all of the statement settings so we can reuse the builder.
 QueryBuilder<T,ID> selectColumns(Iterable<String> columns)
          Same as selectColumns(String...) except the columns are specified as an iterable -- probably will be a Collection.
 QueryBuilder<T,ID> selectColumns(String... columns)
          Add columns to be returned by the SELECT query.
 QueryBuilder<T,ID> selectRaw(String... columns)
          Add raw columns or aggregate functions (COUNT, MAX, ...) to the query.
 QueryBuilder<T,ID> setCountOf(boolean countOf)
          Set whether or not we should only return the count of the results.
protected  boolean shouldPrependTableNameToColumns()
          Return true if we need to prepend table-name to columns.
 
Methods inherited from class com.j256.ormlite.stmt.StatementBuilder
appendStatementString, buildStatementString, prepareStatement, prepareStatementInfo, prepareStatementString, setWhere, verifyColumnName, where
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

QueryBuilder

public QueryBuilder(DatabaseType databaseType,
                    TableInfo<T,ID> tableInfo,
                    Dao<T,ID> dao)
Method Detail

prepare

public PreparedQuery<T> prepare()
                         throws SQLException
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.

Throws:
SQLException

selectColumns

public QueryBuilder<T,ID> selectColumns(String... columns)
Add columns to be returned by the SELECT query. 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. 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.


selectColumns

public QueryBuilder<T,ID> selectColumns(Iterable<String> columns)
Same as selectColumns(String...) except the columns are specified as an iterable -- probably will be a Collection. This can be called multiple times to add more columns to select.


selectRaw

public QueryBuilder<T,ID> selectRaw(String... columns)
Add raw columns or aggregate functions (COUNT, MAX, ...) to the query. This will turn the query into something only suitable for the Dao.queryRaw(String, String...) type of statement. This can be called multiple times to add more columns to select.


groupBy

public QueryBuilder<T,ID> groupBy(String columnName)
Add "GROUP BY" clause to the SQL query statement. This can be called multiple times to add additional "GROUP BY" clauses.

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


groupByRaw

public QueryBuilder<T,ID> groupByRaw(String rawSql)
Add a raw SQL "GROUP BY" clause to the SQL query statement. This should not include the "GROUP BY".


orderBy

public QueryBuilder<T,ID> orderBy(String columnName,
                                  boolean ascending)
Add "ORDER BY" clause to the SQL query statement. This can be called multiple times to add additional "ORDER BY" clauses. Ones earlier are applied first.


orderByRaw

public QueryBuilder<T,ID> orderByRaw(String rawSql)
Add raw SQL "ORDER BY" clause to the SQL query statement.

Parameters:
rawSql - The raw SQL order by clause. This should not include the "ORDER BY".

orderByRaw

public QueryBuilder<T,ID> orderByRaw(String rawSql,
                                     ArgumentHolder... args)
Add raw SQL "ORDER BY" clause to the SQL query statement.

Parameters:
rawSql - The raw SQL order by clause. This should not include the "ORDER BY".
args - Optional arguments that correspond to any ? specified in the rawSql. Each of the arguments must have the sql-type set.

distinct

public QueryBuilder<T,ID> 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.


limit

@Deprecated
public QueryBuilder<T,ID> limit(int maxRows)
Deprecated. Should use limit(Long)


limit

public QueryBuilder<T,ID> limit(Long maxRows)
Limit the output to maxRows maximum number of rows. Set to null for no limit (the default).


offset

@Deprecated
public QueryBuilder<T,ID> offset(int startRow)
                          throws SQLException
Deprecated. Should use offset(Long)

Throws:
SQLException

offset

public QueryBuilder<T,ID> offset(Long startRow)
                          throws SQLException
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 orderBy(String, boolean).

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.

Throws:
SQLException

setCountOf

public QueryBuilder<T,ID> setCountOf(boolean countOf)
Set whether or not we should only return the count of the results. This query can then be used by Dao.countOf(PreparedQuery). To get the count-of directly, use countOf().


having

public QueryBuilder<T,ID> having(String having)
Add raw SQL "HAVING" clause to the SQL query statement. This should not include the "HAVING" string.


join

public QueryBuilder<T,ID> join(QueryBuilder<?,?> joinedQueryBuilder)
                        throws SQLException
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 QueryBuilder or the argument QueryBuilder must have a foreign field of the other one. An exception will be thrown otherwise.

NOTE: This will do combine the WHERE statement of the two query builders with a SQL "AND". See joinOr(QueryBuilder).

Throws:
SQLException

joinOr

public QueryBuilder<T,ID> joinOr(QueryBuilder<?,?> joinedQueryBuilder)
                          throws SQLException
Like join(QueryBuilder) but this combines the WHERE statements of two query builders with a SQL "OR".

Throws:
SQLException

leftJoin

public QueryBuilder<T,ID> leftJoin(QueryBuilder<?,?> joinedQueryBuilder)
                            throws SQLException
Similar to join(QueryBuilder) but it will use "LEFT JOIN" instead. See: LEFT JOIN SQL docs

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

NOTE: This will do combine the WHERE statement of the two query builders with a SQL "AND". See leftJoinOr(QueryBuilder).

Throws:
SQLException

leftJoinOr

public QueryBuilder<T,ID> leftJoinOr(QueryBuilder<?,?> joinedQueryBuilder)
                              throws SQLException
Like leftJoin(QueryBuilder) but this combines the WHERE statements of two query builders with a SQL "OR".

Throws:
SQLException

query

public List<T> query()
              throws SQLException
A short cut to Dao.query(PreparedQuery).

Throws:
SQLException

queryRaw

public GenericRawResults<String[]> queryRaw()
                                     throws SQLException
A short cut to Dao.queryRaw(String, String...).

Throws:
SQLException

queryForFirst

public T queryForFirst()
                throws SQLException
A short cut to Dao.queryForFirst(PreparedQuery).

Throws:
SQLException

queryRawFirst

public String[] queryRawFirst()
                       throws SQLException
A short cut to Dao.queryRaw(String, String...) and GenericRawResults.getFirstResult().

Throws:
SQLException

iterator

public CloseableIterator<T> iterator()
                              throws SQLException
A short cut to Dao.iterator(PreparedQuery).

Throws:
SQLException

countOf

public long countOf()
             throws SQLException
Sets the count-of query flag using setCountOf(boolean) to true and then calls Dao.countOf(PreparedQuery).

Throws:
SQLException

clear

@Deprecated
public void clear()
Deprecated. Renamed to be reset().

Overrides:
clear in class StatementBuilder<T,ID>

reset

public void reset()
Description copied from class: StatementBuilder
Clear out all of the statement settings so we can reuse the builder.

Overrides:
reset in class StatementBuilder<T,ID>

appendStatementStart

protected void appendStatementStart(StringBuilder sb,
                                    List<ArgumentHolder> argList)
Description copied from class: StatementBuilder
Append the start of our statement string to the StringBuilder.

Specified by:
appendStatementStart in class StatementBuilder<T,ID>

getResultFieldTypes

protected FieldType[] getResultFieldTypes()
Description copied from class: StatementBuilder
Get the result array from our statement after the StatementBuilder.appendStatementStart(StringBuilder, List) was called. This will be null except for the QueryBuilder.

Overrides:
getResultFieldTypes in class StatementBuilder<T,ID>

appendWhereStatement

protected boolean appendWhereStatement(StringBuilder sb,
                                       List<ArgumentHolder> argList,
                                       StatementBuilder.WhereOperation operation)
                                throws SQLException
Description copied from class: StatementBuilder
Append the WHERE part of the statement to the StringBuilder.

Overrides:
appendWhereStatement in class StatementBuilder<T,ID>
Throws:
SQLException

appendStatementEnd

protected void appendStatementEnd(StringBuilder sb,
                                  List<ArgumentHolder> argList)
                           throws SQLException
Description copied from class: StatementBuilder
Append the end of our statement string to the StringBuilder.

Specified by:
appendStatementEnd in class StatementBuilder<T,ID>
Throws:
SQLException

shouldPrependTableNameToColumns

protected boolean shouldPrependTableNameToColumns()
Description copied from class: StatementBuilder
Return true if we need to prepend table-name to columns.

Overrides:
shouldPrependTableNameToColumns in class StatementBuilder<T,ID>


This documentation is licensed by Gray Watson under the Creative Commons Attribution-Share Alike 3.0 License.