Android SQLite for Beginners

In this post, I will cover the fundamentals of Android SQLite from a beginner's perspective. In the next post, I will provide a similar introduction to Content Provider and in the last part of the series, I will show how to use Android SQLite and Content Provider to build a Note taking app.

To benefit from this tutorial, it would be ideal that you have covered some fundamentals of Android development. If you are completely new to Android development, you will benefit from my other introductory tutorials to Android development. The focus of this tutorial will be on data persistence in Android using SQLite.

Introduction to SQLite

Let us get the official definition of SQLite Database:

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

http://sqlite.com/about.html

Let us review that official definition of SQLite above:

1. SQLite is a SQL compliant database (very important).
2. It does not need a separate server to run.
3. It does not require separate installation. If the platform you are working with needs SQLite, then it is already included just like in Android.
4. It is free.

What is the SQL in SQLite – The SQL in SQLite stands for Structured Query Language. It is the standard language for communicating with a database and is backed by the American National Standards Institute. Many vendors such as Oracle, Microsoft, Sybase have adopted SQL for their RDBMSs.

What does that mean – that means that if you want to learn about Android SQLite, you will have to actually learn a lot of SQL. The good news is that a lot of this SQL skill is transferable to other database systems.

SQLite in Android

So SQLite is some type of database, why do we need it in Android? Well, we use it for data persistence in Android. And what is data persistence, you may ask? Data persistence is a fancy and more technically correct way of saying data storage. The data you persist are the data that you do not want to lose each time the users of your app close the app.

SQLite predates Android, it is used in major applications other than Android. The designers of Android chose SQLite to be the main data persistence component for Android because of its easy of use, cost, licensing among other reasons. For you as a developer, the biggest benefit of using SQLite in Android is the small number of installation steps required to install SQLite in Android – the actual number of steps to install SQLite in Android is zero because it is already installed. You just have to use it.

SQLite is not the only way to save data in Android, there are other options and each has its own use case. And those are:

1. Shared Preference – This tool is used to save primitive data in Android. Primitive data types are data that has been decomposed into units that are native to the programming language (Java in this case). If you place an order from an online store, your order number is a primitive because it is a Long, or Double, you cannot directly save your Order because it is not a native Java data type.
2. Device Storage – If you want to save the pictures of your recent trip to your device, it may be hard to reduce these to the units that Shared Preference can handle, in this case, you can simply dump those large data on the device storage.
3. Network – In some cases, you may have data that is not efficient to store in the local device and in such case, storing the data on the network is an option. There are a few terminology associated with network data access in Android.
   1. REST – It is likely that you will be accessing your networked data store from Android using the internet. REpresentational State Transfer (REST) is a common standard that you can follow to access network resources from your Android app over HTTP which is the data transfer protocol of the internet. If the network that you want to access from your Android app is yours, then that data has to be formatted into or presented by a web service.
   2. MBASS – If you do not want to become a system admin in other to implement network storage in your Android app, then you may want to save time and effort by using one of the available commercial services that provides network storage and some other utilities to mobile apps. These are called mobile backend as a service or MBASS.

So SQLite is easy – right? – yes, Android SQLite is easy to use! However “easy” maybe relative, what is relatively easy to you may be challenging for me. This point is not lost on the designers of Android so they created helper classes that make it easier to get started with SQLite in Android, so let us briefly examine those classes.

1. SQLiteOpenHelper – This is the most important class that you will work with in Android SQLite. You will use SQLiteOpenHelper to create and upgrade your SQLite database. In other words,SQLiteOpenHelper removes the effort required to install and configure database in other systems.
2. SQLiteDatabase – This is the actual database where your data is stored. When you created your database with SQLiteOpenHelper class, it sets everything in motion to create your database but holds off until you are ready to use that database. And the way SQLiteOpenHelper knows that you are ready to use your database is when you access that database either with getReadableDatabase() orgetWritableDatabase() for read and write operations respectively.
3. Cursor – The reason you store your data in a database is so you can access them later. That access is called a query and a successful query will return a list of the items you queried for. If that list is so long, your Android device may choke if you want to access all of the items in the returned result. This is where the Cursor comes in, the list of the items that you queries for are wrapped in a Cursor and the Cursor hands them over to you in batches of any number.

How to Create Android SQLite Database?

To create a SQLite database in Android, you create a standard Java class file. Yep, just a another class file. To make this class file a database class file, you extend the SQLiteOpenHelper class. As soon as you extend this class, you will be required to perform three actions which are:

1. Constructor – You will be required to implement a constructor that chains up to the super class and provides the following parameters to that constructor
   1. Context – Your familiar context is needed for a host of reasons.
   2. String – The name you want to give to your database such as “awesome_database.anything” commonly .sql or .db are used.
   3. CursorFactory – I am yet to see an example where this parameter is not set to null, so go ahead and null it.
   4. Int – this is the version of your database, you may want to start with 1.
2. onCreate() – You will be required to implement this method because it is called the first time the database is created. Normally, this is where you will want to provide SQL statements to create your database tables.
3. onUpgrade() – You will be required to implement this method because it is called when you upgrade your database. How does Android SQlitedatabase know that you want to upgrade your database – when your database version changes.

Here is an example of a blank database class that inherits from SQLiteOpenHelper class, if these do not make sense, check back for my next two posts that show an example of how to use these.

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "simple_note_app.db";
    private static final int DATABASE_VERSION = 1;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("some sql statement to create table");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("some sql statement to do something");
    }
}

SQL, Yet Another Language to Learn

When you started learning Android development, you were told you need to learn Java, then someone said something about XML and layouts, well here is yet another language to learn – SQL. We have mentioned it quite a bit and you need to get at least the basics of Structured Query Language if you want to create data driven apps with Android SQLite.

In the onCreate() method of the above code snippet, I used the String “some sql statement to create table” as place holder for the actual SQL statement. For this tutorial series, I will create a SimpleNoteApp for demo. This app will have one table for saving Notes. The table will have familiar columns such as the title of the note and the content of the note.

private static final String CREATE_TABLE_NOTE = "create table note"
            + "("
            + "_id" + " integer primary key autoincrement, "
            + "title" + " text not null, "
            + "content" + " text not null, "
            + "modified_time" + " integer not null, "
            + "created_time" + " integer not null " + ")";

I have spread the above SQL statement across multiple lines for readability and I hold the SQL statement in aString variable and with this, I can now update the onCreate() method to this:

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

And that is, the first time that I instantiate this class the database will be created with the SQL statement above, if there is no syntax error. Speaking of syntax error, it is a common source of frustration with writing SQL statements, you have to cross your “i”s and dot your “t”s sort of for it to work. To eliminate syntax error, it is often a good practice to define some of your String literals in a static Java variables like this:

public static final String COLUMN_ID = "_id";
    public final static String COLUMN_NAME = "name";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_CONTENT = "content";
    public static final String COLUMN_MODIFIED_TIME = "modified_time";
    public static final String COLUMN_CREATED_TIME = "created_time";

I normally define my static Strings in a Constant.java file and with that, I can now update the table createstatement like this:

private static final String CREATE_TABLE_NOTE = "create table "
            + Constants.NOTES_TABLE
            + "("
            + Constants.COLUMN_ID + " integer primary key autoincrement, "
            + Constants.COLUMN_TITLE + " text not null, "
            + Constants.COLUMN_CONTENT + " text not null, "
            + Constants.COLUMN_MODIFIED_TIME + " integer not null, "
            + Constants.COLUMN_CREATED_TIME + " integer not null " + ")";

With this, we can now go to the onCreate() method of the Main Activity or anywhere we want to use the database and instantiate it like this:

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

        DatabaseHelper databaseHelper = new DatabaseHelper(this);
    }

Where is the Database?

If you run your app now and there is no error message, then that means the database was created. But where is the database? Let’s see, we instantiated our SQLiteOpenHelper derived class and we provided SQL statement to create the table – and there is no database? Well, there is no database because none was created!

To actually create a database, we have to call one of SQLitedatabase methods getReadableDatabase() orgetWritableDatabase(). So if you want to create the database, you have to go back to the onCreate()method of Main Activity and update it like this:

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

        DatabaseHelper databaseHelper = new DatabaseHelper(this);
        databaseHelper.getWritableDatabase();
    }

Now if you run the app again and there is no error message, then a database was truly created this time. But where is it?

You can use SQLiteManager to see your created database.

First installed SQLiteManager in your PC. then Open  Android Device Monitor from your AndroidStudio : "Android Device Monitor >data>data>your Database Name>Your Table" . 
select your database  and pull  database from Android Device Monitor , save it desktop. 

Now  open SQLiteManager  and browse your saved database . then you can see your details database.

Please comment if  any query.