Android SQLite Usage with ListView

The concept here involves the following:

  1. SQLite Database.
  2. ListView

Please share more examples below.

Share

Related Concepts

4 Examples

  1. Android Swipe Tabs SQLite – Fragments With ListView

    This is an android swipe tabs with ListView containing SQLite data.

    In some of our tutorials we like combining several fundamental concepts so as to give a framework that can be used to build a real world app.

    Like in this we want to utilize the following:

    1. ViewPager – to achieve fragment swipeability.
    2. TabLayout – To achieve tabbed navigation.
    3. Fragments – To render our pages.
    4. SQLite – To store our data in the local device.

    These are important since it can help you build a full application.

    In the small app that we are going to build, we will have three fragments. All these fragments will be hosted by the same activity.

    Each fragment will hold a ListView with each ListView containing some data.

    These fragments will be swipeable so users can use swiping which is a popular and modern navigation mechanism.

    Android SQLite ListView Insert

    Android SQLite Swipeable Tabs ListView

    Also we will be able to use tabs to navigate our application.

    The data inside our ListView will be saved in SQLite database. SQLite is a local file-based database written in C programming language and normally comes packaged already with android framework.

    Of course before retrieving data from the SQLite database we will first need to insert that data in the first place. So we will be able to save data to SQLite via SQL INSERT statement, retrieve that data via SQL SELECT statement and bind the data in our ListViews.

    Each ListView in each Fragment will basically comprise of a unique category of data. So we are categorizing our data in categories. Each category is stored in its fragment.

    Here are some of the ideas that can be applied to this type of app:

    1. Creating a Movie Listing app, with each movie listed according to it’s own genre.
    2. Creating an Ecommerce app with each product listed in its own tab according to product category.
    3. Creating a Medical app with each disease showing prevention/treatment mechanism in a List.
    4. Creating a News RSS app with each category listed in its own fragment with a List of News headlines.

    Project Structure

    It’s important to view th project structure to see how application is organized and which classes and layouts we create and use.

    Below is one for this project.

    Android Project Structure

    1. Create Android Project

    Fire up your android studio and create an android project.

    Android Studio is the defacto android development IDE supported by both Google and Jetbrains company.

    2. Modify Build.gradle

    Android Studio as you may know uses the gradle build system to build android projects.

    So it generates a build.gradle in the app folder and we will modify the dependencies required by our project.

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

    You can use the later versions of those dependencies.

    3. Our Java Classes

    We’ll now come and explore java classes. Android is just a mobile development framework and operating system and the real programming language used to write android apps is java, a very powerful programming language.

    Here are our classes:

    No. Class Description
    1. Spacecrafts.java Is our model class. Defines us one spacecraft’s properties.
    2. InterStellar.java Our Inter-stellar spacecrafts fragment. Spacecrafts here will travel from one star to another within our galaxy.
    3. InterPlanetary.java Our inter-planetary spacecrafts fragment. Spacececrafts here will travel from one planet to the other within our solar system.
    4. InterGalactic.java Our inter-galactic spacecrafts fragment. The spacecrafts listed here will travel from one galaxy to another within the universe.
    5. MyPagerAdapter.java Our FragmentPagerAdapter class. Will hold our fragments to be used for paging fragments by the ViewPager.
    6. Constants.java Our sqlite database constants class. Will hold our database constants.
    7. DBHelper.java Our sqlite database helper class. Will create and upgrade our database tables.
    8. DBAdapter.java Our SQLite database adapter class. We will perform our SQLite CRUD right here.
    9. MainActivity.java Our main and launcher activity.Will be responsible for hosting our fragments as well as our input dialog.

    Let’s look at these classes one by one in great detail.

    (a). Spacecraft.java

    This class will represent for us a single spacecraft.

    We need to create a class that will define us the properties of each fragment. In this case our spacecraft will have only two properties:

    1. Name – Name of our spacecraft.
    2. Category – Category of our spacecraft.First create a java file in your android studio.

    Then add the a package. You may use different package identifiers than me:

    package com.tutorials.hp.tabbedsqlitelistview.mModel;

    We will then create a public class. Public is an accessibility modifer used to denote that a class will be visible to other classes in other packages.

    public class Spacecraft {..}

    Inside the Spacecraft class we will add two instance fields to represent our spacecraft’s properties:

        private String name,category;

    We then create the getter and setter methods for our two instance fields. Remember the instance fields are private so we need to create public getter and setter methods to expose them:

        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getCategory() {
            return category;
        }
    
        public void setCategory(String category) {
            this.category = category;
        }

    We will also override the toString() method, returning the spacecraft name:

    @Override
        public String toString() {
            return name;
        }
    }

    Here’s the full source code for our this class:

    package com.tutorials.hp.tabbedsqlitelistview.mModel;
    
    public class Spacecraft {
        private String name,category;
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getCategory() {
            return category;
        }
    
        public void setCategory(String category) {
            this.category = category;
        }
    
        @Override
        public String toString() {
            return name;
        }
    }

    (b). InterPlanetary.java

    This is our fragment class. We’ll have two other fragment classes.

    Fragments are subactivities introduced in Android 3.0 to allow for easy composition of modular UI units.

    This class will show our inter-planetary spacecrafts in a ListView. This means that we need to define that ListView in this fragment and bind data to it from SQLite right here.

    Fragments are subactivities hence deal with User Interface. They therefore have the capability to be inflated from XML Layouts.

    Let’s go.

    First we specify our package:

    package com.tutorials.hp.tabbedsqlitelistview.mFragments;

    We then specify our imports:

    import android.os.Bundle;
    import android.support.annotation.Nullable;
    import android.support.v4.app.Fragment;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.GridView;
    import android.widget.ListView;
    
    import com.tutorials.hp.tabbedsqlitelistview.R;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;

    Our next step is to actually create the class:

    public class InterPlanetary..{}

    To turn into a Fragment we have to derive from Fragment class.

    Now there are two variations of fragments you can use:

    1. android.app.Fragment – Framework Fragment
    2. android.support.v4.app.Fragment – Supports Library Fragment.

    We’ll use the later since it allows us support devices prior to Android 3.0 when the fragment was introduced. This is unlike the first Fragment.

    public class InterPlanetary extends Fragment {..}

    So with that InterPlanetary class is already a Fragment. An empty one.

    So we are going to give it some content.

    A Fragment is a class like any other class, so let’s define it’s instance fields:

        ListView lv;
        Button refreshBtn;
        ArrayAdapter<Spacecraft> adapter;

    We define a ListView object, Button object and ArrayAdapter as instance fields.

    Then we come and create a static factory method for generating us this Fragment’s instance:

        public static InterPlanetary newInstance() {
            return new InterPlanetary();
        }

    We will now create a private method for loading data from the SQLite database,passing it into our ArrayAdapter instance thens setting the adapter to our ListView:

        private void loadData()
        {
            DBAdapter db=new DBAdapter(getActivity());
            adapter=new ArrayAdapter<Spacecraft>(getActivity(),android.R.layout.simple_list_item_1,db.retrieveSpacecrafts(this.toString()));
            lv.setAdapter(adapter);
        }

    Then a method to initialize our views:

    private void initializeViews(View rootView)
        {
            lv= (ListView) rootView.findViewById(R.id.interplanetary_LV);
            refreshBtn= (Button) rootView.findViewById(R.id.interplanetaryRefresh);
    
            refreshBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    loadData();
                }
            });
        }

    First you can see we reference the ListView and refresh button. Then set the button onclick’s method and invoking the loadData() method.

    We’ll also override the Fragment’s toString() method:

    @Override
        public String toString() {
            return "InterPlanetary";
        }

    We’ll also override the Fragment’s onCreateView() method:

    @Nullable
        @Override
        public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    
            View rootView = inflater.inflate(R.layout.interplanetary, null);
    
            initializeViews(rootView);
            loadData();
    
            return rootView;
        }

    You can see first we inflate the R.layout.interplanetary xml layout and returning it as our root view for the fragment. We return it after initializing our views via the initializeViews() method and loaded data from SQlite database via loadData() method.

    Here’s the complete source code for this class:

    package com.tutorials.hp.tabbedsqlitelistview.mFragments;
    
    import android.os.Bundle;
    import android.support.annotation.Nullable;
    import android.support.v4.app.Fragment;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.GridView;
    import android.widget.ListView;
    
    import com.tutorials.hp.tabbedsqlitelistview.R;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;
    
    public class InterPlanetary extends Fragment {
    
        ListView lv;
        Button refreshBtn;
        ArrayAdapter<Spacecraft> adapter;
    
        public static InterPlanetary newInstance() {
            return new InterPlanetary();
        }
    
        @Nullable
        @Override
        public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    
            View rootView = inflater.inflate(R.layout.interplanetary, null);
    
            initializeViews(rootView);
            loadData();
    
            return rootView;
        }
    
        /*
        INITIALIZE VIEWS
         */
        private void initializeViews(View rootView)
        {
            lv= (ListView) rootView.findViewById(R.id.interplanetary_LV);
            refreshBtn= (Button) rootView.findViewById(R.id.interplanetaryRefresh);
    
            refreshBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    loadData();
                }
            });
        }
    
        /*
        LOAD DATA
         */
        private void loadData()
        {
            DBAdapter db=new DBAdapter(getActivity());
            adapter=new ArrayAdapter<Spacecraft>(getActivity(),android.R.layout.simple_list_item_1,db.retrieveSpacecrafts(this.toString()));
            lv.setAdapter(adapter);
        }
    
        @Override
        public String toString() {
            return "InterPlanetary";
        }
    }

    (c). InterStellar.java

    This is our inter-setllar fragment.

    It will be inflated from R.layout.interstellar layout. It will contain a ListView with inter-stellar spacecrafts from our SQLite database.

    Let’s first specify the class’s package:

    package com.tutorials.hp.tabbedsqlitelistview.mFragments;

    Then add our imports:

    import android.os.Bundle;
    import android.support.annotation.Nullable;
    import android.support.v4.app.Fragment;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.GridView;
    import android.widget.ListView;
    
    import com.tutorials.hp.tabbedsqlitelistview.R;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;

    And create the class making it derive from android.support.v4.app.Fragment class.

    public class InterStellar extends Fragment {..}

    Then define our instance fields:

        ListView lv;
        Button refreshBtn;
        ArrayAdapter<Spacecraft> adapter;

    We will then define our factory method to return us an inter-stellar fragment instance:

        public static InterStellar newInstance() {
            return new InterStellar();
        }

    Then define a method to load us data from SQLite database via the DBAdapter class that we will define later and bind the data to ListView.

        private void loadData()
        {
            DBAdapter db=new DBAdapter(getActivity());
            adapter=new ArrayAdapter<Spacecraft>(getActivity(),android.R.layout.simple_list_item_1,db.retrieveSpacecrafts(this.toString()));
            lv.setAdapter(adapter);
    
        }

    Then initialize our ListView and Button and invoke the loadData() when the refresh button is clicked:

        private void initializeViews(View rootView)
        {
            lv= (ListView) rootView.findViewById(R.id.interstellar_LV);
            refreshBtn= (Button) rootView.findViewById(R.id.interstellarRefresh);
    
            refreshBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    loadData();
                }
            });
        }

    We will override the toString() method which is always available to all Objects.

        @Override
        public String toString() {
            return "InterStellar";
        }

    Lastly for this fragment we will ovveride the Fragment’s onCreateView() which gets invoked when the Fragment’s View is created:

    @Nullable
        @Override
        public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    
            View rootView = inflater.inflate(R.layout.interstellar, null);
    
            initializeViews(rootView);
            loadData();
    
            return rootView;
        }

    Here’s the complete source code:

    package com.tutorials.hp.tabbedsqlitelistview.mFragments;
    
    import android.os.Bundle;
    import android.support.annotation.Nullable;
    import android.support.v4.app.Fragment;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.GridView;
    import android.widget.ListView;
    
    import com.tutorials.hp.tabbedsqlitelistview.R;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;
    
    public class InterStellar extends Fragment {
    
        ListView lv;
        Button refreshBtn;
        ArrayAdapter<Spacecraft> adapter;
    
        public static InterStellar newInstance() {
            return new InterStellar();
        }
    
        @Nullable
        @Override
        public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    
            View rootView = inflater.inflate(R.layout.interstellar, null);
    
            initializeViews(rootView);
            loadData();
    
            return rootView;
        }
    
        /*
        INITIALIZE VIEWS
         */
        private void initializeViews(View rootView)
        {
            lv= (ListView) rootView.findViewById(R.id.interstellar_LV);
            refreshBtn= (Button) rootView.findViewById(R.id.interstellarRefresh);
    
            refreshBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    loadData();
                }
            });
        }
        /*
        LOAD DATA
         */
        private void loadData()
        {
            DBAdapter db=new DBAdapter(getActivity());
            adapter=new ArrayAdapter<Spacecraft>(getActivity(),android.R.layout.simple_list_item_1,db.retrieveSpacecrafts(this.toString()));
            lv.setAdapter(adapter);
    
        }
    
        @Override
        public String toString() {
            return "InterStellar";
        }
    
    }

    (d). InterGalactic.java

    Our inter-galactic fragment. This Fragment will contain our inter-glactic spacecrafts rendered in a ListView.

    Here’s our package and imports:

    package com.tutorials.hp.tabbedsqlitelistview.mFragments;
    
    import android.os.Bundle;
    import android.support.annotation.Nullable;
    import android.support.v4.app.Fragment;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.GridView;
    import android.widget.ListView;
    
    import com.tutorials.hp.tabbedsqlitelistview.R;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;

    We create our Fragment:

    public class InterGalactic extends Fragment {..}

    Inside it we define our instance fields:

        ListView lv;
        Button refreshBtn;
        ArrayAdapter<Spacecraft> adapter;

    Then our factory method for generating this Fragment’s instance:

        public static InterGalactic newInstance()
        {
            return new InterGalactic();
        }

    Our loadData() method to load data from SQLite database:

        private void loadData()
        {
            DBAdapter db=new DBAdapter(getActivity());
            adapter=new ArrayAdapter<Spacecraft>(getActivity(),android.R.layout.simple_list_item_1,db.retrieveSpacecrafts("InterGalactic"));
            lv.setAdapter(adapter);
    
        }

    Then initialize our views:

        private void initializeViews(View rootView)
        {
            lv= (ListView) rootView.findViewById(R.id.intergalactic_LV);
            refreshBtn= (Button) rootView.findViewById(R.id.intergalacticRefresh);
    
            refreshBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    loadData();
                }
            });
        }

    And override toString() method:

        @Override
        public String toString() {
            return "InterGalactic";
        }

    We will then override the onCreateView() method of this Fragment:

        @Nullable
        @Override
        public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    
            View rootView=inflater.inflate(R.layout.intergalactic,null);
    
            initializeViews(rootView);
            loadData();
    
            return rootView;
        }

    Let’s put this class all together:

    package com.tutorials.hp.tabbedsqlitelistview.mFragments;
    
    import android.os.Bundle;
    import android.support.annotation.Nullable;
    import android.support.v4.app.Fragment;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.GridView;
    import android.widget.ListView;
    
    import com.tutorials.hp.tabbedsqlitelistview.R;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;
    
    public class InterGalactic extends Fragment {
    
        ListView lv;
        Button refreshBtn;
        ArrayAdapter<Spacecraft> adapter;
    
        public static InterGalactic newInstance()
        {
            return new InterGalactic();
        }
        @Nullable
        @Override
        public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    
            View rootView=inflater.inflate(R.layout.intergalactic,null);
    
            initializeViews(rootView);
            loadData();
    
            return rootView;
        }
        /*
        INITIALIZE VIEWS
         */
        private void initializeViews(View rootView)
        {
            lv= (ListView) rootView.findViewById(R.id.intergalactic_LV);
            refreshBtn= (Button) rootView.findViewById(R.id.intergalacticRefresh);
    
            refreshBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    loadData();
                }
            });
        }
        /*
        LOAD DATA
         */
        private void loadData()
        {
            DBAdapter db=new DBAdapter(getActivity());
            adapter=new ArrayAdapter<Spacecraft>(getActivity(),android.R.layout.simple_list_item_1,db.retrieveSpacecrafts("InterGalactic"));
            lv.setAdapter(adapter);
    
        }
    
        @Override
        public String toString() {
            return "InterGalactic";
        }
    }

    (e). Constants.java

    This is our Database constants. This class will hold all our SQLite database constants.

    These constants include:

    1. Database Row/Record-ID Column.
    2. Database Name Column.
    3. Database Category Column

    It’s a public class:

    public class Constants {..}

    inside a package:

    package com.tutorials.hp.tabbedsqlitelistview.mDB;

    Inside it we specify the columns we’ll have in our database table:

        static final String ROW_ID="id";
        static final String NAME="name";
        static final String CATEGORY="category";

    Then database properties like:

    1. Database Name.
    2. Table Name.
    3. Table Version.
        static final String DB_NAME="lv_DB";
        static final String TB_NAME="lv_TB";
        static final int DB_VERSION=1;

    Then a String SQL statement constant to create for us our table in SQLite database:

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

    Lastly a String to contain SQL statement to drop or delete our database table:

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

    Here’s the full source code for this class:

    package com.tutorials.hp.tabbedsqlitelistview.mDB;
    
    public class Constants {
        /*
      COLUMNS
       */
        static final String ROW_ID="id";
        static final String NAME="name";
        static final String CATEGORY="category";
    
        /*
        DB PROPERTIES
         */
        static final String DB_NAME="lv_DB";
        static final String TB_NAME="lv_TB";
        static final int DB_VERSION=1;
    
        /*
        TABLE CREATION STATEMENT
         */
        static final String CREATE_TB="CREATE TABLE lv_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "name TEXT NOT NULL,category TEXT NOT NULL);";
    
        /*
        TABLE DELETION STMT
         */
        static final String DROP_TB="DROP TABLE IF EXISTS "+TB_NAME;
    
    }

    (f). DBHelper.java

    Our SQLite Database Helper class.

    This class will be be responsible for mainly creating our SQLite database table and upgrading it.

    This class will derive from SQLiteOpenHelper clas defined in the android.database.sqlite. package.

    Straight away we’ll define our package and add our import statements:

    package com.tutorials.hp.tabbedsqlitelistview.mDB;
    
    import android.content.Context;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;

    The create our DBHelper class:

    public class DBHelper..{..}

    and make it derive from SQLiteOpenHelper:

    public class DBHelper extends SQLiteOpenHelper {..}

    We’ll define our constructor that takes in a Context object.

        public DBHelper(Context context) {
            super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
        }

    We’ll override the onCreate() method of our SQLiteOpenHelper class passing in a SQLiteDatabase object:

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

    Inside the onCreate() as you can see we are creating our database table by invoking the execSQL() method of our SQLiteDatabase class.

    Then we also override the onUpgrade() method, passing in three parameters:

    1. SQLiteDatabase.
    2. Two integers : Old table version and new table version.
        @Override
        public void onUpgrade(SQLiteDatabase db, int i, int i1) {
            try {
                db.execSQL(Constants.DROP_TB);
                db.execSQL(Constants.CREATE_TB);
    
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
        }

    Here’s the full source code for this class:

    package com.tutorials.hp.tabbedsqlitelistview.mDB;
    
    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);
        }
        /*
        CREATE TABLE
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            try
            {
                db.execSQL(Constants.CREATE_TB);
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        /*
        UPGRADE TABLE
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int i, int i1) {
            try {
                db.execSQL(Constants.DROP_TB);
                db.execSQL(Constants.CREATE_TB);
    
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    (g). DBAdapter.java

    This is our database adapter class.

    The main role of this class is to:

    1. Open database connection.
    2. Insert/Save data to database.
    3. Retrieve data from table.
    4. Close database connection.

    First as always we’ll define our package and add imports:

    package com.tutorials.hp.tabbedsqlitelistview.mDB;
    
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;
    
    import java.util.ArrayList;

    Then create our DBAdapter class:

    public class DBAdapter {..}

    Define three instance fields: a Context object, a SQLiteDatabase object and a DBHelper object:

        Context c;
        SQLiteDatabase db;
        DBHelper helper;

    Our constructor will take a Context object and will pass it to DBHelper constructor while we are instantiating the DBHelper class:

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

    We’ll define a public method that will attempt to save data to SQLite database returning a boolean based on the success of this operation.

        public boolean saveSpacecraft(Spacecraft spacecraft) {
            try {
                db = helper.getWritableDatabase();
    
                ContentValues cv = new ContentValues();
                cv.put(Constants.NAME, spacecraft.getName());
                cv.put(Constants.CATEGORY, spacecraft.getCategory());
    
                long result = db.insert(Constants.TB_NAME, Constants.ROW_ID, cv);
                if (result > 0) {
                    return true;
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                helper.close();
            }
    
            return false;
        }

    We’ll also define a method to retrieve data from SQLite database and populate and return an arraylist.

        public ArrayList<Spacecraft> retrieveSpacecrafts(String category) {
            ArrayList<Spacecraft> spacecrafts=new ArrayList<>();
    
            try {
                db = helper.getWritableDatabase();
    
               Cursor c=db.rawQuery("SELECT * FROM "+Constants.TB_NAME+" WHERE "+Constants.CATEGORY+" = '"+category+"'",null);
    
                Spacecraft s;
                spacecrafts.clear();
    
                while (c.moveToNext())
                {
                    String s_name=c.getString(1);
                    String s_category=c.getString(2);
    
                    s=new Spacecraft();
                    s.setName(s_name);
                    s.setCategory(s_category);
    
                    spacecrafts.add(s);
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                helper.close();
            }
    
            return spacecrafts;
        }

    In the above two method we are catching SQLException.

    Here’s the full source code:

    package com.tutorials.hp.tabbedsqlitelistview.mDB;
    
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;
    
    import java.util.ArrayList;
    
    public class DBAdapter {
    
        Context c;
        SQLiteDatabase db;
        DBHelper helper;
    
        /*
        1. INITIALIZE DB HELPER AND PASS IT A CONTEXT
    
         */
        public DBAdapter(Context c) {
            this.c = c;
            helper = new DBHelper(c);
        }
    
        /*
        SAVE DATA TO DB
         */
        public boolean saveSpacecraft(Spacecraft spacecraft) {
            try {
                db = helper.getWritableDatabase();
    
                ContentValues cv = new ContentValues();
                cv.put(Constants.NAME, spacecraft.getName());
                cv.put(Constants.CATEGORY, spacecraft.getCategory());
    
                long result = db.insert(Constants.TB_NAME, Constants.ROW_ID, cv);
                if (result > 0) {
                    return true;
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                helper.close();
            }
    
            return false;
        }
    
        /*
         1. RETRIEVE SPACECRAFTS FROM DB AND POPULATE ARRAYLIST
         2. RETURN THE LIST
         */
        public ArrayList<Spacecraft> retrieveSpacecrafts(String category) {
            ArrayList<Spacecraft> spacecrafts=new ArrayList<>();
    
            try {
                db = helper.getWritableDatabase();
    
               Cursor c=db.rawQuery("SELECT * FROM "+Constants.TB_NAME+" WHERE "+Constants.CATEGORY+" = '"+category+"'",null);
    
                Spacecraft s;
                spacecrafts.clear();
    
                while (c.moveToNext())
                {
                    String s_name=c.getString(1);
                    String s_category=c.getString(2);
    
                    s=new Spacecraft();
                    s.setName(s_name);
                    s.setCategory(s_category);
    
                    spacecrafts.add(s);
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                helper.close();
            }
    
            return spacecrafts;
        }
    
    }

    (h). MyPagerAdapter.java

    This class will derive from FragmentPagerAdapter residing in the android.support.v4.app package.

    This class wil hold our fragments that will be paged via the ViewPager.

    Let’s add the package and import statements:

    package com.tutorials.hp.tabbedsqlitelistview.mAdapter;
    
    import android.support.v4.app.Fragment;
    import android.support.v4.app.FragmentManager;
    import android.support.v4.app.FragmentPagerAdapter;
    
    import java.util.ArrayList;

    Then create a class:

    public class MyPagerAdapter..{}

    Let’s turn that class into our FragmentPagetAdapter by deriving from android.support.v4.app.FragmentPagerAdapter:

    public class MyPagerAdapter extends FragmentPagerAdapter {..}

    We’ll define an instance field which is an arraylist instance. This field will hold all our fragments:

        ArrayList<Fragment> pages=new ArrayList<>();

    Then our MyPagerAdapter constructor:

        public MyPagerAdapter(FragmentManager fm) {
            super(fm);
        }

    We’ll override the getItem() method to return a Fragment instance:

        @Override
        public Fragment getItem(int position) {
            return pages.get(position);
        }

    Then getCount() method to return the total number of Fragments:

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

    Then getPageTitle() will return a CharSequence which represents the title of a single Fragment or page:

        @Override
        public CharSequence getPageTitle(int position) {
            return pages.get(position).toString();
        }

    Lastly the addPage() is our custom method to insert a Fragment or Page into our Fragments’ collection:

        public void addPage(Fragment f)
        {
            pages.add(f);
        }

    Here’s the full source code:

    package com.tutorials.hp.tabbedsqlitelistview.mAdapter;
    
    import android.support.v4.app.Fragment;
    import android.support.v4.app.FragmentManager;
    import android.support.v4.app.FragmentPagerAdapter;
    
    import java.util.ArrayList;
    
    public class MyPagerAdapter extends FragmentPagerAdapter {
    
        /*
        PAGES
         */
        ArrayList<Fragment> pages=new ArrayList<>();
    
        public MyPagerAdapter(FragmentManager fm) {
            super(fm);
        }
    
        @Override
        public Fragment getItem(int position) {
            return pages.get(position);
        }
    
        @Override
        public int getCount() {
            return pages.size();
        }
    
        @Override
        public CharSequence getPageTitle(int position) {
            return pages.get(position).toString();
        }
    
        public void addPage(Fragment f)
        {
            pages.add(f);
        }
    }

    (i). MainActivity.java

    This is our MainActivity. Activity is an android component representing a screen with which the user can interact with.

    In this case our Activity will be responsible for the following:

    1. Host our Fragments, ViewPager and TabLayout.
    2. Host our input dialog.

    Obviously we’ll define a package for our class:

    package com.tutorials.hp.tabbedsqlitelistview;

    Then add imports:

    import android.app.Dialog;
    import android.os.Bundle;
    import android.support.design.widget.TabLayout;
    import android.support.v4.view.ViewPager;
    import android.support.v7.app.AppCompatActivity;
    import android.support.v7.widget.Toolbar;
    import android.view.Menu;
    import android.view.MenuItem;
    import android.view.View;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.Spinner;
    import android.widget.Toast;
    
    import com.tutorials.hp.tabbedsqlitelistview.mAdapter.MyPagerAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mDB.DBAdapter;
    import com.tutorials.hp.tabbedsqlitelistview.mFragments.InterGalactic;
    import com.tutorials.hp.tabbedsqlitelistview.mFragments.InterPlanetary;
    import com.tutorials.hp.tabbedsqlitelistview.mFragments.InterStellar;
    import com.tutorials.hp.tabbedsqlitelistview.mModel.Spacecraft;

    Our MainActivity will derive from AppCompatActivity:

    public class MainActivity extends AppCompatActivity..{..}

    then implement TabLayout.OnTabSelectedListener interface:

    public class MainActivity extends AppCompatActivity implements TabLayout.OnTabSelectedListener {

    We’ll have several instance fields: TabLayout , ViewPager, integer current position, EditText, Button and Spinner:

        private TabLayout tab;
        private ViewPager vp;
        int currentPos=0;
    
        EditText nameEditText;
        Button saveBtn;
        Spinner sp;

    We’ll fill our MyPagerAdapter with our Fragments by calling addPage() and generating a Fragment instance:

        //FILL TAB PAGES
        private void addPages()
        {
            MyPagerAdapter myPagerAdapter=new MyPagerAdapter(getSupportFragmentManager());
            myPagerAdapter.addPage(InterPlanetary.newInstance());
            myPagerAdapter.addPage(InterStellar.newInstance());
            myPagerAdapter.addPage(InterGalactic.newInstance());
    
            vp.setAdapter(myPagerAdapter);
        }

    We’ll also override several TabLayout.OnTabSelectedListener interface methods. We set the current item to our ViewPager inside the onTabSelected() method.

        @Override
        public void onTabSelected(TabLayout.Tab tab) {
            vp.setCurrentItem(currentPos=tab.getPosition());
        }
    
        @Override
        public void onTabUnselected(TabLayout.Tab tab) {
    
        }
    
        @Override
        public void onTabReselected(TabLayout.Tab tab) {
    
        }

    We’ll create a method to create and display our input dialog. Through this dialog users will save data to SQLite database:

        private void displayDialog()
        {
            Dialog d=new Dialog(this);
            d.setTitle("SQLITE DATA");
            d.setContentView(R.layout.dialog_layout);
    
            //INITIALIZE VIEWS
            nameEditText= (EditText) d.findViewById(R.id.nameEditTxt);
            saveBtn= (Button) d.findViewById(R.id.saveBtn);
            sp = (Spinner) d.findViewById(R.id.category_SP);
    
            //SPINNER ADAPTER
            String[] categories = {InterPlanetary.newInstance().toString(),
                    InterStellar.newInstance().toString(),
                    InterGalactic.newInstance().toString()};
            sp.setAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, categories));
    
            //SAVE
            saveBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
    
                    Spacecraft s = new Spacecraft();
                    s.setName(nameEditText.getText().toString());
                    s.setCategory(sp.getSelectedItem().toString());
    
                    if (new DBAdapter(MainActivity.this).saveSpacecraft(s)) {
                        nameEditText.setText("");
                        sp.setSelection(0);
                    } else {
                        Toast.makeText(MainActivity.this, "Not Saved", Toast.LENGTH_SHORT).show();
                    }
                }
            });
    
            //SHOW DIALOG
            d.show();
    
        }

    Then we override our onCreate() method. Here we:

    1. Reference our ViewPager from our xml layout.
    2. Setup the toolbar.
    3. Add Fragments to our MyPagerAdapter.
    4. Reference TabLayout.
    5. Set Tab Gravity to TabLayout.GRAVITY_FILL.
    6. Setup our Tab with ViewPager.
    7. Set Tab Listener to this Activity.
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
            setSupportActionBar(toolbar);
    
            //VIEWPAGER AND TABS
            vp = (ViewPager) findViewById(R.id.viewpager);
            addPages();
    
            //SETUP TAB
            tab = (TabLayout) findViewById(R.id.tabs);
            tab.setTabGravity(TabLayout.GRAVITY_FILL);
            tab.setupWithViewPager(vp);
            tab.addOnTabSelectedListener(this);
        }

    4. Layouts

    (a). activity_main.xml

    Our MainActivity layout.

    <?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.tabbedsqlitelistview.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.TabLayout
                android_id="@+id/tabs"
                android_layout_width="match_parent"
                android_layout_height="wrap_content"
                />
    
        </android.support.design.widget.AppBarLayout>
    
        <include layout="@layout/content_main" />
    
        <android.support.v4.view.ViewPager
            android_id="@+id/viewpager"
            android_layout_width="match_parent"
            android_layout_height="match_parent"
            app_layout_behavior="@string/appbar_scrolling_view_behavior"
            />
    
    </android.support.design.widget.CoordinatorLayout>

    (b). content_main.xml

    This layout will get interpolated into our activity_main.xml:

    <?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.tabbedsqlitelistview.MainActivity"
        tools_showIn="@layout/activity_main">
    
    </RelativeLayout>

    (c). dialog_layout.xml

    This is our input dialog layout:

    <?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">
    
            <!--INPUT VIEWS-->
            <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>
    
            <Spinner
                android_id="@+id/category_SP"
                android_layout_width="match_parent"
                android_layout_height="wrap_content">
            </Spinner>
    
            <!--BUTTON-->
            <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"/>
    </LinearLayout>
        </android.support.v7.widget.CardView>

    (d). inteplanetary.xml

    Our InterPlanetary Fragment layout:

    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout 
        android_orientation="vertical" android_layout_width="match_parent"
        android_layout_height="match_parent"
    
        >
        <android.support.v7.widget.CardView
            android_orientation="horizontal" android_layout_width="match_parent"
            
            android_layout_margin="10dp"
            card_view_cardCornerRadius="10dp"
            card_view_cardElevation="10dp"
            android_layout_height="wrap_content">
    
            <LinearLayout
                android_orientation="vertical"
                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="INETR-PLANETARY"
                    android_id="@+id/nameTxt"
                    android_padding="10dp"
                    android_textColor="@color/colorAccent"
                    android_layout_alignParentLeft="true"
                    />
    
                <ListView
                    android_id="@+id/interplanetary_LV"
                    android_layout_width="match_parent"
                    android_layout_height="wrap_content"
                    />
    
                <Button
                    android_text="Refresh"
                    android_id="@+id/interplanetaryRefresh"
                    android_background="#009968"
                    android_layout_width="wrap_content"
                    android_layout_height="wrap_content" />
            </LinearLayout>
        </android.support.v7.widget.CardView>
    </LinearLayout>

    (e). interstellar.xml

    Our InterStellar Fragment layout.

    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout 
        android_orientation="vertical" android_layout_width="match_parent"
        android_layout_height="match_parent"
        >
        <android.support.v7.widget.CardView
            android_orientation="horizontal" android_layout_width="match_parent"
            
            android_layout_margin="10dp"
            card_view_cardCornerRadius="10dp"
            card_view_cardElevation="10dp"
            android_layout_height="wrap_content">
    
               <LinearLayout
                   android_orientation="vertical"
                   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="INTER-STELLAR"
                    android_id="@+id/nameTxt"
                    android_padding="10dp"
                    android_textColor="@color/colorAccent"
                    android_layout_alignParentLeft="true"
                    />
    
                <ListView
                    android_id="@+id/interstellar_LV"
                    android_layout_width="match_parent"
                    android_layout_height="wrap_content"
                     />
    
                <Button
                    android_text="Refresh"
                    android_id="@+id/interstellarRefresh"
                    android_layout_width="wrap_content"
                    android_background="#009968"
                    android_layout_height="wrap_content" />
    
               </LinearLayout>
        </android.support.v7.widget.CardView>
    </LinearLayout>

    (f). intergalactic.xml

    Our InterGalactic Fragment layout.

    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout 
        android_orientation="vertical" android_layout_width="match_parent"
        android_layout_height="match_parent"
        >
        <android.support.v7.widget.CardView
            android_orientation="horizontal" android_layout_width="match_parent"
            
            android_layout_margin="10dp"
            card_view_cardCornerRadius="10dp"
            card_view_cardElevation="10dp"
            android_layout_height="wrap_content">
    
            <LinearLayout
                android_orientation="vertical"
                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="INTER-GALACTIC"
                    android_id="@+id/nameTxt"
                    android_padding="10dp"
                    android_textColor="@color/colorAccent"
                    android_layout_alignParentLeft="true"
                    />
    
                <ListView
                    android_id="@+id/intergalactic_LV"
                    android_layout_width="match_parent"
                    android_layout_height="wrap_content"
                    />
    
                <Button
                    android_text="Refresh"
                    android_id="@+id/intergalacticRefresh"
                    android_background="#009968"
                    android_layout_width="wrap_content"
                    android_layout_height="wrap_content" />
            </LinearLayout>
        </android.support.v7.widget.CardView>
    </LinearLayout>

    More Resources:

    Resource Link
    GitHub Browse Browse
    GitHub Download Link Download
  2. Android SQLite CRUD – ListView – Database-side Search/Filter

    Android SQLite CRUD – ListView – Database side 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 {{EJS166}} method of the {{EJS167}} class
    {{EJS168}}`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
  3. 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
  4. Android SQLite Full – ListView – INSERT,SELECT,UPDATE, DELETE with ContextMenu

     

    Android SQLite Full – ListView – INSERT,SELECT,UPDATE, DELETE with ContextMenu Tutorial.

    Basically we see how to perform all CRUD(Create Read Update Delete) Operations against a SQLite database. We use ListView as our adapterview. We provide options via ContextMenu.

     

    Nowadays most apps need or require to store content or data in some form.

    There is the cloud but it isn’t a replacement for local storage.Neither will it be in the near future.Storing data locally is important because its easily retrievable,doesn’t require internet connection and is fast. Good old SQLite is still the way to go.

    Together with Realm database,they are two ways that are now common in local data storage.The package we shall be using is android.database.sqlite. This package was added in Android API 1. This implies it has existed since the very inception of android. That package contains classes and apis which we can use when manipulating our database. Android does ship with SQLite database.

    Android.database.sqlite.SQliteDatabase class is defined in the package we talked about above. It inherits from android.database.SQLite.SQLiteCloseable. SQliteDatabase will give use the methods we need to perform our SQLite CRUD operations. It can also allow us execute SQL statements directly if so we desire.

    A database has to be unique for each single application, within that application. Now today we look at ListView and SQLite database.The two are normally commonly used so we try to leave no stone untouched.Nevertheless we have covered full CRUD operations previously but with RecyclerView.Today we look at with ListView.

    In short this is what we do here:

    1. INSERT/SAVE to SQLite database.
    2. SELECT/RETRIEVE data to ListView.
    3. UPDATE/EDIT data and refresh.
    4. DELETE/REMOVE data on ContextMenu Item selected.
    5. We are using Context Menu,material EditTexts and ListView with cards.

    ===

    Project Demo

    The first thing we want to do is create a roof that will contain the SQlite database constants that we desire to use in our project.This makes it easy to reuse and maintain our database classes.

    Constants.java

    This is the Constants class. It will hold all our database constants. Those constants include the column names, database name, table name, database version, table creation statement as well as table droppiing/deleting statement.

    package com.tutorials.hp.listviewsqlite.mDataBase;
    
    public class Constants {
        //COLUMNS
        static final String ROW_ID="id";
        static final String NAME="name";
    
        //DB PROPERTIES
        static final String DB_NAME="hh_DB";
        static final String TB_NAME="hh_TB";
        static final int DB_VERSION=1;
    
        //CREATE TB STMT
        static final String CREATE_TB="CREATE TABLE hh_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "name TEXT NOT NULL);";
    
        //DROP TB STMT
        static final String DROP_TB="DROP TABLE IF EXISTS "+TB_NAME;
    }

    Apart from the Constants class,we have two more database classes : DBHelper and DBAdapter.

    DBHelper.java

    DBHelper shall create our database table as well as upgrade it.It derives from SQliteOpenHelper class.

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

    DBAdapter.java

    DBAdapter below is responsible for all our CRUD operations,saving/inserting data.Then rerieving/selecting that data.We shall also edit/update our SQlite database data as well as deleting. Here’s where we define methods to perform those :

    package com.tutorials.hp.listviewsqlite.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 CON
        public void openDB()
        {
            try
            {
                db=helper.getWritableDatabase();
            }catch (SQLException e)
            {
    
            }
        }
        //CLOSE DB
        public void closeDB()
        {
            try
            {
                helper.close();
            }catch (SQLException e)
            {
    
            }
        }
    
        //SAVE
        public boolean add(String name)
        {
            try
            {
                ContentValues cv=new ContentValues();
                cv.put(Constants.NAME,name);
    
                long result=db.insert(Constants.TB_NAME,Constants.ROW_ID,cv);
                if(result>0)
                {
                    return true;
                }
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
    
            return false;
        }
    
        //SELECT
        public Cursor retrieve()
        {
            String[] columns={Constants.ROW_ID,Constants.NAME};
    
            Cursor c=db.query(Constants.TB_NAME,columns,null,null,null,null,null);
            return c;
        }
    
        //UPDATE/edit
        public boolean update(String newName,int id)
        {
            try
            {
                ContentValues cv=new ContentValues();
                cv.put(Constants.NAME,newName);
    
                int result=db.update(Constants.TB_NAME,cv, Constants.ROW_ID + " =?", new String[]{String.valueOf(id)});
                if(result>0)
                {
                    return true;
                }
            }catch (SQLException e)
            {
                 e.printStackTrace();
            }
    
            return false;
    
        }
    
        //DELETE/REMOVE
        public boolean delete(int id)
        {
            try
            {
                int result=db.delete(Constants.TB_NAME,Constants.ROW_ID+" =?",new String[]{String.valueOf(id)});
                if(result>0)
                {
                    return true;
                }
    
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
    
            return false;
        }
    
    }

    MyLongClickListener.java

    Now we shall be displaying the actions to be performed like add,edit,delete data via the ContextMenu.The user longclicks the ListView and we display the ContextMenu. First lets define the signature for our LongClickListner :

    package com.tutorials.hp.listviewsqlite.mListView;
    
    public interface MyLongClickListener {
    
        void onItemLongClick();
    
    }

    MyViewHolder.java

    Then we shall have a ViewHolder class for our ListView that shall implement the above interface.We also take note,implement the OnCreateContextMenuListener where we create our menu that shall be our ContextMenu.We add our menu items :

    package com.tutorials.hp.listviewsqlite.mListView;
    
    import android.view.ContextMenu;
    import android.view.View;
    import android.widget.TextView;
    
    import com.tutorials.hp.listviewsqlite.R;
    
    public class MyViewHolder implements View.OnLongClickListener,View.OnCreateContextMenuListener {
    
        TextView nameTxt;
        MyLongClickListener longClickListener;
    
        public MyViewHolder(View v) {
            nameTxt= (TextView) v.findViewById(R.id.nameTxt);
    
            v.setOnLongClickListener(this);
            v.setOnCreateContextMenuListener(this);
        }
    
        @Override
        public boolean onLongClick(View v) {
            this.longClickListener.onItemLongClick();
            return false;
        }
    
        public void setLongClickListener(MyLongClickListener longClickListener)
        {
            this.longClickListener=longClickListener;
        }
    
        @Override
        public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {
            menu.setHeaderTitle("Action : ");
            menu.add(0, 0, 0, "NEW");
            menu.add(0,1,0,"EDIT");
            menu.add(0,2,0,"DELETE");
    
        }
    }

    CustomAdapter.java

    Our CustomAdapter class is below :

    package com.tutorials.hp.listviewsqlite.mListView;
    
    import android.content.Context;
    import android.view.LayoutInflater;
    import android.view.View;
    import android.view.ViewGroup;
    import android.widget.BaseAdapter;
    import android.widget.Toast;
    
    import com.tutorials.hp.listviewsqlite.R;
    import com.tutorials.hp.listviewsqlite.mDataObject.Spacecraft;
    
    import java.util.ArrayList;
    
    public class CustomAdapter extends BaseAdapter {
    
        Context c;
        ArrayList<Spacecraft> spacecrafts;
        LayoutInflater inflater;
        Spacecraft spacecraft;
    
        public CustomAdapter(Context c, ArrayList<Spacecraft> spacecrafts) {
            this.c = c;
            this.spacecrafts = spacecrafts;
        }
    
        @Override
        public int getCount() {
            return spacecrafts.size();
        }
    
        @Override
        public Object getItem(int position) {
            return spacecrafts.get(position);
        }
    
        @Override
        public long getItemId(int position) {
            return position;
        }
    
        @Override
        public View getView(final 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);
            }
    
            //BIND DATA
            MyViewHolder holder=new MyViewHolder(convertView);
            holder.nameTxt.setText(spacecrafts.get(position).getName());
    
            convertView.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Toast.makeText(c, spacecrafts.get(position).getName(), Toast.LENGTH_SHORT).show();
                }
            });
    
            holder.setLongClickListener(new MyLongClickListener() {
                @Override
                public void onItemLongClick() {
                    spacecraft= (Spacecraft) getItem(position);
                }
            });
    
            return convertView;
        }
    
        //EXPOSE NAME AND ID
        public int getSelectedItemID()
        {
            return spacecraft.getId();
        }
        public String getSelectedItemName()
        {
            return spacecraft.getName();
        }
    
    }

    MainActivity.java

    Finally we have our MainActivity below :

    package com.tutorials.hp.listviewsqlite;
    
    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.Toolbar;
    import android.view.View;
    import android.view.MenuItem;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.ListView;
    import android.widget.Toast;
    
    import com.tutorials.hp.listviewsqlite.mDataBase.DBAdapter;
    import com.tutorials.hp.listviewsqlite.mDataObject.Spacecraft;
    import com.tutorials.hp.listviewsqlite.mListView.CustomAdapter;
    
    import java.util.ArrayList;
    
    public class MainActivity extends AppCompatActivity {
    
        ListView lv;
        EditText nameEditText;
        Button saveBtn,retrieveBtn;
        ArrayList<Spacecraft> spacecrafts=new ArrayList<>();
        CustomAdapter adapter;
        final Boolean forUpdate=true;
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
            setSupportActionBar(toolbar);
    
            lv= (ListView) findViewById(R.id.lv);
            adapter=new CustomAdapter(this,spacecrafts);
    
            this.getSpacecrafts();
           // lv.setAdapter(adapter);
    
            FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
            fab.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    displayDialog(false);
                }
            });
        }
    
        private void displayDialog(Boolean forUpdate)
        {
            Dialog d=new Dialog(this);
            d.setTitle("SQLITE DATA");
            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);
    
            if(!forUpdate)
            {
                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) {
                        getSpacecrafts();
                    }
                });
            }else {
    
                //SET SELECTED TEXT
                nameEditText.setText(adapter.getSelectedItemName());
    
                saveBtn.setOnClickListener(new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {
                         update(nameEditText.getText().toString());
                    }
                });
                retrieveBtn.setOnClickListener(new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {
                         getSpacecrafts();
                    }
                });
            }
    
            d.show();
    
        }
    
        //SAVE
        private void save(String name)
        {
            DBAdapter db=new DBAdapter(this);
            db.openDB();
            boolean saved=db.add(name);
    
            if(saved)
            {
                nameEditText.setText("");
                getSpacecrafts();
            }else {
                Toast.makeText(this,"Unable To Save",Toast.LENGTH_SHORT).show();
            }
        }
    
        //RETRIEVE OR GETSPACECRAFTS
        private void getSpacecrafts()
        {
            spacecrafts.clear();
            DBAdapter db=new DBAdapter(this);
            db.openDB();
            Cursor c=db.retrieve();
            Spacecraft spacecraft=null;
    
            while (c.moveToNext())
            {
                int id=c.getInt(0);
                String name=c.getString(1);
    
                spacecraft=new Spacecraft();
                spacecraft.setId(id);
                spacecraft.setName(name);
    
                spacecrafts.add(spacecraft);
            }
    
            db.closeDB();
            lv.setAdapter(adapter);
        }
    
        //UPDATE OR EDIT
        private void update(String newName)
        {
            //GET ID OF SPACECRAFT
            int id=adapter.getSelectedItemID();
    
            //UPDATE IN DB
            DBAdapter db=new DBAdapter(this);
            db.openDB();
            boolean updated=db.update(newName,id);
            db.closeDB();
    
            if(updated)
            {
                nameEditText.setText(newName);
                getSpacecrafts();
            }else {
                Toast.makeText(this,"Unable To Update",Toast.LENGTH_SHORT).show();
            }
    
        }
    
        private void delete()
        {
            //GET ID
            int id=adapter.getSelectedItemID();
    
            //DELETE FROM DB
            DBAdapter db=new DBAdapter(this);
            db.openDB();
            boolean deleted=db.delete(id);
            db.closeDB();
    
            if(deleted)
            {
                getSpacecrafts();
            }else {
                Toast.makeText(this,"Unable To Delete",Toast.LENGTH_SHORT).show();
            }
        }
    
        @Override
        public boolean onContextItemSelected(MenuItem item) {
            CharSequence title=item.getTitle();
            if(title=="NEW")
            {
               displayDialog(!forUpdate);
    
            }else  if(title=="EDIT")
            {
                displayDialog(forUpdate);
    
            }else  if(title=="DELETE")
            {
                delete();
            }
    
            return super.onContextItemSelected(item);
        }
    }

    Download

    Look,the full source code reference is above for download.Just download it,extract and import to you android studio.

    The video tutorial is below.Its complete and explained step by step.
    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



Share an Example

Share an Example

Browse
What is the capital of Egypt? ( Cairo )