Fetch specific data from android database sqlite

Question

I'm wondering if it is possible to fetch one specific type of data from an android database, based on sqlite.

Let's say I have a table with rows "Category" and "Title" and I want to get a String array containing the "Title" of those categories matching a given one.

For example:

Title    Category
A        spam
B        important
C        spam

And given "spam", I want to get a String array like

S = {A,C}

Is there a way to do this?

Please note that I'm very new to databases.

Thanks in advance.

EDIT:

I'm actually trying with a query

mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
            KEY_BODY, KEY_CATEGORY},  KEY_CATEGORY + "=" + category, null, null, null, KEY_CATEGORY);

But it returns a Cursor and I need a SimpleCursorAdapter like here for formatting

SimpleCursorAdapter notes =
            new SimpleCursorAdapter(this, R.layout.notes_row, notesCursor, from, to);
    mList.setAdapter(notes);

where from and to are:

String[] from = new String[] { NotesDbAdapter.KEY_TITLE };
int[] to = new int[] { R.id.text1 };

FINAL EDIT:

It finally worked, with this code provided by @ELITE

Cursor cursor = mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_CATEGORY}, KEY_CATEGORY + "=?", new String[]{category}, null, null, KEY_CATEGORY);
ArrayList elements = new ArrayList();
while(cursor.moveToNext()) {
    elements.add(cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
}
// use elements variable,
// here you'll get ["A", "C"]
SimpleCursorAdapter notes =
        new SimpleCursorAdapter(this, R.layout.notes_row, cursor, from, to);
mList.setAdapter(notes);

@ELITE I'm actually trying with a query `mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_CATEGORY}, KEY_CATEGORY + "=" + category, null, null, null, KEY_CATEGORY);` But this returns a Cursor, and I need a SimpleCursorAdapter for formatting. I'll update the main post for clarity. — dari1495, Jan 30 '16 at 11:43

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

Iterator over the cursor and store the result in ArrayList or Vector and then use it.

Cursor cursor = mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_CATEGORY}, KEY_CATEGORY + "=?", new String[]{category}, null, null, KEY_CATEGORY);
ArrayList elements = new ArrayList();
while(cursor.moveToNext()) {
    elements.add(cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
}
cursor.close();
// use elements variable,
// here you'll get ["A", "C"]
SimpleCursorAdapter notes =
        new SimpleCursorAdapter(this, R.layout.notes_row, elements, from, to);
mList.setAdapter(notes);

Hope it'll work.

For more details refer this question

edited May 23 '17 at 12:23

Community

1
1

answered Jan 30 '16 at 11:52

ELITE

5,815
3
19
29

`android.database.sqlite.SQLiteException: no such column: spam (code 1): , while compiling: SELECT _id, title, body, category FROM notes WHERE category=spam ORDER BY category` When using the query :( – dari1495 Jan 30 '16 at 11:59
Query is working correctly now, but I have problems with the formatting, Look at the code at the end of the post, now that I have the ArrayList with the contents I want, how do I format it to simpleCursorAdapter or something that adapts to notes_row (I think this is relevant), to be shown in the screen correctly? – dari1495 Jan 30 '16 at 12:11
you have to pass the object of `ArrayList` to `SimpleCursorAdapter` like this `SimpleCursorAdapter notes = new SimpleCursorAdapter(this, R.layout.notes_row, elements, from, to);` – ELITE Jan 30 '16 at 12:23
it worked, but a bit differently, I used `cursor` instead of `elements` and works perfectly :D – dari1495 Jan 30 '16 at 13:14

score 0 · Answer 2 · answered Jan 30 '16 at 11:44

0

Use the query() of SQLiteDatabase class like this:

SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query("<name of the table>", new String[]{}, "Category=?", new String[]{"spam"}, null, null, null);

answered Jan 30 '16 at 11:44

camelCaseCoder

1,447
19
32

Fetch specific data from android database sqlite

2 Answers2