Android SQLite CRUD example

In this post, I will walk you through how to perform Create, Read, Update and Delete (CRUD) operations in SQLite DB embedded in Android devices.

Prerequisite:

You must know about SQLite before you start creating the application. Take a look at What is SQLite? post which will make you understand much about SQLite.

Here are the quick links:

Quick Links

Project Structure

  • Create new android project [File >> New >> Android Application Project] with project name SQLiteCrudExample
  • Choose Build SDK and minimum required SDK [I chose version 4.1 and 2.2]
  • Enter package name as ‘com.prgguru.example’
  • Click next and select BlankActivity
  • Click next and provide Activity name and Layout name as ‘SQLiteCrudExample’ and ‘main’ respectively.

Code Listings

SQLiteCrudExample.java
Open SQLiteCrudExample.java and replace it with below code. Each line is added with comment and I hope it is self understandable. If you still have any doubt/question, discuss it right away in the comment section.

package com.prgguru.example;

import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.Toast;

public class SQLiteCrudExample  extends ListActivity {
	//DB name
	private final String dbName = "Android";
	//Table name
	private final String tableName = "Versions";
	//String array has list Android versions which will be populated in the list
	private final String[] versionNames= new String[]{"Cupcake", "Donut", "Eclair", "Froyo", "Gingerbread", "Honeycomb", "Ice Cream Sandwich", "Jelly Bean", "Kitkat"};
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        ArrayList<String> results = new ArrayList<String>();
        //Declare SQLiteDatabase object
        SQLiteDatabase sampleDB = null;
        
        try {
        	//Instantiate sampleDB object
        	sampleDB =  this.openOrCreateDatabase(dbName, MODE_PRIVATE, null);
        	//Create table using execSQL
        	sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " + tableName +	" (versionname VARCHAR);");
        	//Insert Android versions into table created
        	for(String ver: versionNames){
        		sampleDB.execSQL("INSERT INTO " + tableName + " Values ('"+ver+"');");
        	}
        	
        	//Create Cursor object to read versions from the table
        	Cursor c = sampleDB.rawQuery("SELECT versionname FROM " + tableName, null);
        	//If Cursor is valid
        	if (c != null ) {
        		//Move cursor to first row
        		if  (c.moveToFirst()) {
        			do {
        				//Get version from Cursor
        				String firstName = c.getString(c.getColumnIndex("versionname"));
        				//Add the version to Arraylist 'results'
        				results.add(firstName);
        			}while (c.moveToNext()); //Move to next row
        		} 
        	}
        	
        	//Set the ararylist to Android UI List
        	this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));
        	
        } catch (SQLiteException se ) {
        	Toast.makeText(getApplicationContext(), "Couldn't create or open the database", Toast.LENGTH_LONG).show();
        } finally {
        	if (sampleDB != null) {
        		sampleDB.execSQL("DELETE FROM " + tableName);
        		sampleDB.close();
        	}
        }
    }
}

Quick explanation of the API classes and methods involved

SQLiteDatabase:
SQLiteDatabase is an Android class which has methods to create, delete, execute SQL commands, and perform other common database management tasks. In our example, we are using execSQL() and rawQuery() methods of SQLiteDatabase class to execute SQLs.
execSQL:
execSQL is a method of SQLiteDatabase Class which is used to execute SQL statements.
rawQuery:
rawQuery is a method of SQLiteDatabase Class which executes SQL statements and returns Cursor.

Demo

That’s all. It’s time to test our code.

Run the application using emulator or device by right clicking on the project >> Run as >> Android applicaiton >> Choose emulator or device.



Download Source Code

Entire project is zipped and is available for download. Unzip the downloaded project and to import the project into eclipse, launch eclipse >> File >> Import.. >> Choose downloaded project(How to import android project in eclipse). If you just want to run the application in your mobile and see the output but don’t want to hit your head with source code, download application(apk) file and install it in your mobile device.

Download Source Code Download Application(apk)

*apk in Android  is the installation file similar to exe in windows.

[pglinkadssmall1]

If you feel this article is helpful and interesting please spread a word about it to your friends and colleagues by sharing the article in Facebook or Twitter.

You are always welcome to provide your comments and feedback from comment box.

Reference
Android SQLite
[pgwriteforus]
[pgfeedback]

Related Articles

Author: Android Guru

Share This Post On
  • LeRoy Gumede

    Great Stuff, Why is yours so short and working :) ? where is the Contact.java and DatabaseHandler.java ?

    • What are you referring here? I haven’t used Contact.java and DatabaseHandler in my code.

  • sugi

    great! (y). keep writing. :)

  • adarshmadrecha

    Thanks for such a good writeup.

  • Maryea

    hyee ….
    i am facing database upgradation problem.. using a pre populated sqlite database… it works fine.. bt when i try to update it by inserting values in existing table using sqlite browser.. it never upgrades… and shows no such record..
    Any idea for solution???

  • What are you referring here? I haven’t used Contact.java and DatabaseHandler in my code.

  • LeRoy Gumede

    Hey, Sorry for the confusion. just comparing yours too another app that connects too database. they use Contact.java and DatabaseHandler.java so that the whole application has access too it.

    But for simple apps I have found that your code is the best :)

Join now to get more access to our Android Tutorials

Join now to get more access to our Android Tutorials

Once you Join, you will receive Mail containing latest Android Tutorials once a month !!

I promise I wont spam you !!

You have Successfully Subscribed!

Pin It on Pinterest

Shares