Android Sq Lite Data Base

Step 1: Create Sq lite helper class for design your Data base table Structures.

Step 2 : And Call the method of insert,update,delete,search,update method from activity class.
            Refer the following java class and XML files.

SQLiteHelper.java



package com.quitz.test.db;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteHelper {
public static ArrayList<String[]> DATA = new ArrayList<String[]>();
private static final String DATABASE_NAME = "student.db";
private static final int DATABASE_VERSION = 1;
private static final String STUDENT_TABLE = "student";
private Context context;
private SQLiteDatabase db = null;
OpenHelper openHelper;

public SQLiteHelper(Context context) {
this.context = context;

if (db != null)
if (db.isOpen())
db.close();

openHelper = new OpenHelper(this.context);
this.db = openHelper.getWritableDatabase();
}

public void close() {
if (openHelper != null) {
openHelper.close();
}
}

public int insertData(String name, String address, String description) {
int entryId = 0;
ContentValues values = new ContentValues();
values.put("name", name);
values.put("address", address);
values.put("department", description);
entryId = (int) this.db.insert(STUDENT_TABLE, null, values);
return entryId;
}

public ArrayList<String[]> selectdatabase(String search) {
Cursor c = null;
DATA.clear();
String[] columns = new String[] { "id", "name", "address", "department" };
c = db.query(STUDENT_TABLE, columns,
"name=? OR address=? OR department=?", new String[] { search,
search, search }, null, null, "id DESC");
if (c.moveToFirst()) {
do {
if (c.getColumnCount() == 4) {
String[] str = new String[3];
str[0] = c.getString(1);
str[1] = c.getString(2);
str[2] = c.getString(3);
DATA.add(str);
}
} while (c.moveToNext());
}

if (c != null && !c.isClosed()) {
c.close();
}

return DATA;
}

public ArrayList<String[]> selectalldatabase() {
Cursor c = null;
DATA.clear();
String[] columns = new String[] { "id", "name", "address", "department" };
c = db.query(STUDENT_TABLE, columns, null, null, null, null, "id DESC");
if (c.moveToFirst()) {
do {
if (c.getColumnCount() == 4) {
String[] str = new String[3];
str[0] = c.getString(1);
str[1] = c.getString(2);
str[2] = c.getString(3);
DATA.add(str);
}
} while (c.moveToNext());
}

if (c != null && !c.isClosed()) {
c.close();
}

return DATA;
}

public void deletedatabase(String search, String status,String col) {
Cursor c = null;
int id = 0;
boolean statu = false;
String[] columns = new String[] { "id", "name", "address", "department" };
c = db.query(STUDENT_TABLE, columns,
"name=? OR address=? OR department=?", new String[] { search,
search, search }, null, null, "id DESC");
if (c.moveToFirst()) {
do {
if (c.getColumnCount() == 4) {
String[] str = new String[3];
str[0] = c.getString(1);
str[1] = c.getString(2);
str[2] = c.getString(3);
id = c.getInt(0);
statu = true;
}
} while (c.moveToNext());
}

if (c != null && !c.isClosed()) {
c.close();
}
if (statu) {
if (status.equals("update")) {
updateData(col, search,id);
}
if (status.equals("delete")) {
if (id != 0) {
deletedata(id);
}
}
}
}

public void deletedata(int id) {
db.delete(STUDENT_TABLE, "id=?", new String[] { String.valueOf(id) });
}

public void deleteAll() {
deleteTable(STUDENT_TABLE);
}

public void deleteTable(String tn) {
this.db.delete(tn, null, null);
}

public int updateData(String columnname, String updatevalue,int id) {
ContentValues values = new ContentValues();
values.put(columnname, updatevalue);
int entryId = (int) this.db.update(STUDENT_TABLE, values, "id=?",
new String[] { Integer.toString(id) });
return entryId;
}

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 " + STUDENT_TABLE + "("
+ "id INTEGER PRIMARY KEY, " + "name TEXT, "
+ "address TEXT, " + "department TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w("Catch DB",
"Upgrading database, this will drop tables and recreate.");
db.execSQL("DROP TABLE IF EXISTS " + STUDENT_TABLE);
onCreate(db);
}
}

}



AndroidDataBaseActivity.java


package com.test.ui;

import java.util.ArrayList;

import com.quitz.test.db.SQLiteHelper;
import com.test.db.R;

import android.app.Activity;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.Window;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;

public class AndroidDataBaseActivity extends Activity  implements OnClickListener {
    /** Called when the activity is first created. */
private SQLiteHelper SQLHelper;
Button insert,update,dalete,display,searchb,deletall;
EditText nameET,addressET,deptET;
ArrayList<String[]> DATA = new ArrayList<String[]>();
ListView lv ;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.main);      
        SQLHelper = new SQLiteHelper(this);    
        nameET=(EditText)findViewById(R.id.editText1);
        addressET=(EditText)findViewById(R.id.editText2);
        deptET=(EditText)findViewById(R.id.editText3);
        insert=(Button)findViewById(R.id.button1);
        update=(Button)findViewById(R.id.button2);
        dalete=(Button)findViewById(R.id.button3);
        display=(Button)findViewById(R.id.button4);
        searchb=(Button)findViewById(R.id.button5);
        deletall=(Button)findViewById(R.id.button6);
        lv = (ListView) findViewById(R.id.srListView);
        insert.setOnClickListener(this);
        update.setOnClickListener(this);
        dalete.setOnClickListener(this);
        display.setOnClickListener(this);
        searchb.setOnClickListener(this);
        deletall.setOnClickListener(this);
    }

public void onClick(View v) {
switch(v.getId())
{
case R.id.button1:
if(SQLHelper.insertData(nameET.getText().toString(), addressET.getText().toString(),deptET.getText().toString())<0){
Toast.makeText(this, "Failed Insert data", 10).show();
}else{
Toast.makeText(this, "Successfully Inserted", 10).show();
}
break;
case R.id.button2:
Toast.makeText(this, "Please Enter update keywork of Name ", 10).show();
String sear=null;
if(!nameET.getText().toString().equals("")){
sear=nameET.getText().toString();
SQLHelper.deletedatabase(sear,"update","name");
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else if(!addressET.getText().toString().equals("")){
sear=addressET.getText().toString();
SQLHelper.deletedatabase(sear,"update","address");
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else if(!deptET.getText().toString().equals("")){
sear=deptET.getText().toString();
SQLHelper.deletedatabase(sear,"update","department");
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else{
Toast.makeText(this, "Please Enter Search keywork of any one Field", 10).show();
}
break;
case R.id.button3:
String searc=null;
if(!nameET.getText().toString().equals("")){
searc=nameET.getText().toString();
SQLHelper.deletedatabase(searc,"delete","");
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else if(!addressET.getText().toString().equals("")){
searc=addressET.getText().toString();
SQLHelper.deletedatabase(searc,"delete","");
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else if(!deptET.getText().toString().equals("")){
searc=deptET.getText().toString();
SQLHelper.deletedatabase(searc,"delete","");
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else{
Toast.makeText(this, "Please Enter Search keywork of any one Field", 10).show();
}
break;
case R.id.button4:
DATA=SQLHelper.selectalldatabase();
if(!DATA.isEmpty())
       lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
break;
case R.id.button5:
String search=null;
if(!nameET.getText().toString().equals("")){
search=nameET.getText().toString();
DATA=SQLHelper.selectdatabase(search);
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else if(!addressET.getText().toString().equals("")){
search=addressET.getText().toString();
DATA=SQLHelper.selectdatabase(search);
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else if(!deptET.getText().toString().equals("")){
search=deptET.getText().toString();
DATA=SQLHelper.selectdatabase(search);
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
}else{
Toast.makeText(this, "Please Enter Search keywork of any one Field", 10).show();
}
break;
case R.id.button6:
SQLHelper.deleteAll();
DATA=SQLHelper.selectalldatabase();
lv.setAdapter(new MyCustomBaseAdapter(this, DATA));
break;

}
}
}

MyCustomBaseAdapter.java

package com.test.ui;

import java.util.ArrayList;

import com.test.db.R;

import android.content.Context;

import android.view.LayoutInflater;

import android.view.View;

import android.view.ViewGroup;

import android.widget.BaseAdapter;

import android.widget.TextView;

public class MyCustomBaseAdapter extends BaseAdapter {

    private static ArrayList<String[]> searchArrayList;

    private LayoutInflater mInflater;

    public MyCustomBaseAdapter(Context context, ArrayList<String[]> results) {

        searchArrayList = results;

        mInflater = LayoutInflater.from(context);

    }

    public int getCount() {

        return searchArrayList.size();

    }

    public Object getItem(int position) {

        return searchArrayList.get(position);

    }

    public long getItemId(int position) {

        return position;

    }

    public View getView(int position, View convertView, ViewGroup parent) {

        ViewHolder holder;

        if (convertView == null) {

            convertView = mInflater.inflate(R.layout.list, null);

            holder = new ViewHolder();

            holder.txtName = (TextView) convertView.findViewById(R.id.textView1);

            holder.txtCityState = (TextView) convertView

                    .findViewById(R.id.textView2);

            holder.txtPhone = (TextView) convertView.findViewById(R.id.textView3);

            convertView.setTag(holder);

        } else {

            holder = (ViewHolder) convertView.getTag();

        }

        String []s=searchArrayList.get(position);

        holder.txtName.setText(s[0]);

        holder.txtCityState.setText(s[1]);                

        holder.txtPhone.setText(s[2]);

        return convertView;

    }

    static class ViewHolder {

        TextView txtName;

        TextView txtCityState;

        TextView txtPhone;

    }

}

Main.xml

<?xml version="1.0" encoding="utf-8"?>

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:layout_marginLeft="5dp"

    android:layout_marginRight="5dp"

    android:orientation="vertical" >

    <LinearLayout

        android:id="@+id/toplayout"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignParentTop="true"

        android:orientation="vertical" >

        <LinearLayout

            android:id="@+id/linearLayout1"

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:layout_marginLeft="5dp"

            android:layout_marginRight="5dp" >

            <TextView

                android:id="@+id/textView1"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Name"

                android:textSize="16dp"

                android:width="90dp" />

            <EditText

                android:id="@+id/editText1"

                android:layout_width="match_parent"

                android:layout_height="35dp"

                android:textColor="#000"

                android:background="@android:drawable/editbox_background"

                android:ems="10" >

            </EditText>

        </LinearLayout>

        <LinearLayout

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:layout_marginLeft="5dp"

            android:layout_marginRight="5dp" >

            <TextView

                android:id="@+id/textView1"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Address"

                android:textSize="16dp"

                android:width="90dp" />

            <EditText

                android:id="@+id/editText2"

                android:layout_width="match_parent"

                android:layout_height="35dp"

                android:textColor="#000"

                android:background="@android:drawable/editbox_background"

                android:ems="10" />

        </LinearLayout>

        <LinearLayout

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:layout_marginLeft="5dp"

            android:layout_marginRight="5dp" >

            <TextView

                android:id="@+id/textView1"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Dept"

                android:textSize="16dp"

                android:width="90dp" />

            <EditText

                android:id="@+id/editText3"

                android:layout_width="match_parent"

                android:layout_height="35dp"

                android:textColor="#000"

                android:background="@android:drawable/editbox_background"

                android:ems="10" />

        </LinearLayout>

        <LinearLayout

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:gravity="center" >

            

            <Button

                android:id="@+id/button1"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Insert"

                android:width="90dp" />

            <Button

                android:id="@+id/button2"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Update"                

                android:width="90dp" />

            <Button

                android:id="@+id/button5"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

               android:text="Search"                

                android:width="90dp" />

        </LinearLayout>

        <LinearLayout

            android:layout_width="match_parent"

            android:layout_height="wrap_content"

            android:gravity="center" >

            <Button

                android:id="@+id/button3"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Delete"

                android:width="90dp" />

            <Button

                android:id="@+id/button4"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

                android:text="Display"

                android:width="90dp" />

            <Button

                android:id="@+id/button6"

                android:layout_width="wrap_content"

                android:layout_height="wrap_content"

               android:text="DeleteAll"                

                android:width="100dp" />

        </LinearLayout>

    </LinearLayout>

    <LinearLayout

        android:id="@+id/linearLayout2"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/toplayout"

        android:orientation="vertical" >

        <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content" >

        <TextView

            android:id="@+id/textView11"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="NAME"

            android:layout_weight="1" android:gravity="center"/>

        <TextView

            android:id="@+id/textView21"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_weight="1"

            android:text="ADDRESS"

           android:gravity="center" />

        <TextView

            android:id="@+id/textView31"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_weight="1"

            android:text="DEPT"

            android:gravity="center" />

    </LinearLayout>

        <ListView

        android:id="@+id/srListView"

        android:layout_width="fill_parent"

        android:layout_height="fill_parent" />

    </LinearLayout>

    <LinearLayout

        android:id="@+id/bottomlayout"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignParentBottom="true"

        android:orientation="vertical" >

    </LinearLayout>

</RelativeLayout>

List.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" >

    <LinearLayout

        android:layout_width="fill_parent"

        android:layout_height="wrap_content" >

        <TextView

            android:id="@+id/textView1"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_weight="1" android:gravity="center"/>

        <TextView

            android:id="@+id/textView2"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_weight="1"

           android:gravity="center" />

        <TextView

            android:id="@+id/textView3"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_weight="1"

            android:gravity="center" />

    </LinearLayout>

</LinearLayout>

Screen shorts:

                                                                     Delete Data:

                                                      

                                                                      Search Data

Inset Data