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"?-->

<button>
</button>
<button>
</button>

 

 

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"?-->

<button>
</button>
<button>
</button>

 

 

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"?-->
android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" >
android:text="NAME - TELPHONE NO - SKYPE ID- ADDRESS" />

 

 

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 adapter = new ArrayAdapter(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:

Related Posts

28 Apr, 2011

Use Of Services
26 Apr, 2011

Use Of Intents
02 Aug, 2009

An Average of 10 Apps Downloaded Per Month by iPhone and Android Users
15 Jul, 2020

Top 10 Android App Development Trends | 2020 Guide
30 Aug, 2016

5 Best Resources to Get Started with Android Nougat
07 Sep, 2015

Android Studio Introduction
29 Aug, 2015

Services – An Android Component
18 Aug, 2015

Applying MediaCodec On An Open Source Android Audio Player
Previous Post ButtonPressed Example In iPhone. Next Post How To Change Background Color After Pressing Button In iPhone

Leave a Comment:

70 comments
Krish says June 30, 2011

Thank you Sushant! Sample example program works perfectly.

Reply
Codefrux Technologies says July 2, 2011

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

Reply
Emmanuel says July 2, 2011

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.

Reply
Sushant says July 4, 2011

Emmanuel,

You are always welcome.

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

Thanks.

Reply
Carox says July 4, 2011

Thanks! I was looking for this code.

Reply
madhavi says July 18, 2011

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

Reply
    Sushant says July 18, 2011

    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..

    Reply
deepa says July 18, 2011

Ya ..arget version has to be Android 2.2

Reply
yaseen says July 24, 2011

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

Reply
    Sushant says July 26, 2011

    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.

    Reply
madhavi says July 25, 2011

Thanks deepa.its working

Reply
madhavi says July 25, 2011

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

Reply
    Sushant says July 26, 2011

    Hello Madhavi.

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

    Reply
lally says August 4, 2011

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

Reply
lally says August 5, 2011

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

Reply
shailesh says August 5, 2011

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

Reply
nomie madrid says August 10, 2011

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

Reply
bajick says August 22, 2011

THank you very much Sushant!

Reply
KIRABO says September 22, 2011

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

Reply
KIRABO says September 22, 2011

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

Reply
david says September 24, 2011

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??

Reply
david says September 25, 2011

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??

Reply
Ahmad Faraz says October 1, 2011

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.

Reply
Osama says October 11, 2011

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

Reply
Breakhearts says October 16, 2011

[…] application which shows the Location by using Google Map. Last blog published in this forum is Use of SQLite. Underlying Algorithm:  Basic description of algorithm in step by step […]

Reply
Natalie says October 30, 2011

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!

Reply
sheetal says November 6, 2011

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

Reply
lhtan says November 14, 2011

how call the data from the database? help….

Reply
suraj says January 30, 2012

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

Reply
pradeep says January 31, 2012

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

Thanks in advance.
Pradeep

Reply
Adnan says February 7, 2012

Hi,

Tnx voor the example!

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

BR,

Adnan

Reply
nakul says February 12, 2012

Really i was looking for this code

Reply
Gordian says March 16, 2012

Tnx Sushant!

Reply
deepak says March 20, 2012

Thanks its helped lot……..

Reply
JaysonP says March 21, 2012

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

Reply
Rabiu says April 3, 2012

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.

Reply
Rabiu says April 3, 2012

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

Reply
savita says April 15, 2012

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….

Reply
mpande says April 18, 2012

Very helful, b blassed.

Reply
mpande says April 18, 2012

Very useful, thanx alot.

Reply
chip says April 19, 2012

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

Reply
Andrew says May 4, 2012

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

Reply
Andrew says May 4, 2012

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

Reply
Andrew says May 4, 2012

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

Reply
nysb says July 15, 2012

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.

Reply
nysb says July 15, 2012

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.

Reply
rahul kumawat says July 19, 2012

thanks sushant it works fine…. nice tutorial 🙂

Reply
rahul kumawat says July 19, 2012

thanks sushant it works fine…. awesome tutorial 🙂

Reply
rahul kumawat says July 19, 2012

thanks sushant it works fine…. perfect tutorial 🙂

Reply
Carlos says July 19, 2012

Very nice thanks

Reply
Kimswagga says August 3, 2012

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

Reply
John says September 26, 2012

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.

Reply
Valerie says October 11, 2012

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?

Reply
mudit srivastava says October 23, 2012

thnks for this code..

Reply
Rambabu says October 31, 2012

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

Reply
Rambabu says October 31, 2012

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

Reply
meriem says November 1, 2012

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”

Reply
sudhakar says January 2, 2013

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

Reply
sudhakar says January 2, 2013

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

Reply
Rashmi says January 23, 2013

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

Reply
premal says February 16, 2013

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.

Reply
siva says February 19, 2013

thank you, its very useful……

Reply
roshan says March 5, 2013

nice one……….

Reply
supriya says March 8, 2013

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

Reply
tatiana says March 19, 2013

Hey.

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

Reply
yasharvc says April 9, 2013

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 🙂

Reply
Amardeep says May 1, 2013

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

Reply
Amardeep says May 1, 2013

where is database in project

Reply
parthu says May 13, 2013

thanks a lottttt….

Reply
Pooria says October 9, 2013

Tank you Sushant, this is very helpful for me

Reply
Add Your Reply

Popular posts

Socket Programming
 Android
Aug 17, 2011
Use Of SQLite
 Android
Jun 29, 2011
TicTacToe Game Implementation
 Android
Apr 16, 2011
View Pager Example In Android Development
 Android
Aug 14, 2012