Android SQLite Cheat Sheet (Without Content Provider)

SQLite has been the primary way of storing data on Android for a while. There has been some third-party options popping up in the last few years, but still SQLite remains at the top. SQLite is an easy way to store your data. By using SQLite, you are creating a database. If you don’t know what a database is, you can think of it as a spreadsheet. You’ve got rows and columns and you store data in the crossings of those rows and columns. Those crossings are called cells.

Defining a FeedReader Contract

The FeedReader Contract and the FeedEntery classes define a base schema of your database.

By implementing the BaseColumns interface, your inner class can inherit a primary key field called  _ID. The names of these three String variables are up to you. You create as many of these variables as  you need. Each of these variables represent one column (except the TABLE_NAME which represents the name of your table). You will use these variables when you write your CRUD commands (create, read, update, delete).

Using the Database Helper

After defining the schema of your database you need to write code that actually creates your database. You do that by extending the SQLiteOpenHelper class. In the class that extends the SQLiteOpenHelper you write code that look a lot like the standard SQLite syntax. This is an example of a database helper class that I’ve written for an app that stores your school grades:

This part of the code speaks for itself. There is not much to explain here if you know any SQLite. I do not know a lot about SQLite, but I know enough to create databases in Android. The complicated part of Android databases is not in creating the database itself, it’s when you start entering data and querying the database that it get tricky.

The CRUD operations

CRUD stands for “create, read, update and delete”. Those four construct the base of any database. That’s all there is to a database. Your database can be dead simple, but if you want to create anything worthwhile you’re gonna need to step it up. Sometimes you are going to be querying only a single column and the result can be an ArrayList<String>, but a lot of times you’re going to need to create an object that holds multiple data types. For example, when I was creating my grade tracking app, I created a class that holds three variables: int id which holds the id of the queried row; int avgGrade which holds the average of the grades that you have in that subject; String subjectName which holds the name of the subject. This is a very basic example of how you can use objects that hold multiple data types.

If I know that I will be doing the same operations multiple times, I like to make a separate class that holds these methods.   In that class I would have all of the variations of the CRUD commands that I know I’ll be using. I’m going to be showing you how to do the most basic variations of the CRUD methods and it’s up to you to specialize them for your needs.

Read

Let’s go through the code. The query() method takes in seven parameters. Those are: String table – the FeedEntery value of your table name; String[] projection – this is an array of the columns that you want to query; String selection – the where command of  SQLite; String[] selectionArgs – replaces the ? for where; String groupBy – groups the rows, setting it to null does not group them; String having – used with the groupBy, setting which rows will end up in the cursor; String sortOrder – sets how you want to sort the queried rows based on a certain column.

You will often need to store your query results in a variable. The best data structure for this is an ArrayList. This is an example of the code:

Put data in the database

This is a very simple part of the CRUD operations. You need to create a ContentValues object. You use the put() method to store the data you want to put into the database. The put() method takes in two parameters: String column – the FeedEntry value of the column you in which you want to store a value; value –  value can be a variable of a few types, it’s the values you put into the database.

Update the database

Update method is something between the read and put methods. Again, you must create the ContentValues object that stores the value you want to put in the database. Also, you need to create the selection and selectionArgs variables which define which rows you want to update.

Delete values from the database

Similar to the update() method, the delete() method takes something from the put() and read() methods. Again, you need to create the selection and selectionArgs variables. After you do that, just call the delete method, which takes in three parameters: String table name – FeedEntery value of your table name, String selection – SQLite where clause; String[] selectionArgs – replaces the ‘?’ in after the where clause.