Home Android SQLite : RecyclerView – SEARCH/FILTER and CRUD

Android SQLite : RecyclerView – SEARCH/FILTER and CRUD

Android SQLite : RecyclerView – SEARCH/FILTER and CRUD




We are covering how to search/filter data against our SQLite database.Take note we are filtering at the SQlite side,not in java but in SQL,at the server side if you like.Our widget today is RecyclerView.We of course start from scratch,inserting,then selecting while applying a search.
This is what we do short:

  • INSERT data to our SQlite database.
  • SELECT that particular data,while applying a dynamic filter.
  • Our search term is dynamically entered by the user via a searchview.
RecyclerView SQLite Search/Filter Project Structure

RecyclerView SQLite Search/Filter Project Structure

First here’s our Constants class that’s going to hold all our SQlite database constants :

 

package com.tutorials.hp.sqliteserversidefilter.mDataBase;

/**
 * Created by Oclemmy on 5/2/2016 for ProgrammingWizards Channel and http://www.Camposha.com.
 */
public class Constants {
    //columns
    static final String ROW_ID="id";
    static final String NAME="name";

    //DB
    static final String DB_NAME="gg_DB";
    static final String TB_NAME="gg_TB";
    static final int DB_VERSION=1;

    //CREATE STMT
    static final String CREATE_TB="CREATE TABLE gg_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "name TEXT NOT NULL);";

    //DROP  TB STMT
     static final String DROP_TB="DRP TABLE IF EXISTS "+TB_NAME;





}

 

Then we shall have our DBHelper class that shall be responsible for upgrading and creating our database table.

 

package com.tutorials.hp.sqliteserversidefilter.mDataBase;

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

/**
 * Created by Oclemmy on 5/2/2016 for ProgrammingWizards Channel and http://www.Camposha.com.
 */
public class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try
        {
            db.execSQL(Constants.CREATE_TB);
        }catch (SQLException e)
        {
            e.printStackTrace();
        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(Constants.DROP_TB);
        onCreate(db);
    }
}

 

 

RecyclerView SQLite Insert Data

RecyclerView SQLite Insert Data

 

Our DBAdapter class below is responsible for performing our CRUD operations including searching our database.We said we do the search at the server side or database level instead of via Java code :

 

package com.tutorials.hp.sqliteserversidefilter.mDataBase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

/**
 * Created by Oclemmy on 5/2/2016 for ProgrammingWizards Channel and http://www.Camposha.com.
 */
public class DBAdapter {
    Context c;
    SQLiteDatabase db;
    DBHelper helper;

    public DBAdapter(Context c) {
        this.c = c;
        helper=new DBHelper(c);
    }

    //OPEN DB
    public void openDB()
    {
        try
        {
            db=helper.getWritableDatabase();
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    //CLOSE
    public void closeDB()
    {
        try
        {
            helper.close();
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    //SAVE OR INSERT
    public boolean add(String name)
    {
        try
        {
            ContentValues cv=new ContentValues();
            cv.put(Constants.NAME, name);

            db.insert(Constants.TB_NAME, Constants.ROW_ID, cv);

            return true;

        }catch (SQLException e)
        {
            e.printStackTrace();
        }

        return false;
    }

    //RETRIEVE OR FILTERING
    public Cursor retrieve(String searchTerm)
    {
        String[] columns={Constants.ROW_ID,Constants.NAME};
        Cursor c=null;

        if(searchTerm != null && searchTerm.length()>0)
        {
            String sql="SELECT * FROM "+Constants.TB_NAME+" WHERE "+Constants.NAME+" LIKE '%"+searchTerm+"%'";
            c=db.rawQuery(sql,null);
            return c;
        }

        c=db.query(Constants.TB_NAME,columns,null,null,null,null,null);
        return c;

    }

}














 

RecyclerView SQLite Database Data

RecyclerView SQLite Database Data

 

Below now is our RecyclerView adapter.It shall be bind our filtered data to the RecyclerView.

package com.tutorials.hp.sqliteserversidefilter.mRecycler;

import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

import com.tutorials.hp.sqliteserversidefilter.R;
import com.tutorials.hp.sqliteserversidefilter.mDataObject.Planet;

import java.util.ArrayList;

/**
 * Created by Oclemmy on 5/2/2016 for ProgrammingWizards Channel and http://www.Camposha.com.
 */
public class MyAdapter extends RecyclerView.Adapter<MyHolder> {

    Context c;
    ArrayList<Planet> planets;

    public MyAdapter(Context c, ArrayList<Planet> planets) {
        this.c = c;
        this.planets = planets;
    }

    @Override
    public MyHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        View v= LayoutInflater.from(parent.getContext()).inflate(R.layout.model,parent,false);
        MyHolder holder=new MyHolder(v);
        return holder;
    }

    @Override
    public void onBindViewHolder(MyHolder holder, int position) {
        holder.nameTxt.setText(planets.get(position).getName());

    }

    @Override
    public int getItemCount() {
        return planets.size();
    }
}

 

Then our MainActivity is below :

 

package com.tutorials.hp.sqliteserversidefilter;

import android.app.Dialog;
import android.database.Cursor;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.support.v7.widget.SearchView;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.tutorials.hp.sqliteserversidefilter.mDataBase.DBAdapter;
import com.tutorials.hp.sqliteserversidefilter.mDataObject.Planet;
import com.tutorials.hp.sqliteserversidefilter.mRecycler.MyAdapter;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {

    RecyclerView rv;
    SearchView sv;
    EditText nameTxt;
    Button saveBtn,retrieveBtn;
    ArrayList<Planet> planets=new ArrayList<>();
    MyAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);
        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);

        rv= (RecyclerView) findViewById(R.id.rv);
        rv.setLayoutManager(new LinearLayoutManager(this));

        sv= (SearchView) findViewById(R.id.sv);
        adapter=new MyAdapter(this,planets);

        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                displayDialog();
            }
        });

        sv.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String query) {
                return false;
            }

            @Override
            public boolean onQueryTextChange(String query) {
                getPlanets(query);
                return false;
            }
        });

    }

    private void displayDialog()
    {
        Dialog d=new Dialog(this);
        d.setTitle("SQLite Database");
        d.setContentView(R.layout.dialog_layout);

        nameTxt= (EditText) d.findViewById(R.id.nameEditTxt);
        saveBtn= (Button) d.findViewById(R.id.saveBtn);
        retrieveBtn= (Button) d.findViewById(R.id.retrieveBtn);

        saveBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                save(nameTxt.getText().toString());
            }
        });
        retrieveBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                getPlanets(null);
            }
        });

        //SHOW DIALOG
        d.show();

    }

    private void save(String name)
    {
        DBAdapter db=new DBAdapter(this);
        db.openDB();
        if(db.add(name))
        {
            nameTxt.setText("");
        }else {
            Toast.makeText(this,"Unable to Save",Toast.LENGTH_SHORT).show();
        }
        db.closeDB();

        getPlanets(null);

    }

    private void getPlanets(String searchTerm)
    {
        planets.clear();

        DBAdapter db=new DBAdapter(this);
        db.openDB();
        Planet p=null;
        Cursor c=db.retrieve(searchTerm);

        while (c.moveToNext())
        {
            int id=c.getInt(0);
            String name=c.getString(1);

            p=new Planet();
            p.setId(id);
            p.setName(name);

            planets.add(p);
        }



        db.closeDB();

        rv.setAdapter(adapter);
    }


}








 

 

RecyclerView SQLite Search Via SearchView

RecyclerView SQLite Search Via SearchView

 

 

 

If you prefer more explanations or want to see the demo then the video tutorial is right below:

 

Leave a Comment