Using a Database With Your Android* App

In mobile apps, using a simple database backend such as SQLite can be useful in a variety of use cases. In this article we will take a look at Android* SQLite API and helper classes for database creation and maintenance. We will discuss how to create and use a prepopulated database, and use a SQLite utility library to implement a database backend for a sample restaurant app.

Contents

Abstract
Overview
A Retail Business Restaurant Sample App – Little Chef.
Using an Existing Database
Accessing Menu Items From The Restaurant Database
Summary

Overview

Android has inbuilt support for the SQLite database. It supports all the features of SQLite, and provides a wrapper API with a consistent interface. Please refer to the below link for detailed information.
http://developer.android.com/guide/topics/data/data-storage.html#db

Android SQLite API is generic and leaves it to the developer to implement all database handling including the creation, versioning, upgrades of a database, and other customizations. If we want to use a pre-populated SQLite database, additional configuration is needed.

The below training guide shows in detail how to use the standard Android SQLite API.
http://developer.android.com/training/basics/data-storage/databases.html

Directly using the Android SQLite API may result in a lot of boilerplate code. There are several Android utility libraries to help ease this process, and they provide additional features on top for easier and efficient SQLite database usage in Android apps.

SQLiteAssetHelper is one such library that is popular in the Android developer community based on usage and developer involvement. Please refer to the following website for full reference.https://github.com/jgilfelt/android-sqlite-asset-helper

A Retail Business Restaurant Sample App – Little Chef.

We will use a sample restaurant app (Little Chef) to discuss the usage of the SQLite database and SQLiteAssetHelper library.

This application allows the user to browse through different menu categories and choices.

The restaurant app can be used by a chef or user, to see different menu categories and items. The sample app implements a swipe gesture to switch between different categories, and selecting a menu item brings up its details.

A SQLite database can be used to store all the menu categories and item details. In future versions of the app, we could extend the database to support other kinds of app data – sales data, loyalty programs, per-user customizations etc.

Using An Existing Database

Depending on the app requirements, we may have to prepopulate the app with an initial set of data. In the case of the sample restaurant app, a basic set of standard menu categories and item details are prepopulated.

Android APIs (eg. SQLiteOpenHelper) can be used to populate the initial set of data as part of database creation and initialization. However, this may not always be optimal, particularly when the dataset is huge. Also, some of the SQLiteOpenHelper calls are not recommended to be used in the main thread. Users may experience long initialization and UI delays on startup, depending on device capabilities. Another approach is to prepopulate the database and package it as part of the app's assets.

For the restaurant sample app, we created a SQLite database offline using python programming API for SQLite. There are GUI clients as well, to manually edit or add data to SQLite database. As recommended in the Android SQLite API documentation, we added a column "_id" for uniquely identifying each row. This will be useful in implementing content provider and adaptor abstractions.

Accessing a SQLite database from the app's assets folder with Android SQLite APIs requires us to copy the database file from the assets folder to the app's database folder path. Supporting database upgrades and versioning complicates this even more.

For the restaurant sample app, we use SQLiteAssetHelper* library to access the prepopulated database, which is packaged as part of the app's assets. Please refer to the README document of SQLiteAssetHelper library for detailed usage instructions.
https://github.com/jgilfelt/android-sqlite-asset-helper

We created a "databases" folder under "assets", and copied the prepopulated "restaurant.sqlite" file to the "databases" folder. Please refer to following code snippet for implementation details.

package com.example.restaurant;

import android.content.Context;

	import android.database.Cursor;

	import android.database.sqlite.SQLiteDatabase;

	import android.database.sqlite.SQLiteQueryBuilder;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

/**

	* Database handler for restaurant app.

	*/

	public class RestaurantDatabase extends SQLiteAssetHelper {

	     private static final String TAG = SQLiteAssetHelper.class.getSimpleName();

	     private static final String DATABASE_NAME = "restaurant.sqlite";

	     private static final int DATABASE_VERSION = 1;

     public interface TABLES {

	         String MENU = "menu";

	         String USER = "user";

	         String CUSTOMER = "customer";

	}

     public interface MenuColumns {

	         String CATEGORY = "category";

	         String NAME = "name";

	         String DESCRIPTION = "description";

	         String NUTRITION = "nutrition";

	          String PRICE = "price";

	          String IMAGENAME = "imagename";

	     }

     public RestaurantDatabase(Context context) {

	          super(context, DATABASE_NAME, null, DATABASE_VERSION);

	     }

     public Cursor getMenuItems() {

	          SQLiteDatabase db = getReadableDatabase();

	          SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

     qb.setTables(TABLES.MENU);

	         Cursor c = qb.query(db, null, null, null, null, null, null);

	          c.moveToFirst();

         return c;

	     }

	}

Accessing Menu Items From The Restaurant Database

On initialization, SQLiteAssetHelper will automatically copy the prepopulated restaurant database from the assets folder to the appropriate database path. Subsequent calls will reuse the database instance, unless an upgrade is requested. The previous code snippet shows the getMenuItems method, which returns a cursor for all menu items in the database.

The following code snippet shows creating an instance of the database, and parsing the menu items from the cursor.

mDb = new RestaurantDatabase(this);

mMenuItems = new ArrayList<MenuItem>();

Set<String> categories = new HashSet<String>();


	Cursor c = mDb.getMenuItems();

		while (c.moveToNext()) {

	
		String category = c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.CATEGORY));

			categories.add(category);
	

	MenuItem menuItem = new MenuItem();

	
		menuItem.setCategory(category);


			menuItem.setName(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.NAME)));


			menuItem.setDescription(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.DESCRIPTION)));


			menuItem.setNutrition(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.NUTRITION)));


			menuItem.setPrice(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.PRICE)));


			menuItem.setImageName(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.IMAGENAME)));

			mMenuItems.add(menuItem);
	

	}


     c.close();

mCategoryList = new ArrayList<String>(categories);

Handling database access in the main thread is not recommended. We can use SQLiteAssetHelper to add additional abstractions, such as the Android content provider interface.

Depending on app requirements and use cases, we can add more functionality to the restaurant sample app like support for database upgrades, versioning, and even backend server support. In the case of a server backend database implementation, we can use the app's local SQLite database as a temporary cache and to provide offline capability.

Summary

This article discussed the usage of the SQLite database with Android apps. Android SQLite API and helper classes were discussed. We used a sample restaurant app to demonstrate how to use prepopulated databases using the SQLiteAssetHelper library.

About the Author

Ashok Emani is a Software Engineer in the Intel Software and Services Group. He currently works on the Intel® Atom™ processor scale enabling projects.

Notices

INFORMATION IN THIS DOCUMENT IS PROVIDED IN CONNECTION WITH INTEL PRODUCTS. NO LICENSE, EXPRESS OR IMPLIED, BY ESTOPPEL OR OTHERWISE, TO ANY INTELLECTUAL PROPERTY RIGHTS IS GRANTED BY THIS DOCUMENT. EXCEPT AS PROVIDED IN INTEL'S TERMS AND CONDITIONS OF SALE FOR SUCH PRODUCTS, INTEL ASSUMES NO LIABILITY WHATSOEVER AND INTEL DISCLAIMS ANY EXPRESS OR IMPLIED WARRANTY, RELATING TO SALE AND/OR USE OF INTEL PRODUCTS INCLUDING LIABILITY OR WARRANTIES RELATING TO FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, OR INFRINGEMENT OF ANY PATENT, COPYRIGHT OR OTHER INTELLECTUAL PROPERTY RIGHT.

UNLESS OTHERWISE AGREED IN WRITING BY INTEL, THE INTEL PRODUCTS ARE NOT DESIGNED NOR INTENDED FOR ANY APPLICATION IN WHICH THE FAILURE OF THE INTEL PRODUCT COULD CREATE A SITUATION WHERE PERSONAL INJURY OR DEATH MAY OCCUR.

Intel may make changes to specifications and product descriptions at any time, without notice. Designers must not rely on the absence or characteristics of any features or instructions marked "reserved" or "undefined." Intel reserves these for future definition and shall have no responsibility whatsoever for conflicts or incompatibilities arising from future changes to them. The information here is subject to change without notice. Do not finalize a design with this information.

The products described in this document may contain design defects or errors known as errata which may cause the product to deviate from published specifications. Current characterized errata are available on request.

Contact your local Intel sales office or your distributor to obtain the latest specifications and before placing your product order.

Copies of documents which have an order number and are referenced in this document, or other Intel literature, may be obtained by calling 1-800-548-4725, or go to: http://www.intel.com/design/literature.htm

Software and workloads used in performance tests may have been optimized for performance only on Intel microprocessors. Performance tests, such as SYSmark* and MobileMark*, are measured using specific computer systems, components, software, operations, and functions. Any change to any of those factors may cause the results to vary. You should consult other information and performance tests to assist you in fully evaluating your contemplated purchases, including the performance of that product when combined with other products.

Any software source code reprinted in this document is furnished under a software license and may only be used or copied in accordance with the terms of that license.