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

public byte[] getKey(int Major, int Minor, int indicator) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor res = db.query(KEYS_TABLE_NAME,
            new String[] { KEYS_COLUMN_INDICATOR, KEYS_COLUMN_VALUE },
            KEYS_COLUMN_MAJORMINOR + "=? AND " +  KEYS_COLUMN_INDICATOR + "=?",
            new String[] { utils.majorMinorToHexString(Major,Minor), Integer.toString(indicator)},
            null,null, null);

    if (res.moveToFirst()) {
        byte[] key = utils.hexStringToByteArray(res.getString(res.getColumnIndex(KEYS_COLUMN_VALUE)));
        return key;
    }
    else {
        return null;
    }
}
 

/**
 * Get the maximum sequence number in the database associated with the given public key
 *
 * @param pubkey - public key for which to search for blocks
 * @return the maximum sequence number found
 */
public int getMaxSeqNum(byte[] pubkey) {
    SQLiteDatabase dbReadable = getReadableDatabase();
    int res = -1;
    String[] projection = new String[]{"max(" +
            TrustChainDBContract.BlockEntry.COLUMN_NAME_SEQUENCE_NUMBER + ")"};
    String whereClause = TrustChainDBContract.BlockEntry.COLUMN_NAME_PUBLIC_KEY + " = ?";
    String[] whereArgs = new String[]{Base64.encodeToString(pubkey, Base64.DEFAULT)};

    Cursor cursor = dbReadable.query(
            TrustChainDBContract.BlockEntry.TABLE_NAME,
            projection,
            whereClause,
            whereArgs,
            null,
            null,
            null
    );
    if (cursor.getCount() == 1) {
        cursor.moveToFirst();
        res = cursor.getInt(cursor.getColumnIndex(
                "max(" + TrustChainDBContract.BlockEntry.COLUMN_NAME_SEQUENCE_NUMBER + ")"));
    }
    cursor.close();
    return res;
}
 

private void fillCache() {
  Cursor cursor = null;

  try {
    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    cursor            = db.query(TABLE, null, null, null, null, null, null);

    while (cursor != null && cursor.moveToNext()) {
      long   id      = cursor.getLong(cursor.getColumnIndexOrThrow(ID_COLUMN));
      String address = cursor.getString(cursor.getColumnIndexOrThrow(ADDRESS_COLUMN));

      if (address == null || address.trim().length() == 0)
        address = "Anonymous";

      idCache.put(id, address);
      addressCache.put(address, id);
    }
  } finally {
    if (cursor != null)
      cursor.close();
  }
}
 

@Nullable
@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String order) {
    OModel model = getModel(getContext(), uri);
    setMatcher(model);
    SQLiteDatabase db = model.getWritableDatabase();
    Cursor cr = db.query(model.getTableName(), projection, selection, selectionArgs, null, null, order);
    Context ctx = getContext();
    if (cr != null && ctx != null)
        cr.setNotificationUri(ctx.getContentResolver(), uri);
    return cr;
}
 

public List getPauseButtonValue() {
    String coloumns[] = {storePauseValue};
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    Cursor cursor = db.query(TABLE_NAME11, coloumns, null, null, null, null, null);
    List<Integer> list = new ArrayList<>();
    while (cursor.moveToNext()) {
        int pauseValue = cursor.getInt(0);
        list.add(pauseValue);
    }
    db.setTransactionSuccessful();
    db.endTransaction();
    cursor.close();
    db.close();
    return list;
}
 

public List<Stats> getSetCards(String setId) {
    List<Stats> cardList = new ArrayList<Stats>();

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.query(TABLE_STATS,
            new String[]{KEY_ID, KEY_TRUE_ANSWERS, KEY_TIME_SPENT, KEY_DATE},
            KEY_ID + "=?",
            new String[]{setId}, null, null, null, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Stats stats = new Stats();
            stats.setSetId(cursor.getString(0));
            stats.setTrueAnswers(cursor.getString(1));
            stats.setTimeSpent(cursor.getString(2));
            stats.setDate(cursor.getString(3));
            // Adding card to list
            cardList.add(stats);
        } while (cursor.moveToNext());
    }

    cursor.close();
    // return contact list
    return cardList;
}
 

private void assertMessageWithSubjectExists(SQLiteDatabase database, String subject) {
    Cursor cursor = database.query("messages", new String[] { "subject" }, null, null, null, null, null);
    try {
        assertTrue(cursor.moveToFirst());
        assertEquals(subject, cursor.getString(0));
    } finally {
        cursor.close();
    }
}
 

/**
 * Initialize data.
 * @param startDate The first month of the report period
 * @param periodLength The number of months in the report period
 * @param currency The report reference currency
 * @param filterColumn The report filtering column in transactions table 
 * @param filterId The report filtering id in transactions table 
 * @param dbAdapter Database adapter to query data
 */
private void init(Context context, Calendar startDate, int periodLength, Currency currency, String filterColumn, int[] filterId, MyEntityManager em) {
	this.context = context;
	this.periodLength = periodLength;
	startDate.set(startDate.get(Calendar.YEAR), startDate.get(Calendar.MONTH), 01, 00, 00, 00);
	this.startDate = startDate;
	
	SQLiteDatabase db = em.db();
	Cursor cursor = null;

	fillEmptyList(startDate, periodLength);

	try {
		// search accounts for which the reference currency is the given currency
		int[] accounts = getAccountsByCurrency(currency, db);
		if (accounts.length==0) {
			max=min=0;
			absMax=absMin=0;
			return;
		}
		
		// prepare query based on given report parameters
		String where = getWhereClause(filterColumn, filterId, accounts);
		String[] pars = getWherePars(startDate, periodLength, filterId, accounts);
		// query data
		cursor = db.query(TRANSACTION_TABLE, new String[]{filterColumn, TransactionColumns.from_amount.name(), TransactionColumns.datetime.name(), TransactionColumns.datetime.name()},
				   where, pars, null, null, TransactionColumns.datetime.name());
		// extract data and fill statistics
		extractData(cursor); 

	} finally {
		if (cursor!=null) cursor.close();
	}
}
 

/**
 * Recupera el detalle de una nota
 * @param id id del regsitro a recuperar
 * @return detalles de la nota, null si no se encuentra
 */
public Note getNote (long id) {
    Note note = null;
    try {
        SQLiteDatabase db = this.getReadableDatabase();


        String[] columns = new String [] { NoteTable._ID,
                                           NoteTable.COLUMN_TITLE,
                                           NoteTable.COLUMN_CONTENT,
                                           NoteTable.COLUMN_CREATION_DATE,
                                           NoteTable.COLUMN_UPDATE_DATE};

        String selection = NoteTable._ID + " = ? ";
        String[] selectionArgs = new String [] {Long.toString(id)};
        Cursor cursor = db.query(NoteTable.TABLE_NAME, columns, selection, selectionArgs, null, null, null);

        if (cursor != null) {
            if (cursor.moveToNext ()) {
                note = new Note (cursor.getLong (cursor.getColumnIndex(NoteTable._ID)),
                                 cursor.getString (cursor.getColumnIndex(NoteTable.COLUMN_TITLE)),
                                 cursor.getString (cursor.getColumnIndex(NoteTable.COLUMN_CONTENT)),
                                 new Date (cursor.getLong (cursor.getColumnIndex(NoteTable.COLUMN_CREATION_DATE))),
                                 new Date (cursor.getLong (cursor.getColumnIndex(NoteTable.COLUMN_UPDATE_DATE))));

            }
            // liberamos y cerramos el cursor.
            cursor.close();
        }

    } catch (Exception ex) {
        Log.e (AppConstants.APP_TAG, "Error en getNote", ex);
    }

    return note;
}
 

public @NonNull String getAddressFromId(long id) {
  String cachedAddress = idCache.get(id);

  if (cachedAddress != null)
    return cachedAddress;

  Cursor cursor = null;

  try {
    Log.w(TAG, "Hitting DB on query [ID].");

    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    cursor            = db.query(TABLE, null, ID_COLUMN + " = ?", new String[] {id+""}, null, null, null);

    if (!cursor.moveToFirst())
      return "Anonymous";

    String address = cursor.getString(cursor.getColumnIndexOrThrow(ADDRESS_COLUMN));

    if (address == null || address.trim().equals("")) {
      return "Anonymous";
    } else {
      idCache.put(id, address);
      return address;
    }
  } finally {
    if (cursor != null)
      cursor.close();
  }
}
 

public Pessoa getDetalhePessoa(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_PESSOA,
            new String[]{Pessoa.TAG_ID,
                    Pessoa.TAG_NOME,
                    Pessoa.TAG_SOBRENOME,
                    Pessoa.TAG_DESCRICAO,
                    Pessoa.TAG_EMPRESA,
                    Pessoa.TAG_PROFISSAO,
                    Pessoa.TAG_FOTO,
                    Pessoa.TAG_CONTATOS,
                    Pessoa.TAG_ULTIMA_ATUALIZACAO},
            Pessoa.TAG_ID + "=?",
            new String[]{String.valueOf(id)}, null, null, null, null);


    Pessoa pessoa = new Pessoa();

    if (cursor.moveToFirst()) {

        pessoa.setId(id);
        pessoa.setNome(cursor.getString(1));
        pessoa.setSobrenome(cursor.getString(2));
        pessoa.setDescricao(cursor.getString(3));
        pessoa.setEmpresa(cursor.getString(4));
        pessoa.setProfissao(cursor.getString(5));
        pessoa.setFoto(cursor.getBlob(6));
        pessoa.setContatos(cursor.getString(7));
        pessoa.setHorarioUltimaAtualizacao(cursor.getString(8));

        cursor.close();
    }

    db.close();
    return pessoa;
}
 

/**查询单个数据
 * @param column
 * @param value
 * @return
 */
public Cursor query(String column, String value) {
	SQLiteDatabase db = this.getReadableDatabase();
	try {
		return db.query(TABLE_NAME, null, getSelection(column), getSelectionArgs(column, value), null, null, null);
	} catch (Exception e) {
		Log.e(TAG, "query  try { return db.query(...} catch (Exception e) {\n" + e.getMessage());
	}
	return null;
}
 

public BeaconResult getBeacon(String uuid, String major, String minor) {
    SQLiteDatabase db = dbHelper.getReadableDatabase();

    try {
        String[] columns = {
                COLUMN_NAME_UUID,
                COLUMN_NAME_MAJOR,
                COLUMN_NAME_MINOR,
                COLUMN_NAME_INFORMAL_NAME,
        };

        Cursor cursor = db.query(TABLE_NAME, columns, PRIMARY_KEY_SELECTION, new String[] {uuid, major, minor}, null, null, null);

        if (cursor != null && cursor.getCount() > 0) {
            cursor.moveToFirst();
            String informalName = cursor.getString(cursor.getColumnIndex(COLUMN_NAME_INFORMAL_NAME));
            cursor.close();

            return new BeaconResult(uuid, major, minor, informalName);
        }

    } finally {
        if (db != null) {
            db.close();
        }
    }

    return null;
}
 

static List<OpenLocateLocation> getSince(SQLiteDatabase database, long millisecondsSince1970) {
    if (database == null) {
        return null;
    }

    Cursor cursor = database.query(TABLE_NAME, null, LocationTable.COLUMN_CREATED_AT + " > " + millisecondsSince1970,
            null, null, null, LocationTable.COLUMN_CREATED_AT, QUERY_LIMIT);

    if (cursor == null || cursor.isClosed()) {
        return null;
    }

    return getLocations(cursor);
}
 

public long getLastReadTime(int id) {
    SQLiteDatabase db = this.getReadableDatabase();
    try (Cursor bookscursor = db.query(BOOK_TABLE, new String[] {BOOK_LASTREAD}, BOOK_ID + "=?", new String[] {""+id}, null, null, null)) {

        if (bookscursor.moveToNext()) {
            return bookscursor.getLong(bookscursor.getColumnIndex(BOOK_LASTREAD));
        }
    }
    return -1;
}
 

/**
 * Gets a cursor containing the top songs played.  Note this only returns songs that have been
 * played at least once in the past NUM_WEEKS
 *
 * @param numResults number of results to limit by.  If <= 0 it returns all results
 * @return the top tracks
 */
public Cursor getTopPlayedResults(int numResults) {
    updateResults();

    final SQLiteDatabase database = getReadableDatabase();
    return database.query(SongPlayCountColumns.NAME, new String[]{SongPlayCountColumns.ID},
            null, null, null, null, SongPlayCountColumns.PLAY_COUNT_SCORE + " DESC",
            (numResults <= 0 ? null : String.valueOf(numResults)));
}
 

public Cursor getDirectShareList() {
  SQLiteDatabase db = databaseHelper.getReadableDatabase();
  return db.query(TABLE_NAME, null, null, null, null, null, DATE + " DESC");
}
 

private void migrateToPackageTable(SQLiteDatabase db, int oldVersion) {
    if (oldVersion >= 63) {
        return;
    }

    resetTransient(db);

    // By pushing _ALL_ repositories to a priority of 10, it makes it slightly easier
    // to query for the non-default repositories later on in this method.
    ContentValues highPriority = new ContentValues(1);
    highPriority.put(RepoTable.Cols.PRIORITY, 10);
    db.update(RepoTable.NAME, highPriority, null, null);

    String[] defaultRepos = context.getResources().getStringArray(R.array.default_repos);
    String fdroidPubKey = defaultRepos[7];
    String fdroidAddress = defaultRepos[1];
    String fdroidArchiveAddress = defaultRepos[REPO_XML_ARG_COUNT + 1];
    String gpPubKey = defaultRepos[REPO_XML_ARG_COUNT * 2 + 7];
    String gpAddress = defaultRepos[REPO_XML_ARG_COUNT * 2 + 1];
    String gpArchiveAddress = defaultRepos[REPO_XML_ARG_COUNT * 3 + 1];

    updateRepoPriority(db, fdroidPubKey, fdroidAddress, 1);
    updateRepoPriority(db, fdroidPubKey, fdroidArchiveAddress, 2);
    updateRepoPriority(db, gpPubKey, gpAddress, 3);
    updateRepoPriority(db, gpPubKey, gpArchiveAddress, 4);

    int priority = 5;
    String[] projection = new String[] {RepoTable.Cols.SIGNING_CERT, RepoTable.Cols.ADDRESS};

    // Order by ID, because that is a good analogy for the order in which they were added.
    // The order in which they were added is likely the order they present in the ManageRepos activity.
    Cursor cursor = db.query(RepoTable.NAME, projection, RepoTable.Cols.PRIORITY + " > 4", null, null, null, RepoTable.Cols._ID);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        String signingCert = cursor.getString(cursor.getColumnIndex(RepoTable.Cols.SIGNING_CERT));
        String address = cursor.getString(cursor.getColumnIndex(RepoTable.Cols.ADDRESS));
        updateRepoPriority(db, signingCert, address, priority);
        cursor.moveToNext();
        priority++;
    }
    cursor.close();
}
 

public Atividade getDetalheAtividade(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_ATIVIDADE,
            new String[]{Atividade.TAG_ID,
                    Atividade.TAG_TITULO,
                    Atividade.TAG_PREDIO,
                    Atividade.TAG_SALA,
                    Atividade.TAG_DESCRICAO,
                    Atividade.TAG_HORARIOS,
                    Atividade.TAG_TIPO,
                    Atividade.TAG_ULTIMA_ATUALIZACAO,
                    Atividade.TAG_FAVORITO},
            Atividade.TAG_ID + "=?",
            new String[]{String.valueOf(id)}, null, null, null, null);

    Atividade atividade = new Atividade();

    if (cursor.moveToFirst()) {

        atividade.setId(id);
        atividade.setTitulo(cursor.getString(1));
        atividade.setLocal(cursor.getString(2), cursor.getString(3));
        atividade.setDescricao(cursor.getString(4));
        atividade.setHorarios(cursor.getString(5));
        atividade.setTipo(cursor.getString(6));

        atividade.setHorarioUltimaAtualizacao(cursor.getString(7));

        if (cursor.getInt(8) == 1) {
            atividade.setFavorito(true);
        }
    }

    cursor.close();
    db.close();

    List<Pessoa> ministrantes = getMinistrantes(atividade);
    atividade.setMinistrantes(ministrantes);

    return atividade;
}
 

/**
 * 获取站点信息
 * 
 * @param s
 *            是要匹配的字串
 * @param isFirstQuery
 *            是否是第一次返回站点信息游标
 * @return 站点信息游标
 */
public List<Map<String, String>> getStations(CharSequence s, boolean isFirstQuery) {
	SQLiteDatabase db = getWritableDatabase();
	if (db == null){
		return null;
	}
	Cursor c = null;
	if (isFirstQuery) {
		c = db.rawQuery(
				"select _id,[Key] as Station,[Value] as Code from RecentSearchB where Type='Station' order by [Time] DESC;",
				null);
	} else {
		if (s.equals("")) {
			c = db.rawQuery("select _id,Station,Code,Fullcode from StationB", null);
		} else {
			char[] chars = s.toString().toLowerCase(Locale.getDefault())
					.toCharArray();
			String arg1 = "";
			for (char ch : chars) {
				arg1 += "%%" + ch;
			}
			arg1 += "%%";
			c = db.query("StationB", new String[] { "_id", "Station", "Code" },
					"Station like ? or Fullcode like ?", new String[] {
							arg1, arg1 }, null, null, null);
		}
	}
	if (c == null){
		return null;
	}
	List<Map<String, String>> list = new ArrayList<Map<String, String>>();
	while(c.moveToNext()){
		Map<String, String> map = new HashMap<String, String>();
		map.put(KEY, c.getString(c.getColumnIndex("Station")));
		map.put(VALUE, c.getString(c.getColumnIndex("Code")));
		list.add(map);
	}
	if (c != null){
		c.close();
	}
	if (db != null){
		db.close();
	}
	return list;
}