
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>
- Android Error Unexpected cast to Button: layout tag was FrameLayout
- ADT quit unexpectedly error on Mac OSX Android Eclipse SDK
- Parsing Data for android-21 failed unsupported major.minor version 51.0
- Android Studio Ctrl Shift o auto import not working
- java.lang.IllegalStateException: You need to use a Theme.AppCompat theme (or descendant) with this activity.
- Android : How to make TextView Scrollable
- This class should be public (android.support.v7.internal.widget.ActionBarView.HomeView) Lint Error
- Integrating Android Facebook SDK 3.17.2 Tutorial
- Android R Cannot Be Resolved To A Variable
- Android : Exception raised during rendering: action_bar API 22
- How to take screenshot on Android
- Read Text file from SD Card : Android Programming
- How to make Android EditText not editable
- Your Android SDK is out of date or is missing templates. Please ensure you are using SDK version 22 or later.
- The declared package does not match the expected package Eclipse
- Can't Run SDK Manager find_java.bat issue
- What is Android Toast.LENGTH_SHORT and Toast. LENGTH_LONG durations
- Android Emulator Soft Back button action using Computer keyboard
- Multiline EditText in Android Example
- Use 5G Network on Android Emulator
- Make Android TextView Clickable like Buttons
- How to empty trash in Android Device
- Android : Execute some code after back button is pressed
- Disable Fading Edges Scroll Effect Android Views
- How To Disable Landscape Mode in Android Application
- Install Python on Alpine Linux - Docker - Docker
- Calculate Sum of List elements using Java Streams - Java
- [fix] URI is not registered (Settings | Languages & Frameworks | Schemas and DTDs) IntelliJ - Java
- Merge multiple zip files without unzipping (extracting) - HowTos
- 9 Ways to Loop Java Map (HashMap) with Code Examples - Java
- Cannot start Android Studio. No JDK found - Android-Studio
- How to turn off Stage Manager - macOS Ventura - MacOS
- How to enable line numbers in IntelliJ Android Studio for all files - Android-Studio