Android Basic Training Course: Building ContentProvider

    In my previous post, I had used standard Content Provider. With it, you can load data from device contacts, media, sd card files,...Moreover, it can be customized by developers to access data from Internet, SQLite database or Files. Whether in the circumstances, it also stands as an intermediary transporter like this description diagram:

    In this post, I will present the way to customizing ContentProvider to loading data from a SQLite database, this can reduce data loading time. In order to prepare well for the understanding of this issue, you can read the previous post about use SQLite database in Android.
    DEMO VIDEO:

Creating a database class

    In Android, SQLite database created by extending SQLiteOpenHelper. So, make a simple subclass and overriding onCreate() and onUpgrade() like this:

DBHelper.java

package info.devexchanges.contentproviderwithsqlitedb;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "db_friend";

    public static final String TABLE_FRIENDS = "friend";
    public static final String ID = "id";
    public static final String COL_NAME = "name";
    public static final String COL_JOB = "job";

    private static final String CREATE_TABLE_FRIENDS = "create table " + TABLE_FRIENDS
            + " (" + ID + " integer primary key autoincrement, " + COL_NAME
            + " text not null, " + COL_JOB + " text not null);";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_FRIENDS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FRIENDS);
        onCreate(db);
    }
}

Building Content Provider

    By making a subclass of ContentProvider, we have to override these require methods:

• onCreate():which is called to initialize the provider. Only called from the application main thread, and must avoid performing lengthy operations, other methods below didn't.
• query(): which returns data to the caller.
• insert(): which inserts new data into the content provider.
• delete(): which deletes data from the content provider.
• update(): which updates existing data in the content provider.
• getType(): which returns the MIME type of data in the content provider.

     By creating this content provider class, you must create a new database instance in it, so onCreate() method will be like:

    @Override
    public boolean onCreate() {
        dbHelper = new DBHelper(getContext());

        // permissions to be writable
        database = dbHelper.getWritableDatabase();
        if (database == null)
            return false;
        else
            return true;
    }

    Content providers work with data at the URI level. For instance, this URI identifies all of the records:

public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + FRIENDS_BASE_PATH);

    As you can see, we must provide an authority for any custom content provider as the name of the content provider. In this example, it is:

private static final String AUTHORITY = "info.devexchanges.contentprovider.CustomContentProvider";

    In query() method, for getting data from database, we must call getReadableDatabase() method of SQLiteOpenHelper before start query by using Cursor. On the contrary, deleting, updating, inserting need getWritableDatabase(). These changing data methods use ContentResolver to access and alter data. Full code for this content provider, after overriding these important methods:

CustomContentProvider.java

package info.devexchanges.contentproviderwithsqlitedb;

import android.content.ContentProvider;
import android.content.ContentResolver;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.support.annotation.Nullable;
import android.text.TextUtils;

public class CustomContentProvider extends ContentProvider {

    private DBHelper dbHelper;
    private SQLiteDatabase database;
    private static final String AUTHORITY = "info.devexchanges.contentprovider.CustomContentProvider";
    public static final int FRIENDS = 100;
    public static final int FRIEND_ID = 110;

    private static final String FRIENDS_BASE_PATH = "friend";
    public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + FRIENDS_BASE_PATH);

    public static final String CONTENT_ITEM_TYPE = ContentResolver.CURSOR_ITEM_BASE_TYPE + "/mt-tutorial";
    public static final String CONTENT_TYPE = ContentResolver.CURSOR_DIR_BASE_TYPE + "/mt-tutorial";
    private static final UriMatcher uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);

    static {
        uriMatcher.addURI(AUTHORITY, FRIENDS_BASE_PATH, FRIENDS);
        uriMatcher.addURI(AUTHORITY, FRIENDS_BASE_PATH + "/#", FRIEND_ID);
    }

    @Override
    public boolean onCreate() {
        dbHelper = new DBHelper(getContext());

        // permissions to be writable
        database = dbHelper.getWritableDatabase();
        if (database == null)
            return false;
        else
            return true;
    }

    @SuppressWarnings("ConstantConditions")
    @Nullable
    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(DBHelper.TABLE_FRIENDS);

        int uriType = uriMatcher.match(uri);
        switch (uriType) {
            case FRIEND_ID:
                queryBuilder.appendWhere(DBHelper.ID + "=" + uri.getLastPathSegment());
                break;
            case FRIENDS:
                break;
            default:
                throw new IllegalArgumentException("Unknown URI");
        }

        Cursor cursor = queryBuilder.query(dbHelper.getReadableDatabase(),
                projection, selection, selectionArgs, null, null, sortOrder);
        cursor.setNotificationUri(getContext().getContentResolver(), uri);
        return cursor;
    }

    @Nullable
    @Override
    public String getType(Uri uri) {
        return null;
    }

    @SuppressWarnings("ConstantConditions")
    @Nullable
    @Override
    public Uri insert(Uri uri, ContentValues values) {
        long row = database.insert(DBHelper.TABLE_FRIENDS, "", values);

        // If record is added successfully
        if (row > 0) {
            Uri newUri = ContentUris.withAppendedId(CONTENT_URI, row);
            getContext().getContentResolver().notifyChange(newUri, null);
            return newUri;
        }
        throw new SQLException("Fail to add a new record into " + uri);

    }

    @SuppressWarnings("ConstantConditions")
    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        int uriType = uriMatcher.match(uri);
        int rowsAffected = 0;
        switch (uriType) {
            case FRIENDS:
                rowsAffected = database.delete(DBHelper.TABLE_FRIENDS, selection, selectionArgs);
                break;
            case FRIEND_ID:
                String id = uri.getLastPathSegment();
                if (TextUtils.isEmpty(selection)) {
                    rowsAffected = database.delete(DBHelper.TABLE_FRIENDS, DBHelper.ID + "=" + id, null);
                } else {
                    rowsAffected = database.delete(DBHelper.TABLE_FRIENDS, selection + " and " + DBHelper.ID + "=" + id, selectionArgs);
                }
                break;
            default:
                throw new IllegalArgumentException("Unknown or Invalid URI " + uri);
        }
        getContext().getContentResolver().notifyChange(uri, null);

        return rowsAffected;
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
        return 0;
    }
}

Registering in AndroidManifest

    All custom content provider class must be register in AndroidManifest.xml with <provider> tag:

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="info.devexchanges.contentproviderwithsqlitedb">

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

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

        <provider
            android:multiprocess="true"
            android:authorities="info.devexchanges.contentprovider.CustomContentProvider"
            android:name="info.devexchanges.contentproviderwithsqlitedb.CustomContentProvider" />
    </application>

</manifest>

Usage on User Interface

    Over here, we have built the content provider with a database. The next part is declaring how to use on UI (activities, fragments or service,...). I will make an Activity which show all table records by a ListView and in it, you can add a new record to database table or remove any item from database by clicking delete button on each row. All access from UI to database through content provider URI.
    About adding a new record, you can use ContentValues by this code:

//Adding new record to database with Content provider
ContentValues values = new ContentValues();
values.put(DBHelper.COL_NAME, getText(txtName));
values.put(DBHelper.COL_JOB, getText(txtJob));
getContentResolver().insert(CustomContentProvider.CONTENT_URI, values);

    Fetching all records from table with ContentResolver and Cursor and store them to an ArrayList:

        Cursor cursor = getContentResolver().query(CustomContentProvider.CONTENT_URI, null, null, null, null);

        if (!cursor.moveToFirst()) {
            Toast.makeText(this, " no record yet!", Toast.LENGTH_SHORT).show();
        } else {
            do {
                String name = cursor.getString(cursor.getColumnIndex(DBHelper.COL_NAME));
                String job = cursor.getString(cursor.getColumnIndex(DBHelper.COL_JOB));

                //Loading to arraylist to set adapter data for ListView
                Friend friend = new Friend(name, job);
                friendList.add(friend);

            } while (cursor.moveToNext());
        }

    And deleting an exist record:

                //deleting a record in database table based on "name"
                String selection = DBHelper.COL_NAME + " = \"" + friend.getName() + "\"";
                int rowsDeleted = activity.getContentResolver().delete(CustomContentProvider.CONTENT_URI, selection, null);

                if (rowsDeleted > 0) {
                    Toast.makeText(activity, "Deleted!", Toast.LENGTH_SHORT).show();
                } else {
                    Toast.makeText(activity, "Error!", Toast.LENGTH_SHORT).show();

    Full code of this Activity:

MainActivity.java

package info.devexchanges.contentproviderwithsqlitedb;

import android.app.Dialog;
import android.content.ContentValues;
import android.database.Cursor;
import android.net.Uri;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {

    private ListView listView;
    private List<Friend> friendList;
    private ListViewAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listView = (ListView) findViewById(R.id.list);
        friendList = new ArrayList<>();

        //set Listview adapter
        adapter = new ListViewAdapter(this, R.layout.item_listview, friendList);
        listView.setAdapter(adapter);
        showAllFriends();
    }

    public void showAllFriends() {
        friendList.clear(); //clear old arraylist data first
        Cursor cursor = getContentResolver().query(CustomContentProvider.CONTENT_URI, null, null, null, null);

        if (!cursor.moveToFirst()) {
            Toast.makeText(this, " no record yet!", Toast.LENGTH_SHORT).show();
        } else {
            do {
                String name = cursor.getString(cursor.getColumnIndex(DBHelper.COL_NAME));
                String job = cursor.getString(cursor.getColumnIndex(DBHelper.COL_JOB));

                //Loading to arraylist to set adapter data for ListView
                Friend friend = new Friend(name, job);
                friendList.add(friend);

            } while (cursor.moveToNext());
        }
        adapter.notifyDataSetChanged();
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return super.onCreateOptionsMenu(menu);
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {

        switch (item.getItemId()) {
            case R.id.add:
                showAddingDialog();
                break;

            default:
                break;

        }
        return super.onOptionsItemSelected(item);
    }

    private void showAddingDialog() {
        // custom dialog
        final Dialog dialog = new Dialog(this);
        dialog.setContentView(R.layout.layout_dialog_add);
        dialog.setTitle("Adding a new friend");

        final EditText txtName = (EditText) dialog.findViewById(R.id.name);
        final EditText txtJob = (EditText) dialog.findViewById(R.id.job);

        Button btnAdd = (Button) dialog.findViewById(R.id.btn_ok);
        btnAdd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if (!hasText(txtJob) || !hasText(txtName)) {
                    Toast.makeText(getBaseContext(), "Please input full information...", Toast.LENGTH_SHORT).show();
                } else {
                    //Adding new record to database with Content provider
                    // Add a new birthday record
                    ContentValues values = new ContentValues();
                    values.put(DBHelper.COL_NAME, getText(txtName));
                    values.put(DBHelper.COL_JOB, getText(txtJob));
                    getContentResolver().insert(CustomContentProvider.CONTENT_URI, values);

                    Toast.makeText(getBaseContext(), "Inserted!", Toast.LENGTH_SHORT).show();
                    //reloading data
                    showAllFriends();
                    //dismiss dialog after adding process
                    dialog.dismiss();
                }
            }
        });

        dialog.show();
    }

    private boolean hasText(TextView textView) {
        if (textView.getText().toString().trim().equals("")) {
            return false;
        } else return true;
    }

    private String getText(TextView textView) {
        return textView.getText().toString().trim();
    }
}

    It's layout (only contains a ListView):

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    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">

    <ListView
        android:id="@+id/list"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
</RelativeLayout>

    Customizing a ListView adapter based on ArrayAdapter (the deletion code I put here):

ListViewAdapter.java

package info.devexchanges.contentproviderwithsqlitedb;

import android.app.Activity;
import android.content.DialogInterface;
import android.support.v7.app.AlertDialog;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.ImageView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.List;

public class ListViewAdapter extends ArrayAdapter<Friend> {

    private MainActivity activity;

    public ListViewAdapter(MainActivity context, int resource, List<Friend> objects) {
        super(context, resource, objects);
        this.activity = context;
    }

    @Override
    public View getView(final int position, View convertView, ViewGroup parent) {
        ViewHolder holder;
        LayoutInflater inflater = (LayoutInflater) activity.getSystemService(Activity.LAYOUT_INFLATER_SERVICE);

        if (convertView == null) {
            convertView = inflater.inflate(R.layout.item_listview, parent, false);
            holder = new ViewHolder(convertView);
            convertView.setTag(holder);
        } else {
            holder = (ViewHolder) convertView.getTag();
        }

        //set data to views
        holder.job.setText(getItem(position).getJob());
        holder.name.setText(getItem(position).getName());

        holder.btnDel.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                showAlertDialog(getItem(position));
            }
        });

        return convertView;
    }

    public void showAlertDialog(final Friend friend) {
        AlertDialog.Builder builder = new AlertDialog.Builder(activity);
        builder.setTitle("Delete Confirm");
        builder.setCancelable(true);
        builder.setMessage("Are you sure?");
        builder.setPositiveButton("OK", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                //deleting a record in database table based on "name"
                String selection = DBHelper.COL_NAME + " = \"" + friend.getName() + "\"";
                int rowsDeleted = activity.getContentResolver().delete(CustomContentProvider.CONTENT_URI, selection, null);

                if (rowsDeleted > 0) {
                    Toast.makeText(activity, "Deleted!", Toast.LENGTH_SHORT).show();

                    //reloading data
                    activity.showAllFriends();
                } else {
                    Toast.makeText(activity, "Error!", Toast.LENGTH_SHORT).show();
                }
            }
        });
        builder.setNegativeButton("Cancel", null);

        builder.show();
    }

    private class ViewHolder {
        private TextView name;
        private TextView job;
        private ImageView btnDel;

        public ViewHolder(View v) {
            name = (TextView) v.findViewById(R.id.name);
            job = (TextView) v.findViewById(R.id.job);
            btnDel = (ImageView) v.findViewById(R.id.btn_del);
        }
    }
}

Some necessary files

    Layout for each ListView item:

item_listview.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content">

    <TextView
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/app_name"
        android:textColor="@android:color/holo_blue_dark"
        android:textStyle="bold" />

    <TextView
        android:id="@+id/job"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@id/name"
        android:text="@string/app_name"
        android:textColor="@android:color/holo_green_dark"
        android:textStyle="italic" />

    <ImageView
        android:id="@+id/btn_del"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:contentDescription="@string/app_name"
        android:src="@drawable/delete" />

</RelativeLayout>

    The POJO class for this project:

Friend.java

package info.devexchanges.contentproviderwithsqlitedb;

public class Friend {

    private String name;
    private String job;

    public Friend(String name, String job) {
        this.name = name;
        this.job = job;
    }

    public String getName() {
        return name;
    }

    public String getJob() {
        return job;
    }
}

    A dialog layout, use in adding the new record above:

layout_dialog_add.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:padding="@dimen/activity_horizontal_margin">

    <EditText
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Put a name"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/job"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Put a job"
        android:inputType="textPersonName" />

    <Button
        android:id="@+id/btn_ok"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Add" />

</LinearLayout>

Run the application

    After running this project, we'll have this output (a list of records):

    When user click on (+) button, a dialog will appear to input the new record information:

    After inserting successful:

    When clicking the delete button at any row, a confirm action dialog appear:

    And this is the Toast notice after delete a record successful:

Conclusions

   This tutorial has taught you not only how to create a SQLite database and wrap it inside of a content provider, but also how straightforward it is to use a content provider to populate a ListView control. In this, I have not provide the way to update (edit) a record in database table, readers can find out yourself! I hope you’ve enjoyed this tutorial and finally, you can get full project code on @Github by click the button below.

Previous Chapter

Table Of  Contents

Next Chapter

Android Basic Training Course: Creating and Managing SQLite Database

    SQLite is an open source relational database that stores data to a text file on an Android device. Normal SQL (Structured Query Language) can be used to access a SQLite database in Android. Each application on a device can have a database associated with it.

    The SQLite library is a core part of the Android environment. Java applications and content providers access SQLite using the interface in the android.sqlite.database namespace.
    One disadvantage of using Android's built-in SQLite support is that the application is forced to use the version of SQLite that the current version of Android happened to ship with. If your application happens to require a newer version of SQLite, or a build with a custom extension or VFS installed, you're out of luck.
    In this post, I will present a simple project about creating and managing a SQLite database which contains only one table, for beginners can approach this hard and important matter. You can see this DEMO VIDEO first for project output:

Creating Database

    Firstly, suppose we have a POJO simple like this:

public class Friend {

    private int id;
    private String name;
    private String job;

    public Friend() {}

    public Friend(String name, String job) {
        this.name = name;
        this.job = job;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }
}

     And we would like to save each POJO object to database as a table, this is it's structure:

Field	Data Type	Key
ID	int	Primary
Name	Text
Job	Text

    In order to create a SQLite database in Android, we must make a subclass of  SQLiteOpenHelper, which provided by Android SDK. Moreover, we also need to write our own methods to handle all database CRUD(Create, Read, Update and Delete) operations. After creating, we have to override 2 requirement methods:

•  onCreate(): These is where we need to write create table statements. This is called when database is created.
•  onUpgrade(): This method is called when database is upgraded like modifying the table structure, adding constraints to database,...

    And this is code for 2 these methods and a constructor:

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "myFriendDB";

    // Friend table name
    private static final String TABLE_FRIEND = "friend";

    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_JOB = "job";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_FRIEND_TABLE = "CREATE TABLE " + TABLE_FRIEND + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_JOB + " TEXT" + ")";
        db.execSQL(CREATE_FRIEND_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FRIEND);

        // Create tables again
        onCreate(db);
    }
}

Inserting a new record

     This action is write data to database, so we must invoke getWritableDatabase(), use ContentValues to put each record field data and after that, calling insert() to complete our work:

public void addNewFriend(Friend friend) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, friend.getName());
        values.put(KEY_JOB, friend.getJob());

        // inserting this record
        db.insert(TABLE_FRIEND, null, values);
        db.close(); // Closing database connection
    }

Deleting a record

    Like adding action, this also make change with data in database, so use delete command of SQL with key is ID field:

public void deleteFriend(Friend friend) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_FRIEND, KEY_ID + " = ?", new String[]{String.valueOf(friend.getId())});
        db.close();
    }

Updating a record

    Also based on the record ID, we update it's value easily with update() method of SQLiteOpenHelper:

public int updateFriend(Friend friend) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, friend.getName());
        values.put(KEY_JOB, friend.getJob());

        // updating row
        return db.update(TABLE_FRIEND, values, KEY_ID + " = ?", new String[]{String.valueOf(friend.getId())});
    }

Getting all records

   We will use SQL SELECT query and create a Cursor object to browse through all the elements of the table:

public List<Friend> getAllFriends() {
        List<Friend> friendList = new ArrayList<>();

        // select query
        String selectQuery = "SELECT  * FROM " + TABLE_FRIEND;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all table records and adding to list
        if (cursor.moveToFirst()) {
            do {
                Friend friend = new Friend();
                friend.setId(Integer.parseInt(cursor.getString(0)));
                friend.setName(cursor.getString(1));
                friend.setJob(cursor.getString(2));

                // Adding friend to list
                friendList.add(friend);
            } while (cursor.moveToNext());
        }

        return friendList;
    }

    Over here, adding some necessary methods, we have full code for SQLite database file:

package info.devexchanges.androidsqlitedatabase;

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

import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "myFriendDB";

    // Friend table name
    private static final String TABLE_FRIEND = "friend";

    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_JOB = "job";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_FRIEND_TABLE = "CREATE TABLE " + TABLE_FRIEND + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_JOB + " TEXT" + ")";
        db.execSQL(CREATE_FRIEND_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FRIEND);

        // Create tables again
        onCreate(db);
    }

    // Adding a new record (friend) to table
    public void addNewFriend(Friend friend) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, friend.getName());
        values.put(KEY_JOB, friend.getJob());

        // inserting this record
        db.insert(TABLE_FRIEND, null, values);
        db.close(); // Closing database connection
    }

    // Getting All Friends in Table of Database
    public List<Friend> getAllFriends() {
        List<Friend> friendList = new ArrayList<>();

        // select query
        String selectQuery = "SELECT  * FROM " + TABLE_FRIEND;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all table records and adding to list
        if (cursor.moveToFirst()) {
            do {
                Friend friend = new Friend();
                friend.setId(Integer.parseInt(cursor.getString(0)));
                friend.setName(cursor.getString(1));
                friend.setJob(cursor.getString(2));

                // Adding friend to list
                friendList.add(friend);
            } while (cursor.moveToNext());
        }

        return friendList;
    }

    // Updating a record in database table
    public int updateFriend(Friend friend) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, friend.getName());
        values.put(KEY_JOB, friend.getJob());

        // updating row
        return db.update(TABLE_FRIEND, values, KEY_ID + " = ?", new String[]{String.valueOf(friend.getId())});
    }

    // Deleting a record in database table
    public void deleteFriend(Friend friend) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_FRIEND, KEY_ID + " = ?", new String[]{String.valueOf(friend.getId())});
        db.close();
    }

    // getting number of records in table
    public int getContactsCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor dataCount = db.rawQuery("select " + KEY_ID + " from " + TABLE_FRIEND, null);

        int count = dataCount.getCount();
        dataCount.close();
        db.close();

        return count;
    }
}

Usage in Interface

    In this sample project, I will get all records from table of database and set them to a ListView. Further, I design a button in ActionBar (through create options menu) to inserting a new record (see this post to learn about Menus in android). Firstly, make it's layout which contains only a ListView like this:
    And this is completed programmatically code for the Activity. In this, the we locate the option menu in ActionBar and handle it event (inserting a new record):

package info.devexchanges.androidsqlitedatabase;

import android.content.DialogInterface;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {

    private ListView listView;
    private ListViewAdapter adapter;
    private DatabaseHelper databaseHelper;
    private List<Friend> friendList;
    private TextView title;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listView = (ListView) findViewById(R.id.list_view);
        title = (TextView)findViewById(R.id.total);

        databaseHelper = new DatabaseHelper(this);
        friendList = new ArrayList<>();
        reloadingDatabase(); //loading table of DB to ListView
    }

    public void reloadingDatabase() {
        friendList = databaseHelper.getAllFriends();
        if (friendList.size() == 0) {
            Toast.makeText(this, "No record found in database!", Toast.LENGTH_SHORT).show();
            title.setVisibility(View.GONE);
        }
        adapter = new ListViewAdapter(this, R.layout.item_listview, friendList, databaseHelper);
        listView.setAdapter(adapter);
        title.setVisibility(View.VISIBLE);
        title.setText("Total records: " + databaseHelper.getContactsCount());
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);

        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        if (item.getItemId() == R.id.add) {
            addingNewFriendDialog();

            return true;
        }
        return super.onOptionsItemSelected(item);
    }

    private void addingNewFriendDialog() {
        AlertDialog.Builder alertDialog = new AlertDialog.Builder(MainActivity.this);
        alertDialog.setTitle("Add a new Friend");

        LinearLayout layout = new LinearLayout(this);
        layout.setPadding(10, 10, 10, 10);
        layout.setOrientation(LinearLayout.VERTICAL);

        final EditText nameBox = new EditText(this);
        nameBox.setHint("Name");
        layout.addView(nameBox);

        final EditText jobBox = new EditText(this);
        jobBox.setHint("job");
        layout.addView(jobBox);

        alertDialog.setView(layout);

        alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {

            @Override
            public void onClick(DialogInterface dialog, int which) {
                Friend friend = new Friend(getText(nameBox), getText(jobBox));
                databaseHelper.addNewFriend(friend);

                reloadingDatabase(); //reload the db to view
            }
        });

        alertDialog.setNegativeButton("Cancel", null);

        //show alert
        alertDialog.show();
    }

    //get text available in TextView/EditText
    private String getText(TextView textView) {
        return textView.getText().toString().trim();
    }
}

    In each ListView row, we have a edit button (to update row information) and a delete button to remove a record from table. So, we custom an adapter as a subclass of ArrayAdapter and handle multiple objects event (click) located on each row. We have this full code, more details, see my comments in it:

package info.devexchanges.androidsqlitedatabase;

import android.app.Activity;
import android.content.DialogInterface;
import android.support.v7.app.AlertDialog;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.TextView;
import android.widget.Toast;

import java.util.List;

public class ListViewAdapter extends ArrayAdapter<Friend> {

    private MainActivity activity;
    private DatabaseHelper databaseHelper;
    private List<Friend> friendList;

    public ListViewAdapter(MainActivity context, int resource, List<Friend> objects, DatabaseHelper helper) {
        super(context, resource, objects);
        this.activity = context;
        this.databaseHelper = helper;
        this.friendList = objects;
    }

    @Override
    public View getView(final int position, View convertView, ViewGroup parent) {
        ViewHolder holder;
        LayoutInflater inflater = (LayoutInflater) activity.getSystemService(Activity.LAYOUT_INFLATER_SERVICE);

        if (convertView == null) {
            convertView = inflater.inflate(R.layout.item_listview, parent, false);
            holder = new ViewHolder(convertView);
            convertView.setTag(holder);
        } else {
            holder = (ViewHolder) convertView.getTag();
        }

        holder.name.setText(getItem(position).getName());

        //Delete an item
        holder.btnDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                databaseHelper.deleteFriend(getItem(position)); //delete in db
                Toast.makeText(activity, "Deleted!", Toast.LENGTH_SHORT).show();

                //reload the database to view
                activity.reloadingDatabase();
            }
        });

        //Edit/Update an item
        holder.btnEdit.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("Update a Friend");

                LinearLayout layout = new LinearLayout(activity);
                layout.setPadding(10, 10, 10, 10);
                layout.setOrientation(LinearLayout.VERTICAL);

                final EditText nameBox = new EditText(activity);
                nameBox.setHint("Name");
                layout.addView(nameBox);

                final EditText jobBox = new EditText(activity);
                jobBox.setHint("job");
                layout.addView(jobBox);

                nameBox.setText(getItem(position).getName());
                jobBox.setText(getItem(position).getJob());

                alertDialog.setView(layout);

                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {

                    @Override
                    public void onClick(DialogInterface dialog, int which) {
                        Friend friend = new Friend(nameBox.getText().toString(), jobBox.getText().toString());
                        friend.setId(getItem(position).getId());
                        databaseHelper.updateFriend(friend); //update to db
                        Toast.makeText(activity, "Updated!", Toast.LENGTH_SHORT).show();

                        //reload the database to view
                        activity.reloadingDatabase();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);

                //show alert dialog
                alertDialog.show();
            }
        });

        //show details when each row item clicked
        convertView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("Friend ");

                LinearLayout layout = new LinearLayout(activity);
                layout.setPadding(10, 10, 10, 10);
                layout.setOrientation(LinearLayout.VERTICAL);

                TextView nameBox = new TextView(activity);
                layout.addView(nameBox);

                TextView jobBox = new TextView(activity);
                layout.addView(jobBox);

                nameBox.setText("Friend name: " + getItem(position).getName());
                jobBox.setText("Friend job: " + getItem(position).getJob());

                alertDialog.setView(layout);
                alertDialog.setNegativeButton("OK", null);

                //show alert
                alertDialog.show();
            }
        });

        return convertView;
    }

    private static class ViewHolder {
        private TextView name;
        private View btnDelete;
        private View btnEdit;

        public ViewHolder (View v) {
            name = (TextView)v.findViewById(R.id.item_name);
            btnDelete = v.findViewById(R.id.delete);
            btnEdit = v.findViewById(R.id.edit);
        }
    }
}

    And layout (xml file) for each ListView item:     Menu file use in main activity:     Running project, we will have a list of records which populated from database table:

    By clicking the "adding button (+)", an AlertDialog to inserting a new record will be shown:

    Clicking update icon at any row, another AlertDialog appeared to update item information:

    Note: In order to handle input action better, you should use a custom Dialog with layout instead of AlertDialog.

Conclusions

    Through this post, I've created a simple database with 1 table, readers can figure out the way to make a new one with multiple tables. I hope that this article will be helpful to people new to the Android platform to understand developing database applications for Android before starting to deal with more complex problems. Moreover, there are some external libraries can help us to make SQLite database easily such as ORMLite, see my previous post to learn about it.

References:

- SQLite Android doc: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html
- SQLite Android official guide: http://developer.android.com/training/basics/data-storage/databases.html
- Readers can read a good tut about database with multiple tables at AndroidHive.
- Another good tutorial from Guru99.

Previous Chapter

Table Of  Contents

Next Chapter

Managing SQLite database with ORMLite in Android

    SQLite is the only relational database management system was supported in popular Mobile OS platforms. Android provides full support for SQLite databases. Any databases you create will be accessible by name to any class in the application, but not outside the application. With creating a subclass of SQLiteOpenHelper, we can initialize a database from simple to complicate in our app. But by this "classic way", it's will very hard in coding and deploying, so we should use a "short cut" to create and manage the database by use an external libary.
    The most powerful library in this case is ORMLite. It provides some simple, lightweight functionality for persisting Java objects to SQL databases while avoiding the complexity and overhead of more standard ORM packages. Moreover, it also solved well the problems concerning the relationship between the entities/tables.
    Today, in this post, I would like to build a simple project which creating/managing a database with ORMLite, resolved entities relationships by ForeignCollection object. See this DEMO VIDEO for output:

Additional Information

This post is part of a series called Android TOP useful libraries

Making pull to refresh layout with Ultra-Pull-To-Refresh library

Making Tab Bar layout with ViewPagerIndicator

Before Start

    Project description:
- I have 2 POJOs: Cat and Kitten. ORMLite will persist theme to 2 corresponding tables.
- Relationship: Cat : Kitten = 1 : n.
    Adding dependencies to app/build.gradle befor start coding:

    compile 'com.j256.ormlite:ormlite-core:4.48'
    compile 'com.j256.ormlite:ormlite-android:4.48'

- Project structure:

Creating Database/Tables

    As note above, ORMLite persists Java objects to database tables. Through @DatabaseField annotation, each POJO property will be persist to each table field. In this 2 models, the 1:n relationships were resolved by using ForeignCollection:

package info.devexchanges.ormlite.model;

import com.j256.ormlite.dao.ForeignCollection;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.ForeignCollectionField;

import java.util.ArrayList;
import java.util.List;

public class Cat {
    @DatabaseField(generatedId=true)
    private int id;

    @DatabaseField (columnName = "name")
    private String name;

    @ForeignCollectionField
    private ForeignCollection<Kitten> kittens;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public void setKittens(ForeignCollection<Kitten> items) {
        this.kittens = items;
    }

    public List<Kitten> getKittens() {
        ArrayList<Kitten> itemList = new ArrayList<>();
        for (Kitten item : kittens) {
            itemList.add(item);
        }
        return itemList;
    }

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

package info.devexchanges.ormlite.model;

import com.j256.ormlite.field.DatabaseField;

public class Kitten {

    @DatabaseField(generatedId=true)
    private int id;

    @DatabaseField (columnName = "name")
    private String name;

    @DatabaseField(foreign=true, foreignAutoRefresh=true)
    private Cat cat;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Cat getCat() {
        return cat;
    }

    public void setCat(Cat cat) {
        this.cat = cat;
    }
}

Building Database Helper and Controller classes

    Like the "original way", we also create a subclass of ORMLiteOpenHelper to initializing database. In this, we can get all tables records to DAO objects (the definition of the Database Access Objects that handle the reading and writing a class from the database), display/update/delete Cat or Kitten also through it's DAO:

package info.devexchanges.ormlite.database;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

import info.devexchanges.ormlite.model.Cat;
import info.devexchanges.ormlite.model.Kitten;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

    // name of the database file for your application -- change to something appropriate for your app
    private static final String DATABASE_NAME = "cat.db";

    // any time you make changes to your database objects, you may have to increase the database version
    private static final int DATABASE_VERSION = 1;

    // the DAO object we use to access the SimpleData table
    private Dao<Cat, Integer> catDAO = null;
    private Dao<Kitten, Integer> kittenDAO = null;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, Cat.class);
            TableUtils.createTable(connectionSource, Kitten.class);

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
        try {
            List<String> allSql = new ArrayList<>();
            for (String sql : allSql) {
                db.execSQL(sql);
            }
        } catch (SQLException e) {
            Log.e(DatabaseHelper.class.getName(), "exception during onUpgrade", e);
            throw new RuntimeException(e);
        }

    }

    public Dao<Cat, Integer> getCatsDAO() {
        if (catDAO == null) {
            try {
                catDAO = getDao(Cat.class);
            } catch (java.sql.SQLException e) {
                e.printStackTrace();
            }
        }
        return catDAO;
    }

    public Dao<Kitten, Integer> getKittenDAO() {
        if (kittenDAO == null) {
            try {
                kittenDAO = getDao(Kitten.class);
            } catch (java.sql.SQLException e) {
                e.printStackTrace();
            }
        }
        return kittenDAO;
    }
}

    We need one more class that will encapsulate all the interactions with the DAO in the same package, let's create a basic code for it, and then we will add methods gradually as we need them in code. Through this class, define the way to display/update/delete table records/POJOs through DAO. In Activities/Adapter code, we will mainly work with this class:

package info.devexchanges.ormlite.database;

import android.content.Context;

import com.j256.ormlite.stmt.DeleteBuilder;

import java.sql.SQLException;
import java.util.ArrayList;

import info.devexchanges.ormlite.model.Cat;
import info.devexchanges.ormlite.model.Kitten;

public class DatabaseManager {

    private static DatabaseManager instance;
    private DatabaseHelper helper;

    public static void init(Context ctx) {
        if (null == instance) {
            instance = new DatabaseManager(ctx);
        }
    }

    static public DatabaseManager getInstance() {
        return instance;
    }

    private DatabaseManager(Context ctx) {
        helper = new DatabaseHelper(ctx);
    }

    public DatabaseHelper getHelper() {
        return helper;
    }

    /**
     * Get all customer in db
     *
     * @return
     */
    public ArrayList<Cat> getAllCats() {
        ArrayList<Cat> cats = null;
        try {
            cats = (ArrayList<Cat>) getHelper().getCatsDAO().queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cats;
    }

    public void addCat(Cat cat) {
        try {
            getHelper().getCatsDAO().create(cat);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void refreshCat(Cat cat) {
        try {
            getHelper().getCatsDAO().refresh(cat);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateCat(Cat wishList) {
        try {
            getHelper().getCatsDAO().update(wishList);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void deleteCat (int catId) {
        try {
            DeleteBuilder<Cat, Integer> deleteBuilder = getHelper().getCatsDAO().deleteBuilder();
            deleteBuilder.where().eq("id", catId);
            deleteBuilder.delete();

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

    public Kitten newKitten() {
        Kitten kitten = new Kitten();
        try {
            getHelper().getKittenDAO().create(kitten);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return kitten;
    }

    public Kitten newKittenAppend(Kitten kitten) {
        try {
            getHelper().getKittenDAO().create(kitten);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return kitten;
    }

    public void updateKitten(Kitten item) {
        try {
            getHelper().getKittenDAO().update(item);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ArrayList<Kitten> getAllKittens() {
        ArrayList<Kitten> kittenArrayList = null;
        try {
            kittenArrayList = (ArrayList<Kitten>) getHelper().getKittenDAO().queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return kittenArrayList;
    }

    public void deleteKitten (int kittenId) {
        try {
            DeleteBuilder<Kitten, Integer> deleteBuilder = getHelper().getKittenDAO().deleteBuilder();
            deleteBuilder.where().eq("id", kittenId);
            deleteBuilder.delete();

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

Display data to Views

    At main activity, we will get data from database to views (ExpandableListView). Firstly, at onCreate(), initialize database by this line:

DatabaseManager.init(this);

    Get all tables records by DAO and save to ArrayList objects:

        //get all data to Lists
        ArrayList<Cat> catArrayList = DatabaseManager.getInstance().getAllCats();

    No more specials, we have full code for main activity:

package info.devexchanges.ormlite.activity;

import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ExpandableListView;
import android.widget.ProgressBar;
import android.widget.TextView;

import java.util.ArrayList;
import java.util.List;

import info.devexchanges.ormlite.R;
import info.devexchanges.ormlite.adapter.ExpandableListAdapter;
import info.devexchanges.ormlite.database.DatabaseManager;
import info.devexchanges.ormlite.model.Cat;
import info.devexchanges.ormlite.model.Kitten;

public class MainActivity extends AppCompatActivity {

    private ProgressBar progressBar;
    private ExpandableListView listView;
    private TextView notice;
    private List<Cat> cats;
    private ExpandableListAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        DatabaseManager.init(this);

        notice = (TextView)findViewById(R.id.notice);
        listView = (ExpandableListView)findViewById(R.id.list_item);
        progressBar = (ProgressBar)findViewById(R.id.progress);

        cats = new ArrayList<>();
        //set data to views
        adapter = new ExpandableListAdapter(this, cats);
        listView.setAdapter(adapter);
    }

    @Override
    protected void onResume() {
        super.onResume();
        Log.d("Main", "resume");
        getDataFromDB();
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.main, menu);
        return super.onCreateOptionsMenu(menu);
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        if (item.getItemId() == R.id.add) {
            Intent i = new Intent(this, AddingActivity.class);
            startActivity(i);

            return true;
        }

        return super.onOptionsItemSelected(item);
    }

    public void getDataFromDB() {
        if (cats != null) cats.clear();

        //get all data to Lists
        ArrayList<Cat> catArrayList = DatabaseManager.getInstance().getAllCats();
        for (int i = 0; i < catArrayList.size(); i++) {
            cats.add(catArrayList.get(i));
        }

        if (cats.size() == 0) {
            //no data in database
            listView.setVisibility(View.GONE);
            notice.setText("Database is Empty");
            notice.setVisibility(View.VISIBLE);
        } else {
            adapter.notifyDataSetChanged();
        }
    }
}

    Layout for this activity:

Adding new items

    Like display code above, saving a new Cat object to database through call:

//save new object to db
DatabaseManager.getInstance().addCat(cat);

    And add a Kitten is more complicate:

                        //save to database
                        DatabaseManager.getInstance().newKittenAppend(kitten);
                        DatabaseManager.getInstance().updateKitten(kitten);

    This "adding items" process has been written in an activity, and this is full code for it:

package info.devexchanges.ormlite.activity;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

import info.devexchanges.ormlite.R;
import info.devexchanges.ormlite.database.DatabaseManager;
import info.devexchanges.ormlite.model.Cat;
import info.devexchanges.ormlite.model.Kitten;

public class AddingActivity extends AppCompatActivity {

    private View btnAddCat;
    private View btnAddKitten;
    private View btnOK;
    private Spinner spinner;
    private ViewGroup layoutAddCat;
    private ViewGroup layoutAddKitten;
    private ViewGroup layoutButtons;
    private EditText editCat;
    private EditText editKitten;
    private View btnCancel;
    private List<Cat> cats;
    private boolean havingCat = true;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_adding);

        btnAddCat = findViewById(R.id.btn_add_cat);
        btnOK = findViewById(R.id.btn_ok);
        layoutAddKitten = (ViewGroup) findViewById(R.id.ll_kit);
        layoutAddCat = (ViewGroup) findViewById(R.id.ll_cat);
        layoutButtons = (ViewGroup) findViewById(R.id.ll_buttons);
        btnCancel = findViewById(R.id.btn_cancel);
        spinner = (Spinner) findViewById(R.id.spinner);
        btnAddKitten = findViewById(R.id.btn_add_kitten);
        editCat = (EditText) findViewById(R.id.txt_name);
        editKitten = (EditText) findViewById(R.id.txt_kitten_name);

        btnAddKitten.setOnClickListener(onAddKittenListener());
        btnAddCat.setOnClickListener(onAddCatListner());
        btnCancel.setOnClickListener(onCancelListener());
        btnOK.setOnClickListener(onConfirmListener());
    }

    private View.OnClickListener onConfirmListener() {
        return new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if (layoutAddCat.getVisibility() == View.VISIBLE) {
                    if (editCat.getText().toString().trim().equals("")) {
                        Toast.makeText(getBaseContext(), "Please input cat name", Toast.LENGTH_SHORT).show();
                    } else {
                        Cat cat = new Cat();
                        cat.setName(editCat.getText().toString().trim());

                        //save new object to db
                        DatabaseManager.getInstance().addCat(cat);
                    }
                } else if (layoutAddKitten.getVisibility() == View.VISIBLE) {
                    if (editKitten.getText().toString().trim().equals("")) {
                        Toast.makeText(getBaseContext(), "Please input kitten name", Toast.LENGTH_SHORT).show();
                    } else {
                        Kitten kitten = new Kitten();
                        Cat cat = (Cat) spinner.getSelectedItem();
                        kitten.setName(editKitten.getText().toString().trim());
                        kitten.setCat(cat);

                        //save to database
                        DatabaseManager.getInstance().newKittenAppend(kitten);
                        DatabaseManager.getInstance().updateKitten(kitten);
                    }
                }
                goneLayouts();
            }
        };
    }

    private View.OnClickListener onCancelListener() {
        return new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                goneLayouts();
            }
        };
    }

    private View.OnClickListener onAddCatListner() {
        return new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                goneLayouts();
                layoutAddCat.setVisibility(View.VISIBLE);
                layoutButtons.setVisibility(View.VISIBLE);
            }
        };
    }

    private View.OnClickListener onAddKittenListener() {
        return new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if (!havingCat) {
                    Toast.makeText(getBaseContext(), "None Cat in DB, please add cat first", Toast.LENGTH_SHORT).show();
                } else {
                    goneLayouts();

                    cats = DatabaseManager.getInstance().getAllCats();
                    if (cats.size() == 0) {
                        havingCat = false;
                    } else {
                        //set spinner adapter
                        ArrayAdapter<Cat> adapter = new ArrayAdapter<>(AddingActivity.this,
                                android.R.layout.simple_dropdown_item_1line, cats);
                        spinner.setAdapter(adapter);
                    }
                    layoutAddKitten.setVisibility(View.VISIBLE);
                    layoutButtons.setVisibility(View.VISIBLE);
                }
            }
        };
    }

    private void goneLayouts() {
        layoutAddCat.setVisibility(View.GONE);
        layoutAddKitten.setVisibility(View.GONE);
        layoutButtons.setVisibility(View.GONE);
        editCat.setText("");
        editKitten.setText("");
    }
}

    And it's layout:
    Output for this screen:

Editing/Updating an item

    From now, we open the ExpandableListview adapter class - extends from BaseExpendableListAdapter - (ExpandableListAdapter.java) to perform code for editing action. We can modify both Cat and Kitten name by clicking Edit Button in each row/header:

private View.OnClickListener onEditCatListener(final Cat cat, final int position) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("EDIT Cat");
                alertDialog.setMessage("Please type a new cat name");

                final EditText input = new EditText(activity);
                LinearLayout.LayoutParams lp = new LinearLayout.LayoutParams(
                        LinearLayout.LayoutParams.MATCH_PARENT,
                        LinearLayout.LayoutParams.MATCH_PARENT);
                input.setLayoutParams(lp);
                input.setText(cat.getName());

                alertDialog.setView(input);
                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {

                        //update database with new cat name
                        cat.setName(input.getText().toString().trim());
                        DatabaseManager.getInstance().updateCat(cat);

                        //update views
                        cats.get(position).setName(input.getText().toString().trim());
                        notifyDataSetChanged();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    private View.OnClickListener onEditKittenListener(final Kitten kitten, final int groupPos,
                                                      final int childPos) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("EDIT Kitten");
                alertDialog.setMessage("Please type a new kitten name");

                final EditText input = new EditText(activity);
                LinearLayout.LayoutParams lp = new LinearLayout.LayoutParams(
                        LinearLayout.LayoutParams.MATCH_PARENT,
                        LinearLayout.LayoutParams.MATCH_PARENT);
                input.setLayoutParams(lp);
                input.setText(kitten.getName());

                alertDialog.setView(input);
                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {

                        //update database
                        kitten.setName(input.getText().toString().trim());
                        DatabaseManager.getInstance().updateKitten(kitten);

                        //update views
                        cats.get(groupPos).getKittens().get(childPos).setName(input.getText().toString());
                        notifyDataSetChanged();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    Output of this action:

Deleting an item

    Similar with editing action, we still coding in adapter class. This action was done by clicking Delete Button:

private View.OnClickListener onDeleteKittenListener(final int groupPosition, final int childPosition) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("Delete?");
                alertDialog.setMessage("Are you sure to delete?");

                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {
                        //delete in database
                        DatabaseManager.getInstance().deleteKitten(getChild(groupPosition, childPosition).getId());

                        //update views
                        activity.getDataFromDB();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    private View.OnClickListener onDeleteCatListener(final int groupPosition) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("Delete?");
                alertDialog.setMessage("Are you sure to delete?");

                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {
                        //delete in database
                        DatabaseManager.getInstance().deleteCat(getGroup(groupPosition).getId());

                        //update views
                        cats.remove(groupPosition);
                        notifyDataSetChanged();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    Output:

Final code

    The most important class in Expandable adapter. Overriding some requirement methods, we finally have full code:

package info.devexchanges.ormlite.adapter;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseExpandableListAdapter;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.TextView;

import java.util.List;

import info.devexchanges.ormlite.R;
import info.devexchanges.ormlite.activity.MainActivity;
import info.devexchanges.ormlite.database.DatabaseManager;
import info.devexchanges.ormlite.model.Cat;
import info.devexchanges.ormlite.model.Kitten;

public class ExpandableListAdapter extends BaseExpandableListAdapter {

    private MainActivity activity;
    private List<Cat> cats;

    public ExpandableListAdapter(MainActivity context, List<Cat> rows) {
        this.activity = context;
        this.cats = rows;
    }

    @Override
    public Kitten getChild(int groupPosition, int childPosititon) {
        return this.cats.get(groupPosition).getKittens().get(childPosititon);
    }

    @Override
    public long getChildId(int groupPosition, int childPosition) {
        return childPosition;
    }

    @Override
    public int getChildrenCount(int groupPosition) {
        return getGroup(groupPosition).getKittens().size();
    }

    @Override
    public Cat getGroup(int groupPosition) {
        return this.cats.get(groupPosition);
    }

    @Override
    public int getGroupCount() {
        return cats.size();
    }

    @Override
    public long getGroupId(int groupPosition) {
        return groupPosition;
    }

    @Override
    public View getChildView(int groupPosition, final int childPosition,
                             boolean isLastChild, View convertView, ViewGroup parent) {
        ChildViewHolder holder;
        LayoutInflater inflater = (LayoutInflater) activity.getSystemService(Activity.LAYOUT_INFLATER_SERVICE);

        if (convertView == null) {
            convertView = inflater.inflate(R.layout.layout_child_list, parent, false);
            holder = new ChildViewHolder(convertView);
            convertView.setTag(holder);
        } else {
            holder = (ChildViewHolder) convertView.getTag();
        }

        holder.textView.setText(getChild(groupPosition, childPosition).getName());
        holder.btnEdit.setOnClickListener(onEditKittenListener(getChild(groupPosition, childPosition),
                groupPosition, childPosition));
        holder.btnDelete.setOnClickListener(onDeleteKittenListener(groupPosition, childPosition));

        return convertView;
    }

    @Override
    public View getGroupView(int groupPosition, boolean isExpanded, View convertView, ViewGroup parent) {
        HeaderViewHolder holder;
        LayoutInflater inflater = (LayoutInflater) activity.getSystemService(Activity.LAYOUT_INFLATER_SERVICE);

        if (convertView == null) {
            convertView = inflater.inflate(R.layout.layout_header_list, parent, false);
            holder = new HeaderViewHolder(convertView);
            convertView.setTag(holder);
        } else {
            holder = (HeaderViewHolder) convertView.getTag();
        }

        holder.textView.setText(getGroup(groupPosition).getName());
        holder.btnEdit.setOnClickListener(onEditCatListener(getGroup(groupPosition), groupPosition));
        holder.btnDelete.setOnClickListener(onDeleteCatListener(groupPosition));

        return convertView;
    }

    private View.OnClickListener onDeleteKittenListener(final int groupPosition, final int childPosition) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("Delete?");
                alertDialog.setMessage("Are you sure to delete?");

                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {
                        //delete in database
                        DatabaseManager.getInstance().deleteKitten(getChild(groupPosition, childPosition).getId());

                        //update views
                        activity.getDataFromDB();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    private View.OnClickListener onDeleteCatListener(final int groupPosition) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("Delete?");
                alertDialog.setMessage("Are you sure to delete?");

                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {
                        //delete in database
                        DatabaseManager.getInstance().deleteCat(getGroup(groupPosition).getId());

                        //update views
                        cats.remove(groupPosition);
                        notifyDataSetChanged();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    private View.OnClickListener onEditCatListener(final Cat cat, final int position) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("EDIT Cat");
                alertDialog.setMessage("Please type a new cat name");

                final EditText input = new EditText(activity);
                LinearLayout.LayoutParams lp = new LinearLayout.LayoutParams(
                        LinearLayout.LayoutParams.MATCH_PARENT,
                        LinearLayout.LayoutParams.MATCH_PARENT);
                input.setLayoutParams(lp);
                input.setText(cat.getName());

                alertDialog.setView(input);
                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {

                        //update database with new cat name
                        cat.setName(input.getText().toString().trim());
                        DatabaseManager.getInstance().updateCat(cat);

                        //update views
                        cats.get(position).setName(input.getText().toString().trim());
                        notifyDataSetChanged();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    private View.OnClickListener onEditKittenListener(final Kitten kitten, final int groupPos,
                                                      final int childPos) {
        return new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                AlertDialog.Builder alertDialog = new AlertDialog.Builder(activity);
                alertDialog.setTitle("EDIT Kitten");
                alertDialog.setMessage("Please type a new kitten name");

                final EditText input = new EditText(activity);
                LinearLayout.LayoutParams lp = new LinearLayout.LayoutParams(
                        LinearLayout.LayoutParams.MATCH_PARENT,
                        LinearLayout.LayoutParams.MATCH_PARENT);
                input.setLayoutParams(lp);
                input.setText(kitten.getName());

                alertDialog.setView(input);
                alertDialog.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialogInterface, int i) {

                        //update database
                        kitten.setName(input.getText().toString().trim());
                        DatabaseManager.getInstance().updateKitten(kitten);

                        //update views
                        cats.get(groupPos).getKittens().get(childPos).setName(input.getText().toString());
                        notifyDataSetChanged();
                    }
                });

                alertDialog.setNegativeButton("Cancel", null);
                alertDialog.show();
            }
        };
    }

    @Override
    public boolean hasStableIds() {
        return false;
    }

    @Override
    public boolean isChildSelectable(int groupPosition, int childPosition) {
        return true;
    }

    private static class ChildViewHolder {
        private TextView textView;
        private View btnEdit;
        private View btnDelete;

        public ChildViewHolder(View v) {
            textView = (TextView) v.findViewById(R.id.kitten_name);
            btnEdit = v.findViewById(R.id.edit);
            btnDelete = v.findViewById(R.id.delete);
        }
    }

    private static class HeaderViewHolder {
        private TextView textView;
        private View btnEdit;
        private View btnDelete;

        public HeaderViewHolder(View v) {
            btnDelete = v.findViewById(R.id.delete);
            textView = (TextView) v.findViewById(R.id.cat_name);
            btnEdit = v.findViewById(R.id.edit);
        }
    }
}

    Now, adding some necessary files to comple our project:
- ExpandableListView group layout, showing Cat name:
- ExpandableListView child layout, displaying Kitten name:
    Main screen after app launching:

- Strings resource:

Conclusions and References

    Through this post, I presented the solution to create and manage the SQLite through ORMLite. With this tools, I hope you can find out a simple way to using database, easy in code and maintainance, the "core processes" was deploy in libary code, shorten the time and reduce complexity for our work. By now, you can read official ORMLite docs page or see it's source code on Github to deep understanding about this powerful libary/tool, . Finally, thanks for reading and please subscribe me for newest tutorials!