This tutorial helps us explore Android SQLite database searching or filtering data.

First we have to save data and retrieve data from that database.

We render the data in our RecyclerView.

We will be searching/filtering data at the sqlite server level as opposed to fetching all data and filtering via Java.

Let's go.

1. Create Basic Activity Project

  1. First create an empty project in android studio. Go to File --> New Project.

  2. Type the application name and choose the company name. New Project Dialog

  3. Choose minimum SDK. Choose minimum SDK

  4. Choose Basic activity. Choose Empty Activity

  5. Click Finish. Finish

Basic activity will have a toolbar and floating action button already added in the layout

Normally two layouts get generated with this option:

No. Name Type Description
1. activity_main.xml XML Layout Will get inflated into MainActivity Layout.Typically contains appbarlayout with toolbar.Also has a floatingactionbutton.
2. content_main.xml XML Layout Will be included into activity_main.xml.You add your views and widgets here.
3. MainActivity.java Class Main Activity.

In this example I used a basic activity.

The activity will automatically be registered in the android_manifest.xml. Android Activities are components and normally need to be registered as an application component.

If you've created yours manually then register it inside the <application>...<application> as following, replacing the MainActivity with your activity name:


        <activity android:name=".MainActivity">

            <intent-filter>

                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />

            </intent-filter>

        </activity>

You can see that one action and category are specified as intent filters. The category makes our MainActivity as launcher activity. Launcher activities get executed first when th android app is run.

Advantage of Creating Basic Activity project

You can optionally choose empty activity over basic activity for this project.

However basic activity has the following advantages:

No. Advantage
1. Provides us a readymade toolbar which gives us actionbar features yet easily customizable
2. Provides us with appbar layout which implements material design appbar concepts.
3. Provides a FloatinActionButton which we can readily use to initiate quick actions especially in examples like these.
4. Decouples our custom content views and widgets from the templating features like toolbar.

Generated Project Structure

AndroidStudio will generate for you a project with default configurations via a set of files and directories.

Here are the most important of them:

No. File Major Responsibility
1. build/ A directory containing resources that have been compiled from the building of application and the classes generated by android tools. Such a tool is the R.java file. R.java file normally holds the references to application resources.
2. libs/ To hold libraries we use in our project.
3. src/main/ To hold the source code of our application.This is the main folder you work with.
4. src/main/java/ Contains our java classes organized as packages.
5. src/main/res/ Contains our project resources folders as follows.
6. src/main/res/drawable/ Contains our drawable resources.
7. src/main/res/layout/ Contains our layout resources.
8. src/main/res/menu/ Contains our menu resources XML code.
9. src/main/res/values/ Contains our values resources XML code.These define sets of name-value pairs and can be strings, styles and colors.
10. AndroidManifest.xml This file gets autogenerated when we create an android project.It will define basic information needed by the android system like application name,package name,permissions,activities,intents etc.
11. build.gradle Gradle Script used to build the android app.

2. Add Dependencies

Lets add support library dependencies in our app level build.gradle:

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    testCompile 'junit:junit:4.12'
    compile 'com.android.support:appcompat-v7:23.3.0'
    compile 'com.android.support:design:23.3.0'
    compile 'com.android.support:cardview-v7:23.3.0'

}

3. Create User Interface

User interfaces are typically created in android using XML layouts as opposed by direct java coding.

This is an example fo declarative programming.

######### Advantages of Using XML over Java

No. Advantage
1. Declarative creation of widgets and views allows us to use a declarative language XML which makes is easier.
2. It's easily maintanable as the user interface is decoupled from your Java logic.
3. It's easier to share or download code and safely test them before runtime.
4. You can use XML generated tools to generate XML

Here are our layouts for this project:

(a). activity_main.xml
  • This layout gets inflated to MainActivity user interface.
  • It includes the content_main.xml.

Here are some of the widgets, views and viewgroups that get employed"

No. View/ViewGroup Package Role
1. CordinatorLayout android.support.design.widget Super-powered framelayout that provides our application's top level decoration and is also specifies interactions and behavioros of all it's children.
2. AppBarLayout android.support.design.widget A LinearLayout child that arranges its children vertically and provides material design app bar concepts like scrolling gestures.
3. ToolBar <android.support.v7.widget A ViewGroup that can provide actionbar features yet still be used within application layouts.
4. FloatingActionButton android.support.design.widget An circular imageview floating above the UI that we can use as buttons.
<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:fitsSystemWindows="true"
    tools:context="com.tutorials.hp.sqliteserversidefilter.MainActivity">

    <android.support.design.widget.AppBarLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:theme="@style/AppTheme.AppBarOverlay">

        <android.support.v7.widget.Toolbar
            android:id="@+id/toolbar"
            android:layout_width="match_parent"
            android:layout_height="?attr/actionBarSize"
            android:background="?attr/colorPrimary"
            app:popupTheme="@style/AppTheme.PopupOverlay" />

    </android.support.design.widget.AppBarLayout>

    <include layout="@layout/content_main" />

    <android.support.design.widget.FloatingActionButton
        android:id="@+id/fab"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="bottom|end"
        android:layout_margin="@dimen/fab_margin"
        android:src="@android:drawable/ic_dialog_email" />

</android.support.design.widget.CoordinatorLayout>
(b). content_main.xml

This layout gets included in your activity_main.xml. You define your UI widgets right here.

In this case we will have a SearchView right on top of a RecyclerView.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    app:layout_behavior="@string/appbar_scrolling_view_behavior"
    tools:context="com.tutorials.hp.sqliteserversidefilter.MainActivity"
    tools:showIn="@layout/activity_main">

    <android.support.v7.widget.SearchView
        android:id="@+id/sv"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:queryHint="Search.."
        ></android.support.v7.widget.SearchView>

    <android.support.v7.widget.RecyclerView
        android:id="@+id/rv"
        android:layout_below="@+id/sv"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        ></android.support.v7.widget.RecyclerView>
</RelativeLayout>
(c). dialog_layout.xml

This is the input dialog layout.

We will need to insert to and retrieve data from SQlite database so we will use this dialog as our input form.

<?xml version="1.0" encoding="utf-8"?>

    <android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal" android:layout_width="500dp"
        xmlns:card_view="http://schemas.android.com/apk/res-auto"
        android:layout_margin="1dp"
        card_view:cardCornerRadius="10dp"
        card_view:cardElevation="5dp"
        android:layout_height="match_parent">

    <LinearLayout
            android:layout_width="match_parent"
            android:orientation="vertical"
            android:layout_height="match_parent">

        <android.support.design.widget.TextInputLayout
            android:id="@+id/nameLayout"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <EditText
                android:id="@+id/nameEditTxt"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:singleLine="true"
                android:hint= "Name" />
        </android.support.design.widget.TextInputLayout>

        <Button android:id="@+id/saveBtn"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:text="Save"
            android:clickable="true"
            android:background="@color/colorAccent"
            android:layout_marginTop="40dp"
            android:textColor="@android:color/white"/>
        <Button android:id="@+id/retrieveBtn"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:text="Retrieve"
            android:clickable="true"
            android:background="@color/colorAccent"
            android:layout_marginTop="40dp"
            android:textColor="@android:color/white"/>
</LinearLayout>
</android.support.v7.widget.CardView>
(d). model.xml

This will define the template for each RecyclerView Viewitem. This layout will get inflated in our recycleview adapter class.

At the root we have a CardView.

<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal" android:layout_width="match_parent"
    xmlns:card_view="http://schemas.android.com/apk/res-auto"
    android:layout_margin="5dp"
    card_view:cardCornerRadius="10dp"
    card_view:cardElevation="5dp"
    android:layout_height="wrap_content">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:text="Name"
            android:id="@+id/nameTxt"
            android:padding="10dp"
            android:layout_alignParentTop="true"
            />
    </RelativeLayout>
</android.support.v7.widget.CardView>

4. Java Classes

Here are our Java classes.

Our POJO

######## (a) Planet.java Our data objet.

Represents a single planet with several properties.

We will be saving planets in our SQLite database.

package com.tutorials.hp.sqliteserversidefilter.mDataObject;

public class Planet {

    String name;
    int id;

    public Planet() {
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
}
Our SQLite Classes

######## (a) Constants.java This class contains our SQLite database constants. These include: databse table name, database name, database version, column names, SQLite table creation and deletion statements.

package com.tutorials.hp.sqliteserversidefilter.mDataBase;

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;

}

######## (b) DBHelper.java This is our SQLiteHelper class.

Allows us to create table and upgrade it.

package com.tutorials.hp.sqliteserversidefilter.mDataBase;

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

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);
    }
}

######## (c) DBAdapter.java Our database adapter class.

Basically responsible for performing CRUD activities like inserting and selecting to and from SQLite database.

We also open and close the SQLite database.

We also search here as part our data retrieval from sqlite database using a searchTerm passed to us.

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;

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;

    }

}
Our RecyclerView Classes

######## (a) MyHolder.java Our RecyclerView.ViewHolder class.

Holds a simple TextView.

package com.tutorials.hp.sqliteserversidefilter.mRecycler;

import android.support.v7.widget.RecyclerView;
import android.view.View;
import android.widget.TextView;

import com.tutorials.hp.sqliteserversidefilter.R;

public class MyHolder extends RecyclerView.ViewHolder {

    TextView nameTxt;

    public MyHolder(View itemView) {
        super(itemView);

        this.nameTxt= (TextView) itemView.findViewById(R.id.nameTxt);
    }
}

######## (b) MyAdapter.java Our RecyclerView.Adapter class.

Will inflate model.xml into a RecyclerView view item.

Will then bind data to those viewitems.

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;

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();
    }
}
Our Activity Class

######## (a) MainActivity.java Our launcher and only activity.

Provides us with the UI for our application.

Shows a RecyclerView and searchview. Listens to searchview change events to filter.

Also defines us an input dialog for data entry.

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);
    }
}

How do You Feel after reading this?

According to scientists, we humans have 8 primary innate emotions: joy, acceptance, fear, surprise, sadness, disgust, anger, and anticipation. Feel free to tell us how you feel about this article using these emotes or via the comment section. This feedback helps us gauge our progress.

Help me Grow.

I set myself some growth ambitions I desire to achieve by this year's end regarding this website and my youtube channel. Am halfway. Help me reach them by:




Recommendations


What do You Think

Dear readers drop us your comments below. We are building a community of students and learners. Start by dropping us your suggestions below. What tutorials do you want us to do for example? Where can we improve? What are some awesome resources out there? Do you have any code you want to share with us?
By the way that example or snippet you have lying in your computer can really help beginner programmers. We can share it here with other students.

Previous Post Next Post