Use of SQLite

25

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:

Did you enjoy this article?
Share
the
Love
Get Free Updates
  • Krish

    Thank you Sushant! Sample example program works perfectly.

  • http://www.codefruxtechnology.com Codefrux Technologies

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

  • Emmanuel

    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.

  • http://edumobile.org Sushant

    Emmanuel,

    You are always welcome.

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

    Thanks.

  • Carox

    Thanks! I was looking for this code.

  • http://www.edumobile.org/android/android-development/use-of-sqlite/ madhavi

    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

  • http://edumobile.org Sushant

    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

    Ya ..arget version has to be Android 2.2

  • yaseen

    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

  • http://www.edumobile.org/android/android-development/use-of-sqlite/ madhavi

    Thanks deepa.its working

  • http://www.edumobile.org/android/android-development/use-of-sqlite/ madhavi

    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

  • http://edumobile.org Sushant

    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.

  • http://edumobile.org Sushant

    Hello Madhavi.

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

  • Rabiu

    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

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

  • http://www.ifm.ac.tz mpande

    Very helful, b blassed.

  • http://www.ifm.ac.tz mpande

    Very useful, thanx alot.

  • chip

    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

  • Kimswagga

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

  • John

    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

    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

    thnks for this code..

  • Rambabu

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

  • Rambabu

    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

    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”

Proudly designed by Theme Junkie.