Simple Database Operation Example In Android

QUESTION
 Develop an android application for student marks sheet
   a. create student database on SDcard
   b. create table
    i.  student { sno, name, dob }
    ii. marks { sno, s1, s2, s3 }
   c. action to be performed
    i.   Add record
    check for duplication
    ii.  View record
    sno, name, total, per, grade
    iii. Search record
    search by no and name
    iv.  Delete record
 
   With Proper Validation

DatabaseActivity.java

package ps.database;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;

public class DatabaseActivity extends Activity implements OnItemClickListener, TextWatcher {
    /**
     *  www.master-gtu.blogspot.com
     *  pankaj sharma(8460479175),
     *  chavda vijay(8460420769) 
     */
 ListView lv;
 SQLiteDatabase db;
 TextView edsearchbyno;
 
  String names[];
 TextView edsearchbyname;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        db=openOrCreateDatabase("/sdcard/database", SQLiteDatabase.CREATE_IF_NECESSARY, null);
        db.execSQL("create table if not exists student(sno text, name text, dob text)");
        db.execSQL("create table if not exists marks(sno text, s1 text, s2 text, s3 text)");
        lv=(ListView) findViewById(R.id.listView1);
        
        Cursor cur=db.rawQuery("select * from student", null);
names=new String[cur.getCount()];
for(int i=0;i<cur.getCount();i++)
{
 cur.moveToNext();
 names[i]=cur.getstring(0)+". "+cur.getString(1);
}
ArrayAdapter<"String">arr=new ArrayAdapter<string>(this, android.R.layout.simple_list_item_1,names);
lv.setAdapter(arr);
lv.setOnItemClickListener(this);
        
        //===============================
        
        edsearchbyname=(TextView) findViewById(R.id.editTextsearchname);
        
        edsearchbyname.addTextChangedListener(this);
        
        //===============================
    }
 @Override
 public void onItemClick(AdapterView arg0, View arg1, int pos, long arg3) {
  // TODO Auto-generated method stub
  Intent myintent =new Intent(this,view.class);
  myintent.putExtra("pos", names[pos].substring(0, names[pos].indexOf('.'))+"");
  Toast.makeText(this,names[pos].substring(0, names[pos].indexOf('.'))+" ",1).show();
  startActivity(myintent);
 }
 @Override
 public boolean onCreateOptionsMenu(Menu menu) {
  // TODO Auto-generated method stub
  super.onCreateOptionsMenu(menu);
  menu.add(0,0,0,"INSERT");
  menu.add(0,1,1,"REFERESH");
  return true;
 }
 @Override
 public boolean onOptionsItemSelected(MenuItem item) {
  // TODO Auto-generated method stub
  super.onOptionsItemSelected(item);
  if(item.getItemId()==0)
  {
   
   startActivity(new Intent(this,insert.class));
  }
  else if(item.getItemId()==1)
  {
   
   startActivity(new Intent(this,DatabaseActivity.class));
  }
  return true;
 }
 //======================searching===========================
 @Override
 public void afterTextChanged(Editable arg0) {
  // TODO Auto-generated method stub
  Cursor cur=db.rawQuery("select * from student where name like '%"+edsearchbyname.getText().toString()+"%'", null);
  if(cur.getCount()>0)
  {
   names=new String[cur.getCount()];
names=new String[cur.getCount()];
for(int i=0;i<cur.getCount();i++)
{
 cur.moveToNext();
 names[i]=cur.getstring(0)+". "+cur.getString(1);
}
ArrayAdapter<"String">arr=new ArrayAdapter<string>(this, android.R.layout.simple_list_item_1,names);
lv.setAdapter(arr);
lv.setOnItemClickListener(this);
   lv.setAdapter(arr);
  }
 }
 @Override
 public void beforeTextChanged(CharSequence arg0, int arg1, int arg2,
   int arg3) {
  // TODO Auto-generated method stub
  
 }
 @Override
 public void onTextChanged(CharSequence arg0, int arg1, int arg2, int arg3) {
  // TODO Auto-generated method stub
  
 }
}

view.java

package ps.database;

import android.app.Activity;
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.Toast;

public class view extends Activity implements OnClickListener
{
 EditText edno,edname,edtotal,edper,edgrade;
 Button btdelete;
 SQLiteDatabase db;
 @Override
 protected void onCreate(Bundle savedInstanceState) {
  // TODO Auto-generated method stub
  super.onCreate(savedInstanceState);
  setContentView(R.layout.view);
  
  db=openOrCreateDatabase("/sdcard/database", SQLiteDatabase.CREATE_IF_NECESSARY, null);
  edno=(EditText) findViewById(R.id.editTextviewnumber);
  edname=(EditText) findViewById(R.id.editTextviewname);
  edtotal=(EditText) findViewById(R.id.editTexttotal);
  edper=(EditText) findViewById(R.id.editTextper);
  edgrade=(EditText) findViewById(R.id.editTextgrade);
  
  btdelete=(Button) findViewById(R.id.buttondelete);
 
  String pos=getIntent().getStringExtra("pos");
  
  Cursor cur=db.rawQuery("select * from student where sno='"+pos+"'", null);
  
  cur.moveToNext();
  
  edno.setText(cur.getString(0));
  edname.setText(cur.getString(1));
  cur.close();
  
  cur=db.rawQuery("select * from marks where sno='"+edno.getText().toString()+"'", null);
  cur.moveToNext();
  int total=Integer.parseInt(cur.getString(1)+"") +Integer.parseInt(cur.getString(2)+"") +Integer.parseInt(cur.getString(3)+"");
  edtotal.setText(total+"");
  int per=total/3;
  edper.setText(total/3+"");
  if(per>65)
   edgrade.setText("A");
  else if(per<=65)
   edgrade.setText("B");

  edno.setEnabled(false);
  edname.setEnabled(false);
  edtotal.setEnabled(false);
  edper.setEnabled(false);
  edgrade.setEnabled(false);
  
  
  btdelete.setOnClickListener(this);
 }
 @Override
 public void onClick(View v) {
  // TODO Auto-generated method stub
  if(btdelete.getId()==v.getId())
  {
   try
   {
   db.execSQL("delete from student where sno='"+edno.getText().toString()+"'");
   db.execSQL("delete from marks where sno='"+edno.getText().toString()+"'");
   Toast.makeText(this, "Delete Success",1).show();
   }
   catch(Exception e)
   {
    Toast.makeText(this, e.toString(), 1).show();
   }
  }
 }
}

insert.java

package ps.database;

import android.app.Activity;
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.Toast;

public class insert extends Activity implements OnClickListener 
{
 EditText edsno,edname,eddob,eds1,eds2,eds3;
 Button btsave,btclear;
 SQLiteDatabase db;
 
 @Override
 protected void onCreate(Bundle savedInstanceState) {
  // TODO Auto-generated method stub
  super.onCreate(savedInstanceState);
  setContentView(R.layout.insert);
  
  db=openOrCreateDatabase("/sdcard/database", SQLiteDatabase.CREATE_IF_NECESSARY, null);
  edsno=(EditText) findViewById(R.id.editTextinsertnumber);
  edname=(EditText) findViewById(R.id.editTextinsertname);
  eddob=(EditText) findViewById(R.id.editTextinsertdob);
  eds1=(EditText) findViewById(R.id.editTextinserts1);
  eds2=(EditText) findViewById(R.id.editTextinserts2);
  eds3=(EditText) findViewById(R.id.editTextinserts3);
  btsave=(Button) findViewById(R.id.buttoninsertsave);
  btclear=(Button) findViewById(R.id.buttoninsertclear);
  
  btsave.setOnClickListener(this);
  btclear.setOnClickListener(this);

 }

 @Override
 public void onClick(View v) {
  // TODO Auto-generated method stub
  if(btsave.getId()==v.getId())
  {
   Cursor cur=db.rawQuery("select * from student where sno='"+edsno.getText().toString()+"'", null);
   
   if(cur.getCount()>0)
   {
    Toast.makeText(this, "aleready exists..",1).show(); 
   }
   else 
   {
    if(edsno.getText().toString().equals("") || edname.getText().toString().equals("") || eddob.getText().toString().equals("") ||eds1.getText().toString().equals("")  || eds2.getText().toString().equals("") ||eds3.getText().toString().equals("") )
    {
     Toast.makeText(this, "Pls Enter All the fields", 1).show(); 
    }
    else 
    {
     db.execSQL("insert into student values('"+edsno.getText().toString()+"','"+edname.getText().toString()+"','"+eddob.getText().toString()+"')");
     db.execSQL("insert into marks values('"+edsno.getText().toString()+"','"+eds1.getText().toString()+"','"+eds2.getText().toString()+"','"+eds3.getText().toString()+"')");
    }

    Toast.makeText(this,"Insert success...", 1).show();
   }
  }
  else if(btclear.getId()==v.getId())
  {
   edsno.setText("");
   edname.setText("");
   eddob.setText("");
   eds1.setText("");
   eds2.setText("");
   eds3.setText("");
  }
 }
}

Create an application that will create database with table of User credential.

PRO15Activity.java

package vc.PRO15;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class PRO15Activity extends Activity implements OnItemClickListener {
    /**
     *  www.master-gtu.blogspot.com
     *  pankaj sharma(8460479175),
     *  chavda vijay(8460420769) 
     */
 SQLiteDatabase db;
 ListView lv;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        db=openOrCreateDatabase("database1",SQLiteDatabase.CREATE_IF_NECESSARY, null);
  db.execSQL("create table if not exists stud(id integer primary key AUTOINCREMENT,name text,age integer)");
  lv =(ListView) findViewById(R.id.list);
  lv.setOnItemClickListener(this);
  Cursor cur=db.query("stud",null, null, null, null, null, null);
  
  if(cur.getCount()>0)
  {
   String name[]=new String[cur.getCount()];
   int i=0;
   while(cur.moveToNext())
   {
    name[i++]=cur.getString(1);
   }
   ArrayAdapter aa=new ArrayAdapter(this,android.R.layout.simple_list_item_1,name);
   lv.setAdapter(aa);
  }
  else
  {
         Toast.makeText(this,"DataBase is Empty...", 1000).show();
  }
    }
 @Override
 public void onItemClick(AdapterView p, View v, int pos, long c) {
  // TODO Auto-generated method stub
  Intent myintent=new Intent(this,update.class);
  TextView iname=(TextView) v;
  myintent.putExtra("label",iname.getText().toString() );
  startActivity(myintent);
 }
 @Override
 public boolean onCreateOptionsMenu(Menu menu) {
  // TODO Auto-generated method stub
  super.onCreateOptionsMenu(menu);
  menu.add(0,5,0,"Insert");
  menu.add(0,1,0,"Update");
  menu.add(0,2,0,"Delete");
  return true;
 }
 @Override
 public boolean onOptionsItemSelected(MenuItem item) {
  // TODO Auto-generated method stub
   super.onOptionsItemSelected(item);
   if(item.getItemId()==5)
   {
    Intent myintent=new Intent(this,update.class);
    myintent.putExtra("flag",item.getItemId());
    startActivity(myintent);
   } 
   else if(item.getItemId()==1)
   {
    Intent myintent=new Intent(this,update.class);
    myintent.putExtra("flag",item.getItemId());
    startActivity(myintent);
   }
   else  if(item.getItemId()==2)
   {
    Intent myintent=new Intent(this,update.class);
    myintent.putExtra("flag",item.getItemId());
    startActivity(myintent);
   }
   return true;
 }
}

insert.java

package vc.PRO15;

import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;

public class insert extends Activity  {
    /**
     *  www.master-gtu.blogspot.com
     *  pankaj sharma(8460479175),
     *  chavda vijay(8460420769) 
     */
 SQLiteDatabase db;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        db=openOrCreateDatabase("database1",SQLiteDatabase.CREATE_IF_NECESSARY, null);
  db.execSQL("create table if not exists stud(id integer primary key,name text,age integer)");
    }
}

update.java

package vc.PRO15;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class update extends Activity implements OnClickListener, TextWatcher, OnItemClickListener  {
    /**
     *  www.master-gtu.blogspot.com
     *  pankaj sharma(8460479175),
     *  chavda vijay(8460420769) 
     */
 SQLiteDatabase db;
 EditText txtname,txtage,txtid;
 Button btnedit,btncancel;
 TextView lblage;
 ListView lv;
 String name[];
 int flag=-1;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.update);
        db=openOrCreateDatabase("database1",SQLiteDatabase.CREATE_IF_NECESSARY, null);
  db.execSQL("create table if not exists stud(id integer primary key AUTOINCREMENT,name text,age integer)");
  
  lblage=(TextView) findViewById(R.id.textView2);
  txtname=(EditText) findViewById(R.id.txtname);
  txtage=(EditText) findViewById(R.id.txtage);
  txtid=(EditText) findViewById(R.id.txtid);
  btnedit=(Button) findViewById(R.id.btnedit);
  btncancel=(Button) findViewById(R.id.btncancel);
  
  lv=(ListView) findViewById(R.id.list);
  lv.setVisibility(-1);
  txtid.setVisibility(-1);
  Intent myintent=getIntent();
  flag=myintent.getIntExtra("flag", 0);
  if(flag==0)
  {
   try
   {
    String label=myintent.getStringExtra("label");
    Cursor cur=db.query("stud",null,"name=?",new String []{label},null, null,null);
    cur.moveToFirst();
    Toast.makeText(this,cur.getString(0)+"", 1).show();
    txtid.setText(cur.getString(0)+"");
    txtname.setText(cur.getString(1));
    txtage.setText(cur.getString(2));
    btnedit.setText("Update");
    txtage.setEnabled(true);
   }
   catch(Exception e)
   {
    Toast.makeText(this,e.toString(), 1).show();
   }
  }
  else if(flag==1)
  {
   txtname.addTextChangedListener(this);
   lv.setVisibility(0);
   invisible();
   txtage.setEnabled(false);
  }
  else if(flag==2)
  {
   txtname.addTextChangedListener(this);
   lv.setVisibility(0);
   invisible();
   btnedit.setText("Delete");
  }
  else if(flag==5)
  {
   btnedit.setText("Save");
  }
  btnedit.setOnClickListener(this);
  btncancel.setOnClickListener(this);
  lv.setOnItemClickListener(this);
    }
    public void invisible()
    {
     lblage.setVisibility(-1);
     txtage.setVisibility(-1);
     btncancel.setVisibility(-1);
     btnedit.setVisibility(-1);
    }
    public void visible()
    {
     lblage.setVisibility(0);
     txtage.setVisibility(0);
     btncancel.setVisibility(0);
     btnedit.setVisibility(0);
    }
    public void clear()
    {
     txtid.setText("");
     txtname.setText("");
     txtage.setText("");
    }
 @Override
 public void onClick(View v) {
  // TODO Auto-generated method stub
  Button action=(Button) v;
  if(action.getText().toString().equals("Edit"))
  {
   txtage.setEnabled(true);
   btnedit.setText("Update");
  }
  else if(action.getText().toString().equals("Update"))
  {
   ContentValues value=new ContentValues();
   value.put("name", txtname.getText().toString());
   value.put("age",txtage.getText().toString());
   db.update("stud",value, "id=?", new String[]{txtid.getText().toString()});
   btnedit.setText("Edit");
   Toast.makeText(this,"Update Successfully",1000).show();
   startActivity(new Intent(this,PRO15Activity.class));
  }
  else if(action.getText().toString().equals("Delete"))
  {
   db.delete("stud", "id=?",new String[]{txtid.getText().toString()});
   Toast.makeText(this, "Record Deleted...", Toast.LENGTH_LONG).show();
   startActivity(new Intent(this,PRO15Activity.class));
  }
  else if(action.getText().toString().equals("Save"))
  {
   if(txtname.getText().toString().equals(""))
   {
    Toast.makeText(this,"Please Enter Name ...", 1).show();
   }
   else if(txtage.getText().toString().equals(""))
   {
    Toast.makeText(this,"Please Enter Age ...", 1).show();
   }
   else
   {
    try
    {
     Cursor cur=db.query("stud", null, "name=?",new String[]{txtname.getText().toString()},null, null,null);
     if(cur.getCount()>0)
     {
      Toast.makeText(this,"Name Allready Exists ...",1).show();
     }
     else
     {
       ContentValues values=new ContentValues();
       values.put("name", txtname.getText().toString());
       values.put("age",txtage.getText().toString());
       db.insert("stud",null, values);
       Toast.makeText(this,"Record Saved...", 1).show();
     }
    }
    catch(Exception e)
    {
     Toast.makeText(this,e.toString(), 1).show();
    }
   }
  }
  else if(v.getId()==btncancel.getId())
  {
   startActivity(new Intent(this,PRO15Activity.class));
  }
 }
 public void enabled()
 {
  txtage.setEnabled(true);
 }
 public void disabled()
 {
  txtage.setEnabled(false);
 }
 @Override
 public void afterTextChanged(Editable arg0) {
  // TODO Auto-generated method stub
  Cursor cur=db.rawQuery("select *from stud where name like '"+txtname.getText().toString()+"%'",null);
  int len=cur.getCount();
  try
  {
   if(txtname.getText().toString().equals(""))
   {
    lv.setAdapter(null);
    name[0]="";
   }
   else if(len>0)
   {
    if(txtname.getText().toString().equals(""))
    {
     lv.setAdapter(null);
     for(int i=0; i<len; i++)
      name[i]="";
    }
    else if(len>0)
    {
     name=new String[len];
     cur.moveToNext();
     for(int i=0; i<len; i++)
     {
      name[i]=cur.getString(1);
      cur.moveToNext();
     }
    }
    ArrayAdapter<String> aa=new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,name);
    lv.setAdapter(aa);
   }
   else
   {
    Toast.makeText(this,"Recoerd Not Found...", 1000).show();
   }
  }
  catch(Exception e)
  {
   Toast.makeText(this,e.toString(),1000).show();
  }
 }
 @Override
 public void beforeTextChanged(CharSequence arg0, int arg1, int arg2,
   int arg3) {
  // TODO Auto-generated method stub
 }
 @Override
 public void onTextChanged(CharSequence arg0, int arg1, int arg2, int arg3) {
  // TODO Auto-generated method stub
 }
 @Override
 public void onItemClick(AdapterView<?> p, View v, int pos, long c) {
  // TODO Auto-generated method stub
  TextView iname=(TextView) v;
  String sname=iname.getText().toString();
  
  Cursor cur=db.rawQuery("select *from stud",null);
  if(cur.getCount()>0)
  {
   while(cur.moveToNext())
   {
    if(sname.equals(cur.getString(1)))
    {
     txtid.setText(cur.getInt(0)+"");
     txtname.setText(cur.getString(1));
     txtage.setText(cur.getString(2));
     break;
    }
   }
   visible();
   lv.setVisibility(-1);
  }
 }
}