Use of SQLite

69

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

69 Responses

  1. John says:

    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.

  2. Valerie says:

    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?

  3. mudit srivastava says:

    thnks for this code..

  4. Rambabu says:

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

  5. Rambabu says:

    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

  6. meriem says:

    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”

  7. sudhakar says:

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

  8. sudhakar says:

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

  9. Rashmi says:

    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

  10. premal says:

    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.

  11. siva says:

    thank you, its very useful……

  12. roshan says:

    nice one……….

  13. supriya says:

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

  14. tatiana says:

    Hey.

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

  15. yasharvc says:

    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 :)

  16. Amardeep says:

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

  17. Amardeep says:

    where is database in project

  18. parthu says:

    thanks a lottttt….

  19. Pooria says:

    Tank you Sushant, this is very helpful for me

Leave a Reply


+ 6 = eight

Proudly designed by Theme Junkie.