Working with SQLite Databases in Android

Posted On August 25, 2010 by Rose Mary filed under Programming

HTML clipboard

In this tutorial, we will see how to work with SQLite Databases and tables in Android.

SQLite is a free, open source, light weight Relational Database Engine, which is widely used for storing data in most of the portable devices including Apple’s iPod. In this way, SQLite has been embedded within the Android platform, which makes it more powerful.

Android-sdk provides easy ways to perform Database operations such as creating databases and tables, inserting values and querying. In this tutorial, we will develop a sample ‘dbeditor’ application, which stores the name and phone number of persons in Database.

This is how our application will finally look like.

First, the project is created using the following command from the android-sdk\tools directory.

C:\android-sdk\tools>activityCreator –out dbeditor mypack.mydemos.dbeditor

Our project has been created within the tools folder as dbeditor.

Then, we have to design the User Interface of the application. It consists of three TextViews( labels), two EditTexts(textfields) , a Spinner(combo) and a Button.(screen-1) ( shown below).

The User Interface is designed using DroidDraw and the xml file ‘main.xml’ is generated. The generated xml file is given below.

( For the method of using DroidDraw and for the process of building,deploying and testing a demo, kindly refer to earlier Android Tutorial by Geetha Ganesan in DeveloperIQ ( july-2008 )

(the tutorial is available at http://geeth.ganesan.googlepages.com )

main.xml

<?xml version="1.0" encoding="utf-8"?>

<AbsoluteLayout

android:id="@+id/widget0"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

xmlns:android="http://schemas.android.com/apk/res/android"

<EditText

android:id="@+id/text1"

android:layout_width="192px"

android:layout_height="47px"

android:textSize="18sp"

android:textColor="#ff000033"

android:layout_x="120px"

android:layout_y="10px"

</EditText>

<EditText

android:id="@+id/text2"

android:layout_width="192px"

android:layout_height="47px"

android:textSize="18sp"

android:layout_x="122px"

android:layout_y="78px"

</EditText>

<Spinner

android:id="@+id/spinner1"

android:layout_width="182px"

android:layout_height="52px"

android:layout_x="10px"

android:layout_y="150px"

</Spinner>

<Button

android:id="@+id/button1"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="OK"

android:layout_x="238px"

android:layout_y="148px"

</Button>

<TextView

android:id="@+id/label1"

android:layout_width="95px"

android:layout_height="41px"

android:text="Name:"

android:textSize="25sp"

android:typeface="serif"

android:textStyle="italic"

android:textColor="#ff33ff00"

android:layout_x="8px"

android:layout_y="15px"

</TextView>

<TextView

android:id="@+id/label2"

android:layout_width="95px"

android:layout_height="41px"

android:text="Number:"

android:textSize="22sp"

android:typeface="serif"

android:textStyle="italic"

android:textColor="#ff33ff00"

android:layout_x="10px"

android:layout_y="82px"

</TextView>

<TextView

android:id="@+id/label3"

android:layout_width="125px"

android:layout_height="47px"

android:textSize="22sp"

android:layout_x="100px"

android:layout_y="262px"

</TextView>

</AbsoluteLayout>

Replace the xml file ‘main.xml’ in tools\contacts\res\layout folder with the generated one.
Next, we have to develop the Java source code. For that, we have to edit the ‘dbeditor.java’ present in tools\dbeditor\src\mypack\mydemos folder.
For SQLite database operations, we need to include the following imports.

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

Cursor interface is like the ‘ResultSet’ interface of Java, which is returned when a table is queried. Along with the necessary widgets, we declare within the dbeditor class, an SQLiteDatabase object and an array containing the list of operations which are to be added to the Spinner. We define the array as,

SQLiteDatabase mydb;private static final String[] array = {"ADD", "DELETE", "VIEW", "UPDATE"};

The following code snippet loads the spinner with the desired values.

ArrayAdapter<String>adapter=newArrayAdapter<String>(this,android.R.layout.simple_spinner_item, array);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinner1.setAdapter(adapter);

Then we create a database named ‘mydb’, open it and create a table named contacts if it doesn’t exist. Code for these operations are enclosed within a try catch block as below.

try{ this.createDatabase("mydb", 1, MODE_PRIVATE, null); mydb = this.openDatabase("mydb", null); mydb.execSQL("CREATE TABLE IF NOT EXISTS contacts (name VARCHAR PRIMARY KEY,number VARCHAR);");}

catch(Exception e)

{text2.setText(""+e);}

We have to set onClickListener for the button. For that, we have created an inner class ‘clicker’ which implements Button.OnClickListener. When the button is clicked, we get the value of the item selected in the spinner and perform the corresponding database operation inside the onClick() method.

The following is the code present in the inner class ‘clicker’.

public void onClick(View v)
{
     Cursor c;
     String s = (String) spinner1.getSelectedItem();
     Object ob1 = text1.getText();
     Object ob2 = text2.getText();
     try
     {
        if(s.equals("ADD"))
         {

mydb.execSQL("INSERT INTO contacts (name,number) values ('"+ob1+"','"+ob2+"');");

    label1.setText(“ADDED”);
}
           else if(s.equals("DELETE"))
           {
mydb.execSQL("DELETE FROM contacts WHERE name='"+ob1+"';");

label1.setText(“DELETED”);

}

else if(s.equals("UPDATE"))

{

mydb.execSQL("UPDATE contacts SET number='"+ob2+"' WHERE name='"+ob1+"';");
                                       label1.setText(“UPDATED”);
           }
           else
           {
c = mydb.rawQuery("SELECT* FROM contacts WHERE name='"+ob1+"';",null);

c.first(); //moves the cursor to the first row.

int n = c.getColumnIndex("number"); //gets the index of the ‘number’ column

String s1 = c.getString(n); //gets the required number.

text2.setText(s1);                                     //displays result in text2.                                          label1.setText(“SHOWING…”);
           }
     }catch(Exception e){text2.setText(""+e);}
}

This is the simplest code for performing database operations through SQL.

The above code provides a basic understanding on how to deal with SQLite Databases and tables. The entire source code of the program is given below.

dbeditor.java

package mypack.mydemos;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.*;

public class dbeditor extends
Activity
{
    EditText text1,text2;
             TextView label1;
     Button button1;
     Spinner spinner1;
     SQLiteDatabase mydb;
     private static final String[] array = {"ADD", "DELETE", "VIEW", "UPDATE"};

     @Override
     public void onCreate(Bundle icicle)
     {
           super.onCreate(icicle);
setContentView(R.layout.main);

text1= (EditText) findViewById(R.id.text1);

text2= (EditText) findViewById(R.id.text2);

label1 = (TextView) findViewById(R.id.label3);

button1 = (Button) findViewById(R.id.button1);

spinner1 = (Spinner) findViewById (R.id.spinner1);

ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item, array);

adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

spinner1.setAdapter(adapter);

button1.setOnClickListener(new clicker());

try
{ this.createDatabase("mydb", 1, MODE_PRIVATE, null);

mydb = this.openDatabase("mydb", null);
mydb.execSQL("CREATE TABLE IF NOT EXISTS contacts (name VARCHAR PRIMARY KEY,number VARCHAR);");

}catch(Exception e){text2.setText(""+e);}

} //--------------------------------------------------

     class clicker implements Button.OnClickListener
     {
public void onClick(View v)
          {
          Cursor c;

String s = (String) spinner1.getSelectedItem();

Object ob1 = text1.getText();
Object ob2 = text2.getText();

try
{                if(s.equals("ADD"))                 {
mydb.execSQL("INSERT INTO contacts (name,number) values ('"+ob1+"','"+ob2+"');");
                                         label1.setText(“ADDED”);
                }
else
if(s.equals("DELETE"))
                {                     mydb.execSQL("DELETE FROM contacts WHERE name='"+ob1+"';");

label1.setText(“DELETED”);
                }
                else if(s.equals("UPDATE"))
                {
                     mydb.execSQL("UPDATE contacts SET number='"+ob2+"' WHERE name='"+ob1+"';");
                                         label1.setText(“UPDATED”);
                }
                else
                {
                  c = mydb.rawQuery("SELECT* FROM contacts WHERE name='"+ob1+"';",null);
c.first();

int n = c.getColumnIndex("number");

String s1 = c.getString(n);                    text2.setText(s1);                                        label1.setText(“SHOWING…”);
                }
                }catch(Exception e){text2.setText(""+e);}
           }
     } //------------------------------------------------------
}

Edit the src\mypack\mydemos\dbeditor.java to the above code. Then build and install the application as a normal application. You can run our application through the emulator. The application will run as predicted.

Accessing the Database through Command Line:
We can access our database through the command line also. For that , navigate to the “android-sdk\tools” directory in a command window.
1. Type adb shell.
2. cd /data/data/mypack.mydemos/databases
3. ls
4. Our databases will show up.
5. Invoke the desired database using sqlite3, such as >sqlite3 mydb.
6. Type .tables.
7. The list of tables present in that database will show up.
8. You can access the table through sql.

Screenshots of the application at various status has been given below:

About Author

T. JAYARAM, studying MCA (Final year) you can reach him on jaiaccet@gmail.com

Added on August 25, 2010 Comment

Comments

Venket K commented, on August 26, 2010 at 10:18 a.m.:

What does it mean to root a phone? Any Idea?

Prof. S. Ashok commented, on August 27, 2010 at 1:25 p.m.:

Dear Venkat
Careful if you choose to root your Android phone. For more on rooting, read our explanatory post on using and understanding root terms.

http://androinica.com/2009/09/08/my-a...

Prof. S. Ashok

Sanjay Sharma commented, on August 27, 2010 at 1:33 p.m.:

Can any one give me the Best institute name to learn Java. I am from Bangalore.

Praveen Kumar commented, on September 2, 2010 at 9:53 p.m.:

SQlite is good enough to handle the enterprise applications data?

suresh commented, on September 19, 2010 at 12:15 p.m.:

hi thank u very much......

suresh commented, on November 11, 2010 at 4:42 p.m.:

hi
thank u very much

suresh commented, on November 11, 2010 at 5:30 p.m.:

hi
thank u very very much
it is really helpful...
especially good at inserting through edittext onclick button...
nobody given correct except here...
please contact me....
take care....

abdalla commented, on December 2, 2010 at 11:04 p.m.:

hello
i tried your code but the compiler is giving me some errors
can you upload it as a zip so i can try it.
thanx

ragu commented, on December 3, 2010 at 12:43 p.m.:

for me it is not working inside onclick event it shows error.it shows that create database statement shows error.

Working with SQLite Databases in Android

Comments

Post a comment