Android: How can I search data in sqlite database and display it in listview

Question

How can I display data in a ListView from my database through search using an EditText or maybe filter it. Can you tell what codes I need have to do it I think Im missing something because my codes don't search it just display my data it a ListView. Any help would be much appreciated.

Here are my codes:

DBListHelper

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Locale;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

class DBlistHelper extends SQLiteOpenHelper {


private static final String DATABASE_PATH = "/data/data/com.example.searching/databases/";
private static final String DATABASE_NAME = "LPG.db";
private static final int SCHEMA_VERSION = 1;
private static final String TABLE_NAME = "Recipes";
private static final String COLUMN_ID = "_id";
private static final String COLUMN_RECIPE = "Recipe";
public static final String COLUMN_INGRIDIENTS = "Ingredients";
//public static final String COLUMN_PROCEDURE = "procedure";



public SQLiteDatabase dbSqlite;

private final Context myContext;

public DBlistHelper(Context context){
    super(context, DATABASE_NAME, null, SCHEMA_VERSION );
    this.myContext= context;

}

@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

public void createDatabase() {

    createDB();

}

public void createDB(){

    boolean dbExist = DBExists();

    if(!dbExist){

        this.getReadableDatabase();

        copyDBFromResource();

    }

}



private boolean DBExists() {

    SQLiteDatabase db = null;

    try{
        String databasePath = DATABASE_PATH + DATABASE_NAME;
        db = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.OPEN_READWRITE);
        db.setLocale(Locale.getDefault());
        db.setLockingEnabled(true);
        db.setVersion(1);

    } catch (SQLiteException e) {

        Log.e("SqlHelper", "database not found");
    }

    if (db != null){

        db.close();
    }

    return db != null ? true : false;

}



private void copyDBFromResource(){

    InputStream inputStream = null;
    OutputStream outStream = null;
    String dbFilePath =  DATABASE_PATH + DATABASE_NAME;

    try{

        inputStream = myContext.getAssets().open(DATABASE_NAME);

        outStream = new FileOutputStream(dbFilePath);

        byte [] buffer = new byte[1024];
        int length;
        while ((length = inputStream.read(buffer)) > 0){
            outStream.write(buffer, 0, length);

        }

        outStream.flush();
        outStream.close();
        inputStream.close();

    } catch (IOException e){

        throw new Error("Problem copying database from resource file.");
    }

}


public void openDatabase () throws SQLException {

    String myPath = DATABASE_PATH + DATABASE_NAME;
    dbSqlite = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

}

@Override
public synchronized void close () {

    if (dbSqlite !=null){

        dbSqlite.close();
    }

    super.close();

}

public Cursor getCursor() {

    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder ();

    queryBuilder.setTables(TABLE_NAME);

    String [] asColumnsToReturn = new String [] { COLUMN_ID, COLUMN_RECIPE, COLUMN_INGRIDIENTS};

    Cursor mCursor = queryBuilder.query(dbSqlite, asColumnsToReturn, null, null, null, null, "Recipe ASC");

    return mCursor;
}

public String getName (Cursor c){
    return(c.getString(1));


}

}

Main.java

import android.os.Bundle;
import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.text.Editable;
import android.text.TextWatcher;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.CursorAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;


public class Main extends Activity {

private DBlistHelper dbrecipelistHelper = null;
private Cursor ourCursor = null;
private recipeAdapter adapter = null;

EditText inputSearch = null;


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

    inputSearch = (EditText) findViewById(R.id.inputSearch);

    ListView myListView = (ListView) findViewById(R.id.listView);


    dbrecipelistHelper = new DBlistHelper(this);

    dbrecipelistHelper.createDatabase();

    dbrecipelistHelper.openDatabase();

    ourCursor=dbrecipelistHelper.getCursor();

    startManagingCursor(ourCursor);

    adapter = new recipeAdapter(ourCursor);

    myListView.setAdapter(adapter);


    //myListView.setOnItemClickListener(onListClick);

    }
    catch (Exception e)
    {

        Log.e("ERROR", "ERROR IN CODE: " + e.toString());   

        e.printStackTrace();

    }



    inputSearch.addTextChangedListener(new TextWatcher() {

        @Override
        public void onTextChanged(CharSequence cs, int arg1, int arg2, int arg3) {
            // When user changed the Text
            Main.this.adapter.getFilter().filter(cs);   
        }

        @Override
        public void beforeTextChanged(CharSequence arg0, int arg1, int arg2,
                int arg3) {
            // TODO Auto-generated method stub

        }

        @Override
        public void afterTextChanged(Editable arg0) {
            // TODO Auto-generated method stub                          
        }
    });




}


class recipeAdapter extends CursorAdapter {

    recipeAdapter(Cursor c){
        super(Main.this, c);
    }

    @Override
    public  void bindView (View row, Context ctxt, Cursor c)
    {
        recipeHolder holder = (recipeHolder)row.getTag();
        holder.populateFrom(c, dbrecipelistHelper);
    }
    @Override
    public  View newView(Context ctxt, Cursor c, ViewGroup parent)
    {
        LayoutInflater inflater = getLayoutInflater();
        View row=inflater.inflate(R.layout.list, parent, false);
        recipeHolder holder = new recipeHolder(row);
        row.setTag(holder);
        return(row);
    }


}

static class recipeHolder {
    private TextView name=null;

    recipeHolder(View row){
        name=(TextView)row.findViewById(R.id.recipeText);
    }

    void populateFrom(Cursor c, DBlistHelper r){
        name.setText(r.getName(c));
    }

}
}

keshav kowshik · Accepted Answer · 2015-03-27T05:49:10.377

2

To Search for the values define edit text and implement text watcher in database enter a query as shown below:

 editText.addTextChangedListener(new TextWatcher(){ 

Cursor cusror;

cursor=db.rawQuery("SELECT * FROM "+ DB_NAME + " WHERE " 
                + DB_NAME.id + " = " + DB_NAME.Id + " AND " + DB_NAME.Title +
                 " LIKE  '"+search.getText()+"%'");

check the values if you are getting the correct values return the cursor. Hope it helps.

edited Mar 27 '15 at 05:49

answered Mar 27 '15 at 04:57

keshav kowshik

2,354
4
22
45

thanks but do i have to get all the data? I already display the listview I need and I want is to filter it using EditText. – Dhon Tañada Mar 27 '15 at 05:28
I did not get you, filter in the sense? – keshav kowshik Mar 27 '15 at 05:33
Do you want only selected values from database? – keshav kowshik Mar 27 '15 at 05:42
I want to filter the listview that match any word or keyword I insert in an EditText. Like a search bar in the top of my ListView layout – Dhon Tañada Mar 27 '15 at 05:45
Check the edited answer. Try to modify that and use it in your code. – keshav kowshik Mar 27 '15 at 05:49
yes like that. I want only selected values to be appear on my ListView – Dhon Tañada Mar 27 '15 at 05:53
yes in db class. Refer tis SO link. http://stackoverflow.com/questions/29292569/android-how-can-i-search-data-in-sqlite-database-and-display-it-in-listview/29293574#29293574 – keshav kowshik Mar 27 '15 at 06:48
Im a little bit confused using the codes. do I have to change the query in my DB class? and my textwatcher is in my main class. – Dhon Tañada Mar 27 '15 at 07:26
yes, sorry for the wrong link check this link http://stackoverflow.com/questions/17924940/search-a-value-from-sqlite-database-and-retreive-in-listview – keshav kowshik Mar 27 '15 at 07:37
If this answer helped you can you please vote and accept the answer. – keshav kowshik Mar 27 '15 at 09:15

Aniruddha Bera · Answer 2 · 2017-07-21T07:44:27.187

Since cursors returns the number of rows from the database being searched, you can use .getCount() on the cursor object to check if you found the data or not.

This would be my approach.

public Cursor fetchData(String arg1){


        SQLiteDatabase database = getReadableDatabase();

        Cursor cursor = database.query(TABLE_NAME, new String[]{COL1,COL2},COL1 + "=?",new String[]{arg1},null,null,null );
        return cursor;
    }

and in the MainActivity class

MyDatabase myDatabase = new MyDatabase(getApplicationContext());
Cursor cursor = myDatabase.fetchData("FRANCE");

if(cursor.getCount()>0)
            Toast.makeText(this, "Not Found", Toast.LENGTH_SHORT).show();

Android: How can I search data in sqlite database and display it in listview

2 Answers2