Android SQLite Database Tutorial
August 11, 2015
SQLiteOpenHelper
android.database.sqlite.SQLiteOpenHelper manages database creation, up gradation, down gradation, version management and opening it. We need to create sub class of SQLiteOpenHelper and override onCreate and onUpgrade and optionally onOpen. If database is not created, onCreate is called where we write script for database creation. If already created, then onOpen is called which opens database. When database version is upgraded, onUpgrade is called. Here we will discuss some methods of SQLiteOpenHelper .
onCreate (SQLiteDatabase db)
onCreate is called for the first time when creation of tables are needed. We need to override this method where we write the script for table creation which is executed by SQLiteDatabase. execSQL method. After executing in first time deployment, this method will not be called onwards. For upgrade and downgrade database, SQLiteOpenHelper provides onUpgrade and onDowngrade respectively.
onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
This method is called when database version is upgraded. Suppose for the first time deployment , database version was 1 and in second deployment there was change in database structure like adding extra column in table. Suppose database version is 2 now. So we can handle it as below inside the method
onUpgrade.
If (newVersion == oldVersion + 1) {
//do database related changes.
}
There may be the cases that someone deployed app for the first time when database version was 1. And then directly deployed latest version in which database version was 4. In this case we need to handle database up gradation, from current old version to new version step by step in different if-else or switch case.
getWritableDatabase ()
When this method is called, the required methods like onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and onOpen(SQLiteDatabase) can be called. Once database is opened, it is cached.
getWritableDatabase returns SQLiteDatabase which provides methods to execute SQL queries for SQLite.
SQLiteDatabase
android.database.sqlite.SQLiteDatabase contains methods to query SQLite database. SQLiteDatabase is initialized as follows
SQLiteDatabase db = getWritableDatabase();
getWritableDatabase() is the method of SQLiteOpenHelper. Find some method description of SQLiteDatabase.
execSQL (String sql)
Executes the single SQL statement which is not a SELECT query.
compileStatement (String sql)
It works like JDBC
prepareStatement(String sql). We write SQL query and for input, use (?). It generates re-usable pre-compiled statement.
SQLiteDatabase db = this.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("DELETE FROM student_info WHERE id = ?");
stmt.bindLong(1, stdId);
stmt.execute();
rawQuery (String sql, String[] selectionArgs)
This method is used for SELECT statement. If the statement has input requirement, we put there (?) and in the respective order we put the input values in String array.
SQLiteDatabase db = this.getWritableDatabase();
String query = "SELECT id, name, age, class_name, city FROM student_info WHERE id = ?";
Cursor cursor = db.rawQuery(query, new String[] {String.valueOf(stdId)});
Complete Example with UI to Insert, Update, Delete and Select Data with Android and SQLite
Here we will provide a complete example, in which we have the UI for adding student. There will be menu from where we can run Report as well as Add Student. In report, there will two button, one for update and second for delete. Find the project structure in Eclipse.
DatabaseHelper.java
package com.concretepage;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
public class DatabaseHelper extends SQLiteOpenHelper {
private static int DATABASE_VERSION = 1;
private static String DB_FILE_NAME = "concretepage";
public DatabaseHelper(Context context) {
super(context, DB_FILE_NAME, null, DATABASE_VERSION);
}
//Create database
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE student_info ( " +
" id INTEGER PRIMARY KEY AUTOINCREMENT," +
" name VARCHAR2(30), " +
" class_name VARCHAR2(20)," +
" age INT,"+
" city VARCHAR2(30) )";
db.execSQL(sql);
}
//Update database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion == oldVersion + 1) {
// db.execSQL("ALTER TABLE student_info ADD COLUMN country VARCHAR(30)");
}
}
//Insert data into table
public void insertData(Student student){
SQLiteDatabase db = this.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("INSERT INTO student_info (name, age, class_name, city) " +
"VALUES (?,?,?,?)");
stmt.bindString(1, student.getName());
stmt.bindLong(2, student.getAge());
stmt.bindString(3, student.getClassName());
stmt.bindString(4, student.getCity());
stmt.execute();
stmt.close();
db.close();
}
//Update data into table
public void updateData(Student student){
SQLiteDatabase db = this.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("UPDATE student_info SET name=?, age=?, class_name=?, city=? "+
"WHERE id = ?");
stmt.bindString(1, student.getName());
stmt.bindLong(2, student.getAge());
stmt.bindString(3, student.getClassName());
stmt.bindString(4, student.getCity());
stmt.bindLong(5, student.getId());
stmt.execute();
stmt.close();
db.close();
}
//Select all data from the table
public List getStudents() {
List students = new ArrayList();
SQLiteDatabase db = this.getWritableDatabase();
String query = "SELECT id, name, age, class_name, city from student_info ORDER BY id ASC";
Cursor cursor = db.rawQuery(query, null);
while (cursor.moveToNext()) {
Student std = new Student();
std.setId(cursor.getInt(0));
std.setName(cursor.getString(1));
std.setAge(cursor.getInt(2));
std.setClassName(cursor.getString(3));
std.setCity(cursor.getString(4));
students.add(std);
}
db.close();
return students;
}
//Delete data from the table for the given id
public void deleteData(int stdId){
SQLiteDatabase db = this.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("DELETE FROM student_info WHERE id = ?");
stmt.bindLong(1, stdId);
stmt.execute();
stmt.close();
db.close();
}
//Select data for the given id
public Student getStudentById(int stdId) {
SQLiteDatabase db = this.getWritableDatabase();
String query = "SELECT id, name, age, class_name, city FROM student_info WHERE id = ?";
Cursor cursor = db.rawQuery(query, new String[] {String.valueOf(stdId)});
cursor.moveToFirst();
Student std = new Student();
std.setId(cursor.getInt(0));
std.setName(cursor.getString(1));
std.setAge(cursor.getInt(2));
std.setClassName(cursor.getString(3));
std.setCity(cursor.getString(4));
db.close();
return std;
}
}
package com.concretepage;
import java.util.ArrayList;
import java.util.List;
import android.app.AlertDialog;
import android.content.Context;
import android.content.DialogInterface;
import android.os.Bundle;
import android.support.v4.app.FragmentActivity;
import android.support.v4.app.LoaderManager;
import android.support.v4.content.Loader;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.inputmethod.InputMethodManager;
public class MainActivity extends FragmentActivity
implements LoaderManager.LoaderCallbacks<List<Student>> {
DatabaseHelper dbHelper= null;
AlertDialog alertDialog = null;
StudentAdapter stdAdapter;
UIElements ui;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = new DatabaseHelper(this);
ui = new UIElements(this);
ui.hideUpdateSpecificComponents();
}
public void saveStudent(View v) {
switch (v.getId()) {
case R.id.save_btn: {
if(validate()) {
Student student = new Student();
student.setName(ui.editName.getText().toString());
student.setAge(Integer.parseInt(ui.editAge.getText().toString()));
student.setClassName(ui.editClass.getText().toString());
student.setCity(ui.editCity.getText().toString());
dbHelper.insertData(student);
singleButtonAlert(this,"Student data Saved.");
ui.resetInputComponents();
} else {
singleButtonAlert(this, "Fill up all fields.");
}
}
}
}
public void updateStudent(View v) {
switch (v.getId()) {
case R.id.update_btn:
Student student = new Student();
String str[] = ui.stdId.getText().toString().split(":");
student.setId(Integer.parseInt(str[1]));
student.setName(ui.editName.getText().toString());
student.setAge(Integer.parseInt(ui.editAge.getText().toString()));
student.setClassName(ui.editClass.getText().toString());
student.setCity(ui.editCity.getText().toString());
dbHelper.updateData(student);
singleButtonAlert(this, "Data updated successfully");
showListView();
}
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
switch (item.getItemId()) {
case R.id.report_menu:
showListView();
return true;
case R.id.add_menu:
ui.showInputComponents();
ui.hideUpdateSpecificComponents();
ui.resetInputComponents();
ui.stdmsg.setText("Add Student");
ui.hideListViewComponents();
return true;
default:
return super.onOptionsItemSelected(item);
}
}
private void showListView(){
hideSoftKeyboard(getCurrentFocus());
ui.showListViewComponents();
stdAdapter = new StudentAdapter(this, new ArrayList<Student>(), ui);
ui.students.setAdapter(stdAdapter);
Loader<List<Student>> loader = getSupportLoaderManager().initLoader(1, null, this);
loader.forceLoad();
ui.hideInputComponents();
ui.hideUpdateSpecificComponents();
ui.stdmsg.setText("Student Report");
}
@Override
public Loader<List<Student>> onCreateLoader(int id, Bundle args) {
return new StudentLoader(this);
}
@Override
public void onLoadFinished(Loader<List<Student>> loader, List<Student> data) {
stdAdapter.setStudents(data);
}
@Override
public void onLoaderReset(Loader<List<Student>> loader) {
stdAdapter.setStudents(new ArrayList<Student>());
}
private void singleButtonAlert(Context context, String msg){
AlertDialog.Builder builder = new AlertDialog.Builder(context,
AlertDialog.THEME_DEVICE_DEFAULT_LIGHT);
builder.setTitle("Student Profile")
.setIcon(R.drawable.ic_launcher)
.setCancelable(false)
.setMessage(msg)
.setPositiveButton("OK", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int arg1) {
dialog.cancel();
}
});
AlertDialog alertDialog = builder.create();
alertDialog.show();
}
public boolean validate() {
boolean flag = true;
if("".equals(ui.editName.getText().toString().trim())){
return false;
} else if ("".equals(ui.editAge.getText().toString().trim())) {
return false;
} else if ("".equals(ui.editClass.getText().toString().trim())) {
return false;
} else if ("".equals(ui.editCity.getText().toString().trim())) {
return false;
}
return flag;
}
private void hideSoftKeyboard(View v) {
if (v != null) {
InputMethodManager imm = (InputMethodManager)getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(v.getWindowToken(), InputMethodManager.HIDE_NOT_ALWAYS);
}
}
}
package com.concretepage;
public class Student {
private int id;
private String name;
private int age;
private String className;
private String city;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
}
package com.concretepage;
import java.util.List;
import android.app.AlertDialog;
import android.content.Context;
import android.content.DialogInterface;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.TextView;
public class StudentAdapter extends BaseAdapter {
private LayoutInflater inflater;
private List<Student> students;
Context context = null;
UIElements ui;
public StudentAdapter(Context context, List<Student> students, UIElements ui) {
this.students = students;
inflater = LayoutInflater.from(context);
this.context = context;
this.ui = ui;
}
@Override
public View getView(final int position, View view, ViewGroup parent) {
final Student student = (Student) getItem(position);
if (view == null) {
view = inflater.inflate(R.layout.student_report, null);
}
TextView id = (TextView) view.findViewById(R.id.id);
id.setText(String.valueOf(student.getId()));
TextView name = (TextView) view.findViewById(R.id.name);
name.setText(student.getName());
TextView age = (TextView) view.findViewById(R.id.age);
age.setText(String.valueOf(student.getAge()));
TextView className = (TextView) view.findViewById(R.id.classname);
className.setText(student.getClassName());
TextView city = (TextView) view.findViewById(R.id.city);
city.setText(student.getCity());
Button updateButton = (Button)view.findViewById(R.id.update_btn);
updateButton.setOnClickListener(new View.OnClickListener() {
public void onClick(View view) {
ui.showInputComponents();
ui.showUpdateSpecificComponents();
ui.hideListViewComponents();
ui.stdId.setText("Id:" + student.getId());
ui.editName.setText(student.getName());
ui.editAge.setText(String.valueOf(student.getAge()));
ui.editClass.setText(student.getClassName());
ui.editCity.setText(student.getCity());
}
});
Button deleteButton = (Button)view.findViewById(R.id.delete_btn);
deleteButton.setOnClickListener(new View.OnClickListener() {
public void onClick(View view) {
doubleButtonAlert(context, student.getId(), position);
}
});
return view;
}
private void doubleButtonAlert(final Context context, final int stdId, final int position){
AlertDialog.Builder builder = new AlertDialog.Builder(context,
AlertDialog.THEME_DEVICE_DEFAULT_LIGHT);
builder.setTitle("Student Profile")
.setIcon(R.drawable.ic_launcher)
.setCancelable(false)
.setMessage("Are you sure to delete?")
.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int arg1) {
DatabaseHelper dbHelper = new DatabaseHelper(context);
dbHelper.deleteData(stdId);
students.remove(position);
notifyDataSetChanged();
dialog.cancel();
}
})
.setNegativeButton("No", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int arg1) {
dialog.cancel();
}
});
AlertDialog alertDialog = builder.create();
alertDialog.show();
}
@Override
public Object getItem(int position) {
return students.get(position);
}
@Override
public long getItemId(int position) {
return position;
}
@Override
public int getCount() {
return students.size();
}
public void setStudents(List<Student> data) {
students.clear();
students.addAll(data);
notifyDataSetChanged();
}
}
package com.concretepage;
import java.util.List;
import android.content.Context;
import android.support.v4.content.AsyncTaskLoader;
public class StudentLoader extends AsyncTaskLoader<List<Student>> {
DatabaseHelper dbHelper= null;
public StudentLoader(Context context) {
super(context);
dbHelper = new DatabaseHelper(context);
}
@Override
public List<Student> loadInBackground() {
return dbHelper.getStudents();
}
}
package com.concretepage;
import android.app.Activity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
public class UIElements {
public EditText editName, editAge, editClass, editCity;
TextView stdmsg, stdId;
ListView students;
Button saveButton, updateButton;
public UIElements(Activity activity) {
editName = (EditText)activity.findViewById(R.id.name);
editAge = (EditText) activity.findViewById(R.id.age);
editClass = (EditText) activity.findViewById(R.id.classname);
editCity = (EditText) activity.findViewById(R.id.city);
stdmsg = (TextView) activity.findViewById(R.id.stdmsg);
students = (ListView) activity.findViewById(R.id.students);
saveButton = (Button) activity.findViewById(R.id.save_btn);
updateButton = (Button) activity.findViewById(R.id.update_btn);
stdId = (TextView) activity.findViewById(R.id.stdId);
}
public void resetInputComponents() {
editName.setText("");
editName.requestFocus();
editAge.setText("");
editClass.setText("");
editCity.setText("");
}
public void hideInputComponents() {
editName.setVisibility(View.GONE);
editAge.setVisibility(View.GONE);
editClass.setVisibility(View.GONE);
editCity.setVisibility(View.GONE);
saveButton.setVisibility(View.GONE);
}
public void showInputComponents() {
editName.setVisibility(View.VISIBLE);
editName.requestFocus();
editAge.setVisibility(View.VISIBLE);
editClass.setVisibility(View.VISIBLE);
editCity.setVisibility(View.VISIBLE);
saveButton.setVisibility(View.VISIBLE);
}
public void hideUpdateSpecificComponents(){
updateButton.setVisibility(View.GONE);
stdId.setVisibility(View.GONE);
}
public void showUpdateSpecificComponents() {
updateButton.setVisibility(View.VISIBLE);
stdId.setVisibility(View.VISIBLE);
saveButton.setVisibility(View.GONE);
}
public void hideListViewComponents() {
students.setVisibility(View.GONE);
}
public void showListViewComponents() {
students.setVisibility(View.VISIBLE);
students.requestFocus();
}
}
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.concretepage"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk android:minSdkVersion="16"/>
<application
android:allowBackup ="false"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name" >
<activity android:name=".MainActivity" android:windowSoftInputMode="stateHidden">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="#ffffff"
tools:context=".MainActivity">
<TextView
android:id="@+id/stdmsg"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="30sp"
android:text="@string/message"
android:textColor="#000000"/>
<TextView
android:id="@+id/stdId"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="30sp"
android:textColor="#000000"/>
<EditText
android:id="@+id/name"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:hint="@string/name_hint"
android:inputType="text"
android:textColor="#000000"/>
<EditText
android:id="@+id/age"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:hint="@string/age_hint"
android:inputType="number"
android:textColor="#000000"/>
<EditText
android:id="@+id/classname"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:hint="@string/class_hint"
android:inputType="text"
android:textColor="#000000"/>
<EditText
android:id="@+id/city"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:hint="@string/city_hint"
android:inputType="text"
android:textColor="#000000"/>
<Button
android:id="@+id/save_btn"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/save_btn_msg"
android:onClick="saveStudent"
android:background="#000000"
android:textColor="#ffffff"/>
<Button
android:id="@+id/update_btn"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/update_btn_msg"
android:onClick="updateStudent"
android:background="#000000"
android:textColor="#ffffff"/>
<ListView
android:id="@+id/students"
android:layout_height="match_parent"
android:layout_width="match_parent"/>
</LinearLayout>
<?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"
android:background="#CDDC39">
<TextView
android:id="@+id/id"
android:textSize="20sp"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textColor="#FF8A80"
android:layout_weight="1"/>
<TextView
android:id="@+id/name"
android:textSize="20sp"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textColor="#FF8A80"
android:layout_weight="1"/>
<TextView
android:id="@+id/age"
android:textSize="20sp"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textColor="#FF8A80"
android:layout_weight="1"/>
<TextView
android:id="@+id/classname"
android:textSize="20sp"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textColor="#FF8A80"
android:layout_weight="1"/>
<TextView
android:id="@+id/city"
android:textSize="20sp"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textColor="#FF8A80"
android:layout_weight="1"/>
<Button
android:id="@+id/update_btn"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/update_msg"
android:onClick="updateStudent"
android:background="#009688"
android:textColor="#ffffff"/>
<Button
android:id="@+id/delete_btn"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/delete_msg"
android:onClick="deleteStudent"
android:background="#009688"
android:textColor="#ffffff"/>
</LinearLayout>
<?xml version="1.0" encoding="utf-8"?>
<menu xmlns:android="http://schemas.android.com/apk/res/android">
<item android:id="@+id/report_menu"
android:title="@string/report"
android:showAsAction="never"/>
<item android:id="@+id/add_menu"
android:title="@string/student_add"
android:showAsAction="never"/>
</menu>
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="app_name">Concretepage</string>
<string name="message"> Add Student </string>
<string name="name_hint"> Enter name </string>
<string name="age_hint"> Enter Age </string>
<string name="class_hint"> Enter Class </string>
<string name="city_hint"> Enter City </string>
<string name="save_btn_msg"> Save </string>
<string name="update_btn_msg"> Update </string>
<string name="delete_msg"> Delete </string>
<string name="update_msg"> Update </string>
<string name="report"> Report </string>
<string name="student_add"> Add Student </string>
</resources>
Output
