SQLite DataBase

SQLite is a open source relational database,it's used to perform database operation on device.It is embedded in android bydefauld. So, there is no need to perform any database setup.

There are several SQLite commands such as these are CREATE,SELECT,INSERT.UPDATE and DELETE.

Now we will learn about complete step to use SQLite Database.lets take a example of student

                                      Download Source Code

1.create modal class for student detail.
package com.example.model;

import java.io.Serializable;

import java.util.ArrayList;

public class Student implements Serializable 

{

private int roll;

private String name,branch,phone;

private ArrayList<StudentMarks> marks;

public Student(int roll, String name, String branch, String phone) {

super();

this.roll = roll;

this.name = name;

this.branch = branch;

this.phone = phone;

}

public Student(int roll, String name, String branch, String phone,

ArrayList<StudentMarks> marks) {

super();

this.roll = roll;

this.name = name;

this.branch = branch;

this.phone = phone;

this.marks = marks;

}

public int getRoll() {

return roll;

}

public void setRoll(int roll) {

this.roll = roll;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getBranch() {

return branch;

}

public void setBranch(String branch) {

this.branch = branch;

}

public String getPhone() {

return phone;

}

public void setPhone(String phone) {

this.phone = phone;

}

public ArrayList<StudentMarks> getMarks() {

return marks;

}

public void setMarks(ArrayList<StudentMarks> marks) {

this.marks = marks;

}

@Override

public String toString() {

// TODO Auto-generated method stub

return name;
 }
}

2. Create database helper class

package com.example.helper;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHelper extends SQLiteOpenHelper

{

public static final String DBNAME = "StudentDB";

public static final int DBVER = 1;

public MyDBHelper(Context context, String name, CursorFactory factory,

int version) {

super(context, name, factory, version);

// TODO Auto-generated constructor stub

}

@Override

public void onCreate(SQLiteDatabase db)

{

db.execSQL("create table student (roll integer primary key,name text,branch text,phone text)");

db.execSQL("create table studmarks (mid integer primary key autoincrement,roll integer,date text,marks real)");

}

@Override

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

// TODO Auto-generated method stub

if(oldVersion==1 && newVersion==2)

{}

if(oldVersion==1 && newVersion==3)

{}

if(oldVersion==2 && newVersion==3)

{}

}

}

4.Create database manager class

package com.example.manager;

import java.util.ArrayList;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import com.example.helper.MyDBHelper;

import com.example.model.Student;

public class StudentManager 

{

  private MyDBHelper helper;

  private SQLiteDatabase db;

  private Context ctx;

  

  public StudentManager(Context ctx)

  {

  this.ctx = ctx;

  helper = new MyDBHelper(ctx, MyDBHelper.DBNAME,null, MyDBHelper.DBVER);

  }

  

  public boolean addStudent(Student stud)

  {

  db = helper.getWritableDatabase();

  

  ContentValues cv = new ContentValues();

  cv.put("roll",stud.getRoll());

  cv.put("name",stud.getName());

  cv.put("branch",stud.getBranch());

  cv.put("phone",stud.getPhone());

     

  long x = db.insert("student", null, cv);

  db.close();

  

  if(x>0)

    return true;

  return false;  

  }

public ArrayList<Student> getStudents()

{

ArrayList<Student> list = new ArrayList<Student>();

db = helper.getReadableDatabase();

Cursor cr = db.rawQuery("select * from student",null);

while(cr.moveToNext())

{

int roll = cr.getInt(0);

String name = cr.getString(1);

String branch = cr.getString(2);

String phone = cr.getString(3);

Student s = new Student(roll, name, branch, phone);

list.add(s);

}

cr.close();

db.close();

return list;

} 

public void deleteStudent(int roll)

{

db = helper.getWritableDatabase();

db.delete("student","roll=?",new String[]{roll+""});

db.close();

}

public void updateStudent(Student s)

{

  db = helper.getWritableDatabase();

  

  ContentValues cv = new ContentValues();

    cv.put("name", s.getName());

  cv.put("branch", s.getBranch());

  cv.put("phone", s.getPhone());

  

  db.update("student", cv, "roll=?",new String[]{s.getRoll()+""});

  db.close();

}

public ArrayList<Student> getStudentByRoll(int roll)

{

ArrayList<Student> s = new ArrayList<Student>();

db = helper.getReadableDatabase();

Cursor c  = db.rawQuery("select * from student where roll="+roll, null);

if(c.moveToNext())

{

int r = c.getInt(0);

String n = c.getString(1) ;

String b = c.getString(2) ;

String p = c.getString(3) ;

s.add(new Student(r, n, b, p));

}

db.close();

return s;

}

}

5.create main_Activity

package com.example.view;

import java.util.ArrayList;

import android.app.AlertDialog;

import android.app.ListActivity;

import android.content.DialogInterface;

import android.content.Intent;

import android.os.Bundle;

import android.view.Menu;

import android.view.MenuItem;

import android.widget.ArrayAdapter;

import android.widget.EditText;

import android.widget.ListView;

import com.example.facultyapp.R;

import com.example.manager.StudentManager;

import com.example.model.Student;

import com.example.model.StudentAdapter;

public class MainActivity extends  ListActivity 

{

ListView lv;

ArrayList<Student> studList;

StudentAdapter sadp;

StudentManager sm;

public int updatepos;

public void deleteStud(Student s)

{

studList.remove(s);

sadp.notifyDataSetChanged();

}

@Override

protected void onCreate(Bundle savedInstanceState) 

{

super.onCreate(savedInstanceState);

sm = new StudentManager(this);

lv = getListView();

studList = sm.getStudents();

sadp = new StudentAdapter(studList, this);

lv.setAdapter(sadp);

}

@Override

public boolean onCreateOptionsMenu(Menu menu) {

    getMenuInflater().inflate(R.menu.main, menu);

return super.onCreateOptionsMenu(menu);

}

@Override

public boolean onOptionsItemSelected(MenuItem item) 

{

int id = item.getItemId();

switch(id)

{

case R.id.addStud:

Intent in = new Intent(MainActivity.this,AddStudentActivity.class);

startActivityForResult(in, 11);

break;

case R.id.searchStud: 

openDialog();

break;

case R.id.allStud:

studList.clear();

sadp.clear();

studList = sm.getStudents();

sadp = new StudentAdapter(studList, MainActivity.this);

lv.setAdapter(sadp);

break;

}

return super.onOptionsItemSelected(item);

}

private void openDialog() 

{

   AlertDialog.Builder bd = new AlertDialog.Builder(MainActivity.this);

   bd.setTitle("Search Student");

   

   final EditText et = new EditText(MainActivity.this);

   bd.setView(et);

   bd.setPositiveButton("Roll", new DialogInterface.OnClickListener()

   {

@Override

public void onClick(DialogInterface dialog, int which) {

studList.clear();

sadp.clear();

int roll = Integer.parseInt(et.getText().toString());

studList = sm.getStudentByRoll(roll);

sadp = new StudentAdapter(studList, MainActivity.this);

lv.setAdapter(sadp);

}

   });

   bd.setNegativeButton("Branch", new DialogInterface.OnClickListener()

   {

@Override

public void onClick(DialogInterface dialog, int which) {

// TODO Auto-generated method stub

}

   });

   AlertDialog dlg = bd.create();

   dlg.show();

}

@Override

protected void onActivityResult(int requestCode, int resultCode, Intent data) 

{

if(requestCode==11 && resultCode==RESULT_OK)

{

Student s = (Student) data.getSerializableExtra("stud");

studList.add(s);

sadp.notifyDataSetChanged();

}

if(requestCode==12 && resultCode==RESULT_OK)

{

Student s = (Student) data.getSerializableExtra("s");

studList.set(updatepos, s);

sadp.notifyDataSetChanged();

}

super.onActivityResult(requestCode, resultCode, data);

}

}

23.Create Layout for adaptor view 

<?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="horizontal" >

    <TextView

        android:id="@+id/nameTv"

        android:layout_width="0dp"

        android:layout_weight="1"

        android:layout_height="wrap_content"

        android:text="Large Text"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <Button

        android:id="@+id/viewBT"

        style="?android:attr/buttonStyleSmall"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="View" />

    <Button

        android:id="@+id/updateBT"

        style="?android:attr/buttonStyleSmall"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Update" />

    <Button

        android:id="@+id/delBT"

        style="?android:attr/buttonStyleSmall"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Delete" />

</LinearLayout>

45. Create Adaptor  

package com.example.model;

import java.util.ArrayList;

import android.app.AlertDialog;

import android.content.Context;

import android.content.DialogInterface;

import android.content.Intent;

import android.view.LayoutInflater;

import android.view.View;

import android.view.View.OnClickListener;

import android.view.ViewGroup;

import android.widget.BaseAdapter;

import android.widget.Button;

import android.widget.TextView;

import com.example.facultyapp.R;

import com.example.manager.StudentManager;

import com.example.view.MainActivity;

import com.example.view.UpdateActivity;

public class StudentAdapter extends BaseAdapter

{

private ArrayList<Student> list;

private Context ctx;

public StudentAdapter(ArrayList<Student> list, Context ctx) {

super();

this.list = list;

this.ctx = ctx;

}

@Override

public int getCount() {

// TODO Auto-generated method stub

return list.size();

}

@Override

public Object getItem(int arg0) {

// TODO Auto-generated method stub

return null;

}

@Override

public long getItemId(int arg0) {

// TODO Auto-generated method stub

return 0;

}

@Override

public View getView(final int position, View convertView, ViewGroup parent) {

// 

LayoutInflater inf = (LayoutInflater) ctx.getSystemService(ctx.LAYOUT_INFLATER_SERVICE);

View vw = inf.inflate(R.layout.studview, null);

TextView tv = (TextView) vw.findViewById(R.id.nameTv);

Button viewBT = (Button) vw.findViewById(R.id.viewBT);

Button delBT = (Button) vw.findViewById(R.id.delBT);

Button upBT = (Button) vw.findViewById(R.id.updateBT);

final Student s = list.get(position);

upBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) 

{

Intent in = new Intent(ctx, UpdateActivity.class);

in.putExtra("stud",s);

MainActivity act = (MainActivity) ctx;

act.updatepos=position;

act.startActivityForResult(in, 12);

}

});

delBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) 

{

   int roll = s.getRoll();

   StudentManager sm = new StudentManager(ctx);

   sm.deleteStudent(roll);

   

   MainActivity act = (MainActivity) ctx;

   act.deleteStud(s);

}

});

viewBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) 

{

AlertDialog.Builder bd = new AlertDialog.Builder(ctx);

bd.setTitle("Student..");

TextView tv = new TextView(ctx);

String data = "Roll - " + s.getRoll() + "\n";

data += "NAme - " + s.getName() + "\n";

data += "Phone - " + s.getPhone() + "\n";

data += "Branch - " + s.getBranch() + "\n";

tv.setText(data);

bd.setView(tv);

bd.setPositiveButton("Close",new DialogInterface.OnClickListener()

{

@Override

public void onClick(DialogInterface dialog, int which) {

// TODO Auto-generated method stub

}

});

AlertDialog dlg = bd.create();

dlg.show();

}

});

tv.setText(s.getName());

return vw;

}

public void clear() {

// TODO Auto-generated method stub

list.clear();

}  

}

6.Create Layout for add student detail  

<?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" >

    <EditText

        android:id="@+id/rollET"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:ems="10"

        android:hint="Roll Number" 

        android:layout_marginTop="10dp"

        >

        <requestFocus />

    </EditText>

    <EditText

        android:id="@+id/nameET"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:ems="10"

        android:hint="Student Name"

        android:layout_marginTop="10dp"

         />

    <Spinner

        android:id="@+id/branchSP"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" 

        android:layout_marginTop="10dp"/>

    <EditText

        android:id="@+id/phoneET"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:ems="10"

        android:layout_marginTop="10dp"

        android:hint="Phone Number" />

    <LinearLayout

        android:layout_width="match_parent"

        android:layout_height="wrap_content" 

        android:layout_marginTop="10dp">

        <Button

            android:id="@+id/saveBT"

            android:layout_width="0dp"

            android:layout_weight="1"

            android:layout_height="wrap_content"

            android:text="Save" />

        <Button

            android:id="@+id/canBT"

            android:layout_width="0dp"

            android:layout_weight="1"

            android:layout_height="wrap_content"

            android:text="Cancel" />

    </LinearLayout>

</LinearLayout>

6. insert New DATA 

package com.example.view;

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.ArrayAdapter;

import android.widget.Button;

import android.widget.EditText;

import android.widget.Spinner;

import android.widget.Toast;

import com.example.facultyapp.R;

import com.example.manager.StudentManager;

import com.example.model.Student;

public class AddStudentActivity extends Activity 

{

EditText rollET,nameET,phoneET;

Spinner branchSP;

Button saveBT,canBT;

String [] branch = 

{

"CS","EC","IT","ME","CE","EE"

};

ArrayAdapter<String> branchadp;

StudentManager sm;

@Override

protected void onCreate(Bundle savedInstanceState) 

{

super.onCreate(savedInstanceState);

setContentView(R.layout.addstud);

rollET = (EditText) findViewById(R.id.rollET);

nameET = (EditText) findViewById(R.id.nameET);

phoneET = (EditText) findViewById(R.id.phoneET);

branchSP = (Spinner) findViewById(R.id.branchSP);

saveBT = (Button) findViewById(R.id.saveBT);

canBT = (Button) findViewById(R.id.canBT);

sm = new StudentManager(this);

branchadp = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item,branch);

branchSP.setAdapter(branchadp);

saveBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) 

{

  int roll  = Integer.parseInt(rollET.getText().toString());

  String name = nameET.getText().toString();

  String branch = (String) branchSP.getSelectedItem();

  String phone = phoneET.getText().toString();

  

  Student stud = new Student(roll, name, branch, phone);

  

  boolean check = sm.addStudent(stud);

  

  if(check)

  {

  Toast.makeText(AddStudentActivity.this, "Record Insert !",2).show();

  Intent in = new Intent();

  in.putExtra("stud", stud);

  setResult(RESULT_OK, in);

  }

  else

  Toast.makeText(AddStudentActivity.this, "Record Insert Failed !",2).show();

  

  finish();

}

});

canBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) {

// TODO Auto-generated method stub

finish();

}

});

}

}

7.Create Layout for Update Student Detail 

<?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" >

    <EditText

        android:id="@+id/rollET"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:ems="10"

        android:hint="Roll Number" 

        android:layout_marginTop="10dp"

        android:editable="false"

        >

        <requestFocus />

    </EditText>

    <EditText

        android:id="@+id/nameET"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:ems="10"

        android:hint="Student Name"

        android:layout_marginTop="10dp"

         />

    <Spinner

        android:id="@+id/branchSP"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" 

        android:layout_marginTop="10dp"/>

    <EditText

        android:id="@+id/phoneET"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:ems="10"

        android:layout_marginTop="10dp"

        android:hint="Phone Number" />

    <LinearLayout

        android:layout_width="match_parent"

        android:layout_height="wrap_content" 

        android:layout_marginTop="10dp">

        <Button

            android:id="@+id/updateBT"

            android:layout_width="0dp"

            android:layout_height="wrap_content"

            android:layout_weight="1"

            android:text="Update" />

        <Button

            android:id="@+id/canBT"

            android:layout_width="0dp"

            android:layout_weight="1"

            android:layout_height="wrap_content"

            android:text="Cancel" />

    </LinearLayout>

</LinearLayout>

7. For  Update screen  

package com.example.view;

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.ArrayAdapter;

import android.widget.Button;

import android.widget.EditText;

import android.widget.Spinner;

import com.example.facultyapp.R;

import com.example.manager.StudentManager;

import com.example.model.Student;

public class UpdateActivity extends Activity {

EditText rollET,nameET,phoneET;

Spinner branchSP;

Button upBT,canBT;

String [] branch = 

{

"CS","EC","IT","ME","CE","EE"

};

ArrayAdapter<String> branchadp;

StudentManager sm;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.updatestud);

rollET = (EditText) findViewById(R.id.rollET);

nameET = (EditText) findViewById(R.id.nameET);

phoneET = (EditText) findViewById(R.id.phoneET);

branchSP = (Spinner) findViewById(R.id.branchSP);

upBT = (Button) findViewById(R.id.updateBT);

canBT = (Button) findViewById(R.id.canBT);

sm = new StudentManager(this);

branchadp = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item,branch);

branchSP.setAdapter(branchadp);

Intent in  = getIntent();

Student s = (Student) in.getSerializableExtra("stud");

rollET.setText(s.getRoll()+"");

nameET.setText(s.getName());

phoneET.setText(s.getPhone());

String b = s.getBranch();

for(int i=0;i<branch.length;i++)

{

if(b.equals(branch[i]))

{

branchSP.setSelection(i);

break;

}

}

canBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) {

// TODO Auto-generated method stub

finish();

}

});

upBT.setOnClickListener(new OnClickListener()

{

@Override

public void onClick(View v) 

{

int roll = Integer.parseInt(rollET.getText().toString());

String name = nameET.getText().toString();

String branch = (String) branchSP.getSelectedItem();

String phone = phoneET.getText().toString();

Student s = new Student(roll, name, branch, phone);

sm.updateStudent(s);

Intent in = new Intent();

in.putExtra("s",s);

setResult(RESULT_OK, in);

finish();

}

});

}

}