Java Code Examples for android.database.sqlite.SQLiteDatabase#compileStatement()

/**
 * Ejecuta varias sentencias SQL (INSERT, UPDATE, DELETE, etc.) en la base de datos usando una
 * misma transacción.
 * 
 * @param sql las sentencia SQL a ejecutar.
 * @param statementBinder el objeto que reemplazarán los '?' de la sentencia varias veces.
 */
void batchExecute(String sql, BatchSQLiteStatementBinder statementBinder) {
    SQLiteDatabase database = null;
    SQLiteStatement statement = null;
    try {
        database = databaseHelper.getWritableDatabase();
        database.beginTransaction();
        statement = database.compileStatement(sql);
        for (int i = 0; i < statementBinder.getBatchSize(); i++) {
            statement.clearBindings();
            statementBinder.bindValues(statement, i);
            statement.execute();
        }
        database.setTransactionSuccessful();
    } catch (Exception ex) {
        Log.e(TAG, "Couldn't execute batch [" + sql + "]", ex);
    } finally {
        SQLiteUtils.close(statement);
        SQLiteUtils.endTransaction(database);
        SQLiteUtils.close(database);
    }
}
 

/**
 * Delete a note.
 *
 * @param id the note id.
 * @throws IOException if something goes wrong.
 */
public static void deleteNote(long id) throws IOException {
    SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase();
    sqliteDatabase.beginTransaction();
    try {
        // delete note
        String query = "delete from " + TABLE_NOTES + " where " + NotesTableFields.COLUMN_ID.getFieldName() + " = " + id;
        SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();

        sqliteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        GPLog.error("DAONOTES", e.getLocalizedMessage(), e);
        throw new IOException(e.getLocalizedMessage());
    } finally {
        sqliteDatabase.endTransaction();
    }
}
 

/**
 * Ejecuta varias sentencias SQL (INSERT, UPDATE, DELETE, etc.) en la base de datos usando una
 * misma transacción.
 * 
 * @param sqls las sentencias SQL a ejecutar.
 */
void batchExecute(String[] sqls) {
    SQLiteDatabase database = null;
    try {
        database = databaseHelper.getWritableDatabase();
        database.beginTransaction();
        for (String sql : sqls) {
            SQLiteStatement statement = database.compileStatement(sql);
            statement.execute();
            statement.close();
        }
        database.setTransactionSuccessful();
    } catch (Exception ex) {
        Log.e(TAG, "Couldn't execute batch " + Arrays.deepToString(sqls), ex);
    } finally {
        SQLiteUtils.endTransaction(database);
        SQLiteUtils.close(database);
    }
}
 

/**
 * 执行更新单个数据，返回受影响的行数
 */
public int execUpdateWithMapping(SQLiteDatabase db, Object entity, TableManager tableManager) throws IOException {
    printSQL();
    mStatement = db.compileStatement(sql);
    if (!Checker.isEmpty(bindArgs)) {
        for (int i = 0; i < bindArgs.length; i++) {
            bind(i + 1, bindArgs[i]);
        }
    }
    int rows = NONE;
    if (Build.VERSION.SDK_INT < Build.VERSION_CODES.HONEYCOMB) {
        mStatement.execute();
        rows = NORMAL;
    } else {
        rows = mStatement.executeUpdateDelete();
    }
    realease();
    if (OrmLog.isPrint) {
        OrmLog.i(TAG, "SQL Execute update, changed rows --> " + rows);
    }
    if (tableManager != null && entity != null) {
        mapRelationToDb(entity, true, true, db, tableManager);
    }
    return rows;
}
 

/**
 * Ejecuta una sentencia SQL (INSERT, UPDATE, DELETE, etc.) en la base de datos.
 * 
 * @param sql la sentencia SQL a ejecutar.
 */
void execute(String sql) {
    SQLiteDatabase database = null;
    SQLiteStatement statement = null;
    try {
        database = databaseHelper.getWritableDatabase();
        database.beginTransaction();
        statement = database.compileStatement(sql);
        statement.execute();
        database.setTransactionSuccessful();
    } catch (Exception ex) {
        Log.e(TAG, "Couldn't execute [" + sql + "]", ex);
    } finally {
        SQLiteUtils.close(statement);
        SQLiteUtils.endTransaction(database);
        SQLiteUtils.close(database);
    }
}
 

private void createFiles(SQLiteDatabase db) {
    mFileNames = new String[mFiles];
    byte[] rawData = new byte[mFileSize+mFiles];
    Random random = new Random();
    random.nextBytes(rawData);

    ByteArrayOutputStream[] streams = new ByteArrayOutputStream[mFiles];
    for (int i = 0; i < mFiles; i++) {
        streams[i] = new ByteArrayOutputStream(mFileSize);
        streams[i].write(rawData, i, mFileSize);
        mFileNames[i] = String.valueOf(i);
    }

    SQLiteStatement insert = db.compileStatement("INSERT INTO files (filename, data) VALUES (?, ?)");
    for (int i = 0; i < mFiles; i++) {
        insert.bindString(1, mFileNames[i]);
        insert.bindBlob(2, streams[i].toByteArray());

        insert.execute();
    }
}
 

public void delete(Beacon beacon) {
    long id = beacon.getSavedId();
    if (id > 0) {
        SQLiteDatabase db = getWritableDatabase();

        if (null == mDeleteItemStmt) {
            mDeleteItemStmt = db.compileStatement("DELETE FROM " + ITEMS_TABLE + " WHERE rowid=?");
        }

        mDeleteItemStmt.bindLong(1, id);
        executeSafeUpdateOrDelete(mDeleteItemStmt);

        Persistable persistable = null == mBeaconPersisters ? null : mBeaconPersisters.get(beacon.getKind());
        if (null != persistable) {
            persistable.onDeleted(beacon);
        }
    }
}
 

/**
 * Delete bookmark.
 *
 * @param id the id of the bookmark to delete.
 * @throws IOException if something goes wrong.
 */
public static void deleteBookmark(long id) throws IOException {
    SQLiteDatabase sqliteDatabase = GeopaparazziApplication.getInstance().getDatabase();
    sqliteDatabase.beginTransaction();
    try {
        // delete note
        String query = "delete from " + TABLE_BOOKMARKS + " where " + COLUMN_ID + " = " + id;
        SQLiteStatement sqlUpdate = sqliteDatabase.compileStatement(query);
        sqlUpdate.execute();

        sqliteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        GPLog.error("DAOBOOKMARKS", e.getLocalizedMessage(), e);
        throw new IOException(e.getLocalizedMessage());
    } finally {
        sqliteDatabase.endTransaction();
    }
}
 

private void upgradeScreenTimeoutFromNever(SQLiteDatabase db) {
    // See if the timeout is -1 (for "Never").
    Cursor c = db.query(TABLE_SYSTEM, new String[] { "_id", "value" }, "name=? AND value=?",
            new String[] { Settings.System.SCREEN_OFF_TIMEOUT, "-1" },
            null, null, null);

    SQLiteStatement stmt = null;
    if (c.getCount() > 0) {
        c.close();
        try {
            stmt = db.compileStatement("INSERT OR REPLACE INTO system(name,value)"
                    + " VALUES(?,?);");

            // Set the timeout to 30 minutes in milliseconds
            loadSetting(stmt, Settings.System.SCREEN_OFF_TIMEOUT,
                    Integer.toString(30 * 60 * 1000));
        } finally {
            if (stmt != null) stmt.close();
        }
    } else {
        c.close();
    }
}
 

SQLiteStatement compileSqlStatementForLogging() {
    // TODO b/31708085 Fix debug logging - it eagerly opens database for write without a need
    SQLiteDatabase db = mDeDatabase.getWritableDatabase();
    String sql = "INSERT OR REPLACE INTO " + AccountsDb.TABLE_DEBUG
            + " VALUES (?,?,?,?,?,?)";
    return db.compileStatement(sql);
}
 

private static void upgradeFeatureTable(SQLiteDatabase db) {
    logger.error("  Upgrade feature table");
    SQLiteStatement statement = db.compileStatement("UPDATE " + TABLE_FEATURES + " SET " +
            COLUMN_FEATURES_X + " = ?, " + COLUMN_FEATURES_Y + " = ? WHERE " +
            COLUMN_FEATURES_ID + " = ?");
    String SQL_SELECT_FEATURES = "SELECT " + COLUMN_FEATURES_ID + ", " + COLUMN_FEATURES_LAT +
            ", " + COLUMN_FEATURES_LON + " FROM " + TABLE_FEATURES;
    db.beginTransaction();
    Cursor cursor = db.rawQuery(SQL_SELECT_FEATURES, null);
    cursor.moveToFirst();
    int[] xy = new int[] {0, 0};
    while (!cursor.isAfterLast()) {
        long id = cursor.getLong(0);
        double lat = cursor.getDouble(1);
        double lon = cursor.getDouble(2);
        cursor.moveToNext();
        if (lat == 0 && lon == 0)
            continue;

        getFourteenthTileXY(lat, lon, xy);
        statement.bindLong(1, xy[0]);
        statement.bindLong(2, xy[1]);
        statement.bindLong(3, id);
        statement.execute();
    }
    cursor.close();
    db.setTransactionSuccessful();
    db.endTransaction();
}
 

public void log(String url, String req, String resp) {
    SQLiteDatabase db = null;
    SQLiteStatement statement;
    long timestamp;
    try {
        db = getWritableDatabase();
        db.beginTransaction();
        statement = db.compileStatement("INSERT INTO ".concat(packetlog_table_name).concat(" (timestamp, url, type, data) values (?, ?, ?, ?)"));

        timestamp = System.currentTimeMillis();
        statement.bindAllArgsAsStrings(new String[]{String.valueOf(timestamp), url, REQUEST, req});
        statement.execute();

        timestamp = System.currentTimeMillis();
        statement.bindAllArgsAsStrings(new String[]{String.valueOf(timestamp), url, RESPONSE, resp});
        statement.execute();

        statement.close();
        db.setTransactionSuccessful();
    } catch (RuntimeException e) {
        e.printStackTrace();
    } finally {
        if (db != null) {
            db.endTransaction();
        }
    }
}
 

/**
 * Move any settings with the given prefixes from the source table to the
 * destination table.
 */
private void movePrefixedSettingsToNewTable(
        SQLiteDatabase db, String sourceTable, String destTable, String[] prefixesToMove) {
    SQLiteStatement insertStmt = null;
    SQLiteStatement deleteStmt = null;

    db.beginTransaction();
    try {
        insertStmt = db.compileStatement("INSERT INTO " + destTable
                + " (name,value) SELECT name,value FROM " + sourceTable
                + " WHERE substr(name,0,?)=?");
        deleteStmt = db.compileStatement(
                "DELETE FROM " + sourceTable + " WHERE substr(name,0,?)=?");

        for (String prefix : prefixesToMove) {
            insertStmt.bindLong(1, prefix.length() + 1);
            insertStmt.bindString(2, prefix);
            insertStmt.execute();

            deleteStmt.bindLong(1, prefix.length() + 1);
            deleteStmt.bindString(2, prefix);
            deleteStmt.execute();
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
        if (insertStmt != null) {
            insertStmt.close();
        }
        if (deleteStmt != null) {
            deleteStmt.close();
        }
    }
}
 

@TargetApi(DatabaseConstants.MIN_API_LEVEL)
private <T> T executeUpdateDelete(
    SQLiteDatabase database,
    String query,
    ExecuteResultHandler<T> handler) {
  SQLiteStatement statement = database.compileStatement(query);
  int count = statement.executeUpdateDelete();
  return handler.handleUpdateDelete(count);
}
 

private boolean performSetItem(String key, String value, boolean isPersistent, boolean allowRetryWhenFull) {
    SQLiteDatabase database = mDatabaseSupplier.getDatabase();
    if (database == null) {
        return false;
    }

    WXLogUtils.d(WXSQLiteOpenHelper.TAG_STORAGE, "set k-v to storage(key:" + key + ",value:" + value + ",isPersistent:" + isPersistent + ",allowRetry:" + allowRetryWhenFull + ")");
    String sql = "INSERT OR REPLACE INTO " + WXSQLiteOpenHelper.TABLE_STORAGE + " VALUES (?,?,?,?);";
    SQLiteStatement statement = null;
    String timeStamp = WXSQLiteOpenHelper.sDateFormatter.format(new Date());
    try {
        statement = database.compileStatement(sql);
        statement.clearBindings();
        statement.bindString(1, key);
        statement.bindString(2, value);
        statement.bindString(3, timeStamp);
        statement.bindLong(4, isPersistent ? 1 : 0);
        statement.execute();
        return true;
    } catch (Exception e) {
        WXLogUtils.e(WXSQLiteOpenHelper.TAG_STORAGE, "DefaultWXStorage occurred an exception when execute setItem :" + e.getMessage());
        if (e instanceof SQLiteFullException) {
            if (allowRetryWhenFull && trimToSize()) {
                //try again
                //setItem/setItemPersistent method only allow try once when occurred a sqliteFullException.
                WXLogUtils.d(WXSQLiteOpenHelper.TAG_STORAGE, "retry set k-v to storage(key:" + key + ",value:" + value + ")");
                return performSetItem(key, value, isPersistent, false);
            }
        }

        return false;
    } finally {
        if(statement != null) {
            statement.close();
        }
    }
}
 

private <T> T executeInsert(
    SQLiteDatabase database,
    String query,
    ExecuteResultHandler<T> handler) {
  SQLiteStatement statement = database.compileStatement(query);
  long count = statement.executeInsert();
  return handler.handleInsert(count);
}
 

public long getCountAdditional() {
    SQLiteDatabase db = this.getWritableDatabase();
    SQLiteStatement statement = db.compileStatement("SELECT COUNT(*) FROM "
            + TABLE_SERVERS
            + " WHERE "
            + KEY_TYPE
            + " = 1");
    long count = statement.simpleQueryForLong();
    db.close();
    return count;
}
 

private void loadSystemSettings(SQLiteDatabase db) {
      SQLiteStatement stmt = null;
String EnergyStar = SystemProperties.get("persist.hht.EnergyStar","false");
      try {
          stmt = db.compileStatement("INSERT OR IGNORE INTO system(name,value)"
                  + " VALUES(?,?);");

          loadBooleanSetting(stmt, Settings.System.DIM_SCREEN,
                  R.bool.def_dim_screen);
	if("false".equals(EnergyStar))
           loadIntegerSetting(stmt, Settings.System.SCREEN_OFF_TIMEOUT,
                  R.integer.def_screen_off_timeout);
	else
		loadIntegerSetting(stmt, Settings.System.SCREEN_OFF_TIMEOUT,
                  R.integer.def_screen_off_timeout_EnergyStar);
         

          // Set default cdma DTMF type
          loadSetting(stmt, Settings.System.DTMF_TONE_TYPE_WHEN_DIALING, 0);

          // Set default hearing aid
          loadSetting(stmt, Settings.System.HEARING_AID, 0);

          // Set default tty mode
          loadSetting(stmt, Settings.System.TTY_MODE, 0);

          loadIntegerSetting(stmt, Settings.System.SCREEN_BRIGHTNESS,
                  R.integer.def_screen_brightness);

          loadBooleanSetting(stmt, Settings.System.SCREEN_BRIGHTNESS_MODE,
                  R.bool.def_screen_brightness_automatic_mode);

          loadDefaultAnimationSettings(stmt);

          loadBooleanSetting(stmt, Settings.System.ACCELEROMETER_ROTATION,
                  R.bool.def_accelerometer_rotation);

          loadDefaultHapticSettings(stmt);

          loadBooleanSetting(stmt, Settings.System.NOTIFICATION_LIGHT_PULSE,
                  R.bool.def_notification_pulse);

          loadUISoundEffectsSettings(stmt);

          loadIntegerSetting(stmt, Settings.System.POINTER_SPEED,
                  R.integer.def_pointer_speed);
      } finally {
          if (stmt != null) stmt.close();
      }
  }
 

/**
 * Saves an existing beacon's main details, and/or custom details.
 * <b>This method is for internal (and beacon extensions) use only.</b>
 * @param beacon    An existing beacon.
 * @param flags     If 0, the beacon's <b>advertiseMode</b>, <b>txPower</b>, <b>name</b> and <b>flags</b> will be saved.
 *                  Other basic details will also be saved depending on the beacon type.
 *                  If non-zero, then only custom details will be saved, on a per-beacon defined basis.
 */
public void update(Beacon beacon, int flags) {
    SQLiteDatabase db = getWritableDatabase();

    if (0 == flags) {
        // we'll do two updates - use a transaction
        db.beginTransaction();

        if (null == mUpdateItemStmt) {
            mUpdateItemStmt = db.compileStatement("UPDATE " + ITEMS_TABLE + " SET advMode=?, txLevel=?, flags=?, name=? WHERE rowid=?");
        }

        mUpdateItemStmt.bindLong(1, beacon.getAdvertiseMode());
        mUpdateItemStmt.bindLong(2, beacon.getTxPowerLevel());
        mUpdateItemStmt.bindLong(3, beacon.getFlags());
        bindStringOrNull(mUpdateItemStmt, 4, beacon.getName());
        mUpdateItemStmt.bindLong(5, beacon.getSavedId());

        executeSafeUpdateOrDelete(mUpdateItemStmt);
    }

    SQLiteStatement updateStatement;
    switch (beacon.getKind()) {
        case KIND_EDDYSTONE_URL:
        case KIND_EDDYSTONE_UID:
        case KIND_EDDYSTONE_EID:
        case KIND_EDDYSTONE_TLM:
            updateStatement = prepareUpdateStatement((EddystoneBase) beacon, db);
            break;
        case KIND_IBEACON:
            updateStatement = prepareUpdateStatement((iBeacon) beacon, db);
            break;
        default:
            Persistable persister = null == mBeaconPersisters ? null : mBeaconPersisters.get(beacon.getKind());
            updateStatement = null == persister ? null : persister.prepareUpdate(beacon, db, flags);
            break;
    }

    if (null != updateStatement) {
        updateStatement.bindLong(1, beacon.getSavedId());
        executeSafeUpdateOrDelete(updateStatement);
    }

    if (0 == flags) {
        db.setTransactionSuccessful();
        db.endTransaction();
    }
}
 

/**
 * Replaces all shortcuts of type {@link Favorites#ITEM_TYPE_SHORTCUT} which have a valid
 * launcher activity target with {@link Favorites#ITEM_TYPE_APPLICATION}.
 */
@Thunk void convertShortcutsToLauncherActivities(SQLiteDatabase db) {
    db.beginTransaction();
    Cursor c = null;
    SQLiteStatement updateStmt = null;

    try {
        // Only consider the primary user as other users can't have a shortcut.
        long userSerial = UserManagerCompat.getInstance(mContext)
                .getSerialNumberForUser(UserHandleCompat.myUserHandle());
        c = db.query(TABLE_FAVORITES, new String[] {
                Favorites._ID,
                Favorites.INTENT,
            }, "itemType=" + Favorites.ITEM_TYPE_SHORTCUT + " AND profileId=" + userSerial,
            null, null, null, null);

        updateStmt = db.compileStatement("UPDATE favorites SET itemType="
                + Favorites.ITEM_TYPE_APPLICATION + " WHERE _id=?");

        final int idIndex = c.getColumnIndexOrThrow(Favorites._ID);
        final int intentIndex = c.getColumnIndexOrThrow(Favorites.INTENT);

        while (c.moveToNext()) {
            String intentDescription = c.getString(intentIndex);
            Intent intent;
            try {
                intent = Intent.parseUri(intentDescription, 0);
            } catch (URISyntaxException e) {
                Log.e(TAG, "Unable to parse intent", e);
                continue;
            }

            if (!Utilities.isLauncherAppTarget(intent)) {
                continue;
            }

            long id = c.getLong(idIndex);
            updateStmt.bindLong(1, id);
            updateStmt.executeUpdateDelete();
        }
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        Log.w(TAG, "Error deduping shortcuts", ex);
    } finally {
        db.endTransaction();
        if (c != null) {
            c.close();
        }
        if (updateStmt != null) {
            updateStmt.close();
        }
    }
}