package com.j256.ormlite.stmt;
import java.sql.SQLException;
import java.util.List;
import com.j256.ormlite.dao.CloseableIterator;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.db.DatabaseType;
import com.j256.ormlite.field.FieldType;
import com.j256.ormlite.stmt.QueryBuilder.InternalQueryBuilderWrapper;
import com.j256.ormlite.stmt.query.Between;
import com.j256.ormlite.stmt.query.Clause;
import com.j256.ormlite.stmt.query.Exists;
import com.j256.ormlite.stmt.query.In;
import com.j256.ormlite.stmt.query.InSubQuery;
import com.j256.ormlite.stmt.query.IsNotNull;
import com.j256.ormlite.stmt.query.IsNull;
import com.j256.ormlite.stmt.query.ManyClause;
import com.j256.ormlite.stmt.query.NeedsFutureClause;
import com.j256.ormlite.stmt.query.Not;
import com.j256.ormlite.stmt.query.Raw;
import com.j256.ormlite.stmt.query.SimpleComparison;
import com.j256.ormlite.table.TableInfo;
/**
* Manages the various clauses that make up the WHERE part of a SQL statement. You get one of these when you call
* {@link StatementBuilder#where} or you can set the where clause by calling {@link StatementBuilder#setWhere}.
*
* <p>
* Here's a page with a <a href="http://www.w3schools.com/Sql/" >good tutorial of SQL commands</a>.
* </p>
*
* <p>
* To create a query which looks up an account by name and password you would do the following:
* </p>
*
* <blockquote>
*
* <pre>
* 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();
* </pre>
*
* </blockquote>
*
* <p>
* In this example, the SQL query that will be generated will be approximately:
* </p>
*
* <blockquote>
*
* <pre>
* SELECT * FROM account WHERE (name = 'foo' AND passwd = '_secret')
* </pre>
*
* </blockquote>
*
* <p>
* If you'd rather chain the methods onto one line (like StringBuilder), this can also be written as:
* </p>
*
* <blockquote>
*
* <pre>
* queryBuilder.where().eq(Account.NAME_FIELD_NAME, "foo").and().eq(Account.PASSWORD_FIELD_NAME, "_secret");
* </pre>
*
* </blockquote>
*
* <p>
* If you'd rather use parens and the like then you can call:
* </p>
*
* <blockquote>
*
* <pre>
* Where where = queryBuilder.where();
* where.and(where.eq(Account.NAME_FIELD_NAME, "foo"), where.eq(Account.PASSWORD_FIELD_NAME, "_secret"));
* </pre>
*
* </blockquote>
*
* <p>
* 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:
* </p>
*
* <blockquote>
*
* <pre>
* 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")));
* </pre>
*
* </blockquote>
*
* <p>
* This produces the following approximate SQL:
* </p>
*
* <blockquote>
*
* <pre>
* SELECT * FROM account WHERE ((name = 'foo' AND passwd = '_secret') OR (name = 'bar' AND passwd = 'qwerty'))
* </pre>
*
* </blockquote>
*
* @author graywatson
*/
public class Where<T, ID> {
private final static int START_CLAUSE_SIZE = 4;
private final TableInfo<T, ID> tableInfo;
private final StatementBuilder<T, ID> statementBuilder;
private final FieldType idFieldType;
private final String idColumnName;
private Clause[] clauseStack = new Clause[START_CLAUSE_SIZE];
private int clauseStackLevel = 0;
private NeedsFutureClause needsFuture = null;
Where(TableInfo<T, ID> tableInfo, StatementBuilder<T, ID> statementBuilder) {
// limit the constructor scope
this.tableInfo = tableInfo;
this.statementBuilder = statementBuilder;
this.idFieldType = tableInfo.getIdField();
if (idFieldType == null) {
this.idColumnName = null;
} else {
this.idColumnName = idFieldType.getDbColumnName();
}
}
/**
* AND operation which takes the previous clause and the next clause and AND's them together.
*/
public Where<T, ID> and() {
addNeedsFuture(new ManyClause(pop("AND"), ManyClause.AND_OPERATION));
return this;
}
/**
* AND operation which takes 2 (or more) arguments and AND's them together.
*
* <p>
* <b>NOTE:</b> There is no guarantee of the order of the clauses that are generated in the final query.
* </p>
* <p>
* <b>NOTE:</b> I couldn't remove the code warning associated with this method when used with more than 2 arguments.
* </p>
*/
public Where<T, ID> and(Where<T, ID> first, Where<T, ID> second, Where<T, ID>... others) {
Clause[] clauses = buildClauseArray(others, "AND");
Clause secondClause = pop("AND");
Clause firstClause = pop("AND");
addClause(new ManyClause(firstClause, secondClause, clauses, ManyClause.AND_OPERATION));
return this;
}
/**
* This method needs to be used carefully. This will absorb a number of clauses that were registered previously with
* calls to {@link Where#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.
*
* <p>
* <b>NOTE:</b> There is no guarantee of the order of the clauses that are generated in the final query.
* </p>
*/
public Where<T, ID> and(int numClauses) {
if (numClauses == 0) {
throw new IllegalArgumentException("Must have at least one clause in and(numClauses)");
}
Clause[] clauses = new Clause[numClauses];
for (int i = numClauses - 1; i >= 0; i--) {
clauses[i] = pop("AND");
}
addClause(new ManyClause(clauses, ManyClause.AND_OPERATION));
return this;
}
/**
* Add a BETWEEN clause so the column must be between the low and high parameters.
*/
public Where<T, ID> between(String columnName, Object low, Object high) throws SQLException {
addClause(new Between(columnName, findColumnFieldType(columnName), low, high));
return this;
}
/**
* Add a '=' clause so the column must be equal to the value.
*/
public Where<T, ID> eq(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.EQUAL_TO_OPERATION));
return this;
}
/**
* Add a '>=' clause so the column must be greater-than or equals-to the value.
*/
public Where<T, ID> ge(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.GREATER_THAN_EQUAL_TO_OPERATION));
return this;
}
/**
* Add a '>' clause so the column must be greater-than the value.
*/
public Where<T, ID> gt(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.GREATER_THAN_OPERATION));
return this;
}
/**
* Add a IN clause so the column must be equal-to one of the objects from the list passed in.
*/
public Where<T, ID> in(String columnName, Iterable<?> objects) throws SQLException {
addClause(new In(columnName, findColumnFieldType(columnName), objects));
return this;
}
/**
* Add a IN clause so the column must be equal-to one of the objects passed in.
*/
public Where<T, ID> in(String columnName, Object... objects) throws SQLException {
if (objects.length == 1 && objects[0].getClass().isArray()) {
throw new IllegalArgumentException("in(Object... objects) seems to be an array within an array");
}
addClause(new In(columnName, findColumnFieldType(columnName), objects));
return this;
}
/**
* 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
* {@link QueryBuilder#selectColumns(String...)} method calls. That 1 argument must match the SQL type of the
* column-name passed to this method.
*
* <p>
* <b>NOTE:</b> The sub-query will be prepared at the same time that the outside query is.
* </p>
*/
public Where<T, ID> in(String columnName, QueryBuilder<?, ?> subQueryBuilder) throws SQLException {
if (subQueryBuilder.getSelectColumnCount() != 1) {
throw new SQLException("Inner query must have only 1 select column specified instead of "
+ subQueryBuilder.getSelectColumnCount());
}
// we do this to turn off the automatic addition of the ID column in the select column list
subQueryBuilder.enableInnerQuery();
addClause(new InSubQuery(columnName, findColumnFieldType(columnName), new InternalQueryBuilderWrapper(
subQueryBuilder)));
return this;
}
/**
* Add a EXISTS clause with a sub-query inside of parenthesis.
*
* <p>
* <b>NOTE:</b> The sub-query will be prepared at the same time that the outside query is.
* </p>
*/
public Where<T, ID> exists(QueryBuilder<?, ?> subQueryBuilder) throws SQLException {
// we do this to turn off the automatic addition of the ID column in the select column list
subQueryBuilder.enableInnerQuery();
addClause(new Exists(new InternalQueryBuilderWrapper(subQueryBuilder)));
return this;
}
/**
* Add a 'IS NULL' clause so the column must be null. '=' NULL does not work.
*/
public Where<T, ID> isNull(String columnName) throws SQLException {
addClause(new IsNull(columnName, findColumnFieldType(columnName)));
return this;
}
/**
* Add a 'IS NOT NULL' clause so the column must not be null. '<>' NULL does not work.
*/
public Where<T, ID> isNotNull(String columnName) throws SQLException {
addClause(new IsNotNull(columnName, findColumnFieldType(columnName)));
return this;
}
/**
* Add a '<=' clause so the column must be less-than or equals-to the value.
*/
public Where<T, ID> le(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.LESS_THAN_EQUAL_TO_OPERATION));
return this;
}
/**
* Add a '<' clause so the column must be less-than the value.
*/
public Where<T, ID> lt(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.LESS_THAN_OPERATION));
return this;
}
/**
* Add a LIKE clause so the column must mach the value using '%' patterns.
*/
public Where<T, ID> like(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.LIKE_OPERATION));
return this;
}
/**
* Add a '<>' clause so the column must be not-equal-to the value.
*/
public Where<T, ID> ne(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.NOT_EQUAL_TO_OPERATION));
return this;
}
/**
* Used to NOT the next clause specified.
*/
public Where<T, ID> not() {
addNeedsFuture(new Not());
return this;
}
/**
* Used to NOT the argument clause specified.
*/
public Where<T, ID> not(Where<T, ID> comparison) {
addClause(new Not(pop("NOT")));
return this;
}
/**
* OR operation which takes the previous clause and the next clause and OR's them together.
*/
public Where<T, ID> or() {
addNeedsFuture(new ManyClause(pop("OR"), ManyClause.OR_OPERATION));
return this;
}
/**
* OR operation which takes 2 arguments and OR's them together.
*
* <p>
* <b>NOTE:</b> There is no guarantee of the order of the clauses that are generated in the final query.
* </p>
* <p>
* <b>NOTE:</b> I can't remove the code warning associated with this method. Use the iterator method below.
* </p>
*/
public Where<T, ID> or(Where<T, ID> left, Where<T, ID> right, Where<T, ID>... others) {
Clause[] clauses = buildClauseArray(others, "OR");
Clause secondClause = pop("OR");
Clause firstClause = pop("OR");
addClause(new ManyClause(firstClause, secondClause, clauses, ManyClause.OR_OPERATION));
return this;
}
/**
* This method needs to be used carefully. This will absorb a number of clauses that were registered previously with
* calls to {@link Where#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.
*
* <p>
* <b>NOTE:</b> There is no guarantee of the order of the clauses that are generated in the final query.
* </p>
*/
public Where<T, ID> or(int numClauses) {
if (numClauses == 0) {
throw new IllegalArgumentException("Must have at least one clause in or(numClauses)");
}
Clause[] clauses = new Clause[numClauses];
for (int i = numClauses - 1; i >= 0; i--) {
clauses[i] = pop("OR");
}
addClause(new ManyClause(clauses, ManyClause.OR_OPERATION));
return this;
}
/**
* Add a clause where the ID is equal to the argument.
*/
public Where<T, ID> idEq(ID id) throws SQLException {
if (idColumnName == null) {
throw new SQLException("Object has no id column specified");
}
addClause(new SimpleComparison(idColumnName, idFieldType, id, SimpleComparison.EQUAL_TO_OPERATION));
return this;
}
/**
* Add a clause where the ID is from an existing object.
*/
public <OD> Where<T, ID> idEq(Dao<OD, ?> dataDao, OD data) throws SQLException {
if (idColumnName == null) {
throw new SQLException("Object has no id column specified");
}
addClause(new SimpleComparison(idColumnName, idFieldType, dataDao.extractId(data),
SimpleComparison.EQUAL_TO_OPERATION));
return this;
}
/**
* 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.
*/
public Where<T, ID> raw(String rawStatement) {
addClause(new Raw(rawStatement));
return this;
}
/**
* A short-cut for calling prepare() on the original {@link QueryBuilder#prepare()}.
*/
public PreparedQuery<T> prepare() throws SQLException {
return statementBuilder.prepareStatement();
}
/**
* A short-cut for calling query() on the original {@link QueryBuilder#query()}.
*/
public List<T> query() throws SQLException {
if (statementBuilder instanceof QueryBuilder) {
return ((QueryBuilder<T, ID>) statementBuilder).query();
} else {
throw new SQLException("Cannot call query on a statement of type " + statementBuilder.getType());
}
}
/**
* A short-cut for calling query() on the original {@link QueryBuilder#iterator()}.
*/
public CloseableIterator<T> iterator() throws SQLException {
if (statementBuilder instanceof QueryBuilder) {
return ((QueryBuilder<T, ID>) statementBuilder).iterator();
} else {
throw new SQLException("Cannot call iterator on a statement of type " + statementBuilder.getType());
}
}
/**
* Clear out the Where object so it can be re-used.
*/
public void clear() {
for (int i = 0; i < clauseStackLevel; i++) {
// help with gc
clauseStack[i] = null;
}
clauseStackLevel = 0;
}
/**
* Used by the internal classes to add the where SQL to the {@link StringBuilder}.
*/
void appendSql(DatabaseType databaseType, StringBuilder sb, List<ArgumentHolder> columnArgList) throws SQLException {
if (clauseStackLevel == 0) {
throw new IllegalStateException("No where clauses defined. Did you miss a where operation?");
}
if (clauseStackLevel != 1) {
throw new IllegalStateException(
"Both the \"left-hand\" and \"right-hand\" clauses have been defined. Did you miss an AND or OR?");
}
// we don't pop here because we may want to run the query multiple times
peek().appendSql(databaseType, sb, columnArgList);
}
private Clause[] buildClauseArray(Where<T, ID>[] others, String label) {
Clause[] clauses;
if (others.length == 0) {
clauses = null;
} else {
clauses = new Clause[others.length];
// fill in reverse order
for (int i = others.length - 1; i >= 0; i--) {
clauses[i] = pop("AND");
}
}
return clauses;
}
private void addNeedsFuture(NeedsFutureClause clause) {
if (needsFuture != null) {
throw new IllegalStateException(needsFuture + " is already waiting for a future clause, can't add: "
+ clause);
}
needsFuture = clause;
push(clause);
}
private void addClause(Clause clause) {
if (needsFuture == null) {
push(clause);
} else {
// we have a binary statement which was called before the right clause was defined
needsFuture.setMissingClause(clause);
needsFuture = null;
}
}
@Override
public String toString() {
if (clauseStackLevel == 0) {
return "empty where clause";
} else {
Clause clause = peek();
return "where clause: " + clause;
}
}
private FieldType findColumnFieldType(String columnName) throws SQLException {
return tableInfo.getFieldTypeByColumnName(columnName);
}
private void push(Clause clause) {
// if the stack is full then we need to grow it
if (clauseStackLevel == clauseStack.length) {
// double its size each time
Clause[] newStack = new Clause[clauseStackLevel * 2];
// copy the entries over to the new stack
for (int i = 0; i < clauseStackLevel; i++) {
newStack[i] = clauseStack[i];
// to help gc
clauseStack[i] = null;
}
clauseStack = newStack;
}
clauseStack[clauseStackLevel++] = clause;
}
private Clause pop(String label) {
if (clauseStackLevel == 0) {
throw new IllegalStateException("Expecting there to be a clause already defined for '" + label
+ "' operation");
}
Clause clause = clauseStack[--clauseStackLevel];
// to help gc
clauseStack[clauseStackLevel] = null;
return clause;
}
private Clause peek() {
return clauseStack[clauseStackLevel - 1];
}
}