Examples of com.j256.ormlite.stmt.Where

com.j256.ormlite.support.ConnectionSource

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

    if (arguments.length > 0) {
      // need to do the (Object) cast to force args to be a single object
      logger.trace("query arguments: {}", (Object) arguments);
    }
    DatabaseConnection connection = connectionSource.getReadOnlyConnection();
    CompiledStatement compiledStatement = null;
    try {
      compiledStatement = connection.compileStatement(query, StatementType.SELECT, noFieldTypes);
      assignStatementArguments(compiledStatement, arguments);
      String[] columnNames = extractColumnNames(compiledStatement);
      RawResultsImpl<Object[]> rawResults =
          new RawResultsImpl<Object[]>(connectionSource, connection, query, Object[].class,
              compiledStatement, columnNames, new ObjectArrayRowMapper(columnTypes));
      compiledStatement = null;
      connection = null;
      return rawResults;
    } finally {
      if (compiledStatement != null) {
        compiledStatement.close();
      }
      if (connection != null) {
        connectionSource.releaseConnection(connection);
      }
    }

View Full Code Here

    logger.debug("running raw update statement: {}", statement);
    if (arguments.length > 0) {
      // need to do the (Object) cast to force args to be a single object
      logger.trace("update arguments: {}", (Object) arguments);
    }
    CompiledStatement compiledStatement =
        connection.compileStatement(statement, StatementType.UPDATE, noFieldTypes);
    try {
      assignStatementArguments(compiledStatement, arguments);
      return compiledStatement.runUpdate();
    } finally {
      compiledStatement.close();
    }
  }

View Full Code Here

    logger.debug("running raw execute statement: {}", statement);
    if (arguments.length > 0) {
      // need to do the (Object) cast to force args to be a single object
      logger.trace("execute arguments: {}", (Object) arguments);
    }
    CompiledStatement compiledStatement =
        connection.compileStatement(statement, StatementType.EXECUTE, noFieldTypes);
    try {
      assignStatementArguments(compiledStatement, arguments);
      return compiledStatement.runExecute();
    } finally {
      compiledStatement.close();
    }
  }

View Full Code Here


  /**
   * Update rows in the database.
   */
  public int update(DatabaseConnection databaseConnection, PreparedUpdate<T> preparedUpdate) throws SQLException {
    CompiledStatement stmt = preparedUpdate.compile(databaseConnection);
    try {
      return stmt.runUpdate();
    } finally {
      if (stmt != null) {
        stmt.close();
      }
    }
  }

View Full Code Here


  /**
   * Delete rows that match the prepared statement.
   */
  public int delete(DatabaseConnection databaseConnection, PreparedDelete<T> preparedDelete) throws SQLException {
    CompiledStatement stmt = preparedDelete.compile(databaseConnection);
    try {
      return stmt.runUpdate();
    } finally {
      if (stmt != null) {
        stmt.close();
      }
    }
  }

View Full Code Here

    this.limit = limit;
    this.type = type;
  }


  public CompiledStatement compile(DatabaseConnection databaseConnection) throws SQLException {
    CompiledStatement stmt = databaseConnection.compileStatement(statement, type, argFieldTypes);
    boolean ok = false;
    try {
      if (limit != null) {
        // we use this if SQL statement LIMITs are not supported by this database type
        stmt.setMaxRows(limit);
      }
      // set any arguments if we are logging our object
      Object[] argValues = null;
      if (logger.isLevelEnabled(Level.TRACE) && argHolders.length > 0) {
        argValues = new Object[argHolders.length];
      }
      for (int i = 0; i < argHolders.length; i++) {
        Object argValue = argHolders[i].getSqlArgValue();
        if (argValue == null) {
          stmt.setNull(i, argFieldTypes[i].getSqlType());
        } else {
          stmt.setObject(i, argValue, argFieldTypes[i].getSqlType());
        }
        if (argValues != null) {
          argValues[i] = argValue;
        }
      }
      logger.debug("prepared statement '{}' with {} args", statement, argHolders.length);
      if (argValues != null) {
        // need to do the (Object) cast to force args to be a single object
        logger.trace("prepared statement arguments: {}", (Object) argValues);
      }
      ok = true;
      return stmt;
    } finally {
      if (!ok) {
        stmt.close();
      }
    }
  }

View Full Code Here

      sb.append("DELETE FROM ");
    }
    databaseType.appendEscapedEntityName(sb, tableName);
    String statement = sb.toString();
    logger.info("clearing table '{}' with '{}", tableName, statement);
    CompiledStatement compiledStmt = null;
    try {
      compiledStmt = connection.compileStatement(statement, StatementType.EXECUTE, noFieldTypes);
      return compiledStmt.runExecute();
    } finally {
      if (compiledStmt != null) {
        compiledStmt.close();
      }
      connectionSource.releaseConnection(connection);
    }
  }

View Full Code Here

  private static int doStatements(DatabaseConnection connection, String label, Collection<String> statements,
      boolean ignoreErrors, boolean expectingZero) throws SQLException {
    int stmtC = 0;
    for (String statement : statements) {
      int rowC = 0;
      CompiledStatement compiledStmt = null;
      try {
        compiledStmt = connection.compileStatement(statement, StatementType.EXECUTE, noFieldTypes);
        rowC = compiledStmt.runUpdate();
        logger.info("executed {} table statement changed {} rows: {}", label, rowC, statement);
      } catch (SQLException e) {
        if (ignoreErrors) {
          logger.info("ignoring {} error '{}' for statement: {}", label, e.getMessage(), statement);
        } else {
          throw SqlExceptionUtil.create("SQL statement failed: " + statement, e);
        }
      } finally {
        if (compiledStmt != null) {
          compiledStmt.close();
        }
      }
      // sanity check
      if (rowC < 0) {
        throw new SQLException("SQL statement " + statement + " updated " + rowC

View Full Code Here

  private static int doCreateTestQueries(DatabaseConnection connection, DatabaseType databaseType,
      List<String> queriesAfter) throws SQLException {
    int stmtC = 0;
    // now execute any test queries which test the newly created table
    for (String query : queriesAfter) {
      CompiledStatement compiledStmt = null;
      try {
        compiledStmt = connection.compileStatement(query, StatementType.EXECUTE, noFieldTypes);
        DatabaseResults results = compiledStmt.runQuery();
        int rowC = 0;
        // count the results
        while (results.next()) {
          rowC++;
        }
        logger.info("executing create table after-query got {} results: {}", rowC, query);
      } catch (SQLException e) {
        // we do this to make sure that the statement is in the exception
        throw SqlExceptionUtil.create("executing create table after-query failed: " + query, e);
      } finally {
        // result set is closed by the statement being closed
        if (compiledStmt != null) {
          compiledStmt.close();
        }
      }
      stmtC++;
    }
    return stmtC;

View Full Code Here

  /**
   * Satisfies the {@link SQLiteOpenHelper#onCreate(SQLiteDatabase)} interface method.
   */
  @Override
  public final void onCreate(SQLiteDatabase db) {
    ConnectionSource cs = getConnectionSource();
    /*
     * The method is called by Android database helper's get-database calls when Android detects that we need to
     * create or update the database. So we have to use the database argument and save a connection to it on the
     * AndroidConnectionSource, otherwise it will go recursive if the subclass calls getConnectionSource().
     */
    DatabaseConnection conn = cs.getSpecialConnection();
    boolean clearSpecial = false;
    if (conn == null) {
      conn = new AndroidDatabaseConnection(db, true);
      try {
        cs.saveSpecialConnection(conn);
        clearSpecial = true;
      } catch (SQLException e) {
        throw new IllegalStateException("Could not save special connection", e);
      }
    }
    try {
      onCreate(db, cs);
    } finally {
      if (clearSpecial) {
        cs.clearSpecialConnection(conn);
      }
    }
  }

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.