Android Basic Training Course: Creating and Managing SQLite Database

Android Basic Training Course: Creating and Managing SQLite Database

Tuesday, January 05, 2016
    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
Hong Thai

Share


Previous post
« Prev Post
Next post
Next Post »
Subscribe to: Post Comments (Atom)