
There are many tutorials that you would find Online for "SQLite with Android Programming" Most of them are really confusing, too lengthy or very descriptive yet do not cover the basic insert, update, delete and select operations at once.
I will try to keep this simple (hope you have a good idea of SQL) and cover all basic operations needed to work with your Android App.
We will create an Android Application that pretty simple, NO! ListView to make it more confusion for beginners.
We will only have a single EditText field, and three buttons Insert, Update and Delete and a TextView to display the results.
So our SQLite database that has a single table called "records_db" and has just two filed
- id (auto incremented primary key)
- record
- Create the database if not present named "record_db".
- Create the table "records_master".
- Perform Insert operations.
- Perform Update operations.
- Perform Delete operations.
- Perform Select operations.
- User can enter a String Value and click Insert, this record will get inserted into the SQLite database.
- User can enter ID,TEXT (e.g. 1,Sam) and click Update button to update the value of any record for a given ID.
- User can enter ID and click Delete button to delete the record.
Note: After every operation, we will call the Select Query and re-print the DB records in the TextView.
So let's get started, we create our simple android Activity first.
<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"
android:background="#eee"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
android:paddingBottom="@dimen/activity_vertical_margin"
tools:context=".MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/title"
android:id="@+id/textView"
android:textColor="#222"
android:textSize="22sp"
android:layout_alignParentTop="true"
android:layout_centerHorizontal="true" />
<EditText
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:id="@+id/inputField"
android:hint="@string/hintText"
android:textColor="#272"
android:layout_marginTop="40dp"
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/textAppearanceSmall"
android:text="@string/resultHeader"
android:id="@+id/resultHeader"
android:layout_marginTop="15dp"
android:layout_below="@+id/insert"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Button
style="?android:attr/buttonStyleSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="5dp"
android:text="@string/insert"
android:id="@+id/insert"
android:onClick="insertRecord"
android:layout_below="@+id/inputField"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Button
style="?android:attr/buttonStyleSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="5dp"
android:text="@string/update"
android:onClick="updateRecord"
android:id="@+id/update"
android:layout_below="@+id/inputField"
android:layout_centerHorizontal="true" />
<Button
style="?android:attr/buttonStyleSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/delete"
android:id="@+id/delete"
android:onClick="deleteRecord"
android:layout_marginTop="5dp"
android:layout_below="@+id/inputField"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true" />
<ScrollView
android:layout_width="match_parent"
android:layout_height="200dp"
android:background="#ddd"
android:layout_alignParentBottom="true"
android:layout_below="@+id/resultHeader"
android:fillViewport="false"
android:id="@+id/scroll">
<TextView
android:layout_width="match_parent"
android:layout_height="200dp"
android:padding="10dp"
android:textColor="#000"
android:text="@string/result"
android:id="@+id/result"
android:layout_below="@+id/resultHeader"
android:layout_alignParentLeft="true"
android:scrollbarStyle="insideInset"
android:scrollbars="vertical"
android:overScrollMode="always"
android:layout_alignParentStart="true"
android:layout_alignParentBottom="true" />
</ScrollView>
</RelativeLayout>
String file : strings.xml
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="app_name">Android SQLite Example</string>
<string name="hello_world">Hello world!</string>
<string name="action_settings">Settings</string>
<string name="title">Android + SQLite Tutorial</string>
<string name="hintText">Enter Record</string>
<string name="resultHeader">Result :</string>
<string name="insert">Insert</string>
<string name="update">Update</string>
<string name="delete">Delete</string>
<string name="result">No records added!</string>
</resources>
SQLiteDbHelper.java
package db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by code2care on 02/02/15.
*
*
* Create static variables
*
* 1. Database name
* 2. Table name
* 3. Field 1 : Id
* 4. Filed 2 : record
*
* Create a table with ID as primary key and autoincrement
*
* We need to Override onCreate and upgrade methods
*
*/
public class SQLiteDbHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "records_db";
public static final String TABLE_NAME = "record_master";
public static final String FIELD_ID = "id";
public static final String FIELD_RECORD = "record";
public SQLiteDbHelper(Context context) {
super(context, DB_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_TABLE = "Create table " + TABLE_NAME + " (" + FIELD_ID + " INTEGER Primary Key AUTOINCREMENT, " + FIELD_RECORD + " TEXT)";
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// db.execSQL("Drop table if exists " + TABLE_NAME);
onCreate(db);
}
}
DbOperations.java
package db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.widget.Toast;
/**
* Created by code2care on 02/02/15.
*
*
* This class contains the following functions
*
*
* 1. insertQuery(String record, Context context)
*
* We call this function when the user clicks on the Insert button
*
* 2. insertQuery(String record, Context context)
*
* We call this function when the user clicks on the Insert button
*
* 3. deleteQuery(String id, Context context)
*
* This function is called when the delete button is clicked.
* We delete the record from the database for the ID inputted by
* the user in the EditText
*
*
* 4. SelectQuery(Context context)
*
* This method is called every time when an insert, update or
* delete operation is carried out.
*
* It returns a String value, that has the latest updated records
* displayed to the user as a TextView
*/
public class DbOperations {
private SQLiteDbHelper dBHelper;
private SQLiteDatabase dataBase;
ContentValues values = new ContentValues();
//Insert record to SQLite db table
public void insertQuery(String record, Context context) {
try {
dBHelper = new SQLiteDbHelper(context);
dataBase = dBHelper.getWritableDatabase();
values.put(SQLiteDbHelper.FIELD_RECORD, record);
//insert
dataBase.insert(SQLiteDbHelper.TABLE_NAME, null, values);
} catch (SQLiteException e) {
Toast.makeText(context, "Error occurred while inserting record to the table!!", Toast.LENGTH_SHORT).show();
} catch (Exception e)
{
Toast.makeText(context, "Error occurred while inserting record to the table!!", Toast.LENGTH_SHORT).show();
} finally {
System.out.println("Helre ");
dataBase.close();
}
}
//Update record to SQLite db table
public void updateQuery(String id, String record, Context context) {
try {
dataBase = dBHelper.getWritableDatabase();
values.put(SQLiteDbHelper.FIELD_RECORD, record);
dBHelper = new SQLiteDbHelper(context);
//update db table for a given ID
dataBase.update(SQLiteDbHelper.TABLE_NAME, values, SQLiteDbHelper.FIELD_ID + "=" + id, null);
} catch (SQLiteException e) {
Toast.makeText(context, "Error occurred while updating the table!!", Toast.LENGTH_SHORT).show();
} catch (Exception e)
{
Toast.makeText(context, "Error occurred while updating the table!!", Toast.LENGTH_SHORT).show();
} finally {
dataBase.close();
}
}
//Delete record to SQLite db table
public void deleteQuery(String id, Context context) {
try {
dBHelper = new SQLiteDbHelper(context);
dataBase = dBHelper.getWritableDatabase();
//Delete the record where id = entered number
dataBase.delete(SQLiteDbHelper.TABLE_NAME, SQLiteDbHelper.FIELD_ID + "=" + id, null);
} catch (SQLiteException e) {
Toast.makeText(context, "Error occurred while deleting record for the table!!", Toast.LENGTH_SHORT).show();
} catch (Exception e) {
Toast.makeText(context, "Error occurred while deleting record for the table!!", Toast.LENGTH_SHORT).show();
} finally {
dataBase.close();
}
}
//Select record to SQLite db table
public String SelectQuery(Context context) {
String result = "";
try {
dBHelper = new SQLiteDbHelper(context);
dataBase = dBHelper.getWritableDatabase();
Cursor mCursor = dataBase.rawQuery("SELECT * FROM " + SQLiteDbHelper.TABLE_NAME, null);
result = "ID"+ "\t RECORD" +"\n========================= \n";
//Loop the records and add it to a string
if (mCursor.moveToFirst()) {
do {
result = result + mCursor.getString(mCursor.getColumnIndex(SQLiteDbHelper.FIELD_ID)) + "\t";
result = result + mCursor.getString(mCursor.getColumnIndex(SQLiteDbHelper.FIELD_RECORD)) + "\n";
} while (mCursor.moveToNext());
}
mCursor.close();
} catch (SQLiteException e) {
Toast.makeText(context, "Error occurred while Selecting records for the table!!", Toast.LENGTH_SHORT).show();
} catch (Exception e) {
Toast.makeText(context, "Error occurred while Selecting records for the table!!", Toast.LENGTH_SHORT).show();
} finally {
dataBase.close();
}
return result;
}
}
And Finally the Manifest file: We do not need any special user permission for this Application.
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.code2care.tools.androidsqliteexample" >
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/Theme.AppCompat.NoActionBar" >
<activity
android:name=".MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
- Change Android Toast background color
- Maven : java.lang.ClassNotFoundException: Xmx512m
- This class should be public (android.support.v7.internal.widget.ActionBarView.HomeView) Lint Error
- Android Alert Dialog with Checkboxes example
- Android Error Generating Final Archive - Debug Certificate Expired
- How to add Newline to text in Android TextView
- Read Text file from SD Card : Android Programming
- [FIX] AndroidRuntime: FATAL EXCEPTION: main - java.lang.RuntimeException NullPointerException
- ActivityManager Warning: Activity not started, its current task has been brought to the front
- INSTALL_FAILED_INSUFFICIENT_STORAGE Android Error
- Android Developers Bluetooth Tutorial
- java.lang.ClassNotFoundException android.support.v7.widget.Toolbar [Fix]
- Android: Save Data in local Db using Android Room
- Channel 50 SMSes received every few minutes Android Phones
- 21 Useful Android Emulator Short-cut Keyboard Keys
- Changing Android Intent Tittle using java code
- Android : No Launcher activity found! Error
- How to change TextView or EditText Text Color on Focus and on Press
- How to display Toast on Button Click : Android
- Android : Execute some code after back button is pressed
- Stop android adb service from command prompt or terminal
- [Soluiton] You already have the latest version of Android Studio installed
- Create Custom Android AlertDialog
- Android R Cannot Be Resolved To A Variable
- How to make Android EditText not editable
- How to remove Siri from Menu Bar [macOS Big Sur] - MacOS
- How to fix Microsoft Windows 10 update error 80070020 - Microsoft
- Bootstrap Nav Menu Dropdown on hover - Bootstrap
- Android Parsing Data for android-L failed Unsupported major.minor version 51.0 Error - Android
- How to open a new tab in Notepad++ - NotepadPlusPlus
- Notepad++ Editor alternatives for Mac OS X - NotepadPlusPlus
- Create SharePoint Site Collection with new Content database in existing web application - SharePoint
- Change Height of Android ActionBar - Android