Creating a SQLite Database


Build your own Android App Dev Empire

Get Instant Access

Android devices will ship with an internal SQLite database. The purpose of this database is to give users and developers a location in which to store information that can be used in Activities.

If you have used Microsoft SQL Server or SQLite, the structure and process for using Android's SQLite database will not seem foreign. Whatever your experience, this section covers all the skills you need to create and use a fully functional SQLite database.

You are going to create a database on your Android Emulator. To do this, you need to access the Android SDK command-line tools and use the shell command to access the Android server.

Refer to Chapter 3 to refresh your memory on setting your Path statement and using the command-line tools.

Once you are shelled into the server, you need to navigate to the location where the database will reside. All SQLite databases for Android reside in the data/data/<package>/ databases directory. Use the cd command to change directories from your current location to the data directory, and then again to the <package> directory. Use ls to list the files and directories at your current location if you are unsure of the <package> directory name. Change the directory to the location where <package> is, android_programmers_ guide.FindAFriend, as shown in the following illustration.

31 C:\Windows\system32\cmd.exe - adb shell — !□! X|

it cd data cd data R cd data cd data B ls ls a n dr oid_programmers_guide . fi n dro id LBS an dro id_pro gr amine vs_gu ide , ñ n dro idU ie ws an droid_pro grammers_guide.ft n droidPhone D iale r ^n dro id_ppo Qi'annei's ide - Google API an droid_pro n dro idCamera android j uni. y uuy le . diiilru id. pruvider!» .uuii Lctu Lïï .android.providers .im c om.go o gle.andro v ide rs.t elephony com. tfoogle . andro id. pro v ide re . eattincfc c o m.go o gle.andro elopme nt com. cfoogrlc . andro id. plionc c o m.go o gle.andro id.xnppS ervice c o m. go o g le . andro id. xrrppS e11 ings c o m.go o gle.andro id.mas f pro xyserv ic e rnm.gnnglr.andrnid-gta1ksfittings c o m. go o g le . andro id. gta lkse rv ic e android.sereenshot tu ni. yuuy le . cil [dru id. luiictr lander* a n dro id_pro gr ammers _gu ide.Google ftpps an dro id_pr o gr amme r s _gu ide . H e 1 lo Uor ldTe xt com.yoogla.andro id.snake

^a n dro id_pro gr ammer s _gu ide .Hello Uor Id I mage a n dro id_pro grammers_guide.F indft Frie n d


If you do not have an android_programmers_guide.FindAFriend directory, create your application as explained in the previous section and quickly run the "Hello World!" default application that is created with your project. This will ensure that you have the correct directory.

Once you have navigated to the android_programmers_guide.FindAFriend directory, run the ls command. This command lists all the files and directories within a specific directory. This command should come up empty. As of right now, there are no files or directories inside your android_programmers_guide.FindAFriend directory.

Given that SQLite databases must be in a databases directory within this directory, this is a good time to create one. The tool mkdir creates directories for you. Therefore, run the command mkdir databases. This creates the directory that will hold your database.


Right now, you are most likely shelled into the server as root. Therefore, the directory you just created will be accessible only to root. This will prove very problematic when you attempt to run your Activity, because each Activity has a different user. To get around this, for development purposes, run chmod 777 databases to grant everyone access to the databases directory. However, in the future, you must be cautious about granting everyone rights to sensitive items on Android. Give to specific users only those rights that they need for specific items.

Now that you have created the directory for the database, you can create the database. Use the cd command to navigate into your databases directory. After you are in the databases directory, use the sqlite3 tool to create your database and name it friends.db, as follows:

# sqlite friends.db

If the command is successful, you should see a SQLite3 version message, in this case 3.5.0, and a SQLite3 prompt—sqlite>. This indicates that the database itself has been successfully created but is still empty. The database contains no tables or data. With this in mind, your next step is to create a table for your Activity's data.

You need to create a table called friends. This table will hold id, name, location, created, and modified fields. These fields will offer more than enough information for your project.

If you are not familiar with SQLite, a SQLite command must terminate with a semicolon. This is helpful if you want to span commands across prompts. Pressing the ENTER key without terminating a SQLite command will give you a continuation prompt, ...>. You can continue to enter your command at this prompt until you use the semicolon. SQLite will treat such continued commands as one full command once the semicolon is used.

To create your friends table within your database, enter the following command at the sqlite> prompt:

CREATE TABLE friends (_id INTEGER PRIMARY KEY, name TEXT, location TEXT, created INTEGER, modified INTEGER);

If your command executes successfully, you will be returned to the sqlite> prompt, as shown in the following illustration.

E39 C:\Wiridows\system32\cmd.exe - adb shell



com .google . and.roid. no tepad

andro id_programmers_guide.HelloUoridImage


It cd andro id_pr o gramme rs_guide .FindAFriend

cd android programmers_guide.FindAFriend

It Is


It mkdir databases


mkdir databases

It cbmod 777 databases

chmod 777 databases

It Is -1

Is -1

drwxrwxrwx root root 2008-05-03 00:20 databases

It cd databases

cd databases

It sqlite3 friends.db

sqlite3 friends.db

SQLite version 3.5.0

Enter ".help" for instructions



...> name TEXT,

name TEXT,

...> location TEXT,

location TEXT,

...> created INTEGER,

created INTEGER,

...> modified INTEGER>;

nodified INTEGER);


sqlite >

Your database is now ready to be used, and you can exit SQLite. Use the command .exit to exit. You can then quit your shell session and return to Eclipse.

Creating the database was the first step in setting up your application. Now that the database and corresponding table are created, you need a method to access the data. The data access method employed by Android is a Content Provider. The following section walks you through creating a custom Content Provider for your new database and accessing your data.

Was this article helpful?

0 0

Post a comment