How to search for a string in an SQLite database

Question

I am new to SQLite in Android. I have successfully created the database with this schema:

public final class BookDbSchema {

    private BookDbSchema() {
    }

    public static final class BookEntry implements BaseColumns{
        public static final String NAME = "bookItems";

        public static final String ID = "id";
        public static final String URL = "url";
        public static final String TITLE = "title";
    }
}

The problem I am having is searching the database for a particular string.

I want to search the id column if 1993 exists. Please how do I do that?

I have read the documentation but I don’t know where to input "1993" for in the query method.

score 1 · Answer 1 · answered Sep 10 '16 at 16:34

You can use the below method :

Cursor query (String table, 
                String[] columns, 
                String selection, 
                String[] selectionArgs, 
                String groupBy, 
                String having, 
                String orderBy, 
                String limit)

query(NAME, new String[]{ID, URL, TITLE}, ID + "=?", new String[]{"1993"}, null, null, null, null);

Tiago Oliveira · Accepted Answer · 2016-09-10T20:31:09.310

i belive you are looking for this

 Context context;
    BookDbSchema mDbHelper;

    public BookDbSchema (Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }
    ...

    ArrayList<String> urls = new ArrayList<>();
    ArrayList<String> titles = new ArrayList<>();
    ArrayList<String> ids= new ArrayList<>();

    mDbHelper = new BookDbSchema(context);
    SQLiteDatabase db = mDbHelper.getReadableDatabase();

    String[] projection = {
            FeedEntry.ID,
            FeedEntry.URL,
            FeedEntry.TITLE
    };

    String selection = FeedEntry.ID + " LIKE ? "; //WHERE
    String[] selectionArgs = {"1993"};   //VALUE

    Cursor c = db.query(
            FeedEntry.NAME,  // Your Table Name
            projection,
            selection,
            selectionArgs,
            null,
            null,
            null
    );


    if(c.getCount() != 0) {
        c.moveToFirst();
        for(int i = 0; i<c.getCount(); i++) {
            urls.add(c.getString(c.getColumnIndex(FeedEntry.URL)));
            titles.add(c.getString(c.getColumnIndex(FeedEntry.TITLE)));
            ids.add(c.getString(c.getColumnIndex(FeedEntry.ID)));
            c.moveToNext();
        }

        String firstUrl = urls.get(0);   //Returns the first url found
        String firstID = ids.get(0);   //Returns the first id found
        int urlSize = urls.size(); // returns the count of urls found
    }else{
        //Nothing found
    }


    c.close();
    db.close();

Thanks. This works but will it work if the result was no found on the first row? — X09, Sep 10 '16 at 18:55
yes, i will edit the awnser so if you have multiple rows with id of 1993 you will be able to get all of them — Tiago Oliveira, Sep 10 '16 at 19:11
i edited my awnser, `urls` and `titles` will now hold every row with id of 1993 — Tiago Oliveira, Sep 10 '16 at 19:23
Please I ran into a little problem. I need to update the row in which the result was found. Is there anyway I can get the id of the row that the result was found? — X09, Sep 10 '16 at 20:28
you just need to add `c.getString(c.getColumnIndex(someColumn)` see the edit — Tiago Oliveira, Sep 10 '16 at 20:34

How to search for a string in an SQLite database

2 Answers2