Android SQLite → Android SQLite Database – ListView – Filter/Search and CRUD – Camposha

Android SQLite

Android SQLite

Android SQLite

Android SQLite Database – ListView – Filter/Search and CRUD

Android SQLite Database – ListView – Filter/Search and CRUD Tutorial.

How to Perform Search and Filter againts SQLite Database. The data is rendered on a ListView. But first we have to insert data to SQLite so we perform basic CRUD to populate the sqlite database. We use SearchView to searc/filter.

 

In today’s tutorial,we cover how to filter/search data from SQLite database.As we always do here at ProgrammingWizards,we start from scratch.So we first create our table programmatically,Insert data,select that data from database while applying a search.We are performing a server side filter if you like,at the database level. In short this is what we do :

  • INSERT data to our SQlite database table.
  • SELECT while performing a dynamic filter,using a searchterm specified in our SearchView.
  • Handle ItemClicks of filtered items.

SQLite ListView Search data Via SearchView

Download

SQlite ListView Search Project Structure

We want to search/filter SQLite database using a SearchView.Our component is ListView. First we will insert data to SQLite,then retrieve then filter in realtime as user searches via a SearchView. Lets jump straight in.

Constants.java

The first class is our Constants class which will hold all our SQlite database 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;

}

DBHelper.java

Our DBHelper class below is responsible for upgrading our SQlite database table :

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 Insert data

DBAdapter.java

Then the DBAdapter class here is responsible for all our CRUD operations including searching.Now for searching we search/filter our data at the server side,at the database level via our SQL statements.

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

CustomAdapter.java

Our customAdapter class below shall be responsible for binding our filtered data to our 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;
    }
}

SQlite ListView Data

MainActivity.java

Then finally we have our MainActivity class :

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 project.

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 *

Price

Free

Rating

Not enough ratings to display
X