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
– dari1495, Commented Jan 30, 2016 at 11:43

Community · Accepted Answer · 2017-05-23 12:23:33Z

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, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 30, 2016 at 11:52

ELITE

5,9703 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dari1495 Over a year ago

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 Over a year ago

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?

ELITE Over a year ago

you have to pass the object of ArrayList to SimpleCursorAdapter like this SimpleCursorAdapter notes = new SimpleCursorAdapter(this, R.layout.notes_row, elements, from, to);

dari1495 Over a year ago

it worked, but a bit differently, I used cursor instead of elements and works perfectly :D

camelCaseCoder · Accepted Answer · 2016-01-30 11:44:03Z

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, 2016 at 11:44

camelCaseCoder

1,45719 silver badges32 bronze badges

Collectives™ on Stack Overflow

Fetch specific data from android database sqlite

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related