Examples of com.j256.ormlite.stmt.Where

com.j256.ormlite.stmt.query.InSubQuery

3schools.com/Sql/" >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

    return true;
  }


  public ID extractId(T data) throws SQLException {
    checkForInitialized();
    FieldType idField = tableInfo.getIdField();
    @SuppressWarnings("unchecked")
    ID id = (ID) idField.extractJavaFieldValue(data);
    return id;
  }

View Full Code Here

        this.sortBy = sortBy;
        return this;
    }


    public void buildQuery(QueryBuilder qb) throws SQLException {
        Where where = qb.where();
        where.eq("nightCard", false);
        where.eq("splitCardHalf", false);
        int clausesCount = 2;
        if (name != null) {
            where.like("name", new SelectArg('%' + name + '%'));
            clausesCount++;
        }
        if (rules != null) {
            where.like("rules", new SelectArg('%' + rules + '%'));
            clausesCount++;
        }


        if (doubleFaced != null) {
            where.eq("doubleFaced", doubleFaced);
            clausesCount++;
        }


        for (Rarity rarity : rarities) {
            where.eq("rarity", rarity);
        }
        if (!rarities.isEmpty()) {
            where.or(rarities.size());
            clausesCount++;
        }


        for (String setCode : setCodes) {
            where.eq("setCode", setCode);
        }
        if (!setCodes.isEmpty()) {
            where.or(setCodes.size());
            clausesCount++;
        }




        if (types.size() != 7) { //if all types selected - no selection needed (Tribal and Conspiracy not selectable yet)
            for (CardType type : types) {
                where.like("types", new SelectArg('%' + type.name() + '%'));
            }
            if (!types.isEmpty()) {
                where.or(types.size());
                clausesCount++;
            }
        }


        for (CardType type : notTypes) {
            where.not().like("types", new SelectArg('%' + type.name() + '%'));
            clausesCount++;
        }


        for (String superType : supertypes) {
            where.like("supertypes", new SelectArg('%' + superType + '%'));
            clausesCount++;
        }
        for (String subType : notSupertypes) {
            where.not().like("supertypes", new SelectArg('%' + subType + '%'));
            clausesCount++;
        }


        for (String subType : subtypes) {
            where.like("subtypes", new SelectArg('%' + subType + '%'));
            clausesCount++;
        }


        if (!black || !blue || !green || !red || !white || !colorless) {
            int colorClauses = 0;
            if (black) {
                where.eq("black", true);
                colorClauses++;
            }
            if (blue) {
                where.eq("blue", true);
                colorClauses++;
            }
            if (green) {
                where.eq("green", true);
                colorClauses++;
            }
            if (red) {
                where.eq("red", true);
                colorClauses++;
            }
            if (white) {
                where.eq("white", true);
                colorClauses++;
            }
            if (colorless) {
                where.eq("black", false).eq("blue", false).eq("green", false).eq("red", false).eq("white", false);
                where.and(5);
                colorClauses++;
            }
            if (colorClauses > 0) {
                where.or(colorClauses);
                clausesCount++;
            }
        }


        if (maxCardNumber != Integer.MAX_VALUE) {
            where.le("cardNumber", maxCardNumber);
            clausesCount++;
        }


        if (clausesCount > 0) {
            where.and(clausesCount);
        } else {
            where.eq("cardNumber", new SelectArg(0));
        }






        if (start != null) {

View Full Code Here

   * <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;
  }

View Full Code Here

   * <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;
  }

View Full Code Here

  @Override
  public String toString() {
    if (clauseStackLevel == 0) {
      return "empty where clause";
    } else {
      Clause clause = peek();
      return "where clause: " + clause;
    }
  }

View Full Code Here

  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;
  }

View Full Code Here

   * </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;
  }

View Full Code Here


  /**
   * 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;
  }

View Full Code Here

   */
  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;
  }

View Full Code Here

      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;
  }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

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

android.database.sqlite.SQLiteStatement

com.j256.ormlite.android.AndroidDatabaseConnection

com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper

com.j256.ormlite.dao.BaseDaoImpl

com.j256.ormlite.dao.BaseDaoImplTest

com.j256.ormlite.dao.Dao.CreateOrUpdateStatus

com.j256.ormlite.dao.DaoManager

com.j256.ormlite.dao.DaoManagerTest

com.j256.ormlite.dao.RuntimeExceptionDaoTest

com.j256.ormlite.db.BaseCoreDatabaseTypeTest

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.