Android SQLite Tutorial for Beginners

If you’re just getting started as an Android app developer, it won’t be long before you need to call on the power of databases to bring your applications to the next level. Some apps are almost entirely data-driven while others simply need to store small amounts of data such as a user’s high score in a game you created.

Either way, learning how to use SQLite is essential for Android app creation. If you want to learn Android development but haven’t quite figured out the basics yet, the Android Tutorial for Beginners course gets you started and includes useful information about using SQLite in your apps.

The importance of a persistent data storage solution within the Android OS is readily apparent because of the unconventional life cycle of an Android application. At any time, the Android runtime could terminate application components to free up resources. Without proper implementation of SQLite into your application, the risk of data loss is very real.

This article teaches the basics of SQLite and how it relates to the Android OS. If you are unfamiliar with database basics, check out Database Design Introduction before diving in to this SQLite tutorial.

What is SQLite?

SQLite is a relational database management system (RDBMS). What makes SQLite unique is that it is considered an embedded solution. Most database management system such as Oracle, MySQL, and SQL Server are standalone server processes that run independently.

SQLite is actually a library that is linked into applications. All database operations are handled within the application through calls and functions contained in the SQLite library. This is great news while you’re learning to use SQLite because it makes it much easier to manipulate even large databases when compared to more conventional database solutions.

In case you’re interested, SQLite is actually written in C and contained within a Java-based “wrapper” provided by the Android SDK.

SQLite does rely on Structured Query Language (SQL); the same language used by most other RDBMSs. If you’re already familiar with SQL from another database system, you have a serious head start using SQLite because you will find that most query commands are structured exactly the same way.

If you are new to SQL, the Introduction to SQL Training is an excellent resource.

Getting Started with SQLite

The easiest way to understand SQLite is to start using it. Since Android comes with SQLite preinstalled, you can create an interactive environment by running an instance of the Android Virtual Device (AVD) emulator.

You can start an AVD session using the popular Eclipse IDE. Once started, you can connect a command prompt window to the emulator using the following ADB (Android Debugging Bridge) command:

adb –e shell

The “-e” flag tells the ADB tool to look for an emulator rather than a physical Android device. SQLite databases are stored in database files specific to the application that is running. If you have an Android application with the package name of “com.udemy.myDBapp,” the SQLite database for this application would be located at:

/data/data/com.udemy.myDBapp/databases/mydatabase.db

The name of the database in this example is mydatabase.db but it could be anything you choose to name it. So for the purposes of this example, change directories in the ADB shell and create a subdirectory hierarchy so you can start experimenting with SQLite. It should look something like this:

cd /data/data

mkdir com.udemy.myDBapp

cd com.udemy.myDBapp

mkdir databases

cd databases

Once you have created this hierarchy correctly, you can initialize the interactive SQLite tool using this command:

sqlite3 ./mydatabase.db

Now you are ready to start entering SQL commands to create, modify, and delete table data from your SQLite database.

SQLite Java Classes

Remember that SQLite is written in C and wrapped in Java by the Android SDK. This wrapper is comprised of a set of Java classes that interact directly with the SQLite database management system.

Cursor

The Cursor class provides access to the results of a database query. For example, if you use the SQL SELECT operation, you could potentially have multiple returns from within the database. Cursor allows you to step through these results which can then be accessed from within the application code.

SQLiteDatabase

This class is the actual interface between your application code and the SQL database.

It includes functions to perform SQL-based operations such as INSERT, DELETE, QUERY, and RAWQUERY (a SQL query statement that returns results in the form of a Cursor object).

You can learn more about how to use the SQLiteDatabase class in Android App Development Fundamentals II.

SQLiteOpenHelper

This helper class is designed to make creating and updating databases easier. Please note that this class must be subclassed within the code of your application and include the onCreate() and onUpgrade() callback methods.

Although these classes may seem a little confusing, this approach is certainly easier to understand than conventional RDBMS interfaces and because SQLite is so lightweight, even if your code isn’t perfect you shouldn’t have any problems creating functional databases for your Android applications.

The fact remains that unless you are making only the most basic of Android applications, you need data management. SQLite makes it easy to incorporate powerful database features that provide your users with a much more interactive experience.