Android SQLite for Beginners

In this post, I will cover the fundamentals of Android SQLite from a beginners 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.

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.

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 loose each time the users of your app close the app.

SQLite predates Android, it is used in other 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 tools 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 maybe hard to reduce these to the units that Shared Preference can handle, in this case you can simple 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 maybe challenging for me. This point is not lost on the designers of Android so they created helper classes that makes 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() or getWritableDatabase() 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.

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 provide 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 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 does not make sense, check back for my next two post that shows 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");
    }
}

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 a String 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 create statement 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_COLOR + " integer 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 any where 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);
    }

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 method getReadableDatabase() or getWritableDatabase(). 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 the command line to view the database that you just created to make sure that it was created successfully before you start writing the code to interact with it.

Command Line
Android SQLite comes with a command line program called sqlite3 that you can use to manage SQLite databases created in an Android app. You can learn all the commands you can run with sqlite3 here  . Follow these steps to use sqlite3

1. Step 1 – Go to the root of your Android SDK installation, and then to platform-tools, here is my example 
2. Step 2 – Open a command line prompt from this location
3. Step 3 – List device – in the command line type

   adb devices

     to show the list of your connected Android devices (if you do not see any device you will not be able to connect).
4. Step 4 – Connect a shell to your device – type the command

   adb -s device_serial_number shell

     to connect to the device that you want to connect to. If you only have one device connected, then you can simply type

   adb shell 

5. Step 5 – Navigate to the folder that contains the database which usually is data/data/”you_app_package_name”/databases
6. Step 6 – Type ls to show the database like below and you should see the database file that we created like this: 
7. Step 7 – There you can clearly see the database that we created, it is empty but this is good mile stone.
8. Step 8 – Show the tables – Connect to that database with sqlite3 by typing sqlite3 “database_name” enter and then type “.tables” to show the tables like this. 

There you have it, proof that a table has been created. This will only work in an Emulator or rooted device. OEM devices may not give you access to the database file.

This has been a quick summary of Android SQLite with an attempt to make it easy for a new beginner to pick up. In the next two tutorial I will provide a walk through on how we can now use Content Provider to perform CRUD (create, read, update & delete) operations against this database.

This tutorial is an excerpt from my upcoming book Android SQLite for Beginners, if you want to master Android SQLite and Content Providers then you may want to get request early access.

Happy Coding.

The post Android SQLite for Beginners appeared first on Val Okafor.