Note: We are currently improving this post.
This is a step by step tutorial where we'll create an Android application that demonstrates a CRUD database application using SQLite. CRUD operations in Android is an essential skill every aspiring Android developer must have.
This is a step by step tutorial where we'll create an Android application that demonstrates a CRUD database application using SQLite. CRUD operations in Android is an essential skill every aspiring Android developer must have.
Android SQLite Database Sample Application
Follow the steps below after you created a new Android project called "AndroidCrudExample"
1. Put a "Create Student" button on your res/layout/activity_main.xml
-Remove the "Hellow World!" TextView there-Change Button Label from "Button" to "Create Student"
-Rename id from "@+id/button1" to "@+id/buttonCreateStudent"
<Button android:id="@+id/buttonCreateStudent" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentLeft="true" android:layout_alignParentTop="true" android:text="Create Student" />
2. On your src/com.example.androidcrudexample/MainActivity.java, set the OnClickListener of the "Create Student" button.
-Set it up for your "buttonCreateStudent"
Button buttonCreateLocation = (Button) findViewById(R.id.buttonCreateStudent); buttonCreateLocation.setOnClickListener(new OnClickListenerCreateStudent());
-Create a listener class "OnClickListenerCreateStudent.java" (all .java files are under your src/com.example.androidcrudexample/)
public class OnClickListenerCreateStudent implements OnClickListener { @Override public void onClick(View view) { } }
3. Prepare your student input form in res/layout/student_input_form.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" > <EditText android:id="@+id/editTextStudentFirstname" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_alignParentLeft="true" android:layout_alignParentTop="true" android:hint="Student Firstname" android:singleLine="true" > <requestFocus /> </EditText> <EditText android:id="@+id/editTextStudentEmail" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_alignParentLeft="true" android:layout_below="@+id/editTextStudentFirstname" android:hint="Student Email" android:singleLine="true" /> </RelativeLayout>
4. Going back to your "OnClickListenerCreateStudent.java"
-Get the application contextfinal Context context = view.getContext();
-Inflate the student_input_form.xml
LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); final View formElementsView = inflater.inflate(R.layout.student_input_form, null, false);
-List down form widgets inside student_input_form.xml as a "final variable" since we'll use it inside an AlertDialog
final EditText editTextStudentFirstname = (EditText) formElementsView.findViewById(R.id.editTextStudentFirstname); final EditText editTextStudentEmail = (EditText) formElementsView.findViewById(R.id.editTextStudentEmail);
-Create an AlertDialog with the inflated student_input_form and an "Add" button
new AlertDialog.Builder(context) .setView(formElementsView) .setTitle("Create Student") .setPositiveButton("Add", new DialogInterface.OnClickListener() { public void onClick(DialogInterface dialog, int id) { dialog.cancel(); } }).show();
-Try to run.
5. Inside the AlertDialog's "Add" onClick() method, we have to save the record.
-Get the user inputted valuesString studentFirstname = editTextStudentFirstname.getText().toString(); String studentEmail = editTextStudentEmail.getText().toString();
-We're actually using an OOP approach here. Create a new "ObjectStudent.java" file with properties "firstname" and "email"
public class ObjectStudent { int id; String firstname; String email; public ObjectStudent(){ } }
-Set the input values as a object, so we can save it to the database
ObjectStudent objectStudent = new ObjectStudent(); objectStudent.studentFirstname = studentFirstname; objectStudent.studentEmail = studentEmail;
6. Now we're saving a record to the SQLite database.
-Create a new "DatabaseHandler.java" filepublic class DatabaseHandler extends SQLiteOpenHelper { private static final int DATABASE_VERSION = 1; protected static final String DATABASE_NAME = "StudentDatabase"; public DatabaseHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { String sql = "CREATE TABLE students " + "( id INTEGER PRIMARY KEY AUTOINCREMENT, " + "firstname TEXT, " + "email TEXT ) "; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String sql = "DROP TABLE IF EXISTS students"; db.execSQL(sql); onCreate(db); } }
-Create new "TableControllerStudent.java" file, extending the DatabaseHandler
public class TableControllerStudent extends DatabaseHandler { public TableControllerStudent(Context context) { super(context); } }
-do the create() method for creating new record
public boolean create(ObjectStudent objectStudent) { ContentValues values = new ContentValues(); values.put("firstname", objectStudent.studentFirstname); values.put("email", objectStudent.studentEmail); SQLiteDatabase db = this.getWritableDatabase(); boolean createSuccessful = db.insert("students", null, values) > 0; db.close(); return createSuccessful; }
-go back inside the AlertDialog of OnClickListenerCreateStudent.java file and call the create() method
boolean createSuccessful = new TableControllerStudent(context).create(objectStudent);
-tell the user wether insert was a success or not
if(createSuccessful){ Toast.makeText(context, "Student information was saved.", Toast.LENGTH_SHORT).show(); }else{ Toast.makeText(context, "Unable to save student information.", Toast.LENGTH_SHORT).show(); }
-Try to run
7. Count records and display it to UI
-On your res/layout/activity_main.xml, place a TextView under your "Create Student" button<TextView android:id="@+id/textViewRecordCount" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@+id/buttonCreateStudent" android:text="0 records found" android:padding="1dp" />
-On your MainActivity.java file, create a countRecords() method
public void countRecords() { }
-On your TableControllerStudent.java, create a count() method
public int count() { SQLiteDatabase db = this.getWritableDatabase(); String sql = "SELECT * FROM students"; int recordCount = db.rawQuery(sql, null).getCount(); db.close(); return recordCount; }
-Go back to your MainActivity.java > countRecords() method and call the count() method you just created
int recordCount = new TableControllerStudent(this).count();
-display the count to the UI
TextView textViewRecordCount = (TextView) findViewById(R.id.textViewRecordCount); textViewRecordCount.setText(recordCount + " records found.");
-On your MainActivity.java > onCreate() method, call the countRecords() method
countRecords();
-On your OnClickListenerCreateStudent.java > inside AlertDialog, also call the countRecords() method
((MainActivity) context).countRecords();
-Try to run.
8. Read the records from the database and display it to UI
-On your activity_main.xml, put a ScrollView with LinearLayout inside, under textViewRecordCount
<ScrollView android:id="@+id/scrollViewRecords" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_below="@+id/textViewRecordCount" > <LinearLayout android:id="@+id/linearLayoutRecords" android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical" > </LinearLayout> </ScrollView>
-On your MainActivity.java file, create a countRecords() method
public void readRecords() { }
-On your TableControllerStudent.java, create a read() method
public List<ObjectStudent> read() { List<ObjectStudent> recordsList = new ArrayList<ObjectStudent>(); String sql = "SELECT * FROM students ORDER BY id DESC"; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(sql, null); if (cursor.moveToFirst()) { do { int id = Integer.parseInt(cursor.getString(cursor.getColumnIndex("id"))); String studentFirstname = cursor.getString(cursor.getColumnIndex("studentFirstname")); String studentEmail = cursor.getString(cursor.getColumnIndex("studentEmail")); ObjectStudent objectStudent = new ObjectStudent(); objectStudent.id = id; objectStudent.studentFirstname = studentFirstname; objectStudent.studentEmail = studentEmail; recordsList.add(objectStudent); } while (cursor.moveToNext()); } cursor.close(); db.close(); return recordsList; }
-On your MainActivity.java, create the readRecords() method to display the records to UI
public void readRecords() { LinearLayout linearLayoutRecords = (LinearLayout) findViewById(R.id.linearLayoutRecords); linearLayoutRecords.removeAllViews(); List<ObjectStudent> students = new TableControllerStudent(this).read(); if (students.size() > 0) { for (ObjectStudent obj : students) { int id = obj.id; String studentFirstname = obj.firstname; String studentEmail = obj.email; String textViewContents = studentFirstname + " - " + studentEmail; TextView textViewLocationItem = new TextView(this); textViewLocationItem.setPadding(0, 10, 0, 10); textViewLocationItem.setText(textViewContents); textViewLocationItem.setTag(Integer.toString(id)); linearLayoutRecords.addView(textViewLocationItem); } } else { TextView locationItem = new TextView(this); locationItem.setPadding(8, 8, 8, 8); locationItem.setText("No records yet."); linearLayoutRecords.addView(locationItem); } }
-Call readRecords() method on your MainActivity.java > onCreate() method
readRecords();
-On your OnClickListenerCreateStudent.java > inside AlertDialog, call the readRecords() method as well, so it will refresh the list everytime we add new record
((MainActivity) context).readRecords();
-Try to run.
9. Now we'll try to update a record
-Create new OnLongClickListenerStudentRecord.java file
public class OnLongClickListenerStudentRecord implements OnLongClickListener { @Override public boolean onLongClick(View view) { return false; } }
-Set the OnLongClickListener for each of the display records, Go to your MainActivity.java > readRecords() method()
textViewLocationItem.setOnLongClickListener(new OnLongClickListenerStudentRecord());
-Go back to your OnLongClickListenerStudentRecord.java file, set the following as class variables
Context context; String id;
...
context = view.getContext(); id = view.getTag().toString();
-add an AlertDialog with simple list view for edit and delete
final CharSequence[] items = { "Edit", "Delete" }; new AlertDialog.Builder(context).setTitle("Student Record") .setItems(items, new DialogInterface.OnClickListener() { public void onClick(DialogInterface dialog, int item) { dialog.dismiss(); } }).show();
-Try to run
-Inside the AlertDialog
if (item == 0) { editRecord(Integer.parseInt(id)); }
-On your OnLongClickListenerStudentRecord.java, add the following editRecord() method
public void editRecord(final int studentId) { }
-To read single record that will fill up the student form for updating it
final TableControllerStudent tableControllerStudent = new TableControllerStudent(context); ObjectStudent objectStudent = tableControllerStudent.readSingleRecord(studentId);
-On your TableControllerStudent.java, add the method readSingleRecord()
public ObjectStudent readSingleRecord(int studentId) { ObjectStudent objectStudent = null; String sql = "SELECT * FROM students WHERE id = " + studentId; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(sql, null); if (cursor.moveToFirst()) { int id = Integer.parseInt(cursor.getString(cursor.getColumnIndex("id"))); String firstname = cursor.getString(cursor.getColumnIndex("firstname")); String email = cursor.getString(cursor.getColumnIndex("email")); objectStudent = new ObjectStudent(); objectStudent.id = id; objectStudent.firstname = firstname; objectStudent.email = email; } cursor.close(); db.close(); return objectStudent; }
-Inflate student_input_form.xml
LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); final View formElementsView = inflater.inflate(R.layout.student_input_form, null, false);
-List down form elements
final EditText editTextStudentFirstname = (EditText) formElementsView.findViewById(R.id.editTextStudentFirstname); final EditText editTextStudentEmail = (EditText) formElementsView.findViewById(R.id.editTextStudentEmail);
-Set single record values
editTextStudentFirstname.setText(objectStudent.firstname); editTextStudentEmail.setText(objectStudent.email);
-Show an AlertDialog with the form and single record filling it up
new AlertDialog.Builder(context) .setView(formElementsView) .setTitle("Edit Record") .setPositiveButton("Save Changes", new DialogInterface.OnClickListener() { public void onClick(DialogInterface dialog, int id) { dialog.cancel(); } }).show();
-inside the AlertDialog, create the object with the updated value
ObjectStudent objectStudent = new ObjectStudent(); objectStudent.id = studentId; objectStudent.firstname = editTextStudentFirstname.getText().toString(); objectStudent.email = editTextStudentEmail.getText().toString();
-Update the record and tell the user whether it was updated or not
boolean updateSuccessful = tableControllerStudent.update(objectStudent); if(updateSuccessful){ Toast.makeText(context, "Student record was updated.", Toast.LENGTH_SHORT).show(); }else{ Toast.makeText(context, "Unable to update student record.", Toast.LENGTH_SHORT).show(); }
-On your TableControllerStudent.java, add the update() method
public boolean update(ObjectStudent objectStudent) { ContentValues values = new ContentValues(); values.put("firstname", objectStudent.firstname); values.put("email", objectStudent.email); String where = "id = ?"; String[] whereArgs = { Integer.toString(objectStudent.id) }; SQLiteDatabase db = this.getWritableDatabase(); boolean updateSuccessful = db.update("students", values, where, whereArgs) > 0; db.close(); return updateSuccessful; }
-Refresh the count and record list
((MainActivity) context).countRecords(); ((MainActivity) context).readRecords();
10. Now we'll try to delete a record
-On your OnLongClickListenerStudentRecord.java > Inside the AlertDialog
else if (item == 1) { boolean deleteSuccessful = new TableControllerStudent(context).delete(id); if (deleteSuccessful){ Toast.makeText(context, "Student record was deleted.", Toast.LENGTH_SHORT).show(); }else{ Toast.makeText(context, "Unable to delete student record.", Toast.LENGTH_SHORT).show(); } ((MainActivity) context).countRecords(); ((MainActivity) context).readRecords(); }
Output Screenshots:
Please note that the following screenshots is not from the source code above, but it looks pretty much like this. On the code above, we used the student "firstname" and "email" fields.
When the "Add" button on the pop up was touched. |
When the added location record on the list was touched. |
When the user chose to edit. |
After touching the "Save Changes" button. It will show the record was updated. |
When the user chose to delete. |
A Quick Android SQLite Database Example
Here's another version of my Android Sqlite tutorial. This version is very simple, contains only two JAVA files, focuses on Android CRUD and hopefully easier to understand. Some people want it this way.When you run this code. |
MainActivity.java - This is where our CRUD commands will be executed. The location object can also be found here.
package com.example.sqliteexample2; import java.util.List; import android.os.Bundle; import android.util.Log; import android.app.Activity; public class MainActivity extends Activity { // for our logs public static final String LOG_TAG = "MainActivity.java"; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DatabaseHandler databaseH = new DatabaseHandler(MainActivity.this); // CREATE LocationObject LocationObj = new LocationObject("Quezon City", "The place where I work."); if (databaseH.create(LocationObj)) { Log.v(LOG_TAG, "Record successfully created."); } // READ List<LocationObject> locations = databaseH.read(); for (LocationObject record : locations) { Log.v(LOG_TAG, "ID: " + record.locationId); Log.v(LOG_TAG, "Name: " + record.locationName); Log.v(LOG_TAG, "Description: " + record.locationDescription); } // UPDATE if (databaseH.update(1, "Quezon City, PH", "The place where I work AND CODE.")) { Log.v(LOG_TAG, "Record successfully updated."); } // DELETE if (databaseH.delete(1)) { Log.v(LOG_TAG, "Record successfully deleted."); } } } /* * Represents location details or fields. */ class LocationObject { public int locationId; public String locationName; public String locationDescription; // constructor for adding public LocationObject(String locationName, String locationDescription) { this.locationName = locationName; this.locationDescription = locationDescription; } // constructor for updating public LocationObject(int locationId, String locationName, String locationDescription) { this.locationId = locationId; this.locationName = locationName; this.locationDescription = locationDescription; } }
DatabaseHandler.java - This handles all database operations or methods like creation, dropping, upgrading database version and ofcource, CRUD.
package com.example.sqliteexample2; import java.util.ArrayList; import java.util.List; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DatabaseHandler extends SQLiteOpenHelper { // for our logs public static final String LOG_TAG = "DatabaseHandler.java"; // database version private static final int DATABASE_VERSION = 2; // database name protected static final String DATABASE_NAME = "NinjaDatabase2"; // table details public String tableName = "locations"; public String fieldLocationId = "id"; public String fieldLocationName = "name"; public String fieldLocationDescription = "description"; // constructor public DatabaseHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } // creating Tables @Override public void onCreate(SQLiteDatabase db) { String sql = ""; sql += "CREATE TABLE " + tableName; sql += " ( "; sql += fieldLocationId + " INTEGER PRIMARY KEY AUTOINCREMENT, "; sql += fieldLocationName + " TEXT, "; sql += fieldLocationDescription + " TEXT "; sql += " ) "; db.execSQL(sql); } /* * When upgrading the database, it will drop the current table and recreate. */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String sql = "DROP TABLE IF EXISTS " + tableName; db.execSQL(sql); onCreate(db); } /* * Create location record. * * @param - location contains location details to be added as single row. */ public boolean create(LocationObject location) { boolean createSuccessful = false; ContentValues values = new ContentValues(); values.put(fieldLocationName, location.locationName); values.put(fieldLocationDescription, location.locationDescription); SQLiteDatabase db = this.getWritableDatabase(); createSuccessful = db.insert(tableName, null, values) > 0; db.close(); return createSuccessful; } /* * Read all location record. */ public List<LocationObject> read() { List<LocationObject> recordsList = new ArrayList<LocationObject>(); // select query String sql = ""; sql += "SELECT * FROM " + tableName; sql += " ORDER BY " + fieldLocationId + " DESC"; SQLiteDatabase db = this.getWritableDatabase(); // execute the query Cursor cursor = db.rawQuery(sql, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { int locationId = Integer.parseInt(cursor.getString(cursor .getColumnIndex(fieldLocationId))); String locationName = cursor.getString(cursor .getColumnIndex(fieldLocationName)); String locationDescription = cursor.getString(cursor .getColumnIndex(fieldLocationDescription)); LocationObject location = new LocationObject(locationId, locationName, locationDescription); // add to list recordsList.add(location); } while (cursor.moveToNext()); } // close the database db.close(); // return the list of records return recordsList; } /* * Update location record. * * @param id - will identify which record is to be updated. * * @param name - the new location name to be saved. * * @param description - the new location description to be saved. */ public boolean update(int id, String name, String description) { boolean updateSuccessful = false; ContentValues values = new ContentValues(); values.put(fieldLocationName, name); values.put(fieldLocationDescription, description); // you can use AND if you have multiple conditions String where = fieldLocationId + " = ?"; // you should use commas when you have multiple conditions String[] whereArgs = { Integer.toString(id) }; SQLiteDatabase db = this.getWritableDatabase(); // use the update command updateSuccessful = db.update(tableName, values, where, whereArgs) > 0; db.close(); return updateSuccessful; } /* * Delete location record. * * @param id - to identify which location record is to be deleted. */ public boolean delete(int id) { boolean deleteSuccessful = false; SQLiteDatabase db = this.getWritableDatabase(); deleteSuccessful = db.delete(tableName, "id ='" + id + "'", null) > 0; db.close(); return deleteSuccessful; } /* * For more methods like reading single record or counting records, see my * full version of Android Sqlite tutorial, specifically, * LocationTableController.java */ }
You can see the output of this code on your logcat. You can find the logcat in Window > Show View > Other > Search for "LogCat" Output should look like this:
Our logcat output. |
Let me know if you have any questions. Hope you'll also check my previous Android Sqlite tutorial after you learn the basics from here! :)
The Code of a Ninja Resources
For FREE programming tutorials, click the red button below and subscribe! :)
website