com.j256.ormlite.stmt

Class Where<T,ID>

java.lang.Object

com.j256.ormlite.stmt.Where<T,ID>

public class Where<T,ID>
extends Object

Manages the various clauses that make up the WHERE part of a SQL statement. You get one of these when you call StatementBuilder.where or you can set the where clause by calling StatementBuilder.setWhere(com.j256.ormlite.stmt.Where<T, ID>).

Here's a page with a good tutorial of SQL commands.

To create a query which looks up an account by name and password you would do the following:

 QueryBuilder<Account, String> qb = accountDao.queryBuilder();
 Where where = qb.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, String> preparedQuery = qb.prepareQuery();
 

In this example, the SQL query that will be generated will be approximately:

 SELECT * FROM account WHERE (name = 'foo' AND passwd = '_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 parens and the like then you can call:

 Where 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 will be necessary to get the grouping correct. For example, here's a complex query:

 Where 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 passwd = '_secret') OR (name = 'bar' AND passwd = 'qwerty'))
 

Author:

graywatson

Constructor Summary

Constructors

Modifier	Constructor and Description
protected	Where(TableInfo<T,ID> tableInfo, StatementBuilder<T,ID> statementBuilder, DatabaseType databaseType)

Method Summary

Modifier and Type	Method and Description
Where<T,ID>	and() AND operation which takes the previous clause and the next clause and AND's them together.
Where<T,ID>	and(int numClauses) This method needs to be used carefully.
Where<T,ID>	and(Where<T,ID> first, Where<T,ID> second) AND operation which takes 2 arguments and AND's them together.
Where<T,ID>	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.
Where<T,ID>	between(String columnName, Object low, Object high) Add a BETWEEN clause so the column must be between the low and high parameters.
long	countOf() A short-cut for calling QueryBuilder.countOf().
Where<T,ID>	eq(String columnName, Object value) Add a '=' clause so the column must be equal to the value.
Where<T,ID>	exists(QueryBuilder<?,?> subQueryBuilder) Add a EXISTS clause with a sub-query inside of parenthesis.
Where<T,ID>	ge(String columnName, Object value) Add a '>=' clause so the column must be greater-than or equals-to the value.
String	getStatement() Returns the associated SQL WHERE statement.
Where<T,ID>	gt(String columnName, Object value) Add a '>' clause so the column must be greater-than the value.
<OD> Where<T,ID>	idEq(Dao<OD,?> dataDao, OD data) Add a clause where the ID is from an existing object.
Where<T,ID>	idEq(ID id) Add a clause where the ID is equal to the argument.
Where<T,ID>	in(String columnName, Iterable<?> objects) Add a IN clause so the column must be equal-to one of the objects from the list passed in.
Where<T,ID>	in(String columnName, Object... objects) Add a IN clause so the column must be equal-to one of the objects passed in.
Where<T,ID>	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.
Where<T,ID>	isNotNull(String columnName) Add a 'IS NOT NULL' clause so the column must not be null.
Where<T,ID>	isNull(String columnName) Add a 'IS NULL' clause so the column must be null.
CloseableIterator<T>	iterator() A short-cut for calling QueryBuilder.iterator().
Where<T,ID>	le(String columnName, Object value) Add a '<=' clause so the column must be less-than or equals-to the value.
Where<T,ID>	like(String columnName, Object value) Add a LIKE clause so the column must mach the value using '%' patterns.
Where<T,ID>	lt(String columnName, Object value) Add a '<' clause so the column must be less-than the value.
Where<T,ID>	ne(String columnName, Object value) Add a '<>' clause so the column must be not-equal-to the value.
Where<T,ID>	not() Used to NOT the next clause specified.
Where<T,ID>	not(Where<T,ID> comparison) Used to NOT the argument clause specified.
Where<T,ID>	notIn(String columnName, Iterable<?> objects) Same as in(String, Iterable) except with a NOT IN clause.
Where<T,ID>	notIn(String columnName, Object... objects) Same as in(String, Object...) except with a NOT IN clause.
Where<T,ID>	notIn(String columnName, QueryBuilder<?,?> subQueryBuilder) Same as in(String, QueryBuilder) except with a NOT IN clause.
Where<T,ID>	or() OR operation which takes the previous clause and the next clause and OR's them together.
Where<T,ID>	or(int numClauses) This method needs to be used carefully.
Where<T,ID>	or(Where<T,ID> left, Where<T,ID> right) OR operation which takes 2 arguments and OR's them together.
Where<T,ID>	or(Where<T,ID> left, Where<T,ID> right, Where<T,ID>... others) OR operation which takes 2 (or more) arguments and OR's them together.
PreparedQuery<T>	prepare() A short-cut for calling QueryBuilder.prepare().
List<T>	query() A short-cut for calling QueryBuilder.query().
QueryBuilder<T,ID>	queryBuilder() Returns the QueryBuilder used to create this Where, if present
T	queryForFirst() A short-cut for calling QueryBuilder.queryForFirst().
GenericRawResults<String[]>	queryRaw() A short-cut for calling QueryBuilder.queryRaw().
String[]	queryRawFirst() A short-cut for calling QueryBuilder.queryRawFirst().
Where<T,ID>	raw(String rawStatement, ArgumentHolder... args) Add a raw statement as part of the where that can be anything that the database supports.
Where<T,ID>	rawComparison(String columnName, String rawOperator, Object value) Make a comparison where the operator is specified by the caller.
Where<T,ID>	reset() Reset the Where object so it can be re-used.
String	toString()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

Where

protected Where(TableInfo<T,ID> tableInfo,
                StatementBuilder<T,ID> statementBuilder,
                DatabaseType databaseType)

Method Detail

and

public Where<T,ID> and()

AND operation which takes the previous clause and the next clause and AND's them together.

and

public Where<T,ID> and(Where<T,ID> first,
                       Where<T,ID> second)

AND operation which takes 2 arguments and AND's them together.

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

NOTE: I can't remove the generics code warning that can be associated with this method. You can instead use the and(int) method.

and

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

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

NOTE: I can't remove the generics code warning that can be associated with this method. You can instead use the and(int) method.

and

public Where<T,ID> and(int numClauses)

This method needs to be used carefully. This will absorb a number of clauses that were registered previously with calls to eq(String, Object) 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.

NOTE: This will throw an exception if numClauses is 0 but will work with 1 or more.

between

public Where<T,ID> between(String columnName,
                           Object low,
                           Object high)
                    throws SQLException

Add a BETWEEN clause so the column must be between the low and high parameters.

Throws:

SQLException

eq

public Where<T,ID> eq(String columnName,
                      Object value)
               throws SQLException

Add a '=' clause so the column must be equal to the value.

Throws:

SQLException

ge

public Where<T,ID> ge(String columnName,
                      Object value)
               throws SQLException

Add a '>=' clause so the column must be greater-than or equals-to the value.

Throws:

SQLException

gt

public Where<T,ID> gt(String columnName,
                      Object value)
               throws SQLException

Add a '>' clause so the column must be greater-than the value.

Throws:

SQLException

in

public Where<T,ID> in(String columnName,
                      Iterable<?> objects)
               throws SQLException

Add a IN clause so the column must be equal-to one of the objects from the list passed in.

Throws:

SQLException

notIn

public Where<T,ID> notIn(String columnName,
                         Iterable<?> objects)
                  throws SQLException

Same as in(String, Iterable) except with a NOT IN clause.

Throws:

SQLException

in

public Where<T,ID> in(String columnName,
                      Object... objects)
               throws SQLException

Add a IN clause so the column must be equal-to one of the objects passed in.

Throws:

SQLException

notIn

public Where<T,ID> notIn(String columnName,
                         Object... objects)
                  throws SQLException

Same as in(String, Object...) except with a NOT IN clause.

Throws:

SQLException

in

public Where<T,ID> in(String columnName,
                      QueryBuilder<?,?> subQueryBuilder)
               throws SQLException

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.

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

Throws:

SQLException

notIn

public Where<T,ID> notIn(String columnName,
                         QueryBuilder<?,?> subQueryBuilder)
                  throws SQLException

Same as in(String, QueryBuilder) except with a NOT IN clause.

Throws:

SQLException

exists

public Where<T,ID> exists(QueryBuilder<?,?> subQueryBuilder)

Add a EXISTS clause with a sub-query inside of parenthesis.

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

isNull

public Where<T,ID> isNull(String columnName)
                   throws SQLException

Add a 'IS NULL' clause so the column must be null. '=' NULL does not work.

Throws:

SQLException

isNotNull

public Where<T,ID> isNotNull(String columnName)
                      throws SQLException

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

Throws:

SQLException

le

public Where<T,ID> le(String columnName,
                      Object value)
               throws SQLException

Add a '<=' clause so the column must be less-than or equals-to the value.

Throws:

SQLException

lt

public Where<T,ID> lt(String columnName,
                      Object value)
               throws SQLException

Add a '<' clause so the column must be less-than the value.

Throws:

SQLException

like

public Where<T,ID> like(String columnName,
                        Object value)
                 throws SQLException

Add a LIKE clause so the column must mach the value using '%' patterns.

Throws:

SQLException

ne

public Where<T,ID> ne(String columnName,
                      Object value)
               throws SQLException

Add a '<>' clause so the column must be not-equal-to the value.

Throws:

SQLException

not

public Where<T,ID> not()

Used to NOT the next clause specified.

not

public Where<T,ID> not(Where<T,ID> comparison)

Used to NOT the argument clause specified.

or

public Where<T,ID> or()

OR operation which takes the previous clause and the next clause and OR's them together.

or

public Where<T,ID> or(Where<T,ID> left,
                      Where<T,ID> right)

OR operation which takes 2 arguments and OR's them together.

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

NOTE: I can't remove the generics code warning that can be associated with this method. You can instead use the or(int) method.

or

public Where<T,ID> or(Where<T,ID> left,
                      Where<T,ID> right,
                      Where<T,ID>... others)

OR operation which takes 2 (or more) arguments and OR's them together.

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

NOTE: I can't remove the generics code warning that can be associated with this method. You can instead use the or(int) method.

or

public Where<T,ID> or(int numClauses)

This method needs to be used carefully. This will absorb a number of clauses that were registered previously with calls to eq(String, Object) 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.

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

NOTE: This will throw an exception if numClauses is 0 but will work with 1 or more.

idEq

public Where<T,ID> idEq(ID id)
                 throws SQLException

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

Throws:

SQLException

idEq

public <OD> Where<T,ID> idEq(Dao<OD,?> dataDao,
                             OD data)
                      throws SQLException

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

Throws:

SQLException

raw

public Where<T,ID> raw(String rawStatement,
                       ArgumentHolder... args)

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

Parameters:

rawStatement - The statement that we should insert into the WHERE.

args - Optional arguments that correspond to any ? specified in the rawStatement. Each of the arguments must have either the corresponding columnName or the sql-type set. WARNING, you cannot use the SelectArg("columnName") constructor since that sets the _value_, not the name. Use new SelectArg("column-name", null);.

rawComparison

public Where<T,ID> rawComparison(String columnName,
                                 String rawOperator,
                                 Object value)
                          throws SQLException

Make a comparison where the operator is specified by the caller. It is up to the caller to specify an appropriate operator for the database and that it be formatted correctly.

Throws:

SQLException

prepare

public PreparedQuery<T> prepare()
                         throws SQLException

A short-cut for calling QueryBuilder.prepare().

Throws:

SQLException

query

public List<T> query()
              throws SQLException

A short-cut for calling QueryBuilder.query().

Throws:

SQLException

queryRaw

public GenericRawResults<String[]> queryRaw()
                                     throws SQLException

A short-cut for calling QueryBuilder.queryRaw().

Throws:

SQLException

queryForFirst

public T queryForFirst()
                throws SQLException

A short-cut for calling QueryBuilder.queryForFirst().

Throws:

SQLException

queryRawFirst

public String[] queryRawFirst()
                       throws SQLException

A short-cut for calling QueryBuilder.queryRawFirst().

Throws:

SQLException

countOf

public long countOf()
             throws SQLException

A short-cut for calling QueryBuilder.countOf().

Throws:

SQLException

iterator

public CloseableIterator<T> iterator()
                              throws SQLException

A short-cut for calling QueryBuilder.iterator().

Throws:

SQLException

reset

public Where<T,ID> reset()

Reset the Where object so it can be re-used.

getStatement

public String getStatement()
                    throws SQLException

Returns the associated SQL WHERE statement.

Throws:

SQLException

queryBuilder

public QueryBuilder<T,ID> queryBuilder()
                                throws SQLException

Returns the QueryBuilder used to create this Where, if present

Throws:

SQLException

toString

public String toString()

Overrides:

toString in class Object