Abstract
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.
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.
Figure 1: A Restaurant Sample App – Little Chef
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.
| 01 | package com.example.restaurant; |
| 02 | |
| 03 | import android.content.Context; |
| 04 | |
| 05 | import android.database.Cursor; |
| 06 | |
| 07 | import android.database.sqlite.SQLiteDatabase; |
| 08 | |
| 09 | import android.database.sqlite.SQLiteQueryBuilder; |
| 10 | |
| 11 | import com.readystatesoftware.sqliteasset.SQLiteAssetHelper; |
| 12 | |
| 13 | /** |
| 14 | |
| 15 | * Database handler for restaurant app. |
| 16 | |
| 17 | */ |
| 18 | |
| 19 | public class RestaurantDatabase extends SQLiteAssetHelper { |
| 20 | |
| 21 | private static final String TAG = SQLiteAssetHelper.class.getSimpleName(); |
| 22 | |
| 23 | private static final String DATABASE_NAME = "restaurant.sqlite"; |
| 24 | |
| 25 | private static final int DATABASE_VERSION = 1; |
| 26 | |
| 27 | public interface TABLES { |
| 28 | |
| 29 | String MENU = "menu"; |
| 30 | |
| 31 | String USER = "user"; |
| 32 | |
| 33 | String CUSTOMER = "customer"; |
| 34 | |
| 35 | } |
| 36 | |
| 37 | public interface MenuColumns { |
| 38 | |
| 39 | String CATEGORY = "category"; |
| 40 | |
| 41 | String NAME = "name"; |
| 42 | |
| 43 | String DESCRIPTION = "description"; |
| 44 | |
| 45 | String NUTRITION = "nutrition"; |
| 46 | |
| 47 | String PRICE = "price"; |
| 48 | |
| 49 | String IMAGENAME = "imagename"; |
| 50 | |
| 51 | } |
| 52 | |
| 53 | public RestaurantDatabase(Context context) { |
| 54 | |
| 55 | super(context, DATABASE_NAME, null, DATABASE_VERSION); |
| 56 | |
| 57 | } |
| 58 | |
| 59 | public Cursor getMenuItems() { |
| 60 | |
| 61 | SQLiteDatabase db = getReadableDatabase(); |
| 62 | |
| 63 | SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); |
| 64 | |
| 65 | qb.setTables(TABLES.MENU); |
| 66 | |
| 67 | Cursor c = qb.query(db, null, null, null, null, null, null); |
| 68 | |
| 69 | c.moveToFirst(); |
| 70 | |
| 71 | return c; |
| 72 | |
| 73 | } |
| 74 | |
| 75 | } |
Code Snippet 1, Using a Prepopulated Database ++
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.
| 01 | mDb = new RestaurantDatabase(this); |
| 02 | |
| 03 | mMenuItems = new ArrayList<MenuItem>(); |
| 04 | |
| 05 | Set<String> categories = new HashSet<String>(); |
| 06 | |
| 07 | |
| 08 | Cursor c = mDb.getMenuItems(); |
| 09 | |
| 10 | while (c.moveToNext()) { |
| 11 | |
| 12 | |
| 13 | String category = c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.CATEGORY)); |
| 14 | |
| 15 | categories.add(category); |
| 16 | |
| 17 | |
| 18 | MenuItem menuItem = new MenuItem(); |
| 19 | |
| 20 | |
| 21 | menuItem.setCategory(category); |
| 22 | |
| 23 | |
| 24 | menuItem.setName(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.NAME))); |
| 25 | |
| 26 | |
| 27 | menuItem.setDescription(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.DESCRIPTION))); |
| 28 | |
| 29 | |
| 30 | menuItem.setNutrition(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.NUTRITION))); |
| 31 | |
| 32 | |
| 33 | menuItem.setPrice(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.PRICE))); |
| 34 | |
| 35 | |
| 36 | menuItem.setImageName(c.getString(c.getColumnIndexOrThrow(RestaurantDatabase.MenuColumns.IMAGENAME))); |
| 37 | |
| 38 | mMenuItems.add(menuItem); |
| 39 | |
| 40 | |
| 41 | } |
| 42 | |
| 43 | |
| 44 | c.close(); |
| 45 | |
| 46 | mCategoryList = new ArrayList<String>(categories); |
Code Snippet 2. Accessing Menu Items From The Database ++
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.
For more such Android resources and tools from Intel, please visit the Intel® Developer Zone
Source: https://software.intel.com/en-us/android/articles/using-a-database-with-your-android-app
Follow Us
