Channels ▼
RSS

Database

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.
 

Comments:

ubm_techweb_disqus_sso_-d05acb6607c1d6e59fbf7a507d3fd195
2013-01-22T13:35:07

Hi Ankit,

You're actually given a limit to the length of these articles, when you write them, so it wouldn't have fitted, or would have required a follow up piece.

I actually actively avoided getting into things like SELECTS and the use of the cursor object though, as there were already lots of articles on this out there. What interested me more was one that looked at both the basics of interacting with and maintaining the database; in particular in tricky scenarios such as schema upgrades.

So while I touch on cursors very briefly, it's not the focus of the piece.


Permalink
ubm_techweb_disqus_sso_-fd1022affa29d67cf964b8c6e204abac
2012-12-24T07:06:18

Hi Gaddo ,nice article with good explanation on using Sql Lite in Android.
If you can please also mention how we use the cursor objects to fetch the data this will complete the article in all respects.
Thanks,
Ankit


Permalink
ubm_techweb_disqus_sso_-d05acb6607c1d6e59fbf7a507d3fd195
2012-06-06T09:08:47

Absolutely it will, as will adding comments to the SQL scripts, for example.

Ultimately, I wrote the parser for demonstration purposes for the article, not as a production-level piece of code. It can be used as the basis for a production-level parser or you could write it in a completely different, and far more efficient, fashion.

But as far as the article is concerned, it does its job.


Permalink
ubm_techweb_disqus_sso_-ecde709d08fbdcb0b50d997ec5c93cdf
2012-06-02T08:48:32

So as far as you use String.split(";") to parse sql-script simple insert
insert into anyTable values (1, ';');
will break parser, won't it?


Permalink

Video