Click here to Skip to main content
13,199,318 members (64,051 online)
Click here to Skip to main content
Add your own
alternative version

Stats

483.1K views
64K downloads
73 bookmarked
Posted 6 Jun 2014

A Simple Android SQLite Example

, 6 Jun 2014
Rate this:
Please Sign up or sign in to vote.
This is a simple demonstration of using SQLite database in Android.

Introduction

In this article, I have attempted to demonstrate the use of SQLite database in Android in the simplest manner possible. Most of the articles and demos which I have seen on the net were not very simple for a layman to understand. Also most of the examples assume a deep knowledge of Android and SQL. This article assumes that the user has a working knowledge of Android and basic SQL commands. The example application shows how to perform basic DML and query operations on an SQLite table in Android.

Background

The example application which I have created is a simple Student Management System, which allows a user to add, delete, modify and view student details. The application accepts a student's roll number, name and marks and adds these details to a student table. For simplicity, I have created all fields of VARCHAR data type, which is a variable length character string.

Using the Code

The SQLiteDatabase class from the android.database.sqlite package and the Cursor class from the android.database package provide all the functionality required for performing Data Manipulation Language (DML) and query operations on an SQLite table.

The following code shows how to create an SQLite database and a table in the database.

db=openOrCreateDatabase("StudentDB", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS student(rollno VARCHAR,name VARCHAR,marks VARCHAR);");

In the above code, the openOrCreateDatabase() function is used to open the StudentDB database if it exists or create a new one if it does not exist. The first parameter of this function specifies the name of the database to be opened or created. The second parameter, Context.MODE_PRIVATE indicates that the database file can only be accessed by the calling application or all applications sharing the same user ID. The third parameter is a Cursor factory object which can be left null if not required.

The db.execSQL() function executes any SQL command. Here it is used to create the student table if it does not already exist in the database.

Following is the full code of the onCreate() method of the main activity.

public void onCreate(Bundle savedInstanceState)
{
    <code>super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

// Initializing controls

    editRollno=(EditText)findViewById(R.id.editRollno);
    editName=(EditText)findViewById(R.id.editName);
    editMarks=(EditText)findViewById(R.id.editMarks);
    btnAdd=(Button)findViewById(R.id.btnAdd);
    btnDelete=(Button)findViewById(R.id.btnDelete);
    btnModify=(Button)findViewById(R.id.btnModify);
    btnView=(Button)findViewById(R.id.btnView);
    btnViewAll=(Button)findViewById(R.id.btnViewAll);
    btnShowInfo=(Button)findViewById(R.id.btnShowInfo);

// Registering event handlers

    btnAdd.setOnClickListener(this);
    btnDelete.setOnClickListener(this);
    btnModify.setOnClickListener(this);
    btnView.setOnClickListener(this);
    btnViewAll.setOnClickListener(this);
    btnShowInfo.setOnClickListener(this);

// Creating database and table

    db=openOrCreateDatabase("StudentDB", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS student(rollno VARCHAR,name VARCHAR,marks VARCHAR);");
}

In the onClick() event handler, we can write the code required to add, delete, modify and view records.

The following code uses the db.execSQL() function to insert a student record in the student table.

db.execSQL("INSERT INTO student VALUES('"+editRollno.getText()+"','"+
editName.getText()+"','"+editMarks.getText()+"');");

The above code generates an INSERT statement by appending the contents of the editable fields into a string and executes the INSERT statement.

In the same way, the DELETE command can be executed as follows:

db.execSQL("DELETE FROM student WHERE rollno='"+editRollno.getText()+"'");

The above code deletes the record of the student whose roll number is entered in the editable field.

The UPDATE command can be executed as follows:

db.execSQL("UPDATE student SET name='"+editName.getText()+"',marks='"+
editMarks.getText()+"' WHERE rollno='"+editRollno.getText()+"'");

The above code updates the record of the student whose roll number is entered in the editable field.

To view a student record, we execute a query using the rawQuery() method of the SQLiteDatabase class as follows:

Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
if(c.moveToFirst())
{
    editName.setText(c.getString(1));
    editMarks.setText(c.getString(2));
}

The above code uses the rawQuery() method of the SQLiteDatabase class to execute the SELECT statement to select the record of the student, whose roll number is specified. It then checks if the record is found using the moveToFirst() method of the Cursor class and displays the name and marks in the respective editable fields.

To view all records, the following code can be used:

Cursor c=db.rawQuery("SELECT * FROM student", null);
if(c.getCount()==0)
{
    showMessage("Error", "No records found");
    return;
}
StringBuffer buffer=new StringBuffer();
while(c.moveToNext())
{
    buffer.append("Rollno: "+c.getString(0)+"\n");
    buffer.append("Name: "+c.getString(1)+"\n");
    buffer.append("Marks: "+c.getString(2)+"\n\n");
}
showMessage("Student Details", buffer.toString());

The above code executes the SELECT command to retrieve records of all students and appends them into a string buffer. Finally, it displays the student details using the user-defined showMessage() function.

Following is the full code of the onClick() event handler:

public void onClick(View view)
{
// Adding a record
    if(view==btnAdd)
    {
    // Checking empty fields
        if(editRollno.getText().toString().trim().length()==0||
           editName.getText().toString().trim().length()==0||
           editMarks.getText().toString().trim().length()==0)
        {
            showMessage("Error", "Please enter all values");
            return;
        }
    // Inserting record
        db.execSQL("INSERT INTO student VALUES('"+editRollno.getText()+"','"+editName.getText()+
                   "','"+editMarks.getText()+"');");
        showMessage("Success", "Record added");
        clearText();
    }
// Deleting a record
    if(view==btnDelete)
    {
    // Checking empty roll number
        if(editRollno.getText().toString().trim().length()==0)
        {
            showMessage("Error", "Please enter Rollno");
            return;
        }
    // Searching roll number
        Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
        if(c.moveToFirst())
        {
        // Deleting record if found
            db.execSQL("DELETE FROM student WHERE rollno='"+editRollno.getText()+"'");
            showMessage("Success", "Record Deleted");
        }
        else
        {
            showMessage("Error", "Invalid Rollno");
        }
        clearText();
    }
// Modifying a record
    if(view==btnModify)
    {
    // Checking empty roll number
        if(editRollno.getText().toString().trim().length()==0)
        {
            showMessage("Error", "Please enter Rollno");
            return;
        }
    // Searching roll number
        Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
        if(c.moveToFirst())
        {
        // Modifying record if found
            db.execSQL("UPDATE student SET name='"+editName.getText()+"',marks='"+editMarks.getText()+
                    "' WHERE rollno='"+editRollno.getText()+"'");
            showMessage("Success", "Record Modified");
        }
        else
        {
            showMessage("Error", "Invalid Rollno");
        }
        clearText();
    }
// Viewing a record
    if(view==btnView)
    {
    // Checking empty roll number
        if(editRollno.getText().toString().trim().length()==0)
        {
            showMessage("Error", "Please enter Rollno");
            return;
        }
    // Searching roll number
        Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
        if(c.moveToFirst())
        {
        // Displaying record if found
            editName.setText(c.getString(1));
            editMarks.setText(c.getString(2));
        }
        else
        {
            showMessage("Error", "Invalid Rollno");
            clearText();
        }
    }
// Viewing all records
    if(view==btnViewAll)
    {
    // Retrieving all records
        Cursor c=db.rawQuery("SELECT * FROM student", null);
    // Checking if no records found
        if(c.getCount()==0)
        {
            showMessage("Error", "No records found");
            return;
        }
    // Appending records to a string buffer
        StringBuffer buffer=new StringBuffer();
        while(c.moveToNext())
        {
            buffer.append("Rollno: "+c.getString(0)+"\n");
            buffer.append("Name: "+c.getString(1)+"\n");
            buffer.append("Marks: "+c.getString(2)+"\n\n");
        }
    // Displaying all records
        showMessage("Student Details", buffer.toString());
    }
// Displaying info
    if(view==btnShowInfo)
    {
    showMessage("Student Management Application", "Developed By Azim");
    }
}

The following user-defined function is used to display message to the user:

public void showMessage(String title,String message)
{
    Builder builder=new Builder(this);
    builder.setCancelable(true);
    builder.setTitle(title);
    builder.setMessage(message);
    builder.show();
}

The following user-defined function is used to clear edit fields:

public void clearText()
{
    editRollno.setText("");
    editName.setText("");
    editMarks.setText("");
    editRollno.requestFocus();
}

Points of Interest

I hope that this article will be helpful to people new to the Android platform to understand developing database applications for Android before starting to write more complex applications.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Azim Zahir
Instructor / Trainer NIIT, India
India India
I am a trainer by profession. Currently I am working with NIIT (Mumbai, India) as a Senior Faculty. I enjoy programming as a hobby. My favorite technologies are Flash, Flex and Silverlight.

Of late I have developed keen interest in WPF and Windows Mobile programming.

Apart from computers, my favorite pastime is bicycling.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
AnswerSQLite database with Android Pin
Member 1336028113-Aug-17 23:46
memberMember 1336028113-Aug-17 23:46 
QuestionUnable to display contents Pin
Member 132743175-Jul-17 17:52
memberMember 132743175-Jul-17 17:52 
Questioncopy to sdcard Pin
Member 1315897511-May-17 6:02
memberMember 1315897511-May-17 6:02 
QuestionInsert Pin
Member 130211975-Mar-17 1:43
memberMember 130211975-Mar-17 1:43 
Questionandroid project Pin
Member 129409968-Jan-17 23:31
memberMember 129409968-Jan-17 23:31 
QuestionNext button Pin
Member 1284203220-Dec-16 3:50
memberMember 1284203220-Dec-16 3:50 
QuestionHelp on accessing local database Pin
Member 1281022423-Oct-16 4:19
memberMember 1281022423-Oct-16 4:19 
QuestionComo eliminar un registro de la vista mostrar todo Pin
Member 1276140527-Sep-16 0:00
memberMember 1276140527-Sep-16 0:00 
GeneralThanks! Pin
Masterofslaye6-Sep-16 11:47
memberMasterofslaye6-Sep-16 11:47 
QuestionShowmessage function Pin
Member 1266108131-Jul-16 7:35
memberMember 1266108131-Jul-16 7:35 
AnswerRe: Showmessage function Pin
Azim Zahir28-Aug-16 5:23
mvpAzim Zahir28-Aug-16 5:23 
QuestionHow to see the Activity codes in this Student Projects ? Pin
Member 125028024-May-16 7:03
memberMember 125028024-May-16 7:03 
Questionone more simple example Pin
Member 1245752113-Apr-16 9:13
memberMember 1245752113-Apr-16 9:13 
AnswerRe: one more simple example Pin
Azim Zahir26-Apr-16 20:40
mvpAzim Zahir26-Apr-16 20:40 
GeneralRe: one more simple example Pin
Member 1245752127-Apr-16 7:44
memberMember 1245752127-Apr-16 7:44 
Questionandroid sqlite database Pin
Member 124352506-Apr-16 20:50
memberMember 124352506-Apr-16 20:50 
AnswerRe: android sqlite database Pin
Azim Zahir26-Apr-16 20:26
mvpAzim Zahir26-Apr-16 20:26 
Questionre: A Simple Android SQLite Example Pin
DevWithJava5-Apr-16 5:59
groupDevWithJava5-Apr-16 5:59 
AnswerRe: re: A Simple Android SQLite Example Pin
Azim Zahir26-Apr-16 20:19
mvpAzim Zahir26-Apr-16 20:19 
QuestionRelational Table. Pin
Member 124288021-Apr-16 7:25
memberMember 124288021-Apr-16 7:25 
AnswerRe: Relational Table. Pin
Azim Zahir4-Apr-16 19:24
mvpAzim Zahir4-Apr-16 19:24 
Questionmissing dbhelper? Pin
Member 1241642128-Mar-16 23:14
memberMember 1241642128-Mar-16 23:14 
Questiondatabase connection for android app Pin
Member 1234097920-Feb-16 19:49
memberMember 1234097920-Feb-16 19:49 
QuestionUploading the saved data in the sqlite database to a remote oracle or sql server Pin
Member 1162246617-Feb-16 9:13
memberMember 1162246617-Feb-16 9:13 
GeneralMy vote of 5 Pin
Member 1231567511-Feb-16 0:48
memberMember 1231567511-Feb-16 0:48 
GeneralMy vote of 5 Pin
Titan921-Nov-15 11:15
memberTitan921-Nov-15 11:15 
QuestionSimple Android SQLite Example Pin
Member 1167124928-Sep-15 17:57
memberMember 1167124928-Sep-15 17:57 
AnswerRe: Simple Android SQLite Example Pin
Azim Zahir10-Oct-15 5:35
mvpAzim Zahir10-Oct-15 5:35 
QuestionAndroid AVD (Emulator) Pin
Member 1200139421-Sep-15 13:28
memberMember 1200139421-Sep-15 13:28 
Questionmy code isnt working :( Pin
Member 1199778020-Sep-15 5:59
memberMember 1199778020-Sep-15 5:59 
AnswerRe: my code isnt working :( Pin
Member 120356875-Oct-15 20:15
memberMember 120356875-Oct-15 20:15 
Generalshow data in listview Pin
Member 1198069314-Sep-15 22:10
memberMember 1198069314-Sep-15 22:10 
Questioncode error Pin
Member 1189858110-Aug-15 2:31
memberMember 1189858110-Aug-15 2:31 
Questionget Details Pin
Member 1181625814-Jul-15 21:23
memberMember 1181625814-Jul-15 21:23 
QuestionThanks for the wonderful app Pin
Member 118261559-Jul-15 8:03
memberMember 118261559-Jul-15 8:03 
AnswerRe: Thanks for the wonderful app Pin
Azim Zahir27-Jul-15 19:05
mvpAzim Zahir27-Jul-15 19:05 
QuestionFetch Pin
Member 1171721823-Jun-15 1:13
memberMember 1171721823-Jun-15 1:13 
AnswerRe: Fetch Pin
Azim Zahir27-Jul-15 19:14
mvpAzim Zahir27-Jul-15 19:14 
QuestionThanks, nice article. But one warning :) Pin
FreeRider115-May-15 2:32
memberFreeRider115-May-15 2:32 
AnswerRe: Thanks, nice article. But one warning :) Pin
Azim Zahir18-May-15 22:09
mvpAzim Zahir18-May-15 22:09 
GeneralRe: Thanks, nice article. But one warning :) Pin
Static Flux17-Nov-15 9:04
memberStatic Flux17-Nov-15 9:04 
AnswerRe: Thanks, nice article. But one warning :) Pin
projectincharge21-Feb-16 20:19
memberprojectincharge21-Feb-16 20:19 
Questionthe project studentdetails Pin
Member 1167970010-May-15 18:38
memberMember 1167970010-May-15 18:38 
AnswerRe: the project studentdetails Pin
Azim Zahir18-May-15 22:17
mvpAzim Zahir18-May-15 22:17 
GeneralMy vote of 5 Pin
Member 116661415-May-15 5:33
memberMember 116661415-May-15 5:33 
GeneralRe: My vote of 5 Pin
Azim Zahir5-May-15 23:41
mvpAzim Zahir5-May-15 23:41 
QuestionThanks for this very basic tutorial Pin
Member 116632154-May-15 6:25
memberMember 116632154-May-15 6:25 
AnswerRe: Thanks for this very basic tutorial Pin
Azim Zahir5-May-15 23:44
mvpAzim Zahir5-May-15 23:44 
QuestionTNX a lot Pin
saeid20102-Apr-15 6:48
membersaeid20102-Apr-15 6:48 
AnswerRe: TNX a lot Pin
Azim Zahir5-May-15 23:47
mvpAzim Zahir5-May-15 23:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171020.1 | Last Updated 6 Jun 2014
Article Copyright 2014 by Azim Zahir
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid