Android SQLite → Android SQLite CRUD – ListView – Serverside Search/Filter – Camposha

Android SQLite

Android SQLite

Android SQLite

Android SQLite CRUD – ListView – Serverside Search/Filter

Android SQLite CRUD – ListView – Serverside Search/Filter

Many a times you need to filter or search data. That data may be contained in an SQLite database.

 

There are two ways to do that:

  • Clientside Search/Filter – Retrieving everything from database,populating arraylist, then searching the arraylist.
  • Serverside Search/Filter – Applying search/filter at the database level and returning only data matching the search term.

The second option is the faster one since we take advantage of the heavily optimized SQL databases.

We use the second approach in this tutorial.

But first we need to insert data into database and populate the ListView. So we do all those.

We use the SearchView as our search input widget.

Let’s go.

1. Create Basic Activity Project

First create an empty project in android studio. Go here for more details.

2. Add gradle dependencies

We add some support libraries inside the 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. Here’s the autogenerated code for activity_main.xml file:

<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout 
    
    
    android_layout_width="match_parent"
    android_layout_height="match_parent"
    android_fitsSystemWindows="true"
    tools_context="com.tutorials.hp.sqlitefilterlistview.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.
We will have a searchview for searching right on top our Listview which is our AdapterView.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 
    
    
    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.sqlitefilterlistview.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>

    <ListView
        android_id="@+id/lv"
        android_layout_below="@+id/sv"
        android_layout_width="match_parent"
        android_layout_height="wrap_content"
        ></ListView>
</RelativeLayout>
(c) dialog_layout.xml

This layout will be used to construct our input dialog.
That dialog is actually our data entry form.

SQLite ListView Insert Data Dialog

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

    <android.support.v7.widget.CardView 
        android_orientation="horizontal" android_layout_width="500dp"
        
        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 layout will be used to construct our ListView View items.

It’s our custom row template.

<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView 
    android_orientation="horizontal" android_layout_width="match_parent"
    
    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>

Java Classes

Let’s now jump to java.

Our Data Object
(a) Planet.java

This is our POJO class. It defines for us the properties for a single planet.

We will be saving planet objects into our sqlite database. As you can see a single planet will have a name and an id as properties.

package com.tutorials.hp.sqlitefilterlistview.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

These are classes helping us work with SQLite database.

(a) Constants.java

This class will hold for us our sqlite database constants, as the name suggests.

These constants include sqlite database name, database version, sqlite tabe name, table column names, database table creation and deletion statements.

All of these are defined as simple string constants.

package com.tutorials.hp.sqlitefilterlistview.mDataBase;

public class Constants {
    //COLUMNS
    static final String ROW_ID="id";
    static final String NAME="name";

    //DB
    static final String DB_NAME="ii_DB";
    static final String TB_NAME="ii_TB";
    static final int DB_VERSION=1;

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

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

}
(a) DBHelper.java

This is our SQLiteHelper class.
This class helps in database table creation and upgade.

package com.tutorials.hp.sqlitefilterlistview.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);
    }
}

SQLite ListView Render Data CardViews

(c) DBAdapter.java

This is our database adapter class.

It is in this class where we will perform our CRUD and data retrieval including searching SQlite data.

First a database connection is opened, data is inserted to sqlite, data can be retrieved or searched by passing in search term.

Finally database connection is closed.

We start by defining three instance fields:

  1. A Context object.
  2. A SQLite database object.
  3. and a DBHelper object.

Then our constrructor will receive a context object via the constructor. That Context will be required by our DBHelper class.

Opening our database is easy, we invoke the getWritableDatabase(), receiving a SQLiteDatabase object which we hold in our db variable.

           db=helper.getWritableDatabase();

As for Closing we simply use the close() method:

            helper.close();

To insert first we need a ContentValues object.

            ContentValues cv=new ContentValues();

Then simply use it’s put() method to hold our data. There we pass a key and value. The key is the database column name while the value is the data to be inserted:

            cv.put(Constants.NAME, name);

Then to insert we use the `insert()` method of the `SQLiteDatabase` class
```java
            db.insert(Constants.TB_NAME, Constants.ROW_ID, cv);
package com.tutorials.hp.sqlitefilterlistview.mDataBase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.provider.SyncStateContract;
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();
        }
    }

    //INSERT DATA
    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 DATA AND FILTER
    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 LisView Class
(a) CustomAdapter.java

Our ListView Adapter class. This class is required as we are working with a custom listview.

First we have to make sure we have defined a model layout.
That model.xml layout will be inflated here and used as the listView’s viewitem.

Data from SQLite is then bound to the ListView.

package com.tutorials.hp.sqlitefilterlistview.mListView;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
import android.widget.Toast;

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

import java.util.ArrayList;

public class CustomAdapter extends BaseAdapter {

    Context c;
    ArrayList<Planet> planets;
    LayoutInflater inflater;

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

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

    @Override
    public Object getItem(int position) {
        return planets.get(position);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        if(inflater==null)
        {
            inflater= (LayoutInflater) c.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        }
        if(convertView==null)
        {
            convertView=inflater.inflate(R.layout.model,parent,false);
        }

        TextView nameTxt= (TextView) convertView.findViewById(R.id.nameTxt);
        nameTxt.setText(planets.get(position).getName());

        final int pos=position;

        convertView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Toast.makeText(c,planets.get(pos).getName(),Toast.LENGTH_SHORT).show();
            }
        });

        return convertView;
    }
}
Our Activity Class
(a) MainActivity.java

This is our main and only activity. Activities represent user interface in android and so does this.

SQLite ListView Search Data SearchView

We define a searchview for searching data and listview for showing data.

Also a dialog that will be used as our data entry form.

package com.tutorials.hp.sqlitefilterlistview;
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.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;
import com.tutorials.hp.sqlitefilterlistview.mDataBase.DBAdapter;
import com.tutorials.hp.sqlitefilterlistview.mDataObject.Planet;
import com.tutorials.hp.sqlitefilterlistview.mListView.CustomAdapter;
import java.util.ArrayList;
public class MainActivity extends AppCompatActivity {
ListView lv;
SearchView sv;
EditText nameEditText;
Button saveBtn,retrieveBtn;
CustomAdapter adapter;
ArrayList<Planet> planets=new ArrayList<>();
@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);
lv= (ListView) findViewById(R.id.lv);
sv= (SearchView) findViewById(R.id.sv);
adapter=new CustomAdapter(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 newText) {
getPlanets(newText);
return false;
}
});
}
private void displayDialog()
{
Dialog d=new Dialog(this);
d.setTitle("SQLite Database");
d.setContentView(R.layout.dialog_layout);
nameEditText= (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(nameEditText.getText().toString());
}
});
retrieveBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getPlanets(null);
}
});
d.show();
}
private void save(String name)
{
DBAdapter db=new DBAdapter(this);
db.openDB();
if(db.add(name))
{
nameEditText.setText("");
}else {
Toast.makeText(this,"Unable To Save",Toast.LENGTH_SHORT).show();
}
db.closeDB();
this.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();
lv.setAdapter(adapter);
}
}

Download

Here are the resources related to this tutorial.

No. Location Link
1. GitHub Direct Download)
2. GitHub Browse
3. YouTube Video Tutorial
4. YouTube Our YouTube Channel

Leave a Reply

Your email address will not be published. Required fields are marked *

Rating

Not enough ratings to display
X