In this program I try to demonstrate the use of sqlite in android. In this demo I took name, phoneno and address from user and store it in database. I have performed Insert, Update and Delete operation through this demo.
1.EditText
Edit text is used for inserting data from user. User can edit this data before inserting for further uses.
2.TextView:
TextView is used for displaying labels on GUI. It helps user to understand about data that he need to insert.
3.ListView:
ListView is used for displaying list of name, phone and address inserted by the user. Which are stored in database.
4.Alert Dialog:
Alert Dialog is used to alert user about their action and ask user to confirm their action, if they need to execute a particular action.
5.SQLite Database:
<SQLite is a lite version of database used in android. It is used for inserting, updating , deleting and retrieval of data from database.
6.OnItemLongClickListener:
This listener is used for activating long touch properties of any item of list. We assign work which need to be done on long touch of an item.
1.AddStudent.java
package com.example.sisoftdatabasedemo;
import android.os.Bundle;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.graphics.Typeface;
import android.view.Menu;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class AddStudent extends Activity {
EditText
name,phone,address;
Button
submit,back;
DatabaseUses
dbu;
TextView
title;
@Override
protected
void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main_page);
name
= (EditText)findViewById(R.id.editName);
phone
= (EditText)findViewById(R.id.editPhone);
address
= (EditText)findViewById(R.id.editAddress);
title=(TextView)findViewById(R.id.titlehead);
Typeface
tf=Typeface.createFromAsset(getAssets(),"algi.ttf");
title.setTypeface(tf);
title.setText("ADD
DATA");
back
=(Button)findViewById(R.id.leftbtn);
back.setOnClickListener(new
View.OnClickListener() {
@Override
public
void onClick(View v) {
//
TODO Auto-generated method stub
Intent
i = new Intent();
setResult(RESULT_OK,
i);
finish();
}
});
submit
= (Button)findViewById(R.id.rightbtn);
submit.setOnClickListener(new
View.OnClickListener() {
@Override
public
void onClick(View v) {
//
TODO Auto-generated method stub
addStudent();
}
});
}
@Override
protected
void onStart() {
//
TODO Auto-generated method stub
super.onStart();
dbu= new DatabaseUses(this);
}
public void
addStudent()
{
AlertDialog
al = new AlertDialog.Builder(this).create();
al.setIcon(R.drawable.add);
al.setTitle("Insert");
al.setMessage("Do
You Want To Continue ?");
al.setButton(AlertDialog.BUTTON_POSITIVE,
"Yes", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
getStudentDetail();
Intent
i = new Intent();
setResult(RESULT_OK,
i);
finish();
}
});
al.setButton(AlertDialog.BUTTON_NEGATIVE, "No", new
DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
}
});
al.show();
}
@Override
public
boolean onCreateOptionsMenu(Menu menu) {
//
Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main_page,
menu);
return
true;
}
public void
getStudentDetail()
{
try
{
String
n=name.getText().toString();
String
ph = phone.getText().toString();
String
add = address.getText().toString();
Student
student = new Student(n,ph,add);
dbu.insertStudentDetail(student);
}
catch(Exception
e)
{
AlertDialog
al=new AlertDialog.Builder(this).create();
al.setMessage(e.toString());
al.show();
//Toast.makeText(getApplication(),
e.toString(), Toast.LENGTH_LONG).show();
}
}
}
2.DatabaseUser.java
package
com.example.sisoftdatabasedemo;
import
android.app.AlertDialog;
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.widget.Toast;
public class DatabaseUses
extends SQLiteOpenHelper {
static final String dbname = "demoDB";
static final String studentTable = "student";
static final String columnId = "StudentId";
static final String columnName = "Name";
static final String columnPhone = "Phone";
static final String columnAddress = "Address";
static final String myView ="stdview";
public DatabaseUses(Context context) {
super(context, dbname, null, 22);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE
"+studentTable+" ("+columnId+" INTEGER PRIMARY KEY
AUTOINCREMENT, "+
columnName+"
TEXT, "+columnPhone+" TEXT, "+columnAddress+" TEXT)");
db.execSQL("create view "
+myView+" as select "+studentTable+"."+columnId+" as
_id,"+studentTable+"."+columnName+","+studentTable+"."+columnPhone+","+studentTable+"."+columnAddress+"
"+" FROM "+studentTable);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion,
int newVersion) {
// TODO Auto-generated method stub
onCreate(db);
}
public void insertStudentDetail(Student st)
{
SQLiteDatabase db =
this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(columnName, st.getName());
cv.put(columnPhone, st.getPhone());
cv.put(columnAddress, st.getAddress());
db.insert(studentTable, columnName, cv);
}
public Cursor getData()
{
SQLiteDatabase db =
this.getWritableDatabase();
Cursor
cur= db.rawQuery("SELECT * FROM "+myView,null);
return
cur;
}
public Cursor getDataById(int id)
{
SQLiteDatabase db =
this.getWritableDatabase();
Cursor
cur= db.rawQuery("SELECT * FROM "+studentTable+" where
"+columnId+"=?",new String []{String.valueOf(id)});
/*String[] col=new
String[]{"_id",columnName,columnPhone,columnAddress};
Cursor
cur= db.query(myView,col,columnId+"=?",new String
[]{String.valueOf(id)},null,null,null);*/
return
cur;
}
public void updateData(Student st,int id)
{
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(columnName,st.getName());
cv.put(columnPhone, st.getPhone());
cv.put(columnAddress, st.getAddress());
db.update(studentTable, cv,
columnId+"=?", new String []{String.valueOf(id)});
}
public
void DeleteEmp(int id)
{
SQLiteDatabase db=this.getWritableDatabase();
db.delete(studentTable,columnId+"=?",
new String [] {String.valueOf(id)});
db.close();
}
}
3.EditDataPage.java
package
com.example.sisoftdatabasedemo;
import
android.app.Activity;
import
android.app.AlertDialog;
import
android.content.DialogInterface;
import
android.content.Intent;
import
android.database.Cursor;
import
android.graphics.Typeface;
import android.os.Bundle;
import
android.support.v4.widget.SimpleCursorAdapter;
import android.view.View;
import android.widget.Button;
import
android.widget.EditText;
import
android.widget.TextView;
import
android.widget.Toast;
public class EditDataPage
extends Activity{
DatabaseUses datauser;
EditText txt_name,txt_phone,txt_add;
int colId;
Button updatebtn,back;
TextView title;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.editdata_page);
Intent indata =this.getIntent();
colId=indata.getExtras().getInt("ID");
title=(TextView)findViewById(R.id.titlehead);
Typeface
tf=Typeface.createFromAsset(getAssets(),"algi.ttf");
title.setTypeface(tf);
title.setText("Edit Data");
txt_name=(EditText)findViewById(R.id.edit_name);
txt_add=(EditText)findViewById(R.id.edit_add);
txt_phone=(EditText)findViewById(R.id.edit_phone);
updatebtn=(Button)findViewById(R.id.rightbtn);
back =(Button)findViewById(R.id.leftbtn);
back.setOnClickListener(new
View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO
Auto-generated method stub
Intent i = new
Intent();
setResult(RESULT_OK,
i);
finish();
}
});
updatebtn.setBackgroundResource(R.drawable.ok);
updatebtn.setOnClickListener(new
View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO
Auto-generated method stub
AlertDialog al = new
AlertDialog.Builder(EditDataPage.this).create();
al.setIcon(R.drawable.add);
al.setTitle("Update");
al.setMessage("Do
You Want To Update ?");
al.setButton(AlertDialog.BUTTON_POSITIVE,
"Yes", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
updateColumn();
Intent
i = new Intent();
setResult(RESULT_OK,
i);
finish();
}
});
al.setButton(AlertDialog.BUTTON_NEGATIVE,
"No", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
}
});
al.show();
}
});
}
@Override
protected void onStart() {
// TODO Auto-generated method stub
super.onStart();
datauser = new DatabaseUses(this);
loadData(colId);
}
public void updateColumn()
{
try
{
String name = txt_name.getText().toString();
String phone =
txt_phone.getText().toString();
String add = txt_add.getText().toString();
Student std = new Student(name,add,phone);
datauser.updateData(std, colId);
}catch(Exception e)
{
AlertDialog alt = new
AlertDialog.Builder(this).create();
alt.setMessage(e.toString());
alt.show();
}
}
public void loadData(int id1)
{
try
{
Cursor c=datauser.getDataById(id1);
startManagingCursor(c);
if(c.moveToFirst())
{
do
{
String
name=c.getString(1).toString();
String
phone=c.getString(2).toString();
String add=c.getString(3).toString();
txt_name.setText(name);
txt_add.setText(add);
txt_phone.setText(phone);
}while (c.moveToNext()) ;
}
}
catch(Exception e)
{
AlertDialog alt = new
AlertDialog.Builder(this).create();
alt.setMessage(e.toString());
alt.show();
}
}
}
4.Student.java
package
com.example.sisoftdatabasedemo;
public class
Student {
String name,address,phone;
public
Student()
{}
public
Student(String name, String address, String phone) {
super();
this.name =
name;
this.address =
address;
this.phone =
phone;
}
public String
getName() {
return name;
}
public void
setName(String name) {
this.name =
name;
}
public String
getAddress() {
return address;
}
public void
setAddress(String address) {
this.address =
address;
}
public String
getPhone() {
return phone;
}
public void
setPhone(String phone) {
this.phone =
phone;
}
}
5.ViewStudentMainPage.java
package
com.example.sisoftdatabasedemo;
import android.app.Activity;
import android.app.AlertDialog;
import
android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.graphics.Typeface;
import android.os.Bundle;
import
android.support.v4.widget.SimpleCursorAdapter;
import
android.text.AlteredCharSequence;
import android.view.View;
import android.widget.AdapterView;
import
android.widget.AdapterView.OnItemLongClickListener;
import android.widget.Button;
import android.widget.GridLayout;
import android.widget.GridView;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
public class ViewStudentMainPage
extends Activity implements OnItemLongClickListener{
static
public ListView list;
DatabaseUses
datauser;
Button
addbtn,back;
TextView
title;
//int
colId;
@Override
protected void onCreate(Bundle
savedInstanceState) {
//
TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.database_main);
title=(TextView)findViewById(R.id.titlehead);
Typeface
tf=Typeface.createFromAsset(getAssets(),"algi.ttf");
title.setTypeface(tf);
title.setText("My
Table ");
list
= (ListView)findViewById(R.id.listView1);
list.setOnItemLongClickListener(this);
back
=(Button)findViewById(R.id.leftbtn);
back.setVisibility(View.GONE);
addbtn=(Button)findViewById(R.id.rightbtn);
addbtn.setOnClickListener(new
View.OnClickListener() {
@Override
public
void onClick(View v) {
//
TODO Auto-generated method stub
Intent
intent = new Intent(ViewStudentMainPage.this,AddStudent.class);
startActivity(intent);
}
});
}
@Override
protected void onStart() {
//
TODO Auto-generated method stub
super.onStart();
datauser=new
DatabaseUses(this);
loadData();
}
public void loadData()
{
try
{
Cursor
c=datauser.getData();
startManagingCursor(c);
String[]
from =
{DatabaseUses.columnName,DatabaseUses.columnPhone,DatabaseUses.columnAddress};
int[]
to =new int[] {R.id.callname,R.id.callphone,R.id.calladdress};
SimpleCursorAdapter
sca=new SimpleCursorAdapter(this, R.layout.listrow, c, from, to);
list.setAdapter(sca);
}
catch(Exception
e)
{
AlertDialog
alt = new AlertDialog.Builder(this).create();
alt.setMessage(e.toString());
alt.show();
}
}
@Override
public boolean onItemLongClick(AdapterView<?>
arg0, View arg1, int arg2,
long
arg3)
{
//
TODO Auto-generated method stub
int
id=(int)arg3;
showEditView(id);
return
true;
}
public void showEditView(final int
id)
{
AlertDialog
al = new AlertDialog.Builder(this).create();
al.setIcon(R.drawable.add);
al.setTitle("Insert");
al.setMessage("Do
You Want To Continue ?");
al.setButton(AlertDialog.BUTTON_POSITIVE,
"Delete", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
delete(id);
}
});
al.setButton(AlertDialog.BUTTON_NEUTRAL,
"Edit", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
Intent
iedit=new Intent(ViewStudentMainPage.this,EditDataPage.class);
iedit.putExtra("ID",
id);
startActivityForResult(iedit,
302);
}
});
al.setButton(AlertDialog.BUTTON_NEGATIVE,
"Cancel", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
}
});
al.show();
}
public void delete(final int id)
{
AlertDialog
al = new AlertDialog.Builder(ViewStudentMainPage.this).create();
al.setIcon(R.drawable.add);
al.setTitle("Delete");
al.setMessage("Do
You Want To Delete ?");
al.setButton(AlertDialog.BUTTON_POSITIVE,
"Yes", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
datauser.DeleteEmp(id);
loadData();
}
});
al.setButton(AlertDialog.BUTTON_NEGATIVE,
"No", new DialogInterface.OnClickListener() {
@Override
public
void onClick(DialogInterface dialog, int which) {
//
TODO Auto-generated method stub
}
});
al.show();
}
}
1.activity_main_page.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"
tools:context=".AddStudent" >
<include android:id="@+id/top"
layout="@layout/heading"
android:layout_alignParentTop="true"></include>
<TextView
android:id="@+id/textName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="23dp"
android:layout_below="@+id/top"
android:layout_marginTop="28dp"
android:text="Name :" />
<EditText
android:id="@+id/editName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textName"
android:layout_alignBottom="@+id/textName"
android:layout_marginLeft="58dp"
android:layout_toRightOf="@+id/textName"
android:ems="10" >
<requestFocus />
</EditText>
<EditText
android:id="@+id/editPhone"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/editAddress"
android:layout_below="@+id/editAddress"
android:layout_marginTop="27dp"
android:ems="10" />
<EditText
android:id="@+id/editAddress"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/editName"
android:layout_below="@+id/editName"
android:layout_marginTop="27dp"
android:ems="10" />
<TextView
android:id="@+id/textPhone"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/editAddress"
android:layout_alignBottom="@+id/editAddress"
android:layout_alignRight="@+id/textName"
android:text="Phone :" />
<TextView
android:id="@+id/textAddress"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/editPhone"
android:layout_alignBottom="@+id/editPhone"
android:layout_alignLeft="@+id/textPhone"
android:text="Address :" />
</RelativeLayout>
2.database_main.xml
<?xml version="1.0"
encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<LinearLayout
android:id="@+id/linearLayout1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="50dp"
android:orientation="horizontal"
>
<TextView
android:id="@+id/name"
android:layout_width="100dp"
android:layout_height="wrap_content"
android:gravity="left"
android:text="NAME"
android:textAppearance="?android:attr/textAppearanceMedium"
/>
<TextView
android:id="@+id/address"
android:layout_width="100dp"
android:layout_height="match_parent"
android:gravity="center"
android:text="PHONE"
android:textAppearance="?android:attr/textAppearanceMedium"
/>
<TextView
android:id="@+id/date"
android:layout_width="100dp"
android:layout_height="wrap_content"
android:gravity="right"
android:text="ADDRESS"
android:textAppearance="?android:attr/textAppearanceMedium"
/>
</LinearLayout>
<include
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
layout="@layout/heading" />
<ListView
android:id="@+id/listView1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/linearLayout1"
>
</ListView>
</RelativeLayout>
3.editdata_page.xml
<?xml version="1.0"
encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent" >
<include
android:id="@+id/include1"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
layout="@layout/heading" />
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/include1"
android:layout_marginLeft="25dp"
android:layout_marginTop="66dp"
android:text="NAME"
android:textAppearance="?android:attr/textAppearanceMedium"
/>
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignRight="@+id/textView1"
android:layout_below="@+id/textView1"
android:layout_marginTop="46dp"
android:text="PHONE"
android:textAppearance="?android:attr/textAppearanceMedium"
/>
<TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/textView2"
android:layout_below="@+id/textView2"
android:layout_marginTop="50dp"
android:text="ADDRESS"
android:textAppearance="?android:attr/textAppearanceMedium"
/>
<EditText
android:id="@+id/edit_name"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textView1"
android:layout_alignBottom="@+id/textView1"
android:layout_marginLeft="34dp"
android:layout_toRightOf="@+id/textView1"
android:ems="10" >
<requestFocus />
</EditText>
<EditText
android:id="@+id/edit_phone"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textView2"
android:layout_alignBottom="@+id/textView2"
android:layout_alignLeft="@+id/edit_name"
android:ems="10"
android:inputType="number" />
<EditText
android:id="@+id/edit_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBottom="@+id/textView3"
android:layout_alignLeft="@+id/edit_phone"
android:ems="10" />
>
</RelativeLayout>
4.heading.xml
<?xml version="1.0"
encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="#ff0000"
>
<Button
android:id="@+id/leftbtn"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:background="@drawable/back"
android:layout_alignParentLeft="true"/>
<TextView
android:id="@+id/titlehead"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBottom="@+id/leftbtn"
android:layout_alignParentTop="true"
android:layout_toLeftOf="@+id/rightbtn"
android:layout_toRightOf="@+id/leftbtn"
android:text="My TABLE"
android:textAppearance="?android:attr/textAppearanceLarge"
android:gravity="center"/>
<Button
android:id="@+id/rightbtn"
android:layout_width="wrap_content"
android:layout_height=quot;wrap_content"
android:background="@drawable/add"
android:layout_alignParentRight="true"/>
</RelativeLayout>
5.listrow.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" >
<TableLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
>
<TableRow
android:layout_height="wrap_content"
android:layout_width="match_parent">
<TextView
android:id="@+id/callname"
android:layout_height="wrap_content"
android:layout_width="100dp"
android:layout_gravity="left"
android:gravity="center">
</TextView>
<TextView
android:id="@+id/callphone"
android:layout_height="wrap_content"
android:layout_width="100dp"
android:layout_gravity="center"
android:gravity="center">
</TextView>
<TextView
android:id="@+id/calladdress"
android:layout_height="wrap_content"
android:layout_width="100dp"
android:layout_gravity="right"
android:gravity="center">
</TextView>
</TableRow>
</TableLayout>
</LinearLayout>
Screen Shots:
After long press on list item
After selecting Delete option
About the Contributor...
Manoj Mimanshak
Trainee at Sisoft Technologies Indirapuram Ghaziabad