SQLite Example

Android ListView from SQLite Database (Insert,Update,Delete):-

Today in this tutorial I will show you:

1. Create table in SQLite
2. Insert data into SQLite
3. Update data into SQLite
4. Delete data from SQLite

Java files:-

DBHelper.java

package com.example.sqliteexample;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {

static String DATABASE_NAME="userdataaa";

public static final String TABLE_NAME="userrr";

public static final String KEY_FNAME="fname";

public static final String KEY_LNAME="lname";

    public static final String KEY_MNAME="mname";

public static final String KEY_ID="id";

public DbHelper(Context context) {

super(context, DATABASE_NAME, null, 1);

}

@Override

public void onCreate(SQLiteDatabase db) {

String CREATE_TABLE="CREATE TABLE "+TABLE_NAME+" ("+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_FNAME+" TEXT, "+KEY_LNAME+" TEXT, "+KEY_MNAME+" TEXT)";

db.execSQL(CREATE_TABLE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);

onCreate(db);

}

}

AddActivity.java

package com.example.sqliteexample;

import android.app.Activity;

import android.app.AlertDialog;

import android.content.ContentValues;

import android.content.DialogInterface;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

public class AddActivity extends Activity implements OnClickListener {

private Button btn_save;

private EditText edit_first,edit_last,edit_mobile;

private DbHelper mHelper;

private SQLiteDatabase dataBase;

private String id,fname,lname,mname;

private boolean isUpdate;

    @Override

    public void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.add_activity);

        

        btn_save=(Button)findViewById(R.id.save_btn);

        edit_first=(EditText)findViewById(R.id.frst_editTxt);

        edit_last=(EditText)findViewById(R.id.last_editTxt);

        edit_mobile=(EditText)findViewById(R.id.mob_editTxt);

         

       isUpdate=getIntent().getExtras().getBoolean("update");

        if(isUpdate)

        {

        id=getIntent().getExtras().getString("ID");

        fname=getIntent().getExtras().getString("Fname");

        lname=getIntent().getExtras().getString("Lname");

            mname=getIntent().getExtras().getString("Mname");

        edit_first.setText(fname);

        edit_last.setText(lname);

            edit_mobile.setText(mname);

       

        }

         

         btn_save.setOnClickListener(this);

         

         mHelper=new DbHelper(this);

        

    }

    // saveButton click event 

public void onClick(View v) {

fname=edit_first.getText().toString().trim();

lname=edit_last.getText().toString().trim();

        mname=edit_mobile.getText().toString().trim();

if(fname.length()>0 && lname.length()>0 && mname.length()>0)

{

saveData();

}

else

{

AlertDialog.Builder alertBuilder=new AlertDialog.Builder(AddActivity.this);

alertBuilder.setTitle("Invalid Data");

alertBuilder.setMessage("Please, Enter valid data");

alertBuilder.setPositiveButton("Ok", new DialogInterface.OnClickListener() {

public void onClick(DialogInterface dialog, int which) {

dialog.cancel();

}

});

alertBuilder.create().show();

}

}

/**

* save data into SQLite

*/

private void saveData(){

dataBase=mHelper.getWritableDatabase();

ContentValues values=new ContentValues();

values.put(DbHelper.KEY_ID,id);

values.put(DbHelper.KEY_FNAME,fname);

values.put(DbHelper.KEY_LNAME,lname );

        values.put(DbHelper.KEY_MNAME,mname);

System.out.println("");

if(isUpdate)

{    

//update database with new data 

dataBase.update(DbHelper.TABLE_NAME, values, DbHelper.KEY_ID+"="+id, null);

}

else

{

//insert data into database

dataBase.insert(DbHelper.TABLE_NAME, null, values);

}

//close database

dataBase.close();

finish();

}

}

DisplayActivity.java

package com.example.sqliteexample;

import java.util.ArrayList;

import android.app.Activity;

import android.app.AlertDialog;

import android.content.DialogInterface;

import android.content.Intent;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.AdapterView;

import android.widget.AdapterView.OnItemClickListener;

import android.widget.AdapterView.OnItemLongClickListener;

import android.widget.ListView;

import android.widget.Toast;

public class DisplayActivity extends Activity {

private DbHelper mHelper;

private SQLiteDatabase dataBase;

private ArrayList<String> userId = new ArrayList<String>();

private ArrayList<String> user_fName = new ArrayList<String>();

private ArrayList<String> user_lName = new ArrayList<String>();

    private ArrayList<String> user_mName = new ArrayList<String>();

private ListView userList;

private AlertDialog.Builder build;

@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.display_activity);

userList = (ListView) findViewById(R.id.List);

mHelper = new DbHelper(this);

//add new record

findViewById(R.id.btnAdd).setOnClickListener(new OnClickListener() {

public void onClick(View v) {

Intent i = new Intent(getApplicationContext(),

AddActivity.class);

i.putExtra("update", false);

startActivity(i);

}

});

//click to update data

userList.setOnItemClickListener(new OnItemClickListener() {

public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,

long arg3) {

Intent i = new Intent(getApplicationContext(),

AddActivity.class);

i.putExtra("Fname", user_fName.get(arg2));

i.putExtra("Lname", user_lName.get(arg2));

                i.putExtra("Mname", user_mName.get(arg2));

i.putExtra("ID", userId.get(arg2));

i.putExtra("update", true);

startActivity(i);

}

});

//long click to delete data

userList.setOnItemLongClickListener(new OnItemLongClickListener() {

public boolean onItemLongClick(AdapterView<?> arg0, View arg1,

final int arg2, long arg3) {

build = new AlertDialog.Builder(DisplayActivity.this);

build.setTitle("Delete " + user_fName.get(arg2) + " "

+ user_lName.get(arg2)+"" +user_mName.get(arg2));

build.setMessage("Do you want to delete ?");

build.setPositiveButton("Yes",

new DialogInterface.OnClickListener() {

public void onClick(DialogInterface dialog,

int which) {

Toast.makeText(

getApplicationContext(),

user_fName.get(arg2) + " "

+ user_lName.get(arg2)+"" +user_mName.get(arg2)

+ " is deleted.", Toast.LENGTH_SHORT).show();

dataBase.delete(

DbHelper.TABLE_NAME,

DbHelper.KEY_ID + "="

+ userId.get(arg2), null);

displayData();

dialog.cancel();

}

});

build.setNegativeButton("No",

new DialogInterface.OnClickListener() {

public void onClick(DialogInterface dialog,

int which) {

dialog.cancel();

}

});

AlertDialog alert = build.create();

alert.show();

return true;

}

});

}

@Override

protected void onResume() {

displayData();

super.onResume();

}

/**

* displays data from SQLite

*/

private void displayData() {

dataBase = mHelper.getWritableDatabase();

Cursor mCursor = dataBase.rawQuery("SELECT * FROM "

+ DbHelper.TABLE_NAME, null);

        userId.clear();

user_fName.clear();

user_lName.clear();

        user_mName.clear();

if (mCursor.moveToFirst()) {

do {

userId.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_ID)));

user_fName.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_FNAME)));

user_lName.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_LNAME)));

                user_mName.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_MNAME)));

} while (mCursor.moveToNext());

}

DisplayAdapter disadpt = new DisplayAdapter(DisplayActivity.this,userId, user_fName, user_lName,user_mName);

userList.setAdapter(disadpt);

mCursor.close();

}

}

DisplayAdapter.java

package com.example.sqliteexample;

import java.util.ArrayList;

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 DisplayAdapter extends BaseAdapter {

private Context mContext;

private ArrayList<String> id;

private ArrayList<String> firstName;

private ArrayList<String> lastName;

    private ArrayList<String> mobileName;

public DisplayAdapter(Context c, ArrayList<String> id,ArrayList<String> fname, ArrayList<String> lname,ArrayList<String>mname) {

this.mContext = c;

this.id = id;

this.firstName = fname;

this.lastName = lname;

        this.mobileName = mname;

}

public int getCount() {

// TODO Auto-generated method stub

return id.size();

}

public Object getItem(int position) {

// TODO Auto-generated method stub

return null;

}

public long getItemId(int position) {

// TODO Auto-generated method stub

return 0;

}

public View getView(int pos, View child, ViewGroup parent) {

Holder mHolder;

LayoutInflater layoutInflater;

if (child == null) {

layoutInflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);

child = layoutInflater.inflate(R.layout.listcell, null);

mHolder = new Holder();

mHolder.txt_id = (TextView) child.findViewById(R.id.txt_id);

mHolder.txt_fName = (TextView) child.findViewById(R.id.txt_fName);

mHolder.txt_lName = (TextView) child.findViewById(R.id.txt_lName);

            mHolder.txt_mobile = (TextView) child.findViewById(R.id.txt_mobile);

child.setTag(mHolder);

} else {

mHolder = (Holder) child.getTag();

}

mHolder.txt_id.setText(id.get(pos));

mHolder.txt_fName.setText(firstName.get(pos));

mHolder.txt_lName.setText(lastName.get(pos));

        mHolder.txt_mobile.setText(mobileName.get(pos));

return child;

}

public class Holder {

TextView txt_id;

TextView txt_fName;

TextView txt_lName;

        TextView txt_mobile;

}

}

XML Files:-

add_activity.xml

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

    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:background="#008b8b"

    android:orientation="vertical"

    android:padding="5dp" >

    <LinearLayout

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

        <TextView

            android:id="@+id/frst_txtV"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="First name"

            android:textColor="#fff" />

        <EditText

            android:id="@+id/frst_editTxt"

            android:layout_width="fill_parent"

            android:layout_height="wrap_content"

            android:layout_toRightOf="@+id/frst_txtV" />

    </LinearLayout>

    <LinearLayout

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

        <TextView

            android:id="@+id/lst_txtV"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_marginTop="10dp"

            android:text="Last name"

            android:textColor="#fff" />

        <EditText

            android:id="@+id/last_editTxt"

            android:layout_width="fill_parent"

            android:layout_height="wrap_content"

            android:layout_marginTop="10dp"

            android:layout_toRightOf="@+id/lst_txtV" />

    </LinearLayout>

    <LinearLayout

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

        <TextView

            android:id="@+id/mob_txtV"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_marginTop="10dp"

            android:text="Mobile No"

            android:textColor="#fff" />

        <EditText

            android:id="@+id/mob_editTxt"

            android:layout_width="fill_parent"

            android:layout_height="wrap_content"

            android:layout_marginTop="10dp"

            android:layout_toRightOf="@+id/lst_txtV" />

    </LinearLayout>

    <LinearLayout

        android:layout_width="match_parent"

        android:layout_height="70dp"

        android:layout_gravity="center"

        android:gravity="center" >

        <Button

            android:id="@+id/save_btn"

            android:layout_width="50dp"

            android:layout_height="30dp"

            android:background="@drawable/btnstroke"

            android:text="Save"

            android:textColor="#000" />

    </LinearLayout>

</LinearLayout>

display_activity.xml

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

    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:background="#008b8b"

    android:gravity="center_horizontal" >

    <Button

        android:id="@+id/btnAdd"

        android:layout_width="50dp"

        android:layout_height="30dp"

        android:layout_alignParentRight="true"

        android:layout_marginRight="10dp"

        android:layout_marginTop="10dp"

        android:background="@drawable/btnstroke"

        android:text="Add" />

    <View

        android:id="@+id/a"

        android:layout_width="match_parent"

        android:layout_height="2dp"

        android:layout_below="@+id/btnAdd"

        android:layout_marginTop="10dp"

        android:layout_marginLeft="5dp"

        android:layout_marginRight="5dp"

        android:background="#fff" />

    <ListView

        android:id="@+id/List"

        android:layout_width="match_parent"

        android:layout_height="match_parent"

        android:layout_marginLeft="5dp"

        android:layout_marginRight="5dp"

        android:layout_below="@+id/a"

        android:divider="#fff"

        android:dividerHeight="2dp" />

</RelativeLayout>

listcell.xml

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

    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:background="#6ab3a8"

    android:gravity="center_vertical"

    android:orientation="horizontal"

    android:padding="8dp" >

    <TextView

        android:id="@+id/txt_id"

        android:layout_width="0dp"

        android:layout_height="wrap_content"

        android:layout_weight="1"

        android:textColor="#fff" />

    <TextView

        android:id="@+id/txt_fName"

        android:layout_width="0dp"

        android:layout_height="wrap_content"

        android:layout_weight="2"

        android:textColor="#fff" />

    <TextView

        android:id="@+id/txt_lName"

        android:layout_width="0dp"

        android:layout_height="wrap_content"

        android:layout_weight="2"

        android:textColor="#fff" />

    <TextView

        android:id="@+id/txt_mobile"

        android:layout_width="0dp"

        android:layout_height="wrap_content"

        android:layout_weight="3"

        android:textColor="#fff" />

</LinearLayout>

Screen Shots:-

                                

Download SQLiteExample.Zip

Keep Updating Every Day...