Using SQLite in an Android app

Within the Android APIs the package android.database.sqlite contains classes to simplify the usage of SQLite database. The easiest way to work with SQLite is to extend the SQLiteOpenHelper.

public class ApplicationDbHelper extends SQLiteOpenHelper {

But at first we are starting with the database design. After that we are creating a contract class that contains for each table a inner class. Each inner class implements the interface BaseColumns from the package android.provider and contains the table name and each column name. The primary key field _ID is inherited from the interface BaseColumns. Furthermore we also add a inner class for a view that we want to use.

public class ApplicationDbContract { 
    public ApplicationDbContract() {}

    public static abstract class Entry implements BaseColumns {
        public static final String TABLE_NAME = "entry";
        public static final String COLUMN_NAME_DESCRIPTION = "description";
        public static final String COLUMN_NAME_VALUE = "value";
        public static final String COLUMN_NAME_DATE = "date";
        public static final String COLUMN_NAME_CATEGORY_ID = "categoryId";
    }

    public static abstract class Category implements BaseColumns {
        public static final String TABLE_NAME = "category";
        public static final String COLUMN_NAME_CATEGORY_NAME = "categoryName";
    }

    public static abstract class ApplicationEntryCategory {
        public static final String VIEW_NAME = "entryCategory";
        public static final String COLUMN_NAME_ID = "id";
        public static final String COLUMN_NAME_DESCRIPTION = "description";
        public static final String COLUMN_NAME_VALUE = "value";
        public static final String COLUMN_NAME_CATEGORY = "category";
        public static final String COLUMN_NAME_DATE = "date";
    }
}

While extending SQLiteOpenHelper we need to implement the abstract methods onCreate and onUpgrade. We also add a constructor which is invoking the constructor of the parent with 'Application.db' as database name and '1' as database version. The SQLiteOpenHelper offers an easy way to access the SQLiteDatabase by calling getWriteableDatabase or getReadableDatabase. We add a private static SQLiteDatabase object to ApplicationDbHelper that is instantiated in the constructor only once in the application. This is done to prevent DatabaseObjectNotClosedException.

    private static SQLiteDatabase _db;

    public ApplicationDbHelper(Context context) {
        super(context, "Application.db", null, 1);

        if(_db == null || !_db.isOpen()) {
            _db = getWritableDatabase();
        }
    }

To close the database a static method is added that is called at application end.

    public static void shutDown() {
        if(_db != null && _db.isOpen()) {
            _db.close();
        }
    }

At next we are adding the SQLite statements to ApplicationDbHelper that will create our tables.

    private static final String SQL_CREATE_CATEGORIES =
           "CREATE TABLE IF NOT EXISTS " +
           ApplicationDbContract.Category.TABLE_NAME + 
           " (" + ApplicationDbContract.Category._ID +
           " INTEGER PRIMARY KEY," +                    
           ApplicationDbContract.Category.COLUMN_NAME_CATEGORY_NAME +
           " TEXT COLLATE NOCASE UNIQUE ON CONFLICT IGNORE)";

    private static final String SQL_CREATE_ENTRIES =
           "CREATE TABLE IF NOT EXISTS " +
           ApplicationDbContract.Entry.TABLE_NAME + " (" +
           ApplicationDbContract.Entry._ID + " INTEGER PRIMARY KEY," + 
           ApplicationDbContract.Entry.COLUMN_NAME_DESCRIPTION + " TEXT," +
           ApplicationDbContract.Entry.COLUMN_NAME_VALUE + " REAL," +
           ApplicationDbContract.Entry.COLUMN_NAME_DATE + " TEXT," +
           ApplicationDbContract.Entry.COLUMN_NAME_CATEGORY_ID + " INTEGER," +
           "FOREIGN KEY(" + ApplicationDbContract.Entry.COLUMN_NAME_CATEGORY_ID +
           ") REFERENCES " + ApplicationDbContract.Category.TABLE_NAME + "(" +
           ApplicationDbContract.Category._ID + ")" + ")";

     private static final String SQL_CREATE_VIEW_ENTRIES_CATEGORY =
            "CREATE VIEW " + ApplicationDbContract.EntryCategory.VIEW_NAME + " AS " +
            "SELECT " + 
            "String.format("E.%s AS %s, E.%s AS %s, E.%s AS %s, E.%s AS %s, C.%s AS %s",
            ApplicationDbContract.Entry._ID, 
                     ApplicationDbContract.EntryCategory.COLUMN_NAME_ID,
            ApplicationDbContract.Entry.COLUMN_NAME_DESCRIPTION,
                     ApplicationDbContract.EntryCategory.COLUMN_NAME_DESCRIPTION,
            ApplicationDbContract.Entry.COLUMN_NAME_VALUE,
                     ApplicationDbContract.EntryCategory.COLUMN_NAME_VALUE,
            ApplicationDbContract.Entry.COLUMN_NAME_DATE,
                     ApplicationDbContract.EntryCategory.COLUMN_NAME_DATE,
            ApplicationDbContract.Category.COLUMN_NAME_CATEGORY_NAME,
                     ApplicationDbContract.EntryCategory.COLUMN_NAME_CATEGORY) +
            " FROM " + String.format("%s AS E, %s AS C",
                                     ApplicationDbContract.Entry.TABLE_NAME,
                                     ApplicationDbContract.Category.TABLE_NAME) +
            " WHERE " + String.format("E.%s = C.%s",
                                      ApplicationDbContract.Entry.COLUMN_NAME_CATEGORY_ID,
                                      ApplicationDbContract.Category._ID) +
            " ORDER BY " + ApplicationDbContract.EntryCategory.COLUMN_NAME_DATE + " DESC";

These statements are executed in the overridden method onCreate.

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(SQL_CREATE_CATEGORIES);
        sqLiteDatabase.execSQL(SQL_CREATE_ENTRIES);
        sqLiteDatabase.execSQL(SQL_CREATE_VIEW_ENTRIES_CATEGORY);
    }

To add new categories and new entries to the database a ContentValues object and the insert method of SQLiteDatabase are used.

    public long addCategory(String category) {
        android.util.Log.v("DbHelper", "Add category");
        ContentValues values = new ContentValues();
        values.put(ApplicationDbContract.Category.COLUMN_NAME_CATEGORY_NAME, category);
        
        long newRowId = -1;
        if (_db != null) {
            newRowId = _db.insert(
                    ApplicationDbContract.Category.TABLE_NAME,
                    null,
                    values);
        }

        return newRowId;
    }

    public long addEntry(String description, Date date, double value, String category) {
        android.util.Log.v("DbHelper", "Add entry");
        
        // get the category id of the category name
        // category id is '-1' if category name is not existing
        long categoryId = getCategoryId(category);

        // if category not exists, add it
        if (categoryId == -1) {
            categoryId = addCategory(category);
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String stringDate = format.format(date);
        ContentValues values = new ContentValues();
        values.put(ApplicationDbContract.Entry.COLUMN_NAME_DESCRIPTION, description);
        values.put(ApplicationDbContract.Entry.COLUMN_NAME_DATE, stringDate);
        values.put(ApplicationDbContract.Entry.COLUMN_NAME_VALUE, value);
        values.put(ApplicationDbContract.Entry.COLUMN_NAME_CATEGORY_ID, categoryId);

        long newRowId = -1;
        if (_db != null) {
            newRowId = _db.insert(
                    ApplicationDbContract.Entry.TABLE_NAME,
                    null,
                    values);
        }

        return newRowId;
    }

To read from the database the query method can be used, which returns a Cursor object. The Cursor object contains all queried rows. With the method moveToFirst the read position is placed on the first entry of the results. On each position the getColumnIndexOrThrow method can be used to get the corresponding column index of a column name. Furthermore the Cursor object offers methods to get the value of a column in a certain type.

    public long getCategoryId(String category) {
        android.util.Log.v("DbHelper", "Get category");

        // the id is set to '-1', it is only changed, if the category exists
        long id = -1;
        String[] columns = {ApplicationDbContract.Category._ID};
        String selection = ApplicationDbContract.Category.COLUMN_NAME_CATEGORY_NAME +
                           " = ?";
        String[] selectionArgs = {category};

        if (_db != null) {
            Cursor c = _db.query(
                    ApplicationDbContract.Category.TABLE_NAME, // table name
                    columns,                                   // results
                    selection,                                 // where columns
                    selectionArgs,                             // where conditions
                    null,                                      // group by
                    null,                                      // having
                    null);                                     // order by

            // if category exists there is one row, otherwise there is no result
            if (c.moveToFirst()) {
                // id is changed, if Cursor c is not empty
                id = c.getLong(c.getColumnIndexOrThrow(
                                      ApplicationDbContract.Category._ID));
            }

            // close Cursor to prevent DatabaseObjectNotClosedException
            c.close();
        }

        return id;
    }

To cycle through all results in the Cursor the method moveToNext can be used.

    public String[] getCategories() {
        android.util.Log.v("DbHelper", "Get categories");

        List<String> values = new ArrayList<String>();
        String[] columns = {
                ApplicationDbContract.Category._ID,
                ApplicationDbContract.Category.COLUMN_NAME_CATEGORY_NAME
        };
        String sortOrder = ApplicationDbContract.Category.COLUMN_NAME_CATEGORY_NAME +
                           " ASC";
 
        if (_db != null) {
            Cursor c = _db.query(
                    ApplicationDbContract.Category.TABLE_NAME, // table name
                    columns,                                   // results
                    null,                                      // where columns
                    null,                                      // where conditions
                    null,                                      // group by
                    null,                                      // having
                    sortOrder                                  // order by
            );

            while (c.moveToNext()) {
                String value = c.getString(c.getColumnIndexOrThrow(ApplicationDbContract.
                                         Category.COLUMN_NAME_CATEGORY_NAME));
                values.add(value);
            }

            // close Cursor to prevent DatabaseObjectNotClosedException
            c.close();
        }

        return values.toArray(new String[values.size()]);
    }

The method rawQuery is an alternative to the query method. It returns also a Cursor object.

    public List<Entry> getEntries() {
        android.util.Log.v("DbHelper", "Get entries");

        List<Entry> entries = new ArrayList<Entry>();
        String query = "SELECT * FROM " + ApplicationDbContract.EntryCategory.VIEW_NAME;

        if (_db != null){
            Cursor c = _db.rawQuery(query, null);
            while(c.moveToNext()){
                Entry entry = new Entry();
                entry.id = c.getLong(c.getColumnIndexOrThrow(
                         ApplicationDbContract.EntryCategory.COLUMN_NAME_ID));
                entry.description = c.getString(c.getColumnIndexOrThrow(ApplicationDbContract.
                                         EntryCategory.COLUMN_NAME_DESCRIPTION));
                entry.value = c.getDouble(c.getColumnIndexOrThrow(ApplicationDbContract.
                                         EntryCategory.COLUMN_NAME_VALUE));
                String date = c.getString(c.getColumnIndexOrThrow(ApplicationDbContract.
                                         EntryCategory.COLUMN_NAME_DATE));
                SimpleDateFormat dbFormat = new SimpleDateFormat("yyyy-MM-dd");

                try {
                    entry.date = dbFormat.parse(date);
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                entry.category = c.getString(c.getColumnIndexOrThrow(ApplicationDbContract.
                                         EntryCategory.COLUMN_NAME_CATEGORY));
                entries.add(entry);
            }

            // close Cursor to prevent DatabaseObjectNotClosedException
            c.close();
        }

        return entries;
    }

To delete a row from a table, the method delete can be used.

    public void deleteEntry(long id) {<
        android.util.Log.v("DbHelper", "Delete entry");

        String selection = ApplicationDbContract.Entry._ID + " = ?";
        String[] selectionArgs = {String.valueOf(id)};

        if (_db != null) {
            _db.delete(ApplicationDbContract.Entry.TABLE_NAME, selection, selectionArgs);
        }
    }

To update a row in a table the ContentValues object and the update method can be used.

    public void updateEntry(){
        android.util.Log.v("DbHelper", "Update entry");

        ContentValues values = new ContentValues();
        values.put(ApplicationDbContract.Entry.COLUMN_NAME_DESCRIPTION, "");

        String selection = ApplicationDbContract.Entry._ID + " = ?";
        String[] selectionArgs = { String.valueOf(0) };

        if (_db != null) {
            _db.update(
                    ApplicationDbContract.Entry.TABLE_NAME,
                    values,
                    selection,
                    selectionArgs
            );
        }
    }