Click here to Skip to main content
15,867,453 members
Articles / Hosted Services / Serverless

Android SQLite for Beginners

Rate me:
Please Sign up or sign in to vote.
4.82/5 (17 votes)
20 Nov 2015CPOL11 min read 17.2K   22   4
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() 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.

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.

Java
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.

Java
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:

Java
@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:

Java
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:

Java
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:

Java
@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() 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:

Java
@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

    sqlite_location

  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:

    sample_database

  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.
    note_app_table

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.

Summary

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 tutorials, 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.

Happy coding!

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

This article was originally posted at http://prontoandroid.com/android-sqlite-for-beginners

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) ValOkafor.com
United States United States
My name is Val Okafor, I am a Senior Software Engineer with specialization in Android Development. Learning and problem solving is my passion and I share my Android development knowledge through my blog ValOkafor.com.

My Android courses are the courses I wish I had when I started. I teach Android development in the context of a fully developed Android app. I believe that new Android concepts are better understood if they are presented in the context of creating an app from scratch to finish.

I focus on creating Productivity Android apps and besides Android development I have 7 years’ experience as System Administrator supporting Enterprise applications and 2 years’ experience as a Web Developer building websites using PHP and ASP.Net.

I have worked for corporations such as The Home Depot, American Council on Exercise, Legend3D and HD Supply, Inc. I have a bachelor's degree in Information Technology from National University San Diego, California and a master's degree in Software Engineering from Regis University Denver, Colorado.

I enjoy sharing my extensive work experience through my blog, social media.

Comments and Discussions

 
QuestionWhere would I find the next parts of the tutorial? Thanks! Pin
Member 1359153423-Dec-17 12:08
Member 1359153423-Dec-17 12:08 
QuestionSQLite Pin
cad1310-Jan-16 1:30
cad1310-Jan-16 1:30 
GeneralMy vote of 3 Pin
KarstenK6-Dec-15 23:23
mveKarstenK6-Dec-15 23:23 
GeneralMy vote of 5 Pin
Santhakumar M4-Dec-15 8:19
professionalSanthakumar M4-Dec-15 8:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.