Android SQLite Database Example

Today I’m going to tell you how to developed Android database application with SQLite .
In above video you can see the working example.

Now we look into details of this example app. In here I have two main layouts first one is to

  1. Create Database & Table
  2. Add Data to a table
  3. Drop Database

This is my activity_main,xml its a main layout of my sample app

Screenshot at 2013-05-14 20:26:42


<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    tools:context=".MainActivity"
    android:background="@drawable/b" >

    <AbsoluteLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentBottom="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentRight="true"
        android:layout_alignParentTop="true" >

    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_x="12dp"
        android:layout_y="10dp"
        android:onClick="createDB"
        android:text="CreateDB and Table" />

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_x="17dp"
        android:layout_y="100dp"
        android:text="Name"
        android:textColor="@android:color/white"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText1"
        android:layout_width="275dp"
        android:layout_height="wrap_content"
        android:layout_x="13dp"
        android:layout_y="129dp"
        android:ems="10" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_x="18dp"
        android:layout_y="187dp"
        android:text="Age"
        android:textColor="@android:color/white"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <Button
        android:id="@+id/button4"
        android:layout_width="142dp"
        android:layout_height="wrap_content"
        android:layout_x="167dp"
        android:layout_y="10dp"
        android:onClick="drop"
        android:text="DropDB" />

    <Button
        android:id="@+id/button3"
        android:layout_width="266dp"
        android:layout_height="wrap_content"
        android:layout_x="26dp"
        android:layout_y="357dp"
        android:onClick="viewData"
        android:text="View Data" />

    <EditText
        android:id="@+id/editText2"
        android:layout_width="148dp"
        android:layout_height="wrap_content"
        android:layout_x="15dp"
        android:layout_y="215dp"
        android:ems="10"
        android:inputType="number" />


    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_x="188dp"
        android:layout_y="214dp"
        android:onClick="addData"
        android:text="Save Data" />

    </AbsoluteLayout>

</RelativeLayout>

This is my MainActivity class which is related to above layout.


package com.madushanka.dbtest;

import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends Activity {

	SQLiteDatabase myDB = null;
	String TableName = "Student";
	String Data = "";
	EditText studentName;
	EditText studentAge;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		
		super.onCreate(savedInstanceState);
		
		setContentView(R.layout.activity_main);
		
		studentName = (EditText) findViewById(R.id.editText1); // getting text fields (Edit Text)
		
		studentAge= (EditText) findViewById(R.id.editText2);

	}
	
	// this method will trigger when user clicked " CreateDB & Table " button 

	public void createDB(View v) { 

		try {
			// Get the database if database is not exists create new database 
			// Database name is " test " 
			
			myDB = this.openOrCreateDatabase("test", MODE_PRIVATE, null);
			
			// Create table with tow columns (Name and Age)
		
			myDB.execSQL("CREATE TABLE IF NOT EXISTS " + TableName
					+ " (Name VARCHAR, Age INT(3));");

			Toast.makeText(getBaseContext(), "Database & Table Created",Toast.LENGTH_LONG).show();
			
		} catch (Exception e) {

			Log.e("Error", "Error", e);

			Toast.makeText(getBaseContext(),
					"Error in creating atabase or Table", Toast.LENGTH_LONG).show();

		}

	}
	
	// this method will trigger when user clicked " Drop DB " button 

	public void drop(View v) {

		try {
			// we use deleteDatabse("Database name ") method to delete database 
			// we have to pass String parameter to give the name of database
			
			deleteDatabase("test"); 

			Toast.makeText(getBaseContext(), "Databse Deleted", Toast.LENGTH_LONG)
					.show();
		} catch (Exception e) {

			Log.e("Error", "Error", e);

			Toast.makeText(getBaseContext(), "Error in deleting",Toast.LENGTH_LONG).show();

		}

	}

	// this method will trigger when user clicked " Save Data " button 

	
	public void addData(View v) {
		
		try{
			
			// getting created database or if database is not exists create new database
			
		myDB = this.openOrCreateDatabase("test", MODE_PRIVATE, null);


		String name =studentName.getText().toString();
		int age = Integer.parseInt(studentAge.getText().toString().trim());

		myDB.execSQL("INSERT INTO " + TableName + " (Name, Age)" + " VALUES ('"
				+ name + "', " + age + ");");

		Toast.makeText(getBaseContext(), "Date Saved ", Toast.LENGTH_LONG).show();
		}
		
		catch(Exception e){
			
			Log.e("Error", "Error", e);
			
			Toast.makeText(getBaseContext(), "No Database found   ", Toast.LENGTH_LONG).show();
			
		}
	}

	// this method will trigger when user clicked " View Data " button 

	public void viewData(View v) {
		
		// creating new intent using ViewActivity Class and start activity to show table data

		Intent i = new Intent(this,ViewActivity.class);
		startActivity(i);

	}

}


Second Layout is activity_view.xml using this layout I will dispaly the table content (table recodes)

Screenshot at 2013-05-14 20:27:46


<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    tools:context=".ViewActivity"
    android:background="@drawable/b" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_centerVertical="true"
        android:text="No Data Found" 
        android:textSize="18dp"
        android:textColor="@android:color/white"/>

</RelativeLayout>

This is my View Activity Class which is related to above layout


package com.madushanka.dbtest;

import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.util.Log;
import android.widget.TextView;
import android.widget.Toast;

public class ViewActivity extends Activity {
	
	SQLiteDatabase myDB = null;
	String TableName = "Student";
	String Data = "";


	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_view);
		
		try {

			myDB = this.openOrCreateDatabase("test", MODE_PRIVATE, null);
			
			//getting the cursor object 
			
			Cursor c = myDB.rawQuery("SELECT * FROM " + TableName, null);

			int Column1 = c.getColumnIndex("Name");
			int Column2 = c.getColumnIndex("Age");

			c.moveToFirst();
			
			if (c != null) {

				do {
					String Name = c.getString(Column1);
					
					int Age = c.getInt(Column2);
					
					Data = Data + Name + "\t\t" + Age + "\n\n";
					
				} while (c.moveToNext());
			}

			TextView tv = new TextView(this); // creating Text View to show data in the app
			tv.setTextColor(Color.WHITE);
			tv.setBackgroundResource(R.drawable.b);
			tv.setTextSize(18F);
			tv.setText("\n"+"Name \t| Age \n ----------------------\n"+Data);
			setContentView(tv);  // set created text view as Content View 
			
		}

		catch (Exception e) {
			
			Log.e("Error", "Error", e);
			
			Toast.makeText(getBaseContext(), "No Data found   ", Toast.LENGTH_LONG).show();
			
		} finally {
			if (myDB != null)
				myDB.close();
		}
	}

	

}


You can download sample apk file from here

About these ads

14 thoughts on “Android SQLite Database Example

  1. sunitha says:

    Can you please show me an example for doing an Android Sqlite database of a registration form(name,age,DOB,phno,email,state,blood group) with code

    • DatePicker datePicker = (DatePicker) findViewById(R.id.datePicker1);
      int day = datePicker.getDayOfMonth();
      int month = datePicker.getMonth() ;
      int year = datePicker.getYear();

      then use insert into………. etc

  2. KEERTHI.THAVVA says:

    While running the this code i am geeting an error when i click on view data.
    i.e NO DATA FOUND. even i store some data into database. hlep me to resolve this error

  3. create another activity to sign in after the registration done start that sign in activity using intent

    Intent intent = new Intent(this, your_Signin_Activity.class);
    startActivity(intent);

  4. VinayVanama says:

    hey its nice tutorial what i need is after entering the data in the editText and save the data it should be append to the listview in the same activity,can u please help me how to write it…?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s