Saturday, 19 May 2012

Create Database in Android using SQLite

Hi all ,Its my great pleasure to write an article about Database in Android.We consider you have some knowledge of Android application development,Java like Singleton class and Database also.So first have a look on XML part :

Menu.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="25"
            android:onClick="btCreate"
            android:text="Create"
            android:textStyle="bold" />

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="25"
            android:onClick="btUpdate"
            android:text="Update"
            android:textStyle="bold" />

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="25"
            android:onClick="btSearch"
            android:text="Search"
            android:textStyle="bold" />

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="25"
            android:onClick="btShowAll"
            android:text="ShowAll"
            android:textStyle="bold" />
    </LinearLayout>

</LinearLayout>
create.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Name:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etName"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Phone:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etPhone"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Email:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etEmail"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <Button
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="50"
            android:onClick="btnSubmit"
            android:text="Submit:"
            android:textStyle="bold" />

        <Button
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="50"
            android:onClick="btnSubmitAgain"
            android:text="SubmitAgain"
            android:textStyle="bold" />
    </LinearLayout>

</LinearLayout>
Update.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Search By Mobile No:"
        android:textSize="20dp"
        android:textStyle="bold|italic" />

    <EditText
        android:id="@+id/etUpdateByMobile"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Name:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etUpdateName"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Phone:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etUpdatePhone"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Email:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etUpdateEmail"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <Button
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="50"
            android:drawableLeft="@drawable/update"
            android:onClick="btnUpdate"
            android:text="Update"
            android:textStyle="bold" />

        <Button
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="50"
            android:drawableLeft="@drawable/delete"
            android:onClick="btnDelete"
            android:text="Delete"
            android:textStyle="bold" />
    </LinearLayout>

</LinearLayout>

Search.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Search By Mobile No:"
        android:textSize="20dp"
        android:textStyle="bold|italic" />

    <EditText
        android:id="@+id/etSearchByMobile"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Name:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etSearchName"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:weightSum="100" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="80"
            android:text="Email:"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/etSearchEmail"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="20" />
    </LinearLayout>

</LinearLayout>
Show.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <TextView
        android:id="@+id/txtName"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="name" />

    <TextView
        android:id="@+id/txtPhone"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Phone" />

    <TextView
        android:id="@+id/txtEmail"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Email" />

</LinearLayout>

DataBaseHandler.java package com.dilip.databaseapp;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public class DataBaseHandler {
                SQLiteDatabase database;
                private static DataBaseHandler obj;
                private final String TABLE_NAME = "db_table1";
                final String COLUMN_FIRST = "Name";
                final String COLUMN_SECOND = "phone";
                final String COLUMN_THIRD = "email";

                private DataBaseHandler(Context context) {
                                DataBase dbobj = new DataBase(context, "mydb.db", null, 1);
                                database = dbobj.getWritableDatabase();
                }

                public static DataBaseHandler getinstance(Context context) {
                                if (obj == null) {
                                                obj = new DataBaseHandler(context);
                                }
                                return obj;
                                // TODO Auto-generated method stub

                }

                public void insertData(String name, String phone, String email) {
                                ContentValues values = new ContentValues();
                                values.put(COLUMN_FIRST, name);
                                values.put(COLUMN_SECOND, phone);
                                values.put(COLUMN_THIRD, email);
                                database.insert(TABLE_NAME, null, values);
                }

                public void updateData(String name, String phone, String email, String id) {
                                ContentValues values = new ContentValues();
                                values.put(COLUMN_FIRST, name);
                                values.put(COLUMN_SECOND, phone);
                                values.put(COLUMN_THIRD, email);

                                database.update(TABLE_NAME, values, BaseColumns._ID + "=" + id, null);
                }

                public void deleteData(String id) {
                                database.delete(TABLE_NAME, BaseColumns._ID + "=" + id, null);
                }

                public Cursor getData() {
                                String[] columns = { BaseColumns._ID, COLUMN_FIRST, COLUMN_SECOND,
                                                                COLUMN_THIRD };
                                return database
                                                                .query(TABLE_NAME, columns, null, null, null, null, null);
                }

                private class DataBase extends SQLiteOpenHelper {

                                public DataBase(Context context, String name, CursorFactory factory,
                                                                int version) {
                                                super(context, name, factory, version);
                                                // TODO Auto-generated constructor stub
                                }

                                @Override
                                public void onCreate(SQLiteDatabase db) {

                                                db.execSQL("CREATE TABLE " + TABLE_NAME + "(" + BaseColumns._ID
                                                                                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_FIRST
                                                                                + " varchar(50)," + COLUMN_SECOND + " varchar(20),"
                                                                                + COLUMN_THIRD + " varchar(30));");

                                }

                                @Override
                                public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                                                // TODO Auto-generated method stub

                                }

                }

}

Menu.java
package com.dilip.databaseapp;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
public class Menu extends Activity {

                @Override
                protected void onCreate(Bundle savedInstanceState) {
                                // TODO Auto-generated method stub
                                super.onCreate(savedInstanceState);
                                setContentView(R.layout.menu);
                }

                public void btCreate(View v) {
                                Intent create = new Intent(this, Create.class);
                                startActivity(create);
                }

                public void btUpdate(View v) {
                                Intent update = new Intent(this, Update.class);
                                startActivity(update);
                }

                public void btSearch(View v) {
                                Intent search = new Intent(this, Search.class);
                                startActivity(search);
                }

                public void btShowAll(View v) {
                                Intent showAll = new Intent(this, ShowAll.class);
                                startActivity(showAll);
                }
}

Update.java
package com.dilip.databaseapp;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.KeyEvent;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.TextView.OnEditorActionListener;
import android.widget.Toast;
public class Update extends Activity {
                DataBaseHandler Handler;
                EditText updateName, updatePhone, updateEmail, updateByPhone;
                String id;

                @Override
                protected void onCreate(Bundle savedInstanceState) {
                                // TODO Auto-generated method stub
                                super.onCreate(savedInstanceState);
                                setContentView(R.layout.update);
                                Handler = DataBaseHandler.getinstance(this);
                                updateName = (EditText) findViewById(R.id.etUpdateName);
                                updatePhone = (EditText) findViewById(R.id.etUpdatePhone);
                                updateEmail = (EditText) findViewById(R.id.etUpdateEmail);
                                updateByPhone = (EditText) findViewById(R.id.etUpdateByMobile);
                                updateByPhone.setOnEditorActionListener(new OnEditorActionListener() {

                                                public boolean onEditorAction(TextView v, int actionId,
                                                                                KeyEvent event) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                                return false;
                                                }

                                });
                                updateByPhone.addTextChangedListener(new TextWatcher() {

                                                @SuppressWarnings("unused")
                                                public void afterTextChanged1(Editable s) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                }

                                                public void beforeTextChanged(CharSequence s, int start, int count,
                                                                                int after) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                }

                                                public void onTextChanged(CharSequence s, int start, int before,
                                                                                int count) {
                                                                search();

                                                }

                                                public void afterTextChanged(Editable s) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                }
                                });

                }

                public void search() {
                                Cursor cursor = Handler.getData();
                                if (cursor.moveToFirst()) {
                                                String phoneNo;
                                                phoneNo = updateByPhone.getText().toString();
                                                do {
                                                                String s1 = cursor.getString(2);
                                                                if (phoneNo.compareTo(s1) == 0) {
                                                                                id = cursor.getString(0);
                                                                                updateName.setText(cursor.getString(1));
                                                                                updateEmail.setText(cursor.getString(3));
                                                                                updatePhone.setText(cursor.getString(2));

                                                                }
                                                } while (cursor.moveToNext());

                                }

                }

                public void btnUpdate(View v) {
                                Handler.updateData(updateName.getText().toString(), updatePhone
                                                                .getText().toString(), updateEmail.getText().toString(), id);
                                Toast.makeText(this, "One Row Updated", Toast.LENGTH_SHORT).show();
                }

                public void btnDelete(View v) {
                                Handler.deleteData(id);
                                updateName.setText("");
                                updateEmail.setText("");
                                updatePhone.setText("");
                                updateByPhone.setText("");
                                Toast.makeText(this, "One Row Deleted", Toast.LENGTH_SHORT).show();
                }
}

Search.java
package com.dilip.databaseapp;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.KeyEvent;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.TextView.OnEditorActionListener;
import android.widget.Toast;
public class Search extends Activity {
                DataBaseHandler Handler;

                EditText searchbyMobile, searchName, searchEmail;

                @Override
                protected void onCreate(Bundle savedInstanceState) {
                                // TODO Auto-generated method stub
                                super.onCreate(savedInstanceState);
                                setContentView(R.layout.search);
                                searchbyMobile = (EditText) findViewById(R.id.etSearchByMobile);
                                searchName = (EditText) findViewById(R.id.etSearchName);
                                searchEmail = (EditText) findViewById(R.id.etSearchEmail);
                                Handler = DataBaseHandler.getinstance(this);
                                searchbyMobile.setOnEditorActionListener(new OnEditorActionListener() {

                                                public boolean onEditorAction(TextView v, int actionId,
                                                                                KeyEvent event) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                                return false;
                                                }

                                });
                                searchbyMobile.addTextChangedListener(new TextWatcher() {

                                                @SuppressWarnings("unused")
                                                public void afterTextChanged1(Editable s) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                }

                                                public void beforeTextChanged(CharSequence s, int start, int count,
                                                                                int after) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                }

                                                public void onTextChanged(CharSequence s, int start, int before,
                                                                                int count) {
                                                                search();

                                                }

                                                public void afterTextChanged(Editable s) {
                                                                // TODO Auto-generated method stub
                                                                search();
                                                }
                                });

                }
public void search() {
                                Cursor cursor = Handler.getData();
                                if (cursor.moveToFirst()) {
                                                String phoneNo;
                                                phoneNo = searchbyMobile.getText().toString();
                                                do {
                                                                String s1 = cursor.getString(2);
                                                                if (phoneNo.compareTo(s1) == 0) {
                                                                                searchName.setText(cursor.getString(1));
                                                                                searchEmail.setText(cursor.getString(3));

                                                                }
                                                } while (cursor.moveToNext());

                                }

                }
}
ShowAll.java
package com.dilip.databaseapp;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.provider.BaseColumns;
import android.widget.CursorAdapter;
import android.widget.SimpleCursorAdapter;
public class ShowAll extends ListActivity {
                DataBaseHandler Handler;
                @Override
                protected void onCreate(Bundle savedInstanceState) {
                                // TODO Auto-generated method stub
                                super.onCreate(savedInstanceState);
                                // setContentView(R.layout.showall);
                                Handler = DataBaseHandler.getinstance(this);
                                Cursor cursor = Handler.getData();
                                // startManagingCursor(cursor);
                                String[] from = new String[] { "Name", "phone", "email",
                                                                BaseColumns._ID };
                                int[] to = new int[] { R.id.txtName, R.id.txtPhone, R.id.txtEmail };
                                if (cursor.moveToFirst()) {
                                                // Now create an array adapter and set it to display using our row
                                                CursorAdapter adapter = new SimpleCursorAdapter(this,
                                                                                R.layout.show, cursor, from, to);
                                                setListAdapter(adapter);
                                }
                }}
And finally Output is as follows: