Use of SQLite

This is a sample program which shows usage of SQLite in android application for saving data. This application has two buttons in the main menu one for saving information and another for showing all saved information. Last blog published in this forum is How to handle bluetooth settings from your application.

Underlying Algorithm:

Basic description of algorithm in step by step form:
1.) Create a Project DatabaseSample.
2.) Replace the following code with res/layout/main.xml :

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout android:layout_width="fill_parent" android:layout_height="fill_parent" xmlns:android="http://schemas.android.com/apk/res/android" >
    <TextView android:id="@+id/text" android:layout_height="wrap_content" android:layout_width="wrap_content" android:text="DataBase Sample App" android:textSize="24.5sp" android:layout_gravity="center"/>
    <Button android:layout_below="@id/text" android:text="Save Data" android:id="@+id/button1" android:layout_gravity="center" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginLeft="50dp" android:layout_marginTop="50dp">
    </Button>
    <Button android:layout_below="@id/button1" android:text="Check Data" android:id="@+id/button2" android:layout_gravity="center" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginLeft="50dp">
    </Button>
</RelativeLayout>

3.) Create a helper class DataManipulator.java that can create the database and encapsulate other SQL details. In this DataManipulator class we will include an important inner class OpenHelper that provides a SQLiteOpenHelper.

package com.app.DatabaseSample;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import java.util.ArrayList;
import java.util.List;

public class DataManipulator
{
    private static final  String DATABASE_NAME = "mydatabase.db";
    private static final int DATABASE_VERSION = 1;
    static final String TABLE_NAME = "newtable";
    private static Context context;
    static SQLiteDatabase db;

    private SQLiteStatement insertStmt;
	
    private static final String INSERT = "insert into " + TABLE_NAME + " (name,number,skypeId,address) values (?,?,?,?)";
    public DataManipulator(Context context) {
	DataManipulator.context = context;
	OpenHelper openHelper = new OpenHelper(DataManipulator.context);
	DataManipulator.db = openHelper.getWritableDatabase();
	this.insertStmt = DataManipulator.db.compileStatement(INSERT);
    }
    public long insert(String name,String number,String skypeId,String address) {
	this.insertStmt.bindString(1, name);
	this.insertStmt.bindString(2, number);
	this.insertStmt.bindString(3, skypeId);
	this.insertStmt.bindString(4, address);
	return this.insertStmt.executeInsert();
    }

    public void deleteAll() {
	db.delete(TABLE_NAME, null, null);
    }

    public List<String[]> selectAll()
    {
	List<String[]> list = new ArrayList<String[]>();
	Cursor cursor = db.query(TABLE_NAME, new String[] { "id","name","number","skypeId","address" }, null, null, null, null, "name asc"); 
	int x=0;
	if (cursor.moveToFirst()) {
  	   do {
		String[] b1=new String[]{cursor.getString(0),cursor.getString(1),cursor.getString(2),
cursor.getString(3),cursor.getString(4)};
		list.add(b1);
		x=x+1;
 	   } while (cursor.moveToNext());
	}
	if (cursor != null && !cursor.isClosed()) {
	   cursor.close();
	} 
	cursor.close();
	return list;
   }

   public void delete(int rowId) {
	db.delete(TABLE_NAME, null, null); 
   }

   private static class OpenHelper extends SQLiteOpenHelper {
	OpenHelper(Context context) {
 	     super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
	     db.execSQL("CREATE TABLE " + TABLE_NAME + " (id INTEGER PRIMARY KEY, name TEXT, number TEXT, skypeId TEXT, address TEXT)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
        {
	     db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
	     onCreate(db);
	}
   }
}

4.) Create a save.xml in res/layout/save.xml:

<?xml version="1.0" encoding="UTF-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:id="@+id/LinearLayout01" android:orientation="vertical"	android:layout_width="wrap_content" android:layout_height="wrap_content" android:paddingLeft="20sp">
    <TextView android:id="@+id/button1_label" android:layout_height="fill_parent" android:layout_width="wrap_content" android:text="Fill Information" android:textSize="24.5sp" android:layout_gravity="center" android:layout_marginBottom="25dip" />
    <TextView android:layout_width="fill_parent"	android:layout_height="wrap_content" android:text="Name:" android:layout_gravity="center" android:textSize="14.5sp" />
    <EditText android:id="@+id/name" android:layout_width="fill_parent" android:layout_height="wrap_content" />
    <TextView android:layout_width="fill_parent"	android:layout_height="wrap_content" android:text="Telephone Number:" android:layout_gravity="center" android:textSize="14.5sp" />
    <EditText android:id="@+id/number" android:layout_width="fill_parent" android:layout_height="wrap_content" />
    <TextView android:layout_width="fill_parent"	android:layout_height="wrap_content" android:text="Skype ID:" android:layout_gravity="center" android:textSize="14.5sp" />
    <EditText android:id="@+id/skypeId" android:layout_width="fill_parent" android:layout_height="wrap_content" />
    <TextView android:layout_width="fill_parent"	android:layout_height="wrap_content" android:text="Address:" android:layout_gravity="center" android:textSize="14.5sp" />
    <EditText android:id="@+id/address" android:layout_width="fill_parent" android:layout_height="wrap_content" />
    <LinearLayout android:id="@+id/LinearLayout02" android:orientation="horizontal" android:layout_width="wrap_content" android:layout_height="wrap_content" android:paddingLeft="20sp">
    <Button android:text="Save" android:id="@+id/Button01add" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="20sp" android:layout_marginLeft="20sp">
    </Button>
    <Button android:text="Back" android:id="@+id/Button01home" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="20sp" android:layout_marginLeft="20sp">
    </Button>
    </LinearLayout>
</LinearLayout>

5.) Create a Activity SaveData.java to Save the information :

package com.app.DatabaseSample;

import android.app.Activity;
import android.view.View;
import android.view.View.OnClickListener;
import android.app.AlertDialog;
import android.app.Dialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.widget.EditText;
import android.widget.TextView;

public class SaveData extends Activity implements OnClickListener {  
    private DataManipulator dh;     
    static final int DIALOG_ID = 0;

    protected void onCreate(Bundle savedInstanceState){
	super.onCreate(savedInstanceState);
	setContentView(R.layout.save);
        View add = findViewById(R.id.Button01add);
	add.setOnClickListener(this);
	View home = findViewById(R.id.Button01home);
	home.setOnClickListener(this);           
    }

    public void onClick(View v){
	switch(v.getId()){
 	    case R.id.Button01home:
		Intent i = new Intent(this, DatabaseSample.class);
		startActivity(i);
	    break;
	    case R.id.Button01add:
  		View editText1 = (EditText) findViewById(R.id.name);
		View editText2 = (EditText) findViewById(R.id.number);
		View editText3 = (EditText) findViewById(R.id.skypeId);
		View editText4 = (EditText) findViewById(R.id.address);	
		String myEditText1=((TextView) editText1).getText().toString();
		String myEditText2=((TextView) editText2).getText().toString();
		String myEditText3=((TextView) editText3).getText().toString();
		String myEditText4=((TextView) editText4).getText().toString();
		this.dh = new DataManipulator(this);
		this.dh.insert(myEditText1,myEditText2,myEditText3,myEditText4);
		showDialog(DIALOG_ID);
            break;
	}
    }  
    protected final Dialog onCreateDialog(final int id) {
	Dialog dialog = null;
	switch(id) {
	    case DIALOG_ID:
		AlertDialog.Builder builder = new AlertDialog.Builder(this);
		builder.setMessage("Information saved successfully ! Add Another Info?").setCancelable(false).setPositiveButton("No", new DialogInterface.OnClickListener() {
			public void onClick(DialogInterface dialog, int id) {
				SaveData.this.finish();
                        }
		}).setNegativeButton("Yes", new DialogInterface.OnClickListener() {
			public void onClick(DialogInterface dialog, int id) {
				dialog.cancel();
			}
		});
		AlertDialog alert = builder.create(); 
		dialog = alert;
		break;

	     default:
	}
	return dialog;
    }
}

6.) Create a ListView to show the all data in a list in res/layout/check.xml :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" >
<TextView android:id="@+id/selection2" android:layout_width="fill_parent" android:layout_height="wrap_content"
android:text="NAME - TELPHONE NO - SKYPE ID- ADDRESS" />
<TextView android:id="@+id/selection" android:layout_width="fill_parent" android:layout_height="wrap_content" />
<ListView android:id="@android:id/list" android:layout_width="fill_parent" android:layout_height="fill_parent" android:drawSelectorOnTop="false" android:textSize="3dip" />
</LinearLayout>

7.) Create a Activity CheckData.java :

package com.app.DatabaseSample;

import java.util.ArrayList;
import java.util.List;
import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;

public class CheckData extends ListActivity  {     
    TextView selection;
    public int idToModify; 
    DataManipulator dm;

    List<String[]> list = new ArrayList<String[]>();
    List<String[]> names2 =null ;
    String[] stg1;
    protected void onCreate(Bundle savedInstanceState){
	super.onCreate(savedInstanceState);
	setContentView(R.layout.check);
        dm = new DataManipulator(this);
	names2 = dm.selectAll();

	stg1=new String[names2.size()]; 
	int x=0;
	String stg;

	for (String[] name : names2) {
		stg = name[1]+" - "+name[2]+ " - "+name[3]+" - "+name[4];
		stg1[x]=stg;
		x++;
	}

	ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,stg1);
        this.setListAdapter(adapter);
	selection=(TextView)findViewById(R.id.selection);
   }      

   public void onListItemClick(ListView parent, View v, int position, long id) {
	selection.setText(stg1[position]);
   }
}

9.) Run the Application.

Steps to Create:

1.) Open Eclipse. Use the New Project Wizard and select Android Project Give the respective project name i.e. DatabaseSample. Enter following information:
Project name: DatabaseSample
Build Target: Android 2.1
Application name: DatabaseSample
Package name: com.app.DatabaseSample
Create Activity: DatabaseSample

On Clicking Finish DatabaseSample code structure is generated with the necessary Android Packages being imported along with DatabaseSample.java. DatabaseSample class will look like following :

package com.app.DatabaseSample;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;

public class DatabaseSample extends Activity implements OnClickListener {
    /** Called when the activity is first created. */
   @Override
   public void onCreate(Bundle savedInstanceState) {
	super.onCreate(savedInstanceState);
	setContentView(R.layout.main);
	View button1Click = findViewById(R.id.button1);
	button1Click.setOnClickListener(this);
	View button2Click = findViewById(R.id.button2);
	button2Click.setOnClickListener(this);        
   }
   @Override
   public void onClick(View v) {
	// TODO Auto-generated method stub
	switch(v.getId()){
  	    case R.id.button1:
		Intent i = new Intent(this, SaveData.class);  
		startActivity(i);
	    break;
	    case R.id.button2:
		Intent i1 = new Intent(this, CheckData.class);  
		startActivity(i1);
	    break;
	}
    }
}

To download source code of above example Click Here.

Output – The final output:

About the Author

Leave a Reply 69 comments

Krish - June 30, 2011 Reply

Thank you Sushant! Sample example program works perfectly.

Codefrux Technologies - July 2, 2011 Reply

Oh Thank you for this Database Example..!Its worked perfectly.. Thanks for sharing…

Emmanuel - July 2, 2011 Reply

Thanks a bunch!! its is exactly what i was looking for in my project.
Qn: i am working on an android app which i want to connect to webservice (in this case json-rpc webservice i have created in php) using Json-rpc method. could you please help me on this if u don’t mind.. thanks again.

Sushant - July 4, 2011 Reply

Emmanuel,

You are always welcome.

Check out “Json-rpc from android blog” . It includes enough code and content to handle Json-rpc.

Thanks.

Carox - July 4, 2011 Reply

Thanks! I was looking for this code.

madhavi - July 18, 2011 Reply

Hi when i am trying to build the application it is saying the no compatable avd with 2.1 version. and build is not complete

    Sushant - July 18, 2011 Reply

    You might be using mismatched build and AVD version.

    To add a new AVD in eclipse:

    * Window => “Android SDK and AVD manager”
    * New

    Check what version you are using in your project and create a new AVD with that target version.

    Thanks..

deepa - July 18, 2011 Reply

Ya ..arget version has to be Android 2.2

yaseen - July 24, 2011 Reply

Hi shusant,
when ever i run the program and click to any function it say “This king of launch is configured to open the debug perspective when it suspends.
This debug perspective is designed to support application debugging. It incorporates views for displaying the debug stack variables and breakpoint management.”

what it means and what is the error i am repeating which gives this.
plz help me in this

    Sushant - July 26, 2011 Reply

    You might not running your app. You are debugging it. You can do following to fix this problem:

    1.) Press Ctrl+F11 and run your app instead of start debugging.
    2.) Go to Run->Remove All breakpoints and then run your app.

    The message you are getting is debugger message.

madhavi - July 25, 2011 Reply

Thanks deepa.its working

madhavi - July 25, 2011 Reply

hi,i am able to see my screen with 2 btns save , check data.But when clicked gettting bellow err : The application datsabasesample(process com.app.DatabseSample) has stopped unexpectedly .Please try again with a btn ‘FORCE CLOSE’
Plz give me solution

    Sushant - July 26, 2011 Reply

    Hello Madhavi.

    I have updated the blog with the source code. You can download it and check it with your code.

lally - August 4, 2011 Reply

hi
shushant great article

can u help me how to display the saved information as a list not in the same single line

plz giv me solution

lally - August 5, 2011 Reply

hi
shushant great article

can u help me how to display the saved information as a list not in the same single line

plz giv me solution

shailesh - August 5, 2011 Reply

hi sushant first thanx for valuable guidance ………this application work proper but where is the database file generated ,i check in the file explorer file in com.package name but …i cant able to find database file

nomie madrid - August 10, 2011 Reply

is it possible to save voices in SQLite? Also, I can retrieve those voices I save.

bajick - August 22, 2011 Reply

THank you very much Sushant!

KIRABO - September 22, 2011 Reply

hey im new to android development, i did this sample and it managed to work perfectly i was wondering if i can be able to view the database in sqlite browser , how would i be able to open it , any assistance will be very much appreciated

KIRABO - September 22, 2011 Reply

hey thanks fr the post i was wondering whether it was possible to export this database to be viewed in sqlite browser if so please advice im new in android development thanks

david - September 24, 2011 Reply

hello sushant. I want to do my final year project with android.i want to make an app dat can mange a list of things to do and alert the user when its time.any help will be appreciated??

david - September 25, 2011 Reply

sushant, am trying to add date and time to every contact in the list so i can recieve a notification in the title bar when its that time.pls can you help??

Ahmad Faraz - October 1, 2011 Reply

Thanks its working properly.It helped me a lot.Now i want to retrieve data from sqlite using select query with where clause.Can u tell me how can i do this.Please help me.Its very urgent.Thanks in advance.

Osama - October 11, 2011 Reply

Is it really necessary to have android 2.2? Will it work on android 1.6?

Natalie - October 30, 2011 Reply

Hi Sushant,

I am new to android programming and I am a student. I am working on a project similar to DNA-Alignment given in this site “https://market.android.com/details?id=blink.dna.align&feature=search_result#?t=W251bGwsMSwxLDEsImJsaW5rLmRuYS5hbGlnbiJd” but I am using Boyer-Moore Algorithm to align the strings. I have done with the framework “main.xml” but I’m getting confused as I don’t know how to manipulate the comparison step by step in android using indents as shown in this java applet “http://home.avvanta.com/~doyle/bmi.html”. Please help me as I am working on my final project for my academics. I am looking forward to hearing from you!

sheetal - November 6, 2011 Reply

Hey can anyone help me regarding use of OCR in Android.

lhtan - November 14, 2011 Reply

how call the data from the database? help….

suraj - January 30, 2012 Reply

Thank a lot for this wonderful post!!!samples program works perfectly and helped us understand sqlite better!!!

pradeep - January 31, 2012 Reply

Can any one explain about Services,Content providers and Broadcast receivers in android.

Thanks in advance.
Pradeep

Adnan - February 7, 2012 Reply

Hi,

Tnx voor the example!

Could you enhance the code, so that we could change saved data aswel?!

BR,

Adnan

nakul - February 12, 2012 Reply

Really i was looking for this code

Gordian - March 16, 2012 Reply

Tnx Sushant!

deepak - March 20, 2012 Reply

Thanks its helped lot……..

JaysonP - March 21, 2012 Reply

Sushant you’re great.. thanks for this very nice post. thank you very big

Rabiu - April 3, 2012 Reply

Hi,i have same errors as madhavi when i try click save data it gives me gives error:the application datsabasesample(process com.app.DatabseSample) has stopped unexpectedly .Please try again with a btn ‘FORCE CLOSE’, i run d app without debugging your code have been the only code i exported without errors. really need this for my project.

Rabiu - April 3, 2012 Reply

Hi,i have same errors as madhavi when i try click save data it gives me gives error:the application datsabasesample(process com.app.DatabseSample) has stopped unexpectedly .Please try again with a btn ‘FORCE CLOSE’, i run d app without debugging your code have been the only code i exported without errors. really need this for my project. Thanks

savita - April 15, 2012 Reply

hi madhavi…i m gettng the same problm of “The application datsabasesample(process com.app.DatabseSample) has stopped unexpectedly .Please try again with a btn ‘FORCE CLOSE’”….plz tell me whot did u do to overcome it….

mpande - April 18, 2012 Reply

Very helful, b blassed.

mpande - April 18, 2012 Reply

Very useful, thanx alot.

chip - April 19, 2012 Reply

Sushant, can I know how to display few columns from database table to different text views? Because I want to make my data to be display more organize.. Thank you

Andrew - May 4, 2012 Reply

I hit the same problem with the Force Close, and looked at my logs. This showed:

FATAL EXCEPTION: main
android.content.ActivityNotFoundException: Unable to find explicit activity class {com.app.DatabaseSample/com.app.DatabaseSample.SaveData};

I checked my code against the source code, and found the activities missing from the manifest xml:

Now it WORKS!! This was a good activity for me, not only did I learn about integration with the sqllite database, but also a good example of using multiple activities. I am going to build a similar app, and will post a link back here.

THANKS FOR THE GREAT EXAMPLE!!!
-ac

Andrew - May 4, 2012 Reply

I hit the same problem with the Force Close, and looked at my logs. This showed:

FATAL EXCEPTION: main
android.content.ActivityNotFoundException: Unable to find explicit activity class {com.app.DatabaseSample/com.app.DatabaseSample.SaveData};

I checked my code against the source code, and found the activities missing from the manifest xml:

<activity android:name=".SaveData" android:label="@string/app_name">
</activity>
<activity android:name=".CheckData" android:label="@string/app_name">

</activity>

Now it WORKS!! This was a good activity for me, not only did I learn about integration with the sqllite database, but also a good example of using multiple activities. I am going to build a similar app, and will post a link back here.

THANKS FOR THE GREAT EXAMPLE!!!
-ac

Andrew - May 4, 2012 Reply

where does the program write, and store the db files ?

nysb - July 15, 2012 Reply

Hi Sushant,

Great article, thanks.

I just started learning Android, and hope you can help with a question of your sample app. In the app, the background is black, while the text’s foreground is white. That seems to be opposite to the default color setting. Could you let me know how you achieved that? I tried looking through the sample code, but couldn’t figure out.

Thanks for your help.

nysb - July 15, 2012 Reply

Hi Sushant,

Great article, thanks.

I just started learning Android, and hope you can help with a question of your sample app. In the app, the background is black, while the text’s foreground is white. That seems to be opposite to the default color setting. Could you let me know how you achieved that? I tried looking through the sample code, but couldn’t figure out.

Thanks for your help.

rahul kumawat - July 19, 2012 Reply

thanks sushant it works fine…. nice tutorial :)

rahul kumawat - July 19, 2012 Reply

thanks sushant it works fine…. awesome tutorial :)

rahul kumawat - July 19, 2012 Reply

thanks sushant it works fine…. perfect tutorial :)

Carlos - July 19, 2012 Reply

Very nice thanks

Kimswagga - August 3, 2012 Reply

Sample Code is Cool!! Post more!!!!

John - September 26, 2012 Reply

Nice looking app. However when I load the code into Eclipse I get an error in the onClick(View). I loaded the code into a Android project from existing code. When I look at the build properties it tells me that it is using 2.3.3. Could this be the reason that I’m seeing these error.

Valerie - October 11, 2012 Reply

Hi

I have the same error as savita, rabiu,…

the application datsabasesample(process com.app.DatabseSample) has stopped unexpectedly .Please try again with a btn ‘FORCE CLOSE’

Does some of you have a solution already?

mudit srivastava - October 23, 2012 Reply

thnks for this code..

Rambabu - October 31, 2012 Reply

Hi susanth thanks and i have small doubt how i could the table and table data by using sqlite browser for above application

Rambabu - October 31, 2012 Reply

Hi susanth thanks and i have small doubt how i could see the table and table data by using sqlite browser for above application

Thanks in advance

meriem - November 1, 2012 Reply

hi,pleaz i need your help when i click save button or check data button i receive :”unfortunately “myproject” has qtopped” also the expession “showDialog(DIALOG_ID);” is barred it tells me that:”The method showDialog(int) from the type Activity is deprecated”

sudhakar - January 2, 2013 Reply

can anyone suggest solution for foreclose problem after you press save button…..and its showing fatal error in log cat also please help

sudhakar - January 2, 2013 Reply

Thank you vey much for the example finally found the solution …Thank you

Rashmi - January 23, 2013 Reply

Hi Sushant,plz help me i have followed the same code as in the blog.. i can see SaveData and Check button in thw mean while onclick of any one button it is followed with err:”Unfortunately DatabaseSample has stopped” plz do help me to come out of this error its very needfull for me.
Thanks

premal - February 16, 2013 Reply

good tutorial….but i want to know is it possible to store received sms directly in database?if you have any idea then please explain.i appreciate if any suggestion from you.

siva - February 19, 2013 Reply

thank you, its very useful……

roshan - March 5, 2013 Reply

nice one……….

supriya - March 8, 2013 Reply

Hello,
plz let me know hot to use database created in sqlite browser in android app

tatiana - March 19, 2013 Reply

Hey.

Great code. Can you help me a little bit? How can i delete the entries?
Thanks.

yasharvc - April 9, 2013 Reply

Hi and thank for your effort but I think there must be some difference between deleteAll and delete(int id)…
Did u get what I mean :)

Amardeep - May 1, 2013 Reply

how to import this project in ADT and how the database work with this project

Amardeep - May 1, 2013 Reply

where is database in project

parthu - May 13, 2013 Reply

thanks a lottttt….

Pooria - October 9, 2013 Reply

Tank you Sushant, this is very helpful for me

Leave a Reply: