Package com.j256.ormlite.stmt

Source Code of com.j256.ormlite.stmt.Where

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&lt;Account, String&gt; qb = accountDao.queryBuilder();
* Where where = qb.where();
* // the name field must be equal to &quot;foo&quot;
* where.eq(Account.NAME_FIELD_NAME, &quot;foo&quot;);
* // and
* where.and();
* // the password field must be equal to &quot;_secret&quot;
* where.eq(Account.PASSWORD_FIELD_NAME, &quot;_secret&quot;);
* PreparedQuery&lt;Account, String&gt; 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, &quot;foo&quot;).and().eq(Account.PASSWORD_FIELD_NAME, &quot;_secret&quot;);
* </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, &quot;foo&quot;), where.eq(Account.PASSWORD_FIELD_NAME, &quot;_secret&quot;));
* </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, &quot;foo&quot;), where.eq(Account.PASSWORD_FIELD_NAME, &quot;_secret&quot;)),
*     where.and(where.eq(Account.NAME_FIELD_NAME, &quot;bar&quot;), where.eq(Account.PASSWORD_FIELD_NAME, &quot;qwerty&quot;)));
* </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 '&gt;=' 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 '&gt;' 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 '&lt;=' 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 '&lt;' 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 '&lt;&gt;' 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];
  }
}
TOP

Related Classes of com.j256.ormlite.stmt.Where

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.