Introduction
Every app involves data. Most data are supplied by users through the various input controls, such as text field, check box, radio group, spinner, and button. While some data is transient, most will require to stay or persist even after the app has stopped running. Android provides many ingenious technologies for storing persistent data locally. In this article, you will learn to perform CRUD, i.e., Create, Read, Update and Delete, on data using a SQLite Database on Android.
Setting the Stage
On your favorite Android IDE, start a new Android app project. Let’s give it an application name of AndroidSQLite
and a domain name of peterleowblog.com
. The resulting package name of your project will be com.peterleowblog.androidsqlite
.
In the project, create an Android activity called MainActivity
. As shown in Figure 1, the user interface (UI) of this MainActivity
comprises the following controls:
- One
EditText
text field for email input. - One
RadioGroup
control with two RadioButtons
for gender selection. - Three
CheckBox
controls for hobbies selection. - One
Spinner
control for zodiac selection. - Three
Button
controls – one each for saving, retrieving, and updating of data respectively.
A user can key in and save new information to a SQLite database. Subsequently, the user can retrieve the information using an email as the keyword, edit and update the information into, or delete it from the SQLite database.
Figure 1: MainActivity
Getting the Resources Ready
You will need the following string
resources in the strings.xml:
="1.0"="utf-8"
<resources>
<string name="app_name">SQLite on Android</string>
<string name="email">Email</string>
<string name="gender">Gender</string>
<string name="female">Female</string>
<string name="male">Male</string>
<string name="hobbies">Hobbies</string>
<string name="coding">Coding</string>
<string name="writing">Writing</string>
<string name="jogging">Jogging</string>
<string name="zodiac">Zodiac</string>
<string name="save">Save</string>
<string name="retrieve">Retrieve</string>
<string name="delete">Delete</string>
<string-array name="zodiac">
<item>Aries</item>
<item>Taurus</item>
<item>Gemini</item>
<item>Cancer</item>
<item>Leo</item>
<item>Virgo</item>
<item>Libra</item>
<item>Scorpio</item>
<item>Sagittarius</item>
<item>Capricorn</item>
<item>Aquarius</item>
<item>Pisces</item>
</string-array>
</resources>
Rendering the View
The XML code that is responsible for rendering the view of Figure 1 is contained in activity_main.xml and shown below:
="1.0"="utf-8"
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<RelativeLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:paddingLeft="16dp"
android:paddingRight="16dp"
android:paddingTop="16dp"
android:paddingBottom="16dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceLarge"
android:text=""
android:id="@+id/textView"
android:layout_alignParentTop="true"
android:layout_centerHorizontal="true" />
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:inputType="textEmailAddress"
android:ems="10"
android:id="@+id/txtEmail"
android:layout_below="@+id/textView4"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="@string/email"
android:id="@+id/textView4"
android:layout_below="@+id/textView"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="@string/gender"
android:id="@+id/textView5"
android:layout_below="@+id/txtEmail"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<RadioGroup
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_below="@+id/textView5"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:id="@+id/radioGroupGender">
<RadioButton
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/male"
android:id="@+id/radMale"
android:checked="false" />
<RadioButton
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/female"
android:id="@+id/radFemale"
android:checked="false" />
</RadioGroup>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="@string/hobbies"
android:id="@+id/textView6"
android:layout_below="@+id/radioGroupGender"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<CheckBox
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/coding"
android:id="@+id/chkCoding"
android:onClick="onCheckboxClicked"
android:checked="false"
android:layout_below="@+id/textView6"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<CheckBox
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/writing"
android:id="@+id/chkWriting"
android:onClick="onCheckboxClicked"
android:layout_below="@+id/chkCoding"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:checked="false" />
<CheckBox
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/jogging"
android:id="@+id/chkJogging"
android:onClick="onCheckboxClicked"
android:layout_below="@+id/chkWriting"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:checked="false" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="@string/zodiac"
android:id="@+id/textView7"
android:layout_below="@+id/chkJogging"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Spinner
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/spinnerZodiac"
android:layout_below="@+id/textView7"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/save"
android:id="@+id/btnSave"
android:onClick="save"
android:layout_below="@+id/spinnerZodiac"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/retrieve"
android:onClick="retrieve"
android:id="@+id/btnRetrieve"
android:layout_alignParentBottom="true"
android:layout_centerHorizontal="true"
android:layout_centerInParent="false"
android:layout_centerVertical="false" />
<Button
android:text="@string/delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_below="@+id/spinnerZodiac"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true"
android:id="@+id/btnDelete" />
</RelativeLayout>
</ScrollView>
Controlling the Activity
Last but not least, the code for handling the UI controls and their behaviors is contained in MainActivity.java and shown below:
package com.peterleowblog.androidsqlite;
import android.app.Activity;
import android.content.res.Resources;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.CheckBox;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Spinner;
import android.widget.TextView;
public class MainActivity extends Activity implements RadioGroup.OnCheckedChangeListener,
AdapterView.OnItemSelectedListener{
private String email, gender, hobbies, zodiac;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
((TextView) findViewById(R.id.textView)).setText
(getResources().getString(R.string.app_name));
email = gender = hobbies = zodiac = "";
RadioGroup radioGroupGender = (RadioGroup) findViewById(R.id.radioGroupGender);
radioGroupGender.setOnCheckedChangeListener(this);
Spinner spinnerZodiac = (Spinner) findViewById(R.id.spinnerZodiac);
ArrayAdapter<CharSequence> adapter =
ArrayAdapter.createFromResource(this, R.array.zodiac,
android.R.layout.simple_spinner_item);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinnerZodiac.setAdapter(adapter);
}
@Override
public void onCheckedChanged(RadioGroup radioGroup, int i) {
int radioButtonId = radioGroup.getCheckedRadioButtonId();
RadioButton radioButton = (RadioButton)radioGroup.findViewById(radioButtonId);
gender = radioButton.getText().toString();
}
public void onCheckboxClicked(View view) {
CheckBox chkJogging = (CheckBox) findViewById(R.id.chkJogging);
CheckBox chkCoding = (CheckBox) findViewById(R.id.chkCoding);
CheckBox chkWriting = (CheckBox) findViewById(R.id.chkWriting);
StringBuilder sb = new StringBuilder();
if (chkJogging.isChecked()) {
sb.append(", " + chkJogging.getText());
}
if (chkCoding.isChecked()) {
sb.append(", " + chkCoding.getText());
}
if (chkWriting.isChecked()) {
sb.append(", " + chkWriting.getText());
}
if (sb.length() > 0) {
hobbies = sb.deleteCharAt(sb.indexOf(",")).toString();
} else {
hobbies = "";
}
}
public void onItemSelected(AdapterView<?> parent,
View view, int position, long id) {
zodiac = parent.getItemAtPosition(position).toString();
}
public void onNothingSelected(AdapterView<?> parent) {
}
public void save(View view) {
}
public void retrieve(View view) {
setupUI();
}
public void delete(View view) {
setupUI();
}
protected void setupUI(){
((EditText)findViewById(R.id.txtEmail)).setText(email);
RadioButton radMale = (RadioButton)findViewById(R.id.radMale);
RadioButton radFemale = (RadioButton)findViewById(R.id.radFemale);
if (gender.equals("Male")){
radMale.setChecked(true);
} else if (gender.equals("Female")){
radFemale.setChecked(true);
} else {
radMale.setChecked(false);
radFemale.setChecked(false);
}
CheckBox chkCoding = (CheckBox)findViewById(R.id.chkCoding);
CheckBox chkWriting = (CheckBox)findViewById(R.id.chkWriting);
CheckBox chkJogging = (CheckBox)findViewById(R.id.chkJogging);
chkCoding.setChecked(false);
chkWriting.setChecked(false);
chkJogging.setChecked(false);
if (hobbies.contains("Coding")) {
chkCoding.setChecked(true);
}
if (hobbies.contains("Writing")) {
chkWriting.setChecked(true);
}
if (hobbies.contains("Jogging")) {
chkJogging.setChecked(true);
}
Resources resource = getResources();
String[] zodiacArray = resource.getStringArray(R.array.zodiac);
for(int i = 0; i < zodiacArray.length; i++){
if(zodiacArray[i].equals(zodiac)){
((Spinner)findViewById(R.id.spinnerZodiac)).setSelection(i);
}
}
}
}
You are now really to delve into the SQLite database implementation. Let’s go…
Learning the Basics
SQLite is an open source transactional SQL database engine that is self-contained, serverless, and requires no configuration. Android provides the android.database.sqlite package that contains the classes that an app can use to create and manage its own private database.
To implement a SQLite database, the recommended way is to create a subclass of SQLiteOpenHelper and override its onCreate() and onUpgrade() methods. When this subclass is instantiated, it will open the database if it exists, create it if it does not, or upgrade it when necessary. Transactions are used to make sure that the database is always kept in a consistent state. You add the methods to manipulate the database in the subclass. For example, the code below shows a subclass of SQLiteOpenHelper called SqliteHelper
:
public class SqliteHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "MyDatabase";
public static final int DATABASE_VERSION = 1;
public SqliteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE users
(email TEXT PRIMARY KEY, gender Text, hobbies Text, zodiac Text)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i2) {
db.execSQL("DROP TABLE IF EXISTS users");
onCreate(db);
}
}
Let’s move on to explore the implementation of SQLite’s CRUD on Android.
Write
Writing to a SQLite database involves either an insert
or update
operation. Follow these steps:
- Call the getWritableDatabase() method of the
SQLiteOpenHelper
to get a SQLiteDatabase object that represents the database and provides methods for read and write operations. For example:
SQLiteDatabase db = this.getWritableDatabase();
- Put the values to write in a ContentValues object. For example:
ContentValues contentValues = new ContentValues();
contentValues.put("gender", gender);
contentValues.put("hobbies", hobbies);
- To insert the values as a new record, call the insert() method of the SQLiteDatabase object and passing it the
ContentValues
object as the third parameter. The first parameter is the table name. This method will return the row ID of the newly inserted row, or -1
if an error occurred. For example:
result = db.insert("users", null, contentValues);
- To update an existing record with the new value, call the update() method of the SQLiteDatabase object and passing it the
ContentValues
object as the second parameter. The third parameter is the WHERE
clause, and the last parameter the argument to pass to the ?
placeholder in the WHERE
clause. This method will return the number of rows affected. For example:
result = db.update("users", contentValues, "email=?", new String[] { email });
Read
To perform read operation on the SQLite database, follow these steps:
- Call the getReadableDatabase() method of the
SQLiteOpenHelper
to get a SQLiteDatabase object that represents the database and provides methods for read operations. For example:
SQLiteDatabase db = this.getReadableDatabase();
- Call the rawQuery() method of the SQLiteDatabase object and passing it the SQL query statement as the first parameter. The second parameter is the argument to pass to the
?
placeholder in the SQL query statement. This method will return a Cursor object that represents the result set returned by the database query. For example:
String sql = "SELECT * FROM users WHERE email=?";
Cursor cursor = db.rawQuery(sql, new String[] { email });
Delete
Lastly, to delete data from the SQLite database, follow these steps:
- Call the getWritableDatabase() method of the
SQLiteOpenHelper
to get a SQLiteDatabase object that represents the database. For example:
SQLiteDatabase db = this.getWritableDatabase();
- Call the delete() method of the SQLiteDatabase object. The second parameter of this method is the
WHERE
clause and the last parameter the argument to replace the ?
placeholder in the WHERE
clause. This method will return the number of rows affected. For example:
db.delete("users", "email=?", new String[] { email });
Making It Happen
It is time to make your project SQLite-capable.
In your project, add a new Java class file called SqliteHelper
that extends the SQLiteOpenHelper
class in your project. When instantiated, it will create a database called MyDatabase
that contains one table called users
that has four fields—email
(primary key), gender
, hobbies
, and zodiac
.
Three methods are included to take care of the SQLite’s CRUD operations. The saveUser()
method performs either an insertion operation if the record is new (no matching email is found), or an update on an existing record that has a matching email
value. The getUser()
method takes one email
value as parameter and retrieves a record that matches this email value from the users
table. Finally, the deleteUser()
method takes one email value as parameter and deletes a record that matches this email value from the users
table. The complete code for the SqliteHelper.java is shown below:
package com.peterleowblog.androidsqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class SqliteHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "MyDatabase";
private static final int DATABASE_VERSION = 1;
public SqliteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE users
(email TEXT PRIMARY KEY, gender Text, hobbies Text, zodiac Text)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i2) {
db.execSQL("DROP TABLE IF EXISTS users");
onCreate(db);
}
public boolean saveUser (String email, String gender, String hobbies, String zodiac)
{
Cursor cursor = getUser(email);
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("gender", gender);
contentValues.put("hobbies", hobbies);
contentValues.put("zodiac", zodiac);
long result;
if (cursor.getCount() == 0) {
contentValues.put("email", email);
result = db.insert("users", null, contentValues);
} else {
result = db.update
("users", contentValues, "email=?", new String[] { email });
}
if (result == -1) {
return false;
} else {
return true;
}
}
public Cursor getUser(String email){
SQLiteDatabase db = this.getReadableDatabase();
String sql = "SELECT * FROM users WHERE email=?";
return db.rawQuery(sql, new String[] { email });
}
public void deleteUser(String email){
SQLiteDatabase db = this.getWritableDatabase();
db.delete("users", "email=?", new String[] { email });
}
}
Once the SqliteHelper.java is ready, turn to MainActivity.java, follow these steps to implement the SQLite Database
option:
- Declare and instantiate a
SqliteHelper
class variable as shown:
public class MainActivity extends Activity
implements RadioGroup.OnCheckedChangeListener,
AdapterView.OnItemSelectedListener{
private String email, gender, hobbies, zodiac;
SqliteHelper sqliteHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
sqliteHelper = new SqliteHelper(this);
- Add the code for writing to the database to the
save()
method as shown:
public void save(View view) {
email = ((EditText)findViewById(R.id.txtEmail)).getText().toString();
if (email.isEmpty()){
Toast.makeText(getApplicationContext(),
"Email cannot be empty!", Toast.LENGTH_LONG).show();
return;
}
boolean result = sqliteHelper.saveUser(email, gender, hobbies, zodiac);
if (result){
Toast.makeText(getApplicationContext(),
"Successfully saved!", Toast.LENGTH_LONG).show();
} else {
Toast.makeText(getApplicationContext(),
"Failed to save!", Toast.LENGTH_LONG).show();
}
}
- Add the code for reading from the database to the
retrieve()
method as shown:
public void retrieve(View view) {
email = ((EditText)findViewById(R.id.txtEmail)).getText().toString();
Cursor cursor = sqliteHelper.getUser(email);
if (cursor.getCount() != 0) {
cursor.moveToFirst();
email = cursor.getString(cursor.getColumnIndex("email"));
gender = cursor.getString(cursor.getColumnIndex("gender"));
hobbies = cursor.getString(cursor.getColumnIndex("hobbies"));
zodiac = cursor.getString(cursor.getColumnIndex("zodiac"));
if (!cursor.isClosed()) {
cursor.close();
}
} else {
email = "";
gender = "";
hobbies = "";
zodiac = "";
}
setupUI();
}
- Add the code for deleting from the database to the
delete()
method as shown:
public void delete(View view) {
email = ((EditText)findViewById(R.id.txtEmail)).getText().toString();
sqliteHelper.deleteUser(email);
email = gender = hobbies = zodiac = "";
setupUI();
}
Testing 1, 2, 3, …
Launch your app on a real device or an AVD, you should see the view appear as shown in Figure 1. Enter an email, make some selections, and then click the Save
button to save the input values to the database table called users
. Each saving with new email value will result in a new record being inserted into the users
table. On the other hand, each saving with email value that already exists in the database will result in that existing record being updated in the users
table. To retrieve a record, enter an email of that record in the Email
text field and press the Retrieve button. To delete a record, simply enter an email of that record in the Email
text field and press the Delete button.
Last but Not Least
When the SQLite database is closed, calling the getWritableDatabase()
and getReadableDatabase()
methods inevitably consume expensive resources and time. As such, you should defer calling them until they are really needed. Once opened successfully, however, the database is cached, and you can call these method whenever you need to write to the database without any more penalty. You should take advantage of the database cache by leaving the database connection open for as long as it is needed. One sure way to close the database is at the end of the life cycle of the calling activity, that is:
@Override
protected void onDestroy() {
sqliteHelper.close();
super.onDestroy();
}
What are you waiting for? Add this code to your MainActivity.java!
History
- 1st January, 2017: Initial version