Create an application to make Insert , update , Delete and retrieve operation on the database

File Name : E18Activity.java

package bsr.exa;
import java.util.Locale;
import android.app.Activity;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class E18Activity extends Activity {
/**
* @author Bipin S Rupadiya , www.gtu-android.blogspot.com
*
* 18) Create an application to make Insert , update , Delete and retrieve operation on the database.
*
*
* */
SQLiteDatabase db;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
createDB();
//do insert
Button btnInsert=(Button)findViewById(R.id.btnInsert );
btnInsert.setOnClickListener(new OnClickListener() {

public void onClick(View arg0) {

insert();
}
});
Button btnClear=(Button)findViewById(R.id.btnClear );
btnClear.setOnClickListener(new OnClickListener() {
public void onClick(View arg0) {
clear();
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
super.onCreateOptionsMenu(menu);
CreateMenu(menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item)
{
return MenuChoice(item);
}

private void CreateMenu(Menu menu)
{
MenuItem mnu1 = menu.add(0, 0, 0, "Insert");
{
mnu1.setAlphabeticShortcut('i');
mnu1.setIcon(android.R.drawable.ic_input_add);
}
MenuItem mnu2 = menu.add(0, 1, 1, "Search");
{
mnu2.setAlphabeticShortcut('s');
mnu2.setIcon(android.R.drawable.ic_search_category_default);

}
MenuItem mnu3 = menu.add(0, 2, 2, "Delete");
{
mnu3.setAlphabeticShortcut('d');
mnu3.setIcon(android.R.drawable.ic_delete);

}
MenuItem mnu4 = menu.add(0, 3, 3, "View");
{
mnu4.setAlphabeticShortcut('d');
mnu4.setIcon(android.R.drawable.ic_menu_info_details);
}
}
private boolean MenuChoice(MenuItem item)
{
Intent intent=new Intent();
switch (item.getItemId()) {
case 0:
insert();
return true;
case 1:
intent.setClass(E18Activity.this, Search.class);
startActivity(intent);
return true;
case 2:
intent.setClass(E18Activity.this, Search.class);
startActivity(intent);
return true;

case 3:
intent.setClass(E18Activity.this, ViewRecord.class);
startActivity(intent);
return true;

}
return false;
}
public void createDB()
{
db=openOrCreateDatabase("Student.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);
db.setLocale(Locale.getDefault());
db.setLockingEnabled(true);
db.setVersion(1);
String sql="create table if not exists Stud(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)";
db.execSQL(sql);
}
public void insert()
{
EditText txtName=(EditText)findViewById(R.id.txtName);
EditText txtAge=(EditText)findViewById(R.id.txtAge);
if(txtName.getText().toString().equals(""))
{
Toast.makeText(E18Activity.this, "Enter Name.", Toast.LENGTH_SHORT).show();
}
else if (txtAge.getText().toString().equals(""))
{
Toast.makeText(E18Activity.this, "Enter Age.", Toast.LENGTH_SHORT).show();
}
else
{

String sql="insert into Stud(name,age) values('"+ txtName.getText().toString() +"',"+txtAge.getText().toString()+")";
db.execSQL(sql);
clear();
Toast.makeText(E18Activity.this, "Record Successfully Inserted.", Toast.LENGTH_SHORT).show();
}
}
public void clear()
{
EditText txtName=(EditText)findViewById(R.id.txtName);
EditText txtAge=(EditText)findViewById(R.id.txtAge);
txtName.setText("");
txtAge.setText("");

txtName.clearFocus();
txtAge.clearFocus();
txtName.requestFocus();


}
@Override
public void onDestroy()
{
super.onDestroy();
db.close();
}
}

File Name : Search.java


package bsr.exa;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
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.Button;
import android.widget.EditText;
import android.widget.RelativeLayout;
import android.widget.Toast;
/**
* @author Bipin S Rupadiya , www.gtu-android.blogspot.com
*
* 18) Create an application to make Insert , update , Delete and retrieve operation on the database.
*
*
* */
public class Search extends Activity {
SQLiteDatabase db;
EditText txtSearch;
EditText txtName;
EditText txtAge;
Button btnEdit;
Button btnDelete;
RelativeLayout rlRecord;
RelativeLayout rlSearch;
String recID="0";
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);

setContentView(R.layout.search);

db=openOrCreateDatabase("Student.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);


txtName = (EditText)findViewById(R.id.txtName);
txtAge = (EditText)findViewById(R.id.txtAge);
txtSearch = (EditText)findViewById(R.id.txtSearch);
btnEdit=(Button)findViewById(R.id.btnEdit);
btnDelete=(Button)findViewById(R.id.btnDelete);

txtSearch.requestFocus();
txtName.setEnabled(false);
txtAge.setEnabled(false);
btnEdit.setEnabled(false);
btnDelete.setEnabled(false);

Button btnSearch=(Button)findViewById(R.id.btnSearch);
btnSearch.setOnClickListener(new OnClickListener() {
public void onClick(View arg0) {
if (txtSearch.getText().toString().equals(""))
{
Toast.makeText(Search.this, "Enter value.", Toast.LENGTH_SHORT).show();
}
else
{
searchRecord();
}
}
});
//---------------Edit/update---------------------------------
final Button btnEdit=(Button)findViewById(R.id.btnEdit);
btnEdit.setOnClickListener(new OnClickListener() {
public void onClick(View arg0) {

if (btnEdit.getText().toString().equals("Edit"))
{
btnEdit.setText("Update");
txtName.setEnabled(true);
txtAge.setEnabled(true);

txtName.requestFocus();

btnDelete.setEnabled(false);

}
else
{
txtName.setEnabled(false);
txtAge.setEnabled(false);

btnDelete.setEnabled(true);

btnEdit.setText("Edit");
String sql="update Stud set name='"+txtName.getText().toString()+"', age="+txtAge.getText().toString()+" where id="+recID;
db.execSQL(sql);
Toast.makeText(Search.this, " Record Updated Successfully" , Toast.LENGTH_LONG).show();
}
}
});
//------------------------Delete ---------------------------

btnDelete.setOnClickListener(new OnClickListener() {

public void onClick(View arg0)
{
// TODO Auto-generated method stub
AlertDialog.Builder alertbox = new AlertDialog.Builder(arg0.getContext());
alertbox.setIcon(android.R.drawable.ic_dialog_alert);
alertbox.setTitle("Confirm");
alertbox.setMessage("Are You Sure? You want to delete this record");
alertbox.setPositiveButton("Delete", new DialogInterface.OnClickListener()
{
public void onClick(DialogInterface arg0, int arg1)
{
// TODO Auto-generated method stub
String sql="Delete from Stud where id="+recID;
db.execSQL(sql);
Toast.makeText(getApplicationContext(), "Record Deleted", Toast.LENGTH_LONG).show();
//clear old search result
txtSearch.setText("");
txtName.setText("");
txtAge.setText("");
txtSearch.requestFocus();
}
});
alertbox.setNegativeButton(" Cancel ", new DialogInterface.OnClickListener()
{
public void onClick(DialogInterface arg0, int arg1)
{
// TODO Auto-generated method stub

}
});
alertbox.show();
}
});
}
public void searchRecord()
{
try
{
txtSearch = (EditText)findViewById(R.id.txtSearch);
txtName = (EditText)findViewById(R.id.txtName);
txtAge = (EditText)findViewById(R.id.txtAge);
//Cursor c=db.rawQuery("select id,name,age from Stud where id="+ txtSearch.getText().toString(), null);
Cursor c=db.rawQuery("select id,name,age from Stud where id=?", new String[]{txtSearch.getText().toString()});

if(c.getCount()>0)
{
c.moveToNext();
recID= c.getString(0);
txtName.setText( c.getString(1));
txtAge.setText(c.getString(2));
btnEdit.setEnabled(true);
btnDelete.setEnabled(true);
}
else
{
Toast.makeText(this, "No Record Found" , Toast.LENGTH_LONG).show();
}
}
catch(Exception e)
{
Toast.makeText(this, ""+e, Toast.LENGTH_LONG).show();
}
}
public void onDestroy()
{
super.onDestroy();
db.close();
}
}

File Name : ViewRecord.java

package bsr.exa;
/**
* @author Bipin S Rupadiya , www.gtu-android.blogspot.com
*
* 18) Create an application to make Insert , update , Delete and retrieve operation on the database.
*
*
* */
import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.Toast;
public class ViewRecord extends ListActivity {
SQLiteDatabase db;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
try
{
db=openOrCreateDatabase("Student.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);
Cursor c=db.rawQuery("select id,name,age from Stud", null);
ArrayList<String> list = new ArrayList<String>();

int count=c.getCount();

if(c.getCount()>0)
{
while(c.moveToNext())
{
list.add(c.getString(0)+" , "+c.getString(1)+" , "+c.getString(2));
}
c.close();
Toast.makeText(this,"Total Records: "+count, Toast.LENGTH_LONG).show();
ArrayAdapter<String> adapter=new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1, list);
getListView().setAdapter(adapter);
}
else
{
Toast.makeText(this, "No Record Found" , Toast.LENGTH_LONG).show();
}
}
catch(Exception e)
{
Toast.makeText(this, ""+e, Toast.LENGTH_LONG).show();
}
}
public void onDestroy()
{
super.onDestroy();
db.close();
}
}

Layout 1 : main.xml

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


<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello"
/>


<RelativeLayout android:layout_width="match_parent"android:id="@+id/relativeLayout1" android:layout_height="match_parent">


<TextView android:layout_width="wrap_content" android:id="@+id/textView1"android:textAppearance="?android:attr/textAppearanceLarge"android:layout_height="wrap_content" android:layout_alignParentTop="true"android:layout_alignParentLeft="true" android:text="Name:"></TextView>


<EditText android:inputType="textPersonName"android:layout_height="wrap_content" android:layout_below="@+id/textView1"android:layout_alignParentLeft="true" android:layout_width="match_parent"android:id="@+id/txtName">
<requestFocus></requestFocus>
</EditText>


<TextView android:layout_width="wrap_content" android:id="@+id/textView2"android:textAppearance="?android:attr/textAppearanceLarge"android:layout_height="wrap_content" android:layout_below="@+id/txtName"android:layout_alignParentLeft="true" android:layout_marginTop="24dp"android:text="Age:"></TextView>


<EditText android:inputType="phone" android:layout_height="wrap_content"android:layout_below="@+id/textView2" android:layout_alignParentLeft="true"android:layout_width="match_parent" android:id="@+id/txtAge"></EditText>


<Button android:layout_width="wrap_content"android:layout_height="wrap_content" android:layout_below="@+id/txtAge"android:layout_toRightOf="@+id/textView1" android:layout_marginTop="24dp"android:id="@+id/btnInsert" android:text="Insert"></Button>


<Button android:layout_width="wrap_content"android:layout_height="wrap_content"android:layout_alignBottom="@+id/btnInsert"android:layout_toRightOf="@+id/btnInsert" android:layout_marginLeft="26dp"android:id="@+id/btnClear" android:text="Clear"></Button>


</RelativeLayout>


</LinearLayout>

Layout 2 : search.xml

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


<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello"
/>


<RelativeLayout android:layout_width="match_parent" android:gravity="left"android:layout_height="wrap_content" android:id="@+id/rlSearch">


<TextView android:layout_width="wrap_content" android:id="@+id/textView1"android:textAppearance="?android:attr/textAppearanceLarge"android:layout_height="wrap_content" android:layout_alignParentTop="true"android:layout_alignParentLeft="true" android:text="Search by ID :"></TextView>


<EditText android:inputType="textPersonName"android:layout_height="wrap_content" android:layout_below="@+id/textView1"android:id="@+id/txtSearch" android:layout_width="250dp"></EditText>


<Button android:layout_width="wrap_content" android:text="Search"android:id="@+id/btnSearch" android:layout_height="wrap_content"android:layout_alignTop="@+id/txtSearch"android:layout_toRightOf="@+id/txtSearch"></Button>


</RelativeLayout>


<RelativeLayout android:layout_height="312dp"android:gravity="center_vertical" android:layout_width="match_parent"android:layout_gravity="center_vertical" android:id="@+id/rlRecord">


<TextView android:layout_width="wrap_content" android:text="Name:"android:id="@+id/textView1" android:textAppearance="?android:attr/textAppearanceLarge" android:layout_alignParentLeft="true"android:layout_alignParentTop="true" android:layout_height="wrap_content"></TextView>


<EditText android:layout_width="match_parent" android:id="@+id/txtName"android:inputType="textPersonName" android:layout_below="@+id/textView1"android:layout_alignParentLeft="true" android:layout_height="wrap_content">
<requestFocus></requestFocus>
</EditText>


<EditText android:layout_width="match_parent" android:id="@+id/txtAge"android:inputType="phone" android:layout_below="@+id/textView2"android:layout_alignParentLeft="true" android:layout_height="wrap_content"></EditText>


<Button android:text="Delete" android:id="@+id/btnDelete"android:layout_height="wrap_content" android:layout_alignTop="@+id/btnEdit"android:layout_toRightOf="@+id/btnEdit" android:layout_width="160dp"></Button>


<Button android:layout_width="160dp" android:text="Edit"android:id="@+id/btnEdit" android:layout_height="wrap_content"android:layout_below="@+id/txtAge" android:layout_alignParentLeft="true"android:layout_marginTop="30dp"></Button>


<TextView android:layout_width="wrap_content" android:text="Age:"android:id="@+id/textView2" android:textAppearance="?android:attr/textAppearanceLarge" android:layout_height="wrap_content"android:layout_below="@+id/txtName" android:layout_alignParentLeft="true"android:layout_marginTop="14dp"></TextView>


</RelativeLayout>


</LinearLayout>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="bsr.exa"
android:versionCode="1"
android:versionName="1.0">
<uses-sdk android:minSdkVersion="8" />


<application android:icon="@drawable/icon"android:label="@string/app_name">
<activity android:name=".E18Activity"
android:label="@string/app_name">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<activity android:name="ViewRecord"></activity>
<activity android:name="Search"></activity>


</application>
</manifest>

Download This Example 

Read more »