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("");
  }
 }
}