Android SQLite Database Tutorial
August 11, 2015
Here we will walk through Android SQLite Database tutorial with onCreate(), onUpgrade(), getWritableDatabase() of SQLiteOpenHelper and execSQL() , compileStatement(), rawQuery() of SQLiteDatabase Example. To work with SQLite in Android, it provides two main classes SQLiteOpenHelper and SQLiteDatabase. SQLiteOpenHelper manages database creation, up gradation and provides the methods like onCreate(), onUpgrade(), getWritableDatabase(). SQLiteDatabase facilitates to interact with database like insert, update, select, delete and provides methods like execSQL(), compileStatement(), rawQuery() etc. On this page, we will provide a complete example with UI to work with Android and SQLite.
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 ListgetStudents() { 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



