Different Android applications work with a range of data types. In this article we are going to see how Android handles and persists data within its applications. To achieve most of the features offered by the modern mobile devices, e.g. tracking contacts, handling events and tasks, a mobile operating system and its applications must be capable of not only storing a large volume of data, but also keeping track of a huge data volume. The data is usually structured in rows and columns, similar to a spreadsheet or a standard database.
Ahead of a traditional application’s requirements for storing data, the Android application life cycle demands quick and consistent persistence of data for it to survive the uncertain mobile environment, where the devices can suddenly lose power or the Android operating system can arbitrarily decide to remove your application from memory. Android provides the light-weight but powerful SQLite relational database engine for persisting data.
In this article, we will provide a simple SQL tutorial so that user can learn to work with Android SQLite persistence. We will also walk through an interesting application similar to MJAndroid, which provides a real-world look at how to manipulate a database in the Android System.
We understand that any relational database presents an efficient, structured and generic system which can be used to manage persistent information. Having a database in place, we have seen that applications use structured queries to do any kind of CRUD operation on the persistent two-dimensional data matrices called tables. Programmers, or Developers, need to write the queries in a high-level language called the Structured Query Language, or more commonly, SQL. SQL is the common language for the relational database management systems (RDBMSs) that have been a popular tool for database management since the late 1970s. Actually, SQL became an industry-wide standard when it was adopted by NIST (National Institute of Standards and Technology) in 1986 and ISO in 1987. It is also used in almost all database tools e.g. teradata, DB2, Oracle and SQL Server installations to, as we shall see, store email on our phone.
Database tables are a natural fit for the data that contains numerous instances of the same kind of thing which is a typical occurrence in software development. E.g. a contact list consists of multiple contacts, all of which potentially have the same type of information like address, mobile, etc. Each “row” of the data in a table stores information about a different person, while each “column” stores a specific attribute of each person: names in one column, address in another column, and mobile number in a third column. When someone is correlated to multiple things such as multiple addresses, the relational databases have ways of handling that too, but we would not go into such detail in this article.
Android OS uses the SQLite database engine which is a self-contained, transactional database engine. It doesn’t require any extra server process. Most of the applications and environments outside the Android world use this database with ease. In contrast to the concept of the traditional enterprise databases, which provide an array of features in terms of fault tolerance and concurrency, SQLite removes the features which are not required in order to achieve smaller goal. As per illustration, there are database systems which use static typing, while SQLite does not store any database type information. Rather this responsibility of keeping the type information is pushed into the coding area of high-level languages, e.g. Java, which maps the database structures into high-level types.
Though SQLite is not a Google project, but Google has contributed a lot to its development. There is al team of software developers spread globally who are dedicated to enhance the capability and the reliability of this database. Reliability is an important aspect of the SQLite database. SQLite library is designed to handle different types of system failures, e.g. low memory, disk errors, power failures etc. It must be noted that these unwanted situations occur very frequently in the handheld devices. Under any circumstance, the database shouldn’t be allowed to go in a state from where it can’t be recovered, since this would lead to a showstopper situation on mobile devices where we have lots of critical data stored in the database. Also the SQLite database is not susceptible to easy corruption.
SQL Data Definition Commands
Statements in the SQL language fall into following two categories:
- Category 1 - SQL Statements which are used to create and modify tables
- Category 2 - SQL Statements which are used to perform certain operations on the data. These operations are create, read, update, and delete.
In this document we will talk about the first category of SQL statements.
This is the first step any developer takes while working with a database. We need to create the database table first in order to store data. The CREATE TABLE command creates a new table in a SQLite database. This command requires a name which should be unique among the tables in the database, followed by the various columns to hold the data. Each column name should be unique within the table. Also the data type of these columns should be defined while creating the table. While creating the table, we can specify some other attributes for every column as under –
- Whether the values should be unique or not.
- Whether the column should have any default value or not.
- Whether the column should allow any null value or not.
A table is similar to a spreadsheet which has multiple rows and columns. Let us consider the example of an employee database, each row in the table contains the information for one employee.
This deletes a table which is added using the CREATE TABLE statement. It takes the name of the table as an input and deletes the table. Once deleted, we lose the data that is stored in the table.
CREATE TABLE EMPLOYEE ( EMPID int(5) AUTO_INCREMENT, FIRSTNAME varchar(50) NOT NULL, MIDDLENAME varchar(50), LASTNAME varchar(50) NOT NULL, EMAIL_ID varchar(50) NOT NULL, DATE_OF_BIRTH date NOT NULL, DATE_OF_JOINING date NOT NULL, LAST_DATE date, ACTIVITY_FLAG varchar(10), PRIMARY KEY (EMPID));
DROP TABLE EMPLOYEE;
As a standard practice followed in any SQL tool, we must terminate every SQL command in SQLLite with a semicolon ‘;’. If we require modifying a table which is already created, we need to use the ‘ALTER TABLE’ command.
SQLite data types
While creating tables we must specify the data type against each column which we add in the tables that we define. SQLite supports the following data types which can be used to define the column data types:
- TEXT - A text string stored using encoding UTF-8, UTF-16BE, or UTF-16LE. This data type is being used most commonly.
- REAL - A floating-point value, stored as an 8-byte IEEE (Institute of Engineer Electrical & Electronics) floating-point number.
- BLOB - Arbitrary binary data, stored exactly as if it was input. We can use the BLOB data type to store any kind of variable-length data, such as an executable file, or a downloaded image. Normally, blobs can add a large performance overhead to a mobile database and we should usually avoid using them.
- INTEGER - A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
Database constraints are used to mark a column with particular attributes or properties. These constraints enforce data-oriented limitations, such as requiring all values in a column to be unique, e.g. a column which is declared to store the Social Security numbers of all the employees in an organization shouldn’t allow null values. There could be other constraints based on the business model and the relationship models e.g. foreign key constraints.
Database tables should have one or more column(s) which can uniquely identify a single row. In SQL terminology, it is referred to as the PRIMARY KEY. This column should be used only as an identifier for each row in the database table and has no meaning to the rest of the world. Thus, we do not need to specify values for this column. As an alternative, we can let SQLite assign incrementing integer values as new rows are added. There are databases which require to specially marking the column as auto incrementing in order to achieve this result. SQLite also offers an explicit AUTOINCREMENT constraint, but it auto increments primary keys by default. The incrementing standards in the column take on a role similar to an opaque object pointer in a high-level language such as Java or C. Other database tables and code in a high-level language can use the column to reference that particular row.
When the database rows have a unique primary key, it becomes easier for us to start thinking about the dependencies and relations between the tables. For example a database used to store the information about employee and employer has an integer column called employer_id that would contain the primary key values of rows in a different table which is used to store the information of the employers. This table can be called employers. If we perform a query and select one or more rows from the employers table, we can grab their IDs and look up employees in an employee’s table through the table's employer_id column. This allows a program to find the employees of a given employer. The two tables (stripped down to a few columns relevant to this example) might look like this:
CREATE TABLE employers (company_id INTEGER PRIMARY KEY, // store unique id for the company company_name TEXT //put the company name); CREATE TABLE employees (name TEXT, annual_salary REAL NOT NULL CHECK (annual_salary > 0), employer_id REFERENCES employers(_id));
The idea of a table referring to another table’s primary key has got appropriate support in SQL or Structured Query Language. This is referred to as the FOREIGN KEY column constraint, which enforces the validity of cross table references.
This constraint tells the database those integers in a column with a foreign key constraint must refer to valid primary keys of database rows in another table.
If we insert a row into the employees table with an employer_id for a row that does not exist in the employers table, many flavors of SQL will raise a constraint violation. This may help us to avoid orphaned references, also known as enforcement of foreign keys (Concept is coming from Enforcement Integrity Constraint).
However, the foreign key constraint in SQLite is optional, and is turned off in Android. In Android, we cannot rely on a foreign key constraint to catch incorrect foreign key references, so we should be careful while creating database schemas that use foreign keys.
There are several other constraints with less far-reaching effects:
- UNIQUE - This enforces that the value of the given column should be different from the values in that column in all existing rows, whenever a row is inserted or updated. Any insert or update operation that attempts to insert a duplicate value will result in a SQLite constraint violation.
- NOT NULL - This enforces that a value is required a value in the said column. We can’t have NULL in such columns. It must be noted that a primary key is both UNIQUE and NOT NULL.
- CHECK - This is used to handle Boolean-valued expression and requires that the expression returns true for any value inserted in the column. In the example shown above, the CHECK (annual_salary > 0), attribute returns Boolean value.
Database transactions are done by making one or multiple SQL statements. It is very important that these statements must execute all together in order to make the transaction successful. This is termed as ‘ATOMIC’ behavior of transaction. Either all statements execute successfully or none of them should have any effect on the database. This is very important as there could be cases when your application encounters an unfortunate situation such as a system crash. A transaction guarantees that if any of the SQL commands fail for some reason or the other, the device fails partway through a given sequence of operations, none of the operations will affect the database. In database jargon, SQLite transactions support the widely recited ACID transaction properties:
ACID (Atomicity, Consistency, Isolation and Durability)
ACID stands for -
Given the volatility of the Android mobile environment, it is recommended to make our application transaction support fault tolerance on top of the ACID features.
SQL and the Data Model for Android Applications
After gathering these basic Structured Query Language programming concepts, let us start to think about how to use these concepts in an Android application. Our motive should be to have an application based on the most commonly used Model-View-Controller or the MVC design pattern that possesses a well-written User Interface programs, specifically in a well organized fashion which suits well for Android infrastructure.
There is a fundamental difference between mobile phone apps and desktop apps when we talk about handling data persistence. Desktop-based applications, for example word processors, text editors, drawing programs, presentation programs etc, use the document model of the MVC or the Model-View-Controller pattern. Here when we open a document, the tool reads the document into memory, and converts into objects in the memory to form the data model. These programs are responsible for the following operations –
- prepare the views against these data model
- process the user input using their controller and
- finally, modify the data model as shown in the image below -
Figure 1. MVC Pattern.
The key significance of this design is that we explicitly open and save documents in order to make the data model persist between program invocations. We have already seen how the user interface components work in the Android System. We will travel around the Android APIs (Application Programming Interface) for database manipulation, which will help us prepare an implementation of an application data model which works in a new style.
Robust use of Android system combines data models and user interface elements in a different manner. Apps run on mobile devices with limited memory, which can run out of battery power at unpredictable and inappropriate times. Small mobile devices also place a premium on reducing the interactive problem on the user: reminding a user that he ought to save a document when he is trying to answer a phone call is not a good user experience. The whole concept of a document is not present in Android. The user should always have the accurate data in hand and must be confident about the data safety and security. To make it easy to store and use application data incrementally, item by item, and always have it in persistent memory without explicitly saving the whole data model. Android provides support in its database, view, and activity classes for database-centric data model. We will see how to use the Android database classes to implement this kind of model.
The Android Database Classes
Let us talk about the Java classes which are responsible to provide access to the SQLite functions discussed in the earlier sections of this document:
- SQLite Database: This is the Android’s Java interface to its relational database, SQLite. It also supports an SQL or Structured Query Language implementation which is rich enough to handle any requirement for mobile application.
- Cursor: A cursor is a container which holds the results of a database query and also supports the commonly used MVC design pattern. Cursors are very much similar to the JDBC result sets which returns the value of any database query. A cursor also has the ability to represent multiple objects without creating instance for each and every one. Using a cursor, we can scroll up to the beginning of the query results and can access single row, one at a time as and when required. We have methods named as Cursor.getAs*(int column_Number) (e.g., getAsString) which are used to access data within the cursor. The cursor returns the values depending upon the current position of the cursor index, which we can increase by calling the method Cursor.moveToNext, or decrease by calling Cursor.moveToPrevious as we need. The current index of the cursor is considered as a pointer to the result object.
- SQLiteOpenHelper: The online helper provides a life cycle framework to create and upgrade our application database. This class is used to provide assistance in the critical task which transitions the data from one version of an application to a new set of database tables in a newer version of same or another application.
- SQLiteQueryBuilder: The query builder provides a high-level abstraction to create SQLite queries to be used in Android applications. In fact we can simplify the task of writing queries for our application and it also saves an enormous amount of time.
Android and Social Networking
The recent Android devices come with an array of good features. One of these features is their ability to execute the applications with enhanced opportunities for social networking among all users. This feature is quite similar to the reality of the Internet — the first generation of Internet applications were developed to provide user access to information and most of those applications are equally popular.
The next tradition of Internet applications was to connect one user to another. Applications e.g. Orkut, Skype, Facebook, YouTube, Whatsapp and many others enriches us with the ability to connect with people of similar interests. This also allows our application users to provide some or all of the content which can make the application more users oriented.
Android has the ability to use this concept and add new dimension in the mobile world. It is expected that a new set of generation of applications are to be developed for users of mobile devices e.g. social networking applications which are easy to use while walking down the street, applications which are aware of the user’s location, applications which allow the easy sharing of the content-rich information e.g. pictures, contacts, videos, etc.
MJAndroid provides a real time example to show how Android can address this growing functionality. We can take the example of the MJAndroid MicroJobs application, where the user tries to locate one important job in his/her geographic vicinity, where a user can work for some time to earn some extra money. There are employers who are looking for temporary help. These employers then furnish the details of the available jobs, descriptions, hours, and offered wages on a web-based database which can be accessed over Android mobile phones or devices. People, who are looking for part time jobs, can use the Micro Jobs application to
- Access these databases
- Look for these kinds of jobs in their area
- Communicate with their friends and discuss about the potential employers and the jobs
- Finally, call the employer directly if they are interested to take up the job.
For our purposes here, we won’t create an online service; we will just walk through over some canned data on the phone. The application has a lot of other features which extend the central idea in ways that are unique to mobile devices, like the Mapping Concept, where any android device has the ability to support dynamic and interactive maps. We will see that with a very little programming assistance. We should be able to show dynamic maps of our local neighborhood. Also we will start getting location updates from the internal GPS or the Global Positioning System. This will be used to automatically scroll the map as we move. We should be able to scroll the map in two directions, zoom in and out, and even switch to different views e.g. street view, satellite view etc.
Loading and starting the Application
Running MJAndroid from the SDK (Software Development Kit) is slightly complicated given the fact that the application uses a MapView. Android here requires a special Maps API key. Whenever we use a MapView, this key is tightly coupled to our development machine. Since this application relies heavily on the maps Application Programming Interface or API, we will require to have set up our Application Programming Interface key for the application to work properly. To start MJAndroid we just need to open and run the eclipse project for this article as we have done for other articles.
Through this Article, we have tried to deliver some advanced level of Android database application so that the user can easily understand the process of working with data persistence. Here we have discussed the features of the Android database programming model for database applications. First we have discussed the main features of the database. After that we have given a solid idea about the Model View Controller pattern. The Model View Controller is the new market trend for developing the project. Then we have given a standard idea how MVC works with the Android System. After that we discussed an idea about the MJAndroid. Actually, the MJAndroid is very small level Application and its job is searching data from the system. Hope this article will give you a clear idea about data persistence in Android based applications.