Using an SQLite Database on Android Platform – Introduction

by
Michał Kołodziejski
Senior Software Engineer at Vertabelo

Posted: February 12, 2014

According to the report of the International Data Corporation, Android operating system reached more than 80% market share during the 3rd quarter of 2013. Together with iOS, Android dominates in the mobile devices’ world.

This made me think that it may be worth writing a few words about how to create mobile applications for these two mobile platforms. But not the kind of “hello world” applications – there are plenty of tutorials about that. I’d like to focus on the use of local SQLite databases. I assume that you know how to write and run (on an emulator or a device, it doesn’t matter) a very simple app.

Today I’ll say a little bit about some Android platform basics:

  • how to create a database,
  • how to connect to it,
  • and how to insert and retrieve data.

In subsequent articles I’d like to cover more sophisticated issues, such as migration between DB versions. Ready to take off?

Starting Point: In-Memory Application

We need something to start with. I wrote a simple Android application: “My TODO List.” It consists of a single activity with a list of tasks, two dialogs and a single manager holding the data in memory.

When we launch the application, the list of ToDo tasks is empty. We can add tasks through a menu option. After a task is complete, we may either mark it is as “done” or just delete it.

You can download the apps source here.

This application doesn’t meet the requirements – its storage is temporary. Implementation of the TodoTasksManager interface – InMemoryTodoTasksManagerImpl – holds the data in memory. After the process finishes or gets killed, all provided data is lost. We need to add a persistent storage to it.

Persistent Storage – SQLite Database

Every Android application may have one or more private SQLite databases. Android SDK provides a special set of classes which make it really easy to create database structures and operate on the data. You can find them in the following packages:

  • android.database
  • android.database.sqlite

In order to store our data in a SQLite database we need to follow these 4 steps:

  1. Create DDL

    You can do it manually, writing CREATE TABLE statements, or you can use the proper tool – Vertabelo.

    Log into or sign up for Vertabelo. Add one table. Add the necessary columns and choose their data types. Generate SQL script and download it. Done!




  2. Copy DDL to resources folder

    We’ll keep the script in the res/raw/ folder. Just copy your script there.

  3. Provide implementation of “open helper”

    As I mentioned before, there are some very useful classes in the Android SDK that help to operate on a SQLite database. One of them is SQLiteOpenHelper. It’s an abstract class. We must extend it and provide implementation of two methods: onCreate and onUpgrade. The first one is invoked when a database is opened for the first time after installation of an application. You should create a database structure there. The second one is called when a database version has changed and it needs to be migrated. We’ll leave it empty as we don’t need it now.

    Most of the tutorials about using SQLite database in Android show some code like this:

    private static final String CREATE_TABLE =
            "CREATE TABLE my_table (id integer NOT NULL PRIMARY KEY, "
                + "column_1 varchar(255) NOT NULL, "
                + "column_2 varchar(255) NOT NULL"
                +");";
    
    
    @Override
    public void onCreate(SQLiteDatabase db) {
    	db.execSQL(CREATE_TABLE);
    }
    

    I don’t like it. It’s awful. You know why? Imagine you have more than 20 tables in a DB. It’s not that much, I worked on a mobile project having more than 60 tables. Will you manage the DB structure in Strings? Or each time the schema changes will you convert the DDL over and over again into Strings? It won’t work. At least not efficiently.

    The solution is simpler. Keep the DDL in a file and automate the process. In step 2 we’ve already copied the file to the res/raw/ folder, so it can now be identified with R.raw.db_create constant. All we need to do is to read the whole file and execute each statement separately. It could look like this:

    @Override
    public void onCreate(SQLiteDatabase db) {
        readAndExecuteSQLScript(db, ctx, R.raw.db_create);
    }
    
    
    private void readAndExecuteSQLScript(SQLiteDatabase db, Context ctx, 
            Integer sqlScriptResId) {
        Resources res = ctx.getResources();
        
        try {
            InputStream is = res.openRawResource(sqlScriptResId);
            InputStreamReader isr = new InputStreamReader(is);
            BufferedReader reader = new BufferedReader(isr);
                
            executeSQLScript(db, reader);
                
            reader.close();
            isr.close();
            is.close();
            
        } catch (IOException e) {
            throw new RuntimeException("Unable to read SQL script", e);
        }
    }
    
    private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) 
            throws IOException {
        String line;
        StringBuilder statement = new StringBuilder();
        while ((line = reader.readLine()) != null) {
            statement.append(line);
            statement.append("\n");
            if (line.endsWith(";")) {
                db.execSQL(statement.toString());
                statement = new StringBuilder();
            }
        }
    }
    

  4. Provide alternative implementation of TodoTasksManager interface

    Now, just implement the methods of TodoTasksManager interface using the DB. Opening a connection to the DB is as simple as this:

    DBOpenHelper dbOpenHelper = new DBOpenHelper(ctx);
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
    

    We perform CRUD operations by calling the appropriate methods of an instance of SQLiteDatabase:

    • insert
    • query
    • update
    • delete

    After the implementation of your manager is finished, replace the in-memory instance in the TodoListActivity with the new one. That’s all!

You can find the full source code of such an implementation here. Download it, build and run it, play with it. It’s yours.

The next article shows how to manage schema changes between different versions of an application. It sounds trivial, but it’s a little tricky.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Learn how to process trees and graphs in SQL, and how to effectively organize long SQL queries. View course Discover our other courses: