Android Studio search existing sqlite db

Question

So im developing an app and I want the user to search for a food item in my database and then to be returned with a list view of all food names that match the search criteria. I already have my slqite database created and added to the assets folder. The database is called foodDatabase.db

I have created the Database Helper like so:

package com.example.codebind.databasedemo;

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

/**
 * Created by tom on 15/03/2018.
 */

public class FoodDatabaseHelper extends SQLiteOpenHelper{
    public static final String DATABASE_NAME = "FoodDatabase.db";
    public static final String TABLE_NAME = "dataset";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "Food";
    public static final String COL_3 = "Description";
    public static final String COL_4 = "Protein";
    public static final String COL_5 = "Fat";
    public static final String COL_6 = "Carbohydrate";
    public static final String COL_7 = "Energy";
    public static final String COL_8 = "Starch";
    public static final String COL_9 = "Sugar";
    public static final String COL_10 = "Cholesterol";

    public FoodDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("create table " + TABLE_NAME +" (ID INTEGER 
PRIMARY KEY AUTOINCREMENT,FOOD TEXT,DESCRIPTION TEXT,PROTEIN BLOB,FAT 
BLOB,CARBOHYDRATE BLOB,ENERGY BLOB,STARCH BLOB,SUGAR BLOB,CHOLESTEROL BLOB) 
");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " +TABLE_NAME);
        onCreate(sqLiteDatabase);
    }
}

and currently my MainActivity.java is clean. I haven't touched it yet.

I'm asking for guidance on how to add a listener so that when the user enters a food name the app will return all foods from the foodDatabase.db that meets the search query.

Thanks

score 2 · Accepted Answer · answered Mar 15 '18 at 21:50

The basis of your search will be a query based upon the FOOD column (I believe).

e.g. SELECT * FROM dataset WHERE FOOD LIKE '%your_food%'

For example assume that your database has data such as (note only food and id columns have been populated with data) :-

Then the query SELECT * FROM dataset WHERE FOOD LIKE '%mash%' would result in :-

i.e. Foods with an id of 2 and 3 contain the food mash

You could run this query using the SQLiteDatabase query method. The query method returns a Cursor with the extracted data. So a method in your DatabaseHelper could be :-

public Cursor getFoodsWithProvidedFood(String provided_food) {
    return this.getWritableDatabase().query(
            TABLE_NAME,
            null,
            COL_2 + " LIKE '%" + provided_food + "%' ",
    null,
            null,
            null,
            null
    );
}

Converting this into a complete but very basic App you could have :-

The Database Helper - FoodDatabaseHelper.java

public class FoodDatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "FoodDatabase.db";
    public static final String TABLE_NAME = "dataset";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "Food";
    public static final String COL_3 = "Description";
    public static final String COL_4 = "Protein";
    public static final String COL_5 = "Fat";
    public static final String COL_6 = "Carbohydrate";
    public static final String COL_7 = "Energy";
    public static final String COL_8 = "Starch";
    public static final String COL_9 = "Sugar";
    public static final String COL_10 = "Cholesterol";

    SQLiteDatabase sqLiteDatabase; //<<<< Added

    public FoodDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        sqLiteDatabase = this.getWritableDatabase(); //<<<< Amended
    }

    //@Override
    public void onNotRecommendedCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("create table " + TABLE_NAME +
                        " (ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                "FOOD TEXT," +
                "DESCRIPTION TEXT," +
                "PROTEIN BLOB," +
                "FAT BLOB," +
                "CARBOHYDRATE BLOB," +
                "ENERGY BLOB," +
                "STARCH BLOB," +
                "SUGAR BLOB," +
                "CHOLESTEROL BLOB)");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crtsql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
                "(" +
                COL_1 + " INTEGER PRIMARY KEY, " +
                COL_2 + " TEXT, " +
                COL_3 + " TEXT, " +
                COL_4 + " BLOB, " +
                COL_5 + " BLOB, " +
                COL_6 + " BLOB, " +
                COL_7 + " BLOB, " +
                COL_8 + " BLOB, " +
                COL_9 + " BLOB, " +
                COL_10 + " BLOB " +
                ")";
        db.execSQL(crtsql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " +TABLE_NAME);
        onCreate(sqLiteDatabase);
    }

    public void insertFood(
            String food,
            String description,
            byte[] protien,
            byte[] fat,
            byte[] carbohydrate,
            byte[] energy,
            byte[] starch,
            byte[] sugar,
            byte[] cholesterol) {
        ContentValues cv = new ContentValues();
        cv.put(COL_2,food);
        cv.put(COL_3,description);
        cv.put(COL_4,protien);
        cv.put(COL_5,fat);
        cv.put(COL_6,carbohydrate);
        cv.put(COL_7,energy);
        cv.put(COL_8,starch);
        cv.put(COL_9,sugar);
        cv.put(COL_10,cholesterol);
        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(TABLE_NAME,null,cv);
    }

    public Cursor getFoodsWithProvidedFood(String provided_food) {
        return this.getWritableDatabase().query(
                TABLE_NAME,
                null,
                COL_2 + " LIKE '%" + provided_food + "%' ",
        null,
                null,
                null,
                null
        );
    }
}

Notes
It will likely cause fewer issue if you have just one place where you define table and column names.
- hence the changed onCreate
- although column names in SQLite are case-insensitive the Cursor's getColumnIndex method (as used in the main activity) is case-sensitive (IMO a bug).
- AUTOINCREMENT doesn't do what it implies, rather INTEGER PRIMARY KEY itself makes a column one that increments adding AUTOINCREMENT is a special case that ensures that the id is greater at the expense of overheads. Hence AUTOINCREMENT has been removed.
Two additional methods have been added
- insertFood to insert(add) data to the dataset table.
- getFoodsWithProvidedFood as described above.

The Activity - MainActivity.java

public class MainActivity extends AppCompatActivity {

    FoodDatabaseHelper foodDBHlpr;
    Cursor mCsr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        foodDBHlpr = new FoodDatabaseHelper(this);
        byte[] dummy = new byte[]{0,1,2,3,4,5,6,7,8,9};

        // Add some data oif none exists
        if (DatabaseUtils.queryNumEntries(foodDBHlpr.getWritableDatabase(),FoodDatabaseHelper.TABLE_NAME) < 1) {
            foodDBHlpr.insertFood("Fish and Chips", "The English Seaside meal",
                    dummy, dummy, dummy, dummy, dummy, dummy, dummy);
            foodDBHlpr.insertFood("Bangers and Mash", "Yummy!!",
                    dummy, dummy, dummy, dummy, dummy, dummy, dummy);
            foodDBHlpr.insertFood("Mashed Potatoe", "Boring",
                    dummy, dummy, dummy, dummy, dummy, dummy, dummy);
        }

        // get a Cursor with the extracted foods
        mCsr = foodDBHlpr.getFoodsWithProvidedFood("Mash");
        // Loop Through the Cursor
        while (mCsr.moveToNext()) {
            Log.d("FOODFOUND","You found the food called - " + mCsr.getString(mCsr.getColumnIndex(FoodDatabaseHelper.COL_2)));
        }
        if (mCsr.getCount() < 1) {
            Log.d("FOODFOUND","No foods found that match the search criteria.");
        }
        mCsr.close(); //<<<< Should always close Cursors when done with them
    }
}

Result in the Log :-

03-15 21:48:21.170 1702-1702/foodsdb.so49307874_foodsdb D/FOODFOUND: You found the food called - Bangers and Mash
03-15 21:48:21.170 1702-1702/foodsdb.so49307874_foodsdb D/FOODFOUND: You found the food called - Mashed Potatoe

wow thanks man this is awesome and very well explained! I'll let you know how it goes in the morning. Thanks again! — , Mar 16 '18 at 00:47
Hi Mike, thanks for this, how would I use this database in the asset folder to populate a ListView when I run the app? — , Mar 16 '18 at 20:22
Actually looking at that other question now. If you edit the question to include the structure (table name(s) and column names) I can consider those in the response. — MikeT, Mar 16 '18 at 20:26
the example table you used in your first answer is literally the exact same as the one I have created. — , Mar 16 '18 at 20:56

Android Studio search existing sqlite db

1 Answers1

The Database Helper - FoodDatabaseHelper.java

The Activity - MainActivity.java

Result in the Log :-