RSS

Android SQLite Database Example

14 May

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
 
10 Comments

Posted by on May 14, 2013 in Android, Java, Uncategorized

 

Tags: , , , , , , , , , , , , ,

10 responses to “Android SQLite Database Example

  1. sunitha

    June 4, 2013 at 5:09 am

    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

     
    • Madushanka Perera

      August 29, 2013 at 3:00 pm

      that one also same as this example in here i have used only name and age
      So add more text fields for get other informations and pass it to database .

       
  2. maddyhacking

    August 28, 2013 at 11:32 am

    hi thanks For this useful information.But i want to save data from radiogroup and save it in database and also retrieve it when needed.Any help from you will be appreciable.

     
    • Madushanka Perera

      August 29, 2013 at 2:56 pm

      you have to create radio group using java code
      first create layout using code
      then get database data to array next using for or while loop add radio options to your layout using that data

       
  3. anil

    March 10, 2014 at 6:52 am

    how to store date in database using datepicker

     
    • Madushanka Perera

      March 10, 2014 at 3:18 pm

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

      then use insert into………. etc

       
  4. KEERTHI.THAVVA

    March 16, 2014 at 6:20 am

    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

     
  5. goks

    March 18, 2014 at 9:42 am

    Will i have to use a DBadapter or Handler to save the data to the database??

     
    • Madushanka Perera

      March 18, 2014 at 12:43 pm

      If you want you can use. Else you can simply do like above example

       

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

 
Follow

Get every new post delivered to your Inbox.

Join 896 other followers

%d bloggers like this: