Android Database Tutorial - Basic SQLite DB

UPDATE: This is superseded by the Room Database Tutorial.

Here is the new tutorial: Room Database, Fragments, RecyclerView, LiveData, ViewModel and Data Binding

Is this what you're looking for?

The areas of Android Development that are touched on in this tutorial are:

• SQLiteOpenHelper - For Database purposes,
• AsyncTask - To load the ListView without using the Main UI Thread,
• Thread - To execute queries without using the Main UI Thread,
• ViewHolder Pattern - To display the BlogPost titles in the ListView,
• Dynamic Creation of Database Row IDs - a lot of the database logic deals with the BlogPost titles, so I needed a way to handle insertion.

Introduction

I'm making this tutorial as a future reference for myself, and publishing it so it may be a basic step by step guide for others to implement Databases in their Android Application. I have Googled for "Android Database Tutorial". The Google Developer results are confusing, Android Hive is old/erroneous and Vogella's one is really detailed/long.

The App

This will be a tutorial on the basics of creating a database in Android. The end result will be an app that can create records and display them as a ListView. The reason the app is so simple is to remove the clutter, to allow the database to be the main focus of the tutorial.

Also there isn't a whole lot of data integrity checks, logging, user feedback, exception handling here, so keep your panties unbunched.

I can evolve this app in the following ways, and I will with future posts:

• Advanced Data Types in the Database,
• Content Providers,
• RecyclerView for a nice layout to the ListView,
• ActionBar commands to create and delete BlogPosts,
• Pulling from a RESTful Service, etc...

Android Database Technology

Android uses an SQLite Database when storing data on the device. You can use a remote database too, but it's best to use a RESTful Web Service to accomplish tasks in that way. Storing content locally is good if the user doesn't have a network connection and wants to view current data, or create data for synching to the cloud later.

Database Implementation Flow

I'll provide a synopsis of the steps below. As Android Database interaction is Model Driven, we'll implement our Model Class first. "Model" is just a fancy word for "POJO", which itself is a fancy word for "Plain Old Java Object". Secondly we'll implement the outline of our Database Class, to provide the foundation to build interactions on. The Database Class will be a Singleton, so only one instance will exist in our app, it's best to get into good practices early. There's two schools of thought on the next step; one, implement the queries as needed, as not to bloat functionality, two, write the CRUD logic up front to reduce development time later on and we're in the database zone at this time. I'd recommend the former for normal development, but for the purposes of keeping this tutorial streamlined, I'll take the latter approach. Finally we'll add the UI Activities to make use of the database functionality.

I have the source code posted to GitHub: Android Database Tutorial-Basic repository. Each of the steps below will be a commit.

Step 0: New App with Blank Activity

This step is optional, you may already have the app that you wish to use the database in. Create the new app, give it a name and select the "Blank Activity" to begin with. I just accepted all the defaults for the naming. As none of that matters much in the context of this tutorial. Close MainActivity.java and activity_main.xml when they open. We'll deal with them later. At this point I added my project to Source Control with git.

Commit Diff: 394ff7f

Step 1: Implement the Model Class

We'll add a new package under our app, for good practice. We'll call it "model". Next add a Java Class, which will be our model. We'll call it "BlogPost". Our app will display words that the user creates in a ListView on screen. With that in mind, here is our simple Model Class, it has the ID and the Title fields.

The ID field always needs to be present in a Model Class that we want to be in the database. The ID is the Primary Key for the database. The Database will start from 1, not from 0 as you'd expect. Hopefully, simple stuff here.

Commit Diff: 391add4

Step 2: Implement the Database Class

In this step, we will create the outline of the Database Class. Add another package, call it "database". Add a Java Class, call it "DatabaseConnection" and the kind will be Singleton.

In our code editor we make it so DatabaseConnection.java extends SQLiteOpenHelper. SQLiteOpenHelper requires that we implement its two abstract methods, onCreate() and onUpgrade(). The lifecycle method onCreate() is called when the app is first launched. In the onCreate() method implementation, we issue a CREATE TABLE command to the database. The lifecycle method onUpgrade() is called when the app is updated to a later version, i.e when an update is installed from the Google Play Store. In the onUpgrade(), you can manage the addition and removal of columns from the database table. But in our implementation, we'll just drop the old database in favour of the new version, which will be created when we call the onCreate() method manually.

Commit Diff: c685b65

Step 3: Basic Queries for our Use Cases

The use cases of this app are;

1. Create a new BlogPost.
2. Delete an existing BlogPost,
3. View all the BlogPosts in the database.

Still in our DatabaseConnection class, we add the following three methods after the onUpgrade() method.

Commit Diff: 16bf861

Step 3 Bonus: The other CRUD queries

The abbreviation CRUD stands for Create, Retrieve, Update and Delete. So far we have the Creation, Deletion and Retrieval of records implementation. Let's see how the Updating and other forms of Retrieval of records are handled with respect to our Model. Again, keeping things simple, we'll ignore the possibility of raising exceptions and handling return codes here.

The following methods are pretty self explanatory, they allow for the retrieval of one record, allow updating of one record, count the records, determine if a record exists, and find the ID of an existing record in the database.

Commit Diff: 2340692

Step 4: UI Activity to Insert, Delete and Display Records

As this tutorial is not about UI programming, I'll just leave code for calling the queries the commit ID, for the rest of the code, here for you to examine. The AsyncTask and ViewHolder Pattern is defined in that Commit ID. The UI is an EditText to input BlogPost titles, then two buttons to Add and Delete the BlogPosts, based on their titles, in the ListView based on their titles matching the text in the EditText.

Loading https://gist.github.com/8488564....

Commit Diff: 7b59a0e, this one is a bit of a mish-mash of updates.

Conclusion

I hope this tutorial was basic enough for novice programmers to understand and comprehensive for intermediate programmers to get ideas of this possibilities of storing data locally on the Android device.

All the code is in the GitHub Repository, AndroidDatabaseTutorial-Basic. If the app fails to build on your IDE, start by changing the "buildToolsVersion" in your "app/build.gradle" to match the version in your SDK Manager.