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

/**
 * @return certificate id or -1 if not found
 */
public long getCertificateId(byte[] certificateBytes){
    Formatter formatter = new Formatter();
    for (byte b : Sha256Helper.sha256(certificateBytes)){
        formatter.format("%02x", b);
    }

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor c = db.query(TABLE_CERTIFICATES,
            new String[]{COLUMN_ID},
            COLUMN_FINGERPRINT + "=?", new String[]{formatter.toString()},
            null, null, null);

    long returnValue;
    if (c.moveToFirst()){
        returnValue = c.getLong(0);
    } else {
        returnValue = -1;
    }
    c.close();
    db.close();
    return returnValue;
}
 

private Cursor getSingleQuestionCursorByField(String field, int value) throws QuestionNotFoundException {
    SQLiteDatabase db = new DatabaseOpenHelper(context).getReadableDatabase();

    Cursor cursor = db.query(DATABASE_QUESTIONS_TABLE_NAME, SELECT_ALL,
            field + " = " + value, null, null, null, null);
    cursor.moveToFirst();

    try {
        if (cursor.getCount() < 1) {
            throw new QuestionNotFoundException(context.getString(R.string.notfound));
        }
    } finally {
        db.close();
    }

    return cursor;
}
 

/**
 * 查询数据库中所有的数据
 *
 */

public ArrayList<SearchHistorysBean> findAll(){
	ArrayList<SearchHistorysBean> data = new ArrayList<SearchHistorysBean>();;
	SQLiteDatabase db = helper.getReadableDatabase();
	Cursor cursor = db.query("t_historywords", null, null, null, null, null, "updatetime desc");
	//遍历游标，将数据存储在
	while(cursor.moveToNext()){
		SearchHistorysBean searchDBData = new SearchHistorysBean();
		searchDBData._id =cursor.getInt(cursor.getColumnIndex("_id"));
		searchDBData.historyword = cursor.getString(cursor.getColumnIndex("historyword"));
		searchDBData.updatetime = cursor.getLong(cursor.getColumnIndex("updatetime"));
		data.add(searchDBData);
	}
	cursor.close();
	db.close();
	return data;
}
 

@Nullable
@Override
public Cursor query(@NonNull Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    SQLiteDatabase database = mSQLiteHelper.getWritableDatabase();
    String table = getType(uri);
    if (TextUtils.isEmpty(table)) {
        throw new UnsupportedOperationException("No such table to query");
    } else {
        return database.query(table,
                projection,
                selection,
                selectionArgs,
                null,
                null,
                sortOrder);
    }
}
 

private Pair<String, String> getImageFilenames(int id) {
    SQLiteDatabase db = mDb.getReadableDatabase();
    Cursor result = db.query(ImageDb.TABLE_NAME,
            new String[] { ImageDb.COLUMN_IMAGE_THUMBNAIL_FILENAME,
                ImageDb.COLUMN_IMAGE_FILENAME }, // cols to return
            ImageDb.COLUMN_ID + " = ?", // select query
            new String[] { Integer.toString(id) }, // args to select query
            null,
            null,
            null,
            null);
    if (result.getCount() > 0) {
        result.moveToFirst();
        String thumbFilename = result.getString(0);
        String imageFilename = result.getString(1);
        result.close();
        return new Pair<String, String>(thumbFilename, imageFilename);
    } else {
        return null;
    }
}
 

public String getQuestTrackerDump() {
    SQLiteDatabase db = this.getReadableDatabase();
    StringBuilder sb = new StringBuilder();
    Cursor c = db.query(qt_table_name, null, null, null, null, null, null);
    while (c.moveToNext()) {
        String key = c.getString(c.getColumnIndex("KEY"));
        String active = c.getString(c.getColumnIndex("ACTIVE"));
        int cond0 = c.getInt(c.getColumnIndex("CND0"));
        int cond1 = c.getInt(c.getColumnIndex("CND1"));
        int cond2 = c.getInt(c.getColumnIndex("CND2"));
        int cond3 = c.getInt(c.getColumnIndex("CND3"));
        int cond4 = c.getInt(c.getColumnIndex("CND4"));
        int cond5 = c.getInt(c.getColumnIndex("CND5"));
        int type = c.getInt(c.getColumnIndex("TYPE"));
        String time = c.getString(c.getColumnIndex("TIME"));
        sb.append(KcaUtils.format("[%s] A:%s C:%02d,%02d,%02d,%02d,%02d,%02d K:%d T:%s\n",
                key, active, cond0, cond1, cond2, cond3, cond4, cond5, type, time));
    }
    c.close();
    return sb.toString().trim();
}
 

public List getHint() {
    String coloumns[] = {HINTID};
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    Cursor cursor = db.query(TABLE_NAME3, coloumns, null, null, null, null, null);
    List<Integer> list = new ArrayList<>();

    while (cursor.moveToNext()) {
        int hintId = cursor.getInt(0);
        list.add(hintId);
    }

    db.setTransactionSuccessful();
    db.endTransaction();
    cursor.close();
    db.close();
    return list;
}
 

/**
 * 
 * @param table1
 *            Table without foreign key.
 * @param table2
 *            Table with foreign key.
 * @param table1Id
 *            id of table1.
 * @param table2Id
 *            id of table2.
 * @return success or failed.
 */
protected boolean isFKInsertCorrect(String table1, String table2, long table1Id, long table2Id) {
	SQLiteDatabase db = Connector.getDatabase();
	Cursor cursor = null;
	try {
		cursor = db.query(table2, null, "id = ?", new String[] { String.valueOf(table2Id) },
				null, null, null);
		cursor.moveToFirst();
		long fkId = cursor.getLong(cursor.getColumnIndexOrThrow(BaseUtility.changeCase(table1
				+ "_id")));
		return fkId == table1Id;
	} catch (Exception e) {
		e.printStackTrace();
		return false;
	} finally {
		cursor.close();
	}
}
 

private static Cursor getDataItemsByHostAndPath(SQLiteDatabase db, String packageName, String signatureDigest, String host, String path) {
    String[] params;
    String selection;
    if (path == null) {
        params = new String[]{packageName, signatureDigest};
        selection = "packageName =? AND signatureDigest =?";
    } else if (host == null) {
        params = new String[]{packageName, signatureDigest, path};
        selection = "packageName =? AND signatureDigest =? AND path =?";
    } else {
        params = new String[]{packageName, signatureDigest, host, path};
        selection = "packageName =? AND signatureDigest =? AND host =? AND path =?";
    }
    selection += " AND deleted=0";
    return db.query("dataItemsAndAssets", GDIBHAP_FIELDS, selection, params, null, null, "packageName, signatureDigest, host, path");
}
 

public boolean hasHistoryItems() {
  SQLiteOpenHelper helper = new DBHelper(activity);
  SQLiteDatabase db = null;
  Cursor cursor = null;
  try {
    db = helper.getReadableDatabase();
    cursor = db.query(DBHelper.TABLE_NAME, COUNT_COLUMN, null, null, null, null, null);
    cursor.moveToFirst();
    return cursor.getInt(0) > 0;
  } finally {
    close(cursor, db);
  }
}
 

public String getData(String data){
    SQLiteDatabase db=this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, new String[] { COLUMN_ACCOUNT,COLUMN_PASSWORD, COLUMN_LINK
            }, COLUMN_ACCOUNT + " = ?", new String[] { data },
            null, null, null, null);
    if (cursor!=null && cursor.moveToFirst()){
        do{
            data1=cursor.getString(1);
        }while (cursor.moveToNext());
    }
    return data1;
}
 

@Override
public JsonObject lookup(String tableName, String itemId) throws MobileServiceLocalStoreException {
    try {
        JsonObject result = null;
        String invTableName = normalizeTableName(tableName);

        Map<String, ColumnDataInfo> table = this.mTables.get(invTableName);

        SQLiteDatabase db = this.getWritableDatabaseSynchronized();
        
        try {
            Cursor cursor = null;

            try {
                cursor = db.query(invTableName, table.keySet().toArray(new String[0]), "id = '" + itemId + "'", null, null, null, null);

                if (cursor.moveToNext()) {
                    result = parseRow(cursor, table);
                }
            } finally {
                if (cursor != null && !cursor.isClosed()) {
                    cursor.close();
                }
            }
        } finally {
            this.closeDatabaseSynchronized(db);
        }

        return result;
    } catch (Throwable t) {
        throw new MobileServiceLocalStoreException(t);
    }
}
 

public ArrayList<String[]> getSmbList() {
    SQLiteDatabase sqLiteDatabase = getReadableDatabase();

    Cursor cursor = sqLiteDatabase.query(getTableForOperation(Operation.SMB), null,
            null, null, null, null, null);
    cursor.moveToFirst();
    ArrayList<String[]> row = new ArrayList<>();
    try {

        while (cursor.moveToNext()) {
            try {
                row.add(new String[] {
                        cursor.getString(cursor.getColumnIndex(COLUMN_NAME)),
                        SmbUtil.getSmbDecryptedPath(context, cursor.getString(cursor.getColumnIndex(COLUMN_PATH)))
                });
            } catch (CryptException e) {
                e.printStackTrace();

                // failing to decrypt the path, removing entry from database
                Toast.makeText(context,
                        context.getResources().getString(R.string.failed_smb_decrypt_path),
                        Toast.LENGTH_LONG).show();
                removeSmbPath(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)),
                        "");
                continue;
            }
        }
    } finally {
        cursor.close();
    }
    return row;
}
 

public ArrayList<Question> getStaredQuestions() {
    SQLiteDatabase db = new DatabaseOpenHelper(context).getReadableDatabase();
    Cursor cursor = db.query(DATABASE_QUESTIONS_TABLE_NAME, SELECT_ALL, " stared = 1 ", null, null, null,
            COLUM_UPDATE_AT + " DESC");
    try {
        return getAllQuestionsByCursor(cursor);
    } finally {
        cursor.close();
        db.close();
    }
}
 

public ArrayList<AppRule> getAllRules() {
    ArrayList<AppRule> list = new ArrayList<>();

    SQLiteDatabase db = this.dbHelper.getReadableDatabase();
    String[] selection = {
            natDBHelper.COLUMN_APPNAME,
            natDBHelper.COLUMN_APPUID,
            natDBHelper.COLUMN_ONIONTYPE,
            natDBHelper.COLUMN_LOCALHOST,
            natDBHelper.COLUMN_LOCALNETWORK
    };
    Cursor cursor = db.query(natDBHelper.NAT_TABLE_NAME, selection, null, null, null, null, null);

    if (!cursor.moveToFirst()) {
        Log.e(TAG, "getAllRules size is null!");
        return list;
    }

    AppRule appRule;

    do {
        appRule = new AppRule(
                true,
                cursor.getString(0),
                cursor.getLong(1),
                cursor.getString(2),
                cursor.getLong(3) == 1,
                cursor.getLong(4) == 1
        );
        list.add(appRule);
    } while (cursor.moveToNext());

    cursor.close();
    db.close();
    Log.d(TAG, "getAllRules size: " + String.valueOf(list.size()));
    return list;
}
 

@Test
public void persistConfiguration() {
    Context ctx = InstrumentationRegistry.getTargetContext();
    SQLiteDatabase db = new SQLiteOpenHelper(ctx).getWritableDatabase();
    SQLiteConfigurationDAO dao = new SQLiteConfigurationDAO(db);

    Config config = new Config();
    config.setActivitiesInterval(1000);
    config.setDesiredAccuracy(200);
    config.setDistanceFilter(300);
    config.setFastestInterval(5000);
    config.setInterval(10000);
    config.setLocationProvider(0);
    config.setMaxLocations(15000);
    config.setUrl("http://server:1234/locations");
    config.setSyncUrl("http://server:1234/syncLocations");
    config.setSyncThreshold(200);
    config.setStopOnTerminate(false);
    config.setStopOnStillActivity(false);
    config.setStationaryRadius(50);
    config.setStartOnBoot(true);
    config.setStartForeground(true);
    config.setSmallNotificationIcon("smallico");
    config.setLargeNotificationIcon("largeico");
    config.setNotificationTitle("test");
    config.setNotificationText("in progress");
    config.setNotificationIconColor("yellow");
    config.setNotificationsEnabled(true);

    dao.persistConfiguration(config);
    dao.persistConfiguration(config); // try once more

    Cursor cursor = db.query(SQLiteConfigurationContract.ConfigurationEntry.TABLE_NAME, null, null, null, null, null, null);
    Assert.assertEquals(1, cursor.getCount());
    cursor.close();

    try {
        Config storedConfig = dao.retrieveConfiguration();
        Assert.assertEquals(1000, storedConfig.getActivitiesInterval().intValue());
        Assert.assertEquals(200, storedConfig.getDesiredAccuracy().intValue());
        Assert.assertEquals(300, storedConfig.getDistanceFilter().intValue());
        Assert.assertEquals(5000, storedConfig.getFastestInterval().intValue());
        Assert.assertEquals(10000, storedConfig.getInterval().intValue());
        Assert.assertEquals(0, storedConfig.getLocationProvider().intValue());
        Assert.assertEquals(15000, storedConfig.getMaxLocations().intValue());
        Assert.assertEquals("http://server:1234/locations", storedConfig.getUrl());
        Assert.assertEquals("http://server:1234/syncLocations", storedConfig.getSyncUrl());
        Assert.assertEquals(200, storedConfig.getSyncThreshold().intValue());
        Assert.assertEquals(Boolean.FALSE, storedConfig.getStopOnTerminate());
        Assert.assertEquals(Boolean.FALSE, storedConfig.getStopOnStillActivity());
        Assert.assertEquals(50, storedConfig.getStationaryRadius(), 0);
        Assert.assertEquals(Boolean.TRUE, storedConfig.getStartOnBoot());
        Assert.assertEquals(Boolean.TRUE, storedConfig.getStartForeground());
        Assert.assertEquals("smallico", storedConfig.getSmallNotificationIcon());
        Assert.assertEquals("largeico", storedConfig.getLargeNotificationIcon());
        Assert.assertEquals("test", storedConfig.getNotificationTitle());
        Assert.assertEquals("in progress", storedConfig.getNotificationText());
        Assert.assertEquals("yellow", storedConfig.getNotificationIconColor());

    } catch (JSONException e) {
        Assert.fail(e.getMessage());
    }
}
 

public static void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
        case 1:
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
            break;
        case 2:
        case 3:
        case 4:
        case 5:
        case 6:
        case 7:
            db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + COLUMN_LAST_ACTIVATED_BUTTON_ID + " int;");
        case 8:
        case 9:
        case 10:
        case 11:
        case 12:
            // update receiver classpath
            String[] columns = {COLUMN_ID, COLUMN_CLASSNAME, COLUMN_TYPE};
            Cursor cursor = db.query(TABLE_NAME, columns,
                    null, null, null, null, null);
            cursor.moveToFirst();

            while (!cursor.isAfterLast()) {
                long id = cursor.getLong(0);
                String className = cursor.getString(1);
                String type = cursor.getString(2);

                String newClassName;
                if (Receiver.Type.UNIVERSAL.toString().equals(type)) {
                    newClassName = "eu.power_switch.obj.receiver.UniversalReceiver";
                } else {
                    newClassName = className.replace("eu.power_switch.obj.device.", "eu.power_switch.obj.receiver.device.");
                }

                Log.d("old className: " + className);
                Log.d("new className: " + newClassName);

                ContentValues values = new ContentValues();
                values.put(COLUMN_CLASSNAME, newClassName);
                db.update(TABLE_NAME, values, COLUMN_ID + "=" + id, null);

                cursor.moveToNext();
            }

            cursor.close();
    }
}
 

private void listing9_21() {
  // Listing 9-21: Querying a database
  HoardDBOpenHelper hoardDBOpenHelper =
    new HoardDBOpenHelper(this,
      HoardDBOpenHelper.DATABASE_NAME, null,
      HoardDBOpenHelper.DATABASE_VERSION);

  // Specify the result column projection. Return the minimum set
  // of columns required to satisfy your requirements.
  String[] result_columns = new String[] {
    HoardContract.KEY_ID,
    HoardContract.KEY_GOLD_HOARD_ACCESSIBLE_COLUMN,
    HoardContract.KEY_GOLD_HOARDED_COLUMN };

  // Specify the where clause that will limit our results.
  String where = HoardContract.KEY_GOLD_HOARD_ACCESSIBLE_COLUMN + "=?";
  String whereArgs[] = {"1"};

  // Replace these with valid SQL statements as necessary.
  String groupBy = null;
  String having = null;

  // Return in ascending order of gold hoarded.
  String order = HoardContract.KEY_GOLD_HOARDED_COLUMN + " ASC";
  SQLiteDatabase db = hoardDBOpenHelper.getWritableDatabase();
  Cursor cursor = db.query(HoardDBOpenHelper.DATABASE_TABLE,
    result_columns, where, whereArgs, groupBy, having, order);

  // Listing 9-22: Extracting values from a Cursor
  float totalHoard = 0f;
  float averageHoard = 0f;

  // Find the index to the column(s) being used.
  int GOLD_HOARDED_COLUMN_INDEX =
    cursor.getColumnIndexOrThrow(HoardContract.KEY_GOLD_HOARDED_COLUMN);

  // Find the total number of rows.
  int cursorCount = cursor.getCount();

  // Iterate over the cursors rows.
  // The Cursor is initialized at before first, so we can
  // check only if there is a "next" row available. If the
  // result Cursor is empty this will return false.
  while (cursor.moveToNext())
    totalHoard += cursor.getFloat(GOLD_HOARDED_COLUMN_INDEX);

  // Calculate an average -- checking for divide by zero errors.
  averageHoard = cursor.getCount() > 0 ?
                   (totalHoard / cursorCount) : Float.NaN;

  // Close the Cursor when you've finished with it.
  cursor.close();
}
 

public Cursor getIdentities() {
    SQLiteDatabase database = databaseHelper.getReadableDatabase();
    return database.query(TABLE_NAME, null, null, null, null, null, null);
}
 

/**
 *
 * @since 5.6.5
 * @param pPrimaryKeyParameters
 * @param pColumns
 * @return
 */
public Cursor getTileCursor(final String[] pPrimaryKeyParameters, final String[] pColumns) {
    final SQLiteDatabase db = getDb();
    return db.query(DatabaseFileArchive.TABLE, pColumns, primaryKey, pPrimaryKeyParameters, null, null, null);
}