Android SQLite Search – Searching SQLite Database in Android





Today’s walkthrough is on Android Sqlite Search. You might have come across applications like Whatsapp, which allows you to search data from the SQLite database. Searching data from android SQLite is way too simple as we are going to use generic SQL Syntax To Accoplish Android SQLite Search.

How to Perform Android SQLite Search

To search data from Android SQLite datbase, we are going to use SQL LIKE clause, then handle our query to rawQuery method of SQLiteDatabase as shown in the code below:

Make Sure You Also Read: How to create android Settings Screen using PreferenceFragment

Searching android sqlite using SQL LIKE CLAUSE

public static void search(Context context, String searchText) {
        
    DbHelper helper = new DbHelper(context);
    db = helper.getWritableDatabase();

    String sql = "SELECT * FROM " + DbConstants.TABLE_WORDS + " WHERE " + DbConstants.WORD + " LIKE '%" + searchText + "%'";
    Cursor cursor = db.rawQuery(sql, null);

    if(cursor.getCount() > 0){
        // means search has returned data

        if (cursor.moveToFirst()) {
            do {
                String wordId = cursor.getString(cursor.getColumnIndex(DbConstants.WORD_ID));
                String word = cursor.getString(cursor.getColumnIndex(DbConstants.WORD));
                String meaning = cursor.getString(cursor.getColumnIndex(DbConstants.MEANING));
                String category = cursor.getString(cursor.getColumnIndex(DbConstants.CATEGORY));


                // display your search result here in RecyclerView or in any manner
            } while (cursor.moveToNext());
        }

    } else {
        Toast.makeText(context, "No data was found in the system!", Toast.LEGNTH_LONG).show();
    }
    cursor.close();
}

DbHelper class (DbHelper.java)
DbHelper.java class is responsible for creating SQLite database and also creating needed table under this tutorial

Make Sure You Also Read: How to change android home button for applications



package com.qkaruemedicaldictonary.free.medicaldictionary.db;


import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.qkaruemedicaldictonary.free.medicaldictionary.dialogs.T;

public class DbHelper extends SQLiteOpenHelper {
    private static final String CREATE_TABLE = "CREATE TABLE " + DbConstants.TABLE_WORDS + " (" +
            DbConstants.ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            DbConstants.WORD_ID + " VARCHAR(255) NOT NULL," +
            DbConstants.WORD + " VARCHAR(255) NOT NULL," +
            DbConstants.MEANING + " TEXT NOT NULL," +
            DbConstants.LETTER + " VARCHAR(255) NOT NULL," +
            DbConstants.STATUS + " VARCHAR(255) NOT NULL," +
            DbConstants.CATEGORY + " VARCHAR(255)  DEFAULT 0);";

    private static final String CREATE_HISTORY_TABLE = "CREATE TABLE " + DbConstants.TABLE_HISTORY + " (" +
            DbConstants.ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            DbConstants.HISTORY_WORD + " TEXT NOT NULL);";

    private Context context;


    public DbHelper(Context context) {
        super(context, DbConstants.DB_NAME, null, DbConstants.DB_VERSION);
        this.context = context;
        this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            db.execSQL(CREATE_TABLE);
            db.execSQL(CREATE_HISTORY_TABLE);
        } catch (SQLException e) {
            T.longToast(context, e.toString());
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            db.execSQL("DROP TABLE IF EXISTS " + DbConstants.TABLE_WORDS);
            db.execSQL("DROP TABLE IF EXISTS " + DbConstants.TABLE_HISTORY);
            onCreate(db);
        } catch (SQLException e) {
            T.longToast(context, e.toString());
        }
    }
}

DbConstants.java class
DbConstants.java is resposible for storing database constants like table columns, database name, and so on.



package com.qkaruemedicaldictonary.free.medicaldictionary.db;


public class DbConstants {
    public static final String DB_NAME = "wordsDatabase";

    public static final int DB_VERSION = 7;

    public static final String TABLE_WORDS = "wordsTable";
    public static final String ID = "_id";
    public static final String WORD_ID = "wordId"; // also used in the table history
    public static final String CATEGORY = "category";
    public static final String WORD = "word";
    public static final String MEANING = "meaning";
    public static final String LETTER = "letter";
    public static final String STATUS = "status";


    // creating table history
    public static final String TABLE_HISTORY = "historyTable";
    public static final String HISTORY_WORD = "historyWord";
}

Explanation on Android SQLite Search

The most important section of this tutorial is to understand how to use SQL LIKE clause. The general formart of SQL LIKE clause should be: SELECT * FROM table_name WHERE column_name LIKE ‘%search_keyword%’ . And that’s how to perfrom Android SQLite Search

Video on how to search android SQLite database