Android Tutorial : SQLite Database Example

SQLite is an Open Source SQL Database which is used to store data and perform some task like insert, update, delete, search/read data or database operation.

android.database.sqlite package contain SQLite class.

SQLiteOpenHelper class is also provide functionality to use SQLite Database.

So, Now we will see the example of CRUD Operation with SQLite Database or Insert , Update , Delete, View Operation in easy way.

Let's see the example of Android SQLite Database Example :

File Name : MainActivity.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161

package com.example.sqlitedemo; import android.os.Bundle; import android.app.Activity; import android.database.sqlite.SQLiteDatabase; import android.view.Menu; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.app.AlertDialog.Builder; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.view.View.OnClickListener; public class MainActivity extends Activity implements OnClickListener { EditText editId,editName,editSalary; Button btnAdd,btnDelete,btnModify,btnView,btnViewAll,btnShowInfo; SQLiteDatabase db; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); editId=(EditText)findViewById(R.id.editId); editName=(EditText)findViewById(R.id.editName); editSalary=(EditText)findViewById(R.id.editSalary); btnAdd=(Button)findViewById(R.id.btnAdd); btnDelete=(Button)findViewById(R.id.btnDelete); btnModify=(Button)findViewById(R.id.btnModify); btnView=(Button)findViewById(R.id.btnView); btnViewAll=(Button)findViewById(R.id.btnViewAll); btnAdd.setOnClickListener(this); btnDelete.setOnClickListener(this); btnModify.setOnClickListener(this); btnView.setOnClickListener(this); btnViewAll.setOnClickListener(this); db=openOrCreateDatabase("EmployeeDB", Context.MODE_PRIVATE, null); db.execSQL("CREATE TABLE IF NOT EXISTS employee(id VARCHAR,name VARCHAR,salary VARCHAR);"); } public void onClick(View view) { if(view==btnAdd) { if(editId.getText().toString().trim().length()==0|| editName.getText().toString().trim().length()==0|| editSalary.getText().toString().trim().length()==0) { showMessage("Error", "Please enter all values"); return; } db.execSQL("INSERT INTO employee VALUES('"+editId.getText()+"','"+editName.getText()+ "','"+editSalary.getText()+"');"); showMessage("Success", "Record added"); clearText(); } if(view==btnDelete) { if(editId.getText().toString().trim().length()==0) { showMessage("Error", "Please enter Id"); return; } Cursor c=db.rawQuery("SELECT * FROM employee WHERE id='"+editId.getText()+"'", null); if(c.moveToFirst()) { db.execSQL("DELETE FROM employee WHERE id='"+editId.getText()+"'"); showMessage("Success", "Record Deleted"); } else { showMessage("Error", "Invalid Id"); } clearText(); } if(view==btnModify) { if(editId.getText().toString().trim().length()==0) { showMessage("Error", "Please enter Id"); return; } Cursor c=db.rawQuery("SELECT * FROM employee WHERE id='"+editId.getText()+"'", null); if(c.moveToFirst()) { db.execSQL("UPDATE employee SET name='"+editName.getText()+"',salary='"+editSalary.getText()+ "' WHERE id='"+editId.getText()+"'"); showMessage("Success", "Record Modified"); } else { showMessage("Error", "Invalid Id"); } clearText(); } if(view==btnView) { if(editId.getText().toString().trim().length()==0) { showMessage("Error", "Please enter Id"); return; } Cursor c=db.rawQuery("SELECT * FROM employee WHERE id='"+editId.getText()+"'", null); if(c.moveToFirst()) { editName.setText(c.getString(1)); editSalary.setText(c.getString(2)); } else { showMessage("Error", "Invalid Id"); clearText(); } } if(view==btnViewAll) { Cursor c=db.rawQuery("SELECT * FROM employee", null); if(c.getCount()==0) { showMessage("Error", "No records found"); return; } StringBuffer buffer=new StringBuffer(); while(c.moveToNext()) { buffer.append("Id: "+c.getString(0)+"\n"); buffer.append("Name: "+c.getString(1)+"\n"); buffer.append("Salary: "+c.getString(2)+"\n\n"); } showMessage("Employee Details", buffer.toString()); } } public void showMessage(String title,String message) { Builder builder=new Builder(this); builder.setCancelable(true); builder.setTitle(title); builder.setMessage(message); builder.show(); } public void clearText() { editId.setText(""); editName.setText(""); editSalary.setText(""); editId.requestFocus(); } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.activity_main, menu); return true; } }

File Name : activity_main.xml

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

<?xml version="1.0" encoding="utf-8"?> <AbsoluteLayout xmlns:android="http://schemas.android.com/apk/res/android" android:id="@+id/myLayout" android:stretchColumns="0" android:layout_width="fill_parent" android:layout_height="fill_parent"> <TextView android:text="@string/title" android:layout_x="110dp" android:layout_y="10dp" android:layout_width="wrap_content" android:layout_height="wrap_content"/> <TextView android:text="@string/id" android:layout_x="30dp" android:layout_y="50dp" android:layout_width="wrap_content" android:layout_height="wrap_content"/> <EditText android:id="@+id/editId" android:inputType="number" android:layout_x="150dp" android:layout_y="50dp" android:layout_width="150dp" android:layout_height="40dp"/> <TextView android:text="@string/name" android:layout_x="30dp" android:layout_y="100dp" android:layout_width="wrap_content" android:layout_height="wrap_content"/> <EditText android:id="@+id/editName" android:inputType="text" android:layout_x="150dp" android:layout_y="100dp" android:layout_width="150dp" android:layout_height="40dp"/> <TextView android:text="@string/salary" android:layout_x="30dp" android:layout_y="150dp" android:layout_width="wrap_content" android:layout_height="wrap_content"/> <EditText android:id="@+id/editSalary" android:inputType="number" android:layout_x="150dp" android:layout_y="150dp" android:layout_width="150dp" android:layout_height="40dp"/> <Button android:id="@+id/btnAdd" android:text="@string/add" android:layout_x="30dp" android:layout_y="200dp" android:layout_width="100dp" android:layout_height="40dp"/> <Button android:id="@+id/btnDelete" android:text="@string/delete" android:layout_x="150dp" android:layout_y="200dp" android:layout_width="100dp" android:layout_height="40dp"/>n <Button android:id="@+id/btnModify" android:text="@string/modify" android:layout_x="30dp" android:layout_y="250dp" android:layout_width="100dp" android:layout_height="40dp"/> <Button android:id="@+id/btnView" android:text="@string/view" android:layout_x="150dp" android:layout_y="250dp" android:layout_width="100dp" android:layout_height="40dp"/> <Button android:id="@+id/btnViewAll" android:text="@string/view_all" android:layout_x="30dp" android:layout_y="300dp" android:layout_width="220dp" android:layout_height="40dp"/> </AbsoluteLayout>

File Name : strings.xml

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

<?xml version="1.0" encoding="utf-8"?> <resources> <string name="app_name">SQLiteDemo</string> <string name="hello_world">Hello world!</string> <string name="menu_settings">Settings</string> <string name="title">Employee Details</string> <string name="id">Enter ID: </string> <string name="name">Enter Name: </string> <string name="salary">Enter Salary: </string> <string name="add">Add</string> <string name="delete">Delete</string> <string name="modify">Modify</string> <string name="view">View</string> <string name="view_all">View All</string> <string name="show_info">Show Information</string> </resources>

Output: