Performing operations with SQLiteDatabase in android

Android framework provides an API by the name SQLiteDatabase API. This API provides some set of classes and interfaces which are used by an application developer to create database and performing some operation commonly used classes and interfaces are as follows:

1. SQLiteOpenHelper


2. SqliteDatabase


3. Cursor

 

1. SQliteOpenHelper ((: This is an abstract class that means if an application developer wants to use this class then we should define the sub class of this class. SQLiteOpenHelper class provides us facility to create database, to create tables into database to open database in readable or in writable mode and to append database or to upgrade. SQLiteOpenHelper class provides us a four parametrized constructor which is used by an application developer to create database.

public SQLiteOpenHelper (Context ctx, String dbName, CursorFactory fact, int dbVer)

{

}

Abstract methods of SQLiteOpenHelper class are:

1. onCreate(SQLiteDatabase db) : This method is invoked only once whenever SQLiteDatabase is open in readable or writable mode for the first time. An application developer may use this method to perform such options which needs to execute only once Like creation of table into database.


2. onUpgradeDatabase(SQLiteDatabase db, int oldVersion, into newVersion){}

Non-Abstract methods of SQLiteOpenHelper class are:

1. getReadableDatabase() : This method returns SQLiteDatabase object in readable mode.


2. getWritableDatabase() : This methods returns SQLiteDatabase object in writable mode.

SQLiteDatabase : This class provides facility to interact with SQLiteDatabase. An application developer may use tthis class to interact (Insertion, Deletion, Update, Select and perform transactions creation of user define functions etc.) with SQLiteDatabase.

Commonly used methods of this class are as follows:

1. insert(String tableName, String nullColumnbHack, ContentValues cv) : This methods is used to insert a record in SQLiteDatabase table.

ContentValues : Android framework provides this class to hold or to store data in the form of key value pair where key always representss the name of column. It is case sensitive.

ContextValues cv = new ContentValues();

put (String key, Type Value);

put(String key, Type Value) : Method is used to store data in content value object.

 

Let's start with Sample operations to perform with SQLiteDatabase in android Codes a given below :

 

Download Source Code

Do not forget to share with your friends!

 

EmployeeDAO.java

 

package com.tech.dbdao;

import com.tech.dbdto.Employee;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;
import android.widget.Toast;

public class EmployeeDAO {
public static final String DBNAME = "Employee.db";
public static final String TABLENAME = "EmpDetail";
public static final String TABLEQUERY = "create table EmpDetail(_id integer primary key autoincrement," +
"NAME text, JOB text, SALARY integer)";
public static final int DB_VERSION = 1;
public static final int READ_MODE = 1;
public static final int WRITE_MODE = 2;

Context ctx;
MyHelper helper;
SQLiteDatabase db;

public EmployeeDAO(Context c) {
ctx = c;
helper = new MyHelper(c, DBNAME, null, DB_VERSION);
}

public void saveDate(Employee e)
{
ContentValues cv = new ContentValues();
cv.put("NAME", e.getNAME());
cv.put("JOB", e.getJOB());
cv.put("SALARY", e.getSALARY());
db.insert(TABLENAME, null, cv);
Toast.makeText(ctx, "Record successfully saved with NAME : "+e.getNAME(), Toast.LENGTH_SHORT).show();
}

public Cursor fetchAllEmp()
{
Cursor cr = db.query(TABLENAME, null, null, null, null, null, null);
return cr;
}

public Employee findById(int eid)
{
Employee emp=null;
Cursor cursor=db.query(TABLENAME, null,    "_id=?", new String[]{""+eid}, null,null,null);
if(cursor.moveToFirst())
{
int id=cursor.getInt(0);
String name=cursor.getString(1);
String job=cursor.getString(2);
String sal=cursor.getString(3);
emp=new Employee(name, job, Integer.parseInt(sal));
emp.setId(id);
}
return emp;
}

public void updateEmp(Employee emp)
{
ContentValues row=new ContentValues();
row.put("NAME", emp.getNAME());
row.put("JOB", emp.getJOB());
row.put("SALARY", emp.getSALARY());
db.update(TABLENAME, row,"_id=?",
new String[]{""+emp.getId()});

Toast.makeText(ctx, "One record is updated successfully..."+emp.getNAME(), Toast.LENGTH_LONG).show();
}

public void deleteEmp(int eid)
{
db.delete(TABLENAME, "_id=?", new String[]{""+eid});
Toast.makeText(ctx, "Record has been deleted", Toast.LENGTH_SHORT).show();
}

public void openDB(int MODE)
{
if(MODE == READ_MODE)
{
db = helper.getReadableDatabase();
}
else
{
db = helper.getWritableDatabase();
}
}

class MyHelper extends SQLiteOpenHelper
{

public MyHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase arg0) {
arg0.execSQL(TABLEQUERY);
}

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
// TODO Auto-generated method stub

}

}
}

 

 

Employee.java

 

package com.tech.dbdto;

public class Employee {
int id;
String NAME, JOB;
int SALARY;
public Employee(String nAME, String jOB, int sALARY) {
super();
NAME = nAME;
JOB = jOB;
SALARY = sALARY;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNAME() {
return NAME;
}
public void setNAME(String nAME) {
NAME = nAME;
}
public String getJOB() {
return JOB;
}
public void setJOB(String jOB) {
JOB = jOB;
}
public int getSALARY() {
return SALARY;
}
public void setSALARY(int sALARY) {
SALARY = sALARY;
}
}

 

MyApplication.java

[caption id="attachment_841" align="alignnone" width="200"] Employee Database management in android sqlite.[/caption]

package com.tech.dbdemo;

import com.tech.dbdao.EmployeeDAO;

import android.app.Application;

public class MyApplication extends Application {
static EmployeeDAO mydao;
@Override
public void onCreate() {
super.onCreate();
mydao = new EmployeeDAO(getApplicationContext());
}
}

 

Home_Activity.java

 

package com.tech.dbdemo;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;

public class Home_Activity extends Activity{
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
super.onCreateOptionsMenu(menu);

MenuItem itemSave = menu.add("Save");
MenuItem itemDelete = menu.add("Delete");
MenuItem itemFind = menu.add("Find");
MenuItem itemView = menu.add("View");

itemSave.setIntent(new Intent(this, SaveEmployeeData.class));
itemDelete.setIntent(new Intent(this, DeleteEmployee.class));
itemFind.setIntent(new Intent(this, FindEmployee.class));
itemView.setIntent(new Intent(this, ViewAll.class));
return true;
}
}

 

SaveEmployeeData.java

 

package com.tech.dbdemo;

import com.tech.dbdao.EmployeeDAO;
import com.tech.dbdto.Employee;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class SaveEmployeeData extends Activity implements OnClickListener{
EditText etName, etJob, etSalary;
Button btnSave, btnNew, btnExit;

EmployeeDAO eDAO;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.saverecords_activity);

etName = (EditText)findViewById(R.id.etName);
etJob = (EditText)findViewById(R.id.etJob);
etSalary = (EditText)findViewById(R.id.etSalary);

btnSave = (Button)findViewById(R.id.btnSave);
btnNew = (Button)findViewById(R.id.btnNew);
btnExit = (Button)findViewById(R.id.btnExit);

btnSave.setOnClickListener(this);
btnNew.setOnClickListener(this);
btnExit.setOnClickListener(this);
}
@Override
protected void onStart() {
super.onStart();
eDAO = MyApplication.mydao;
eDAO.openDB(EmployeeDAO.WRITE_MODE);
}
@Override
public void onClick(View arg0) {
if(arg0 == btnSave)
{
eDAO.saveDate(new Employee(etName.getText().toString(), etJob.getText().toString(), Integer.parseInt(etSalary.getText().toString())));
}
if(arg0 == btnNew)
{
etName.setText(null);
etJob.setText(null);
etSalary.setText(null);
}
if(arg0 == btnExit)
{
finish();
}
}
}

 

DeleteEmployee.java

[caption id="attachment_840" align="alignnone" width="200"] Delete an employee record[/caption]

package com.tech.dbdemo;

import com.tech.dbdao.EmployeeDAO;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;

public class DeleteEmployee extends Activity implements OnClickListener {
TextView tvfind;
Button btnFind, btnDel;

EmployeeDAO eDAO;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.findbyid_activity);

tvfind = (TextView)findViewById(R.id.tvfindid);

btnFind = (Button)findViewById(R.id.btnFindid);
btnFind.setText("Delete");
btnFind.setOnClickListener(this);
}
@Override
protected void onStart() {
super.onStart();
eDAO = MyApplication.mydao;
eDAO.openDB(EmployeeDAO.WRITE_MODE);
}
@Override
public void onClick(View arg0) {
if(arg0 == btnFind)
{
eDAO.deleteEmp(Integer.parseInt(tvfind.getText().toString()));
}
}
}

 

findbyid_activity.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:background="#088A4B"
android:layout_width="match_parent"
android:layout_height="match_parent" android:orientation="vertical">
<LinearLayout android:id="@+id/linearLayout1"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp"
android:weightSum="1.0"
android:layout_height="wrap_content"
android:layout_width="match_parent">
<TextView android:text="ID"
android:id="@+id/textView1"
android:layout_weight=".7"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></TextView>
<EditText android:text=""
android:id="@+id/tvfindid"
android:hint="Enter employee id"
android:layout_weight=".3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></EditText>
</LinearLayout>
<Button android:text="Find"
android:id="@+id/btnFindid"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp"
android:layout_marginTop="50dp"
android:layout_width="fill_parent"
android:layout_height="wrap_content"></Button>
</LinearLayout>

FindEmployee.java

[caption id="attachment_839" align="alignnone" width="200"] Find employee by id[/caption]

package com.tech.dbdemo;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;

public class FindEmployee extends Activity implements OnClickListener{
TextView tvfind;
Button btnFind;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.findbyid_activity);

tvfind = (TextView)findViewById(R.id.tvfindid);

btnFind = (Button)findViewById(R.id.btnFindid);
btnFind.setOnClickListener(this);
}
@Override
public void onClick(View arg0) {
if(arg0 == btnFind)
{
Intent intnt = new Intent(this, UpdateEmp_Activity.class);
intnt.putExtra("id", tvfind.getText().toString());
startActivity(intnt);
}
}
}

 

saverecords_activity.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:background="#088A4B"
android:layout_height="match_parent" android:orientation="vertical">
<LinearLayout android:layout_width="match_parent" android:weightSum="1.0" android:id="@+id/linearLayout1" android:layout_height="wrap_content">
<TextView android:text="Name" android:layout_weight=".7" android:id="@+id/textView1" android:layout_width="wrap_content" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:layout_weight=".3" android:hint="Enter name" android:id="@+id/etName" android:layout_width="wrap_content" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:layout_width="match_parent" android:weightSum="1.0" android:id="@+id/linearLayout2" android:layout_height="wrap_content">
<TextView android:text="Job" android:layout_weight=".7" android:id="@+id/textView2" android:layout_width="wrap_content" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:layout_weight=".3" android:hint="Enter job" android:id="@+id/etJob" android:layout_width="wrap_content" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:layout_width="match_parent" android:weightSum="1.0" android:id="@+id/linearLayout3" android:layout_height="wrap_content">
<TextView android:text="Salary" android:layout_weight=".7" android:id="@+id/textView3" android:layout_width="wrap_content" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:layout_weight=".3" android:hint="Enter salary" android:id="@+id/etSalary" android:layout_width="wrap_content" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:layout_width="match_parent" android:weightSum="1.0" android:id="@+id/linearLayout4" android:layout_height="wrap_content">
<Button android:text="Save" android:layout_weight=".33" android:id="@+id/btnSave" android:layout_width="wrap_content" android:layout_height="wrap_content"></Button>
<Button android:text="New" android:layout_weight=".34" android:id="@+id/btnNew" android:layout_width="wrap_content" android:layout_height="wrap_content"></Button>
<Button android:text="Exit" android:layout_weight=".33" android:id="@+id/btnExit" android:layout_width="wrap_content" android:layout_height="wrap_content"></Button>
</LinearLayout>
</LinearLayout>

 

SaveEmployeeData.java

[caption id="attachment_838" align="alignnone" width="200"] Save and update employee information[/caption]

package com.tech.dbdemo;

import com.tech.dbdao.EmployeeDAO;
import com.tech.dbdto.Employee;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class SaveEmployeeData extends Activity implements OnClickListener{
EditText etName, etJob, etSalary;
Button btnSave, btnNew, btnExit;

EmployeeDAO eDAO;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.saverecords_activity);

etName = (EditText)findViewById(R.id.etName);
etJob = (EditText)findViewById(R.id.etJob);
etSalary = (EditText)findViewById(R.id.etSalary);

btnSave = (Button)findViewById(R.id.btnSave);
btnNew = (Button)findViewById(R.id.btnNew);
btnExit = (Button)findViewById(R.id.btnExit);

btnSave.setOnClickListener(this);
btnNew.setOnClickListener(this);
btnExit.setOnClickListener(this);
}
@Override
protected void onStart() {
super.onStart();
eDAO = MyApplication.mydao;
eDAO.openDB(EmployeeDAO.WRITE_MODE);
}
@Override
public void onClick(View arg0) {
if(arg0 == btnSave)
{
eDAO.saveDate(new Employee (etName.getText().toString(), etJob.getText().toString(), Integer.parseInt(etSalary.getText().toString())));
}
if(arg0 == btnNew)
{
etName.setText(null);
etJob.setText(null);
etSalary.setText(null);
}
if(arg0 == btnExit)
{
finish();
}
}
}

 

updateemp_activity.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:background="#088A4B"
android:layout_width="match_parent"
android:layout_height="match_parent" android:orientation="vertical">
<LinearLayout android:id="@+id/linearLayout5" android:weightSum="1.0" android:layout_width="match_parent" android:layout_height="wrap_content">
<TextView android:text="ID" android:id="@+id/textView4" android:layout_width="wrap_content"  android:layout_weight=".3" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:id="@+id/ETID" android:layout_width="wrap_content"  android:layout_weight=".7" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:id="@+id/linearLayout1" android:weightSum="1.0" android:layout_width="match_parent" android:layout_height="wrap_content">
<TextView android:text="NAME" android:id="@+id/textView1" android:layout_width="wrap_content" android:layout_weight=".3" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:id="@+id/ETNAME" android:layout_width="wrap_content" android:layout_weight=".7" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:id="@+id/linearLayout2" android:weightSum="1.0" android:layout_width="match_parent" android:layout_height="wrap_content">
<TextView android:text="JOB" android:id="@+id/textView2" android:layout_width="wrap_content" android:layout_weight=".3" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:id="@+id/ETJOB" android:layout_width="wrap_content" android:layout_weight=".7" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:id="@+id/linearLayout3" android:weightSum="1.0" android:layout_width="match_parent" android:layout_height="wrap_content">
<TextView android:text="SALARY" android:id="@+id/textView3" android:layout_width="wrap_content" android:layout_weight=".3" android:layout_height="wrap_content"></TextView>
<EditText android:text="" android:id="@+id/ETSAL" android:layout_width="wrap_content" android:layout_weight=".7" android:layout_height="wrap_content"></EditText>
</LinearLayout>
<LinearLayout android:id="@+id/linearLayout4" android:weightSum="1.0" android:layout_width="match_parent" android:layout_height="wrap_content">
<Button android:text="Update" android:id="@+id/BTNUPDATE" android:layout_width="wrap_content" android:layout_weight=".5" android:layout_height="wrap_content"></Button>
<Button android:text="Exit" android:id="@+id/BTNEXIT" android:layout_width="wrap_content" android:layout_weight=".5" android:layout_height="wrap_content"></Button>
</LinearLayout>

</LinearLayout>

 

UpdateEmp_Activity.java

 

package com.tech.dbdemo;

import com.tech.dbdao.EmployeeDAO;
import com.tech.dbdto.Employee;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class UpdateEmp_Activity extends Activity implements OnClickListener{
EmployeeDAO eDAO = null;
Employee emp = null;
EditText etid, etname, etjob, etsal;
Button btnupdate, btnexit;
@Override
protected void onCreate(Bundle savedInstanceState){
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.updateemp_activity);

etid = (EditText)findViewById(R.id.ETID);
etname = (EditText)findViewById(R.id.ETNAME);
etjob = (EditText)findViewById(R.id.ETJOB);
etsal = (EditText)findViewById(R.id.ETSAL);

btnupdate = (Button)findViewById(R.id.BTNUPDATE);
btnexit = (Button)findViewById(R.id.BTNEXIT);

btnupdate.setOnClickListener(this);
btnexit.setOnClickListener(this);
}
@Override
protected void onStart() {
// TODO Auto-generated method stub
super.onStart();
eDAO = MyApplication.mydao;
eDAO.openDB(EmployeeDAO.WRITE_MODE);
Intent intnt = getIntent();
String id = intnt.getStringExtra("id");
emp = eDAO.findById(Integer.parseInt(id));
etid.setText(id);
Log.d("NAME ",emp.getNAME());
etname.setText(emp.getNAME());
etjob.setText(emp.getJOB());
int salary = emp.getSALARY();
etsal.setText(""+salary);
}
@Override
public void onClick(View arg0) {
if(arg0 == btnupdate)
{
eDAO.updateEmp(emp);
}
else if(arg0 == btnexit)
finish();
}
}

allemplist_activity.xml

 

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:background="#088A4B"
android:layout_width="match_parent"
android:layout_height="match_parent">
<ListView android:id="@+id/lvEmplist" android:layout_width="match_parent" android:layout_height="wrap_content"></ListView>
</LinearLayout>

 

listdetailholder_layout.xml

 

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:weightSum="1.0"
android:layout_width="match_parent"
android:layout_height="match_parent">
<TextView android:text=""
android:id="@+id/tvID"
android:textColor="#F8ECE0"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></TextView>
<TextView android:text=""
android:id="@+id/tvNAME"
android:layout_below="@+id/tvID"
android:textColor="#F8ECE0"
android:layout_width="wrap_content"
android:layout_height="wrap_content"></TextView>
<TextView android:layout_width="wrap_content"
android:text=""
android:id="@+id/tvJOB"
android:textColor="#F8ECE0"
android:layout_height="wrap_content"
android:layout_alignParentRight="true"></TextView>
<TextView android:layout_width="wrap_content"
android:text=""
android:layout_below="@+id/tvJOB"
android:textColor="#F8ECE0"
android:id="@+id/tvSALARY"
android:layout_height="wrap_content"
android:layout_alignParentRight="true"></TextView>
</RelativeLayout>

 

ViewAll.java

[caption id="attachment_837" align="alignnone" width="200"] View All Employee List. Name, Salary, Designation, ID[/caption]

package com.tech.dbdemo;

import com.tech.dbdao.EmployeeDAO;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;

public class ViewAll extends Activity {
ListView lvEmp;
EmployeeDAO eDAO;
SimpleCursorAdapter adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.allemplist_activity);

lvEmp = (ListView)findViewById(R.id.lvEmplist);
TextView tv = new TextView(this);
tv.setText("Following details of employees are : ");
lvEmp.addHeaderView(tv);
}
@Override
protected void onStart() {
super.onStart();
eDAO = MyApplication.mydao;
eDAO.openDB(EmployeeDAO.READ_MODE);
Cursor c = eDAO.fetchAllEmp();
adapter = new SimpleCursorAdapter (this, R.layout.listdetailholder_layout, c, new String[]{"_id", "NAME", "JOB", "SALARY"}, new int[]{R.id.tvID, R.id.tvNAME, R.id.tvJOB, R.id.tvSALARY});
lvEmp.setAdapter(adapter);
}
}