Java Code Examples for android.database.sqlite.SQLiteStatement

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, SessionEntity entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
    stmt.bindString(2, entity.getSessionKey());
    stmt.bindLong(3, entity.getPeerId());
    stmt.bindLong(4, entity.getPeerType());
    stmt.bindLong(5, entity.getLatestMsgType());
    stmt.bindLong(6, entity.getLatestMsgId());
    stmt.bindString(7, entity.getLatestMsgData());
    stmt.bindLong(8, entity.getTalkId());
    stmt.bindLong(9, entity.getCreated());
    stmt.bindLong(10, entity.getUpdated());
}
 

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

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, AddressBook entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
 
    String name = entity.getName();
    if (name != null) {
        stmt.bindString(2, name);
    }
 
    String author = entity.getAuthor();
    if (author != null) {
        stmt.bindString(3, author);
    }
}
 

private void testAndroidSQLiteWrite(Statistics statistics) {
    Trace trace = new Trace("Android Write");
    SQLiteDatabase db = platformSQLite.getReadableDatabase();
    SQLiteStatement statement = db.compileStatement(
        String.format("insert into %s (%s, %s) values (?,?)",
            Record.TABLE_NAME,
            Record.COLUMN_CONTENT,
            Record.COLUMN_CREATED_TIME));
    try {
        db.beginTransaction();
        for (int i = 0; i < COUNT; i++) {
            Record record = Record.create(i);
            statement.bindString(1, record.getContent());
            statement.bindDouble(2, record.getCreatedTime());
            long id = statement.executeInsert();
            record.setId(id);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
    statistics.write( trace.exit() );
}
 

public void updateFences(List<String> fences) {
    SQLiteStatement stmt = null;
    try {
        long start = System.currentTimeMillis();
        db.beginTransaction();
        db.execSQL("DELETE FROM " + DBHelper.TABLE_GEOFENCES);
        stmt = db.compileStatement(
                "INSERT OR IGNORE INTO " + DBHelper.TABLE_GEOFENCES + " (" + DBHelper.TG_FENCE + ") VALUES (?)"
        );
        for (String fence : fences) {
            stmt.clearBindings();
            stmt.bindString(1, fence);
            stmt.executeInsert();
        }
        db.setTransactionSuccessful();
        count = fences.size();
        Logger.log.geofence("Saved "+fences.size()+" in "+(System.currentTimeMillis() - start) + " ms");
    } catch (SQLException ex) {
        Logger.log.geofenceError("Storage error", ex);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        db.endTransaction();
    }
}
 

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

@Nullable
private String getExternalSensorId(ExternalSensorSpec sensor, SQLiteDatabase db) {
  String sql =
      "SELECT IFNULL(MIN("
          + SensorColumns.SENSOR_ID
          + "), '') FROM "
          + Tables.EXTERNAL_SENSORS
          + " WHERE "
          + SensorColumns.CONFIG
          + "=? AND "
          + SensorColumns.TYPE
          + "=?";
  SQLiteStatement statement = db.compileStatement(sql);
  statement.bindBlob(1, sensor.getConfig());
  statement.bindString(2, sensor.getType());
  String sensorId = statement.simpleQueryForString();
  if (!sensorId.isEmpty()) {
    return sensorId;
  }
  return null;
}
 

/**
 * Submits a batch of commands to the database for execution..
 * 
 * @param sqls SQLs to execute.
 */
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);
    }
}
 

private void loadVibrateSetting(SQLiteDatabase db, boolean deleteOld) {
    if (deleteOld) {
        db.execSQL("DELETE FROM system WHERE name='" + Settings.System.VIBRATE_ON + "'");
    }

    SQLiteStatement stmt = null;
    try {
        stmt = db.compileStatement("INSERT OR IGNORE INTO system(name,value)"
                + " VALUES(?,?);");

        // Vibrate on by default for ringer, on for notification
        int vibrate = 0;
        vibrate = AudioSystem.getValueForVibrateSetting(vibrate,
                AudioManager.VIBRATE_TYPE_NOTIFICATION,
                AudioManager.VIBRATE_SETTING_ONLY_SILENT);
        vibrate |= AudioSystem.getValueForVibrateSetting(vibrate,
                AudioManager.VIBRATE_TYPE_RINGER, AudioManager.VIBRATE_SETTING_ONLY_SILENT);
        loadSetting(stmt, Settings.System.VIBRATE_ON, vibrate);
    } finally {
        if (stmt != null) stmt.close();
    }
}
 

/**
 * @inheritdoc
 */
@Override
protected void bindValues(SQLiteStatement stmt, Green_AtUsersBean entity) {
    stmt.clearBindings();

    String accountid = entity.getAccountid();
    if (accountid != null) {
        stmt.bindString(1, accountid);
    }

    String userid = entity.getUserid();
    if (userid != null) {
        stmt.bindString(2, userid);
    }

    String at_user_info_json = entity.getAt_user_info_json();
    if (at_user_info_json != null) {
        stmt.bindString(3, at_user_info_json);
    }
}
 

@Override
protected final void bindValues(SQLiteStatement stmt, Word entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
    stmt.bindString(2, localeConverter.convertToDatabaseValue(entity.getLocale()));
 
    Calendar timeLastUpdate = entity.getTimeLastUpdate();
    if (timeLastUpdate != null) {
        stmt.bindLong(3, timeLastUpdateConverter.convertToDatabaseValue(timeLastUpdate));
    }
    stmt.bindLong(4, entity.getRevisionNumber());
    stmt.bindString(5, contentStatusConverter.convertToDatabaseValue(entity.getContentStatus()));
    stmt.bindString(6, entity.getText());
    stmt.bindString(7, entity.getPhonetics());
    stmt.bindLong(8, entity.getUsageCount());
 
    SpellingConsistency spellingConsistency = entity.getSpellingConsistency();
    if (spellingConsistency != null) {
        stmt.bindString(9, spellingConsistencyConverter.convertToDatabaseValue(spellingConsistency));
    }
}
 

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, UseArea entity) {
    stmt.clearBindings();
 
    String areaid = entity.getAreaid();
    if (areaid != null) {
        stmt.bindString(1, areaid);
    }
 
    String areaid2345 = entity.getAreaid2345();
    if (areaid2345 != null) {
        stmt.bindString(2, areaid2345);
    }
 
    String areaName = entity.getAreaName();
    if (areaName != null) {
        stmt.bindString(3, areaName);
    }
 
    Boolean main = entity.getMain();
    if (main != null) {
        stmt.bindLong(4, main ? 1L: 0L);
    }
}
 

/**
 * @inheritdoc
 */
@Override
protected void bindValues(SQLiteStatement stmt, ImageCache entity) {
    stmt.clearBindings();

    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }

    String url = entity.getUrl();
    if (url != null) {
        stmt.bindString(2, url);
    }

    String path = entity.getPath();
    if (path != null) {
        stmt.bindString(3, path);
    }
}
 

@Override
public Metrics runCase() {
    mDbHelper = new DbHelper(App.getInstance(), getClass().getName());
    Metrics result = new Metrics(getClass().getSimpleName()+" ("+mInsertions+" insertions)", mTestSizeIndex);
    SQLiteDatabase db = mDbHelper.getWritableDatabase();

    byte[] titleByteArry = new byte[50];
    byte[] urlByteArray = new byte[100];
    byte[] lyricsByteArray = new byte[2000];
    byte[] aboutByteArray = new byte[2000];
    Map<Integer, SQLiteStatement> statementCache = new HashMap<>();

    result.started();
    db.beginTransaction();
    mInsertId = 1;
    doInsertions(db, mInsertions, statementCache, titleByteArry, urlByteArray, lyricsByteArray, aboutByteArray);
    db.setTransactionSuccessful();
    db.endTransaction();
    result.finished();

    return result;
}
 

private void bindArgsToStatement(SQLiteStatement myStatement, ReadableArray sqlArgs) {
    for (int i = 0; i < sqlArgs.size(); i++) {
        ReadableType type = sqlArgs.getType(i);
        if (type == ReadableType.Number){
            double tmp = sqlArgs.getDouble(i);
            if (tmp == (long) tmp) {
                myStatement.bindLong(i + 1, (long) tmp);
            } else {
                myStatement.bindDouble(i + 1, tmp);
            }
        } else if (sqlArgs.isNull(i)) {
            myStatement.bindNull(i + 1);
        } else {
            myStatement.bindString(i + 1, SQLitePluginConverter.getString(sqlArgs,i,""));
        }
    }
}
 

public void deleteWallpapers(@NonNull List<Wallpaper> wallpapers) {
    if (!openDatabase()) {
        LogUtil.e("Database error: deleteWallpapers() failed to open database");
        return;
    }

    String query = "DELETE FROM " +TABLE_WALLPAPERS+ " WHERE " +KEY_URL+ " = ?";
    SQLiteStatement statement = mDatabase.get().mSQLiteDatabase.compileStatement(query);
    mDatabase.get().mSQLiteDatabase.beginTransaction();

    for (Wallpaper wallpaper : wallpapers) {
        statement.clearBindings();
        statement.bindString(1, wallpaper.getUrl());
        statement.execute();
    }

    mDatabase.get().mSQLiteDatabase.setTransactionSuccessful();
    mDatabase.get().mSQLiteDatabase.endTransaction();
}
 

@Override
protected final void bindValues(SQLiteStatement stmt, MagicPhotoEntity entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
    stmt.bindLong(2, entity.getWidth());
    stmt.bindLong(3, entity.getHeight());
 
    String groupPointsStr = entity.getGroupPointsStr();
    if (groupPointsStr != null) {
        stmt.bindString(4, groupPointsStr);
    }
 
    String groupTypeStr = entity.getGroupTypeStr();
    if (groupTypeStr != null) {
        stmt.bindString(5, groupTypeStr);
    }
 
    String imagePath = entity.getImagePath();
    if (imagePath != null) {
        stmt.bindString(6, imagePath);
    }
}
 

@Override
protected final void bindValues(SQLiteStatement stmt, Message entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
 
    String content = entity.getContent();
    if (content != null) {
        stmt.bindString(2, content);
    }
    stmt.bindLong(3, entity.getClient_id());
    stmt.bindLong(4, entity.getCreated_at());
    stmt.bindDouble(5, entity.getSorted_by());
    stmt.bindLong(6, entity.getCommand_id());
    stmt.bindLong(7, entity.getSender_id());
    stmt.bindLong(8, entity.getChannel_id());
}
 

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, HistoryEntity entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
 
    Long calculateTime = entity.getCalculateTime();
    if (calculateTime != null) {
        stmt.bindLong(2, calculateTime);
    }
 
    Integer rate = entity.getRate();
    if (rate != null) {
        stmt.bindLong(3, rate);
    }
}
 

/**
 * @inheritdoc
 */
@Override
protected void bindValues(SQLiteStatement stmt, Green_TimeLineStatus entity) {
    stmt.clearBindings();

    Integer _id = entity.get_id();
    if (_id != null) {
        stmt.bindLong(1, _id);
    }

    String accountid = entity.getAccountid();
    if (accountid != null) {
        stmt.bindString(2, accountid);
    }

    String mblogid = entity.getMblogid();
    if (mblogid != null) {
        stmt.bindString(3, mblogid);
    }

    String json = entity.getJson();
    if (json != null) {
        stmt.bindString(4, json);
    }
}
 

private void upgradeScreenTimeout(SQLiteDatabase db) {
     // Change screen timeout to current default
     String EnergyStar = SystemProperties.get("persist.hht.EnergyStar","false");
     db.beginTransaction();
     SQLiteStatement stmt = null;
     try {
         stmt = db.compileStatement("INSERT OR REPLACE INTO system(name,value)"
                 + " VALUES(?,?);");
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);
         db.setTransactionSuccessful();
     } finally {
         db.endTransaction();
         if (stmt != null)
             stmt.close();
     }
 }
 

private long performGetLength() {
    SQLiteDatabase database = mDatabaseSupplier.getDatabase();
    if (database == null) {
        return 0;
    }

    String sql = "SELECT count(" + WXSQLiteOpenHelper.COLUMN_KEY + ") FROM " + WXSQLiteOpenHelper.TABLE_STORAGE;
    SQLiteStatement statement = null;
    try {
        statement = database.compileStatement(sql);
        return statement.simpleQueryForLong();
    } catch (Exception e) {
        WXLogUtils.e(WXSQLiteOpenHelper.TAG_STORAGE, "DefaultWXStorage occurred an exception when execute getLength:" + e.getMessage());
        return 0;
    } finally {
        if(statement != null) {
            statement.close();
        }
    }
}
 

@Override
protected final void bindValues(SQLiteStatement stmt, TransactionEntity entity) {
    stmt.clearBindings();
 
    Long _id = entity.get_id();
    if (_id != null) {
        stmt.bindLong(1, _id);
    }
    stmt.bindString(2, entity.getMessage_id());
    stmt.bindString(3, entity.getHashid());
 
    Integer status = entity.getStatus();
    if (status != null) {
        stmt.bindLong(4, status);
    }
 
    Integer pay_count = entity.getPay_count();
    if (pay_count != null) {
        stmt.bindLong(5, pay_count);
    }
 
    Integer crowd_count = entity.getCrowd_count();
    if (crowd_count != null) {
        stmt.bindLong(6, crowd_count);
    }
}
 

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

public SQLiteStatement getNextJobDelayedUntilWithNetworkStatement() {
    if(nextJobDelayedUntilWithNetworkStatement == null) {
        String sql = "SELECT " + DbOpenHelper.DELAY_UNTIL_NS_COLUMN.columnName
                + " FROM " + tableName + " WHERE "
                + DbOpenHelper.RUNNING_SESSION_ID_COLUMN.columnName + " != " + sessionId
                + " ORDER BY " + DbOpenHelper.DELAY_UNTIL_NS_COLUMN.columnName + " ASC"
                + " LIMIT 1";
        nextJobDelayedUntilWithNetworkStatement = db.compileStatement(sql);
    }
    return nextJobDelayedUntilWithNetworkStatement;
}
 

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, Aqi entity) {
    stmt.clearBindings();
 
    String areaid = entity.getAreaid();
    if (areaid != null) {
        stmt.bindString(1, areaid);
    }
 
    Integer aqi = entity.getAqi();
    if (aqi != null) {
        stmt.bindLong(2, aqi);
    }
 
    String quality = entity.getQuality();
    if (quality != null) {
        stmt.bindString(3, quality);
    }
 
    Integer pm2_5 = entity.getPm2_5();
    if (pm2_5 != null) {
        stmt.bindLong(4, pm2_5);
    }
 
    Integer pm10 = entity.getPm10();
    if (pm10 != null) {
        stmt.bindLong(5, pm10);
    }
 
    Integer so2 = entity.getSo2();
    if (so2 != null) {
        stmt.bindLong(6, so2);
    }
 
    Integer no2 = entity.getNo2();
    if (no2 != null) {
        stmt.bindLong(7, no2);
    }
}
 

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, Category entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
    stmt.bindString(2, entity.getTitle());
    stmt.bindString(3, entity.getDescription());
    stmt.bindString(4, entity.getImage());
}
 

public void updateDownloadCurrentBytes(final DownloadInfo di) {
    SQLiteStatement downloadCurrentBytes = getUpdateCurrentBytesStatement();
    downloadCurrentBytes.clearBindings();
    downloadCurrentBytes.bindLong(1, di.mCurrentBytes);
    downloadCurrentBytes.bindLong(2, di.mIndex);
    downloadCurrentBytes.execute();
}
 

/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, Category entity) {
    stmt.clearBindings();
 
    Long id = entity.getId();
    if (id != null) {
        stmt.bindLong(1, id);
    }
    stmt.bindString(2, entity.getName());
    stmt.bindLong(3, entity.getType());
    stmt.bindString(4, entity.getIcon());
}
 

public void putBulkItemValue(JsonArray api_data) {
    SQLiteDatabase db = null;
    SQLiteStatement statement;
    try {
        if (api_data.size() > 0) {
            db = getWritableDatabase();
            db.delete(slotitem_table_name, null, null);
            db.beginTransaction();
            statement = db.compileStatement("INSERT INTO ".concat(slotitem_table_name).concat(" (KEY, KCID, VALUE) values (?, ?, ?)"));

            for (JsonElement item: api_data) {
                int column = 1;
                JsonObject item_data = item.getAsJsonObject();
                String api_id = item_data.get("api_id").getAsString();
                String api_slotitem_id = item_data.get("api_slotitem_id").getAsString();
                statement.bindString(column++, api_id);
                statement.bindString(column++, api_slotitem_id);
                statement.bindString(column++, item_data.toString());
                statement.execute();
            }
            statement.close();
            db.setTransactionSuccessful();
        }
    } catch (RuntimeException e) {
        e.printStackTrace();
    } finally {
        if (db != null) {
            db.endTransaction();
        }
    }
}