Channels ▼
RSS

Tools

Using SQLite on Android


As with most platforms, Android gives us a few options to store data so that it persists even after we've terminated the application. Of the various ways we can do this, text files — either stored in the application's own files directory or to the phone's SD card — represent one approach. Preferences are also frequently used to store data because they can be both hidden from the user and persist as long as the application is installed. And while not strictly speaking in the same category, Assets can be useful for storing read-only data, too. Assets are essentially files that you bundle into the application package prior to compilation in the project assets folder, which can be accessed at runtime. I will take a closer look at these later.

Sometimes, however, we need to be able to carry out complex operations on persistent data, or the volume of data requires a more efficient method of management than a flat text file or preference entry will allow. This is where a mobile database comes in.

Android comes with SQLite (version 3.5.9+), which has been available on the platform since release 1.5 of the OS (the "Cupcake" release). For readers unfamiliar with SQLite, it is a self-contained, server-less, transactional SQL database. While it has its limitations, SQLite serves as a powerful tool in the Android developer's arsenal.

What I'll principally cover here is one way to use a SQLite database in Android, concentrating on its management; specifically, creation and update, rather than all the runtime operations.

Managing SQLite

To begin with, we can manage SQLite using a class that extends SQLiteOpenHelper, which comes with a constructor and two required methods; onCreate and onUpgrade.

Naturally, the first of these is executed when the constructor is instantiated; it is here that, via the superclass, we provide four important pieces of data:

  • Context. This is the context of the application. It can be useful to set this in the constructor and store it locally for later use in other methods.
  • Database name. This is the filename (as a String) of the physical database file being accessed.
  • Cursor factory. The cursor factory in use, if any.
  • Database Version. This is the version of your database (as an integer), which I'll discuss in more detail later. Your initial version should be 1.

For our example, we put these four pieces together and get the following:

class DB extends SQLiteOpenHelper {

	final static int DB_VERSION = 1;
        final static String DB_NAME = "mydb.s3db";
	Context context;
	
	public DB(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		// Store the context for later use
		this.context = context;
	}

The constructor does two things. First, it checks whether the database exists and, if not, will call the onCreate method. Second, if the database does exist, it will check whether the existing database version number differs from the one implemented in the constructor, so as to determine if the database has been updated. If it has, the onUpgrade method will be called.

Additionally, as we now know that the onCreate method is called only when the database does not exist, it can be used as a handy way to determine if you're dealing with a first run of the application following installation. As such, you can use this method to call any other methods that you need executed only on the first run, such as EULA dialogs.

Let's look at the database itself. For the purposes of this article, I'm just going to use a very simple employee database with a SQL creation script as follows:

CREATE TABLE employees (
 _id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 ext TEXT NOT NULL,
 mob TEXT NOT NULL,
 age INTEGER NOT NULL DEFAULT '0'
);

We can easily construct this by hard-coding and executing the creation SQL, line by line, in our code as follows:

@Override
public void onCreate(SQLiteDatabase database) {
	database.execSQL(
"CREATE TABLE employees ( _id INTEGER PRIMARY KEY " 
	+ "AUTOINCREMENT, name TEXT NOT NULL, ext TEXT NOT NULL, "
	+ "mob TEXT NOT NULL, age INTEGER NOT NULL DEFAULT '0')");
}

As you can see, this can get quite unwieldy once the database reaches a certain size and complexity, so the ideal solution would be to bundle a SQL creation script as an asset file. To use this approach, you need to write a method that takes in a SQL script from the assets directory and parses it, executing it line by line:

@Override
public void onCreate(SQLiteDatabase database) {
executeSQLScript(database, "create.sql");
}

private void executeSQLScript(SQLiteDatabase database, 
                              String dbname) {
ByteArrayOutputStream outputStream = new  
                              ByteArrayOutputStream();
	byte buf[] = new byte[1024];
	int len;
    	AssetManager assetManager = context.getAssets();
	InputStream inputStream = null;
		
	try{
		inputStream = assetManager.open(dbname);
		while ((len = inputStream.read(buf)) != -1) {
			outputStream.write(buf, 0, len);
		}
		outputStream.close();
		inputStream.close();
		    
		String[] createScript = outputStream.toString().split(";");
		for (int i = 0; i < createScript.length; i++) {
		    	String sqlStatement = createScript[i].trim();
			// TODO You may want to parse out comments here
			if (sqlStatement.length() > 0) {
					database.execSQL(sqlStatement + ";");
		    	}
		}
	} catch (IOException e){
		// TODO Handle Script Failed to Load
	} catch (SQLException e) {
		// TODO Handle Script Failed to Execute
	}
}

While this is a more complex approach than simply executing each SQL statement for very simple databases, it quickly pays dividends once the database becomes more complex or you need to pre-populate it. You'll also see that I abstracted the creation into a separate method called executeSQLScript so that it can be reused in other situations, as I'll explain later on.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video