Click here to Skip to main content
11,417,750 members (43,963 online)
Click here to Skip to main content

A Simple Android SQLite Example

, 6 Jun 2014 CPOL
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.

Comments and Discussions

 
QuestionTNX a lot Pin
saeid20102-Apr-15 7:48
membersaeid20102-Apr-15 7:48 
QuestionAbsolute Layout Pin
CostCode8-Mar-15 3:12
memberCostCode8-Mar-15 3:12 
AnswerRe: Absolute Layout Pin
Azim Zahir25-Apr-15 2:57
mvpAzim Zahir25-Apr-15 2:57 
QuestionAndroid SQLite Pin
Member 106686937-Mar-15 10:12
memberMember 106686937-Mar-15 10:12 
QuestionThanks Pin
almurad28-Feb-15 0:12
memberalmurad28-Feb-15 0:12 
QuestionAndroid Database Connection Pin
Member 1146607827-Feb-15 0:44
memberMember 1146607827-Feb-15 0:44 
AnswerRe: Android Database Connection Pin
Azim Zahir3-Mar-15 23:17
mvpAzim Zahir3-Mar-15 23:17 
GeneralRe: Android Database Connection Pin
Member 1153845822-Mar-15 22:37
memberMember 1153845822-Mar-15 22:37 
GeneralRe: Android Database Connection Pin
Member 1161720217-Apr-15 22:29
memberMember 1161720217-Apr-15 22:29 
Questioncreate multiple tables Pin
Member 1147475223-Feb-15 22:20
memberMember 1147475223-Feb-15 22:20 
AnswerRe: create multiple tables Pin
Azim Zahir3-Mar-15 23:20
mvpAzim Zahir3-Mar-15 23:20 
QuestionServer side code Pin
Member 102289224-Feb-15 20:58
memberMember 102289224-Feb-15 20:58 
QuestionVery Helpful for beginners Pin
Sudhir Kumar Rana4-Feb-15 1:15
professionalSudhir Kumar Rana4-Feb-15 1:15 
AnswerRe: Very Helpful for beginners Pin
Azim Zahir3-Mar-15 23:23
mvpAzim Zahir3-Mar-15 23:23 
QuestionPopulating a ListView Pin
Member 996588225-Jan-15 10:22
memberMember 996588225-Jan-15 10:22 
QuestionStore Video (Object) in Database Pin
Member 996588221-Jan-15 7:10
memberMember 996588221-Jan-15 7:10 
QuestionWishes azim Pin
Member 1138184417-Jan-15 3:34
memberMember 1138184417-Jan-15 3:34 
QuestionProblem with the absolute layout Pin
Hafiz9114-Jan-15 21:50
memberHafiz9114-Jan-15 21:50 
AnswerRe: Problem with the absolute layout Pin
Azim Zahir18-Jan-15 23:46
mvpAzim Zahir18-Jan-15 23:46 
QuestionVery Very Helpfull Pin
Member 112354518-Jan-15 0:21
memberMember 112354518-Jan-15 0:21 
AnswerRe: Very Very Helpfull Pin
Azim Zahir18-Jan-15 23:47
mvpAzim Zahir18-Jan-15 23:47 
QuestionChange Database Location Pin
Member 99658826-Jan-15 8:20
memberMember 99658826-Jan-15 8:20 
Questionplz help me ... i have problem Pin
Member 1133922331-Dec-14 22:39
memberMember 1133922331-Dec-14 22:39 
GeneralMy vote of 1 Pin
andrusha00729-Dec-14 14:41
memberandrusha00729-Dec-14 14:41 
Questionsqlite database androidn Pin
Member 1133922329-Dec-14 7:15
memberMember 1133922329-Dec-14 7:15 
Questionregarding this code Pin
Member 1105053919-Dec-14 0:47
memberMember 1105053919-Dec-14 0:47 
QuestionGetting single entry into listview from sqlite database Pin
Member 101663416-Dec-14 3:22
memberMember 101663416-Dec-14 3:22 
Questioncual es la librería que usaste para la conexión bd? Pin
Member 1125762023-Nov-14 10:48
memberMember 1125762023-Nov-14 10:48 
QuestionWhere is database in real device? Pin
Aldemaran22-Nov-14 5:18
memberAldemaran22-Nov-14 5:18 
AnswerRe: Where is database in real device? Pin
Azim Zahir24-Nov-14 22:57
memberAzim Zahir24-Nov-14 22:57 
QuestionAwesome Article Pin
Harjesh21-Nov-14 19:03
memberHarjesh21-Nov-14 19:03 
GeneralReally Useful Pin
Member 1124927419-Nov-14 23:53
memberMember 1124927419-Nov-14 23:53 
QuestionPlease support Pin
caodungviet17-Nov-14 6:30
membercaodungviet17-Nov-14 6:30 
Questionerrors? Pin
Member 111906015-Nov-14 23:29
memberMember 111906015-Nov-14 23:29 
AnswerRe: errors? Pin
Azim Zahir7-Nov-14 23:19
memberAzim Zahir7-Nov-14 23:19 
QuestionDefault values from Another 'Constant' class Pin
Member 108361293-Nov-14 23:39
memberMember 108361293-Nov-14 23:39 
AnswerRe: Default values from Another 'Constant' class Pin
Azim Zahir8-Nov-14 0:06
memberAzim Zahir8-Nov-14 0:06 
GeneralRe: Default values from Another 'Constant' class Pin
Member 1083612910-Nov-14 19:26
memberMember 1083612910-Nov-14 19:26 
GeneralComment Pin
Member 1056797031-Oct-14 10:27
memberMember 1056797031-Oct-14 10:27 
GeneralRe: Comment Pin
Azim Zahir31-Oct-14 19:06
memberAzim Zahir31-Oct-14 19:06 
Questionhelp please Pin
Yumi Morita21-Oct-14 1:15
memberYumi Morita21-Oct-14 1:15 
AnswerRe: help please Pin
Azim Zahir24-Oct-14 19:12
memberAzim Zahir24-Oct-14 19:12 
Questionthanks Pin
Member 1111722313-Oct-14 10:17
memberMember 1111722313-Oct-14 10:17 
AnswerRe: thanks Pin
Azim Zahir18-Oct-14 20:02
memberAzim Zahir18-Oct-14 20:02 
QuestionSQLite Data's Does not show on real device Pin
Member 1051553910-Oct-14 1:44
memberMember 1051553910-Oct-14 1:44 
AnswerRe: SQLite Data's Does not show on real device Pin
Azim Zahir11-Oct-14 7:49
memberAzim Zahir11-Oct-14 7:49 
QuestionThank Pin
Member 1112068230-Sep-14 22:50
memberMember 1112068230-Sep-14 22:50 
QuestionThanks so much Pin
Şefik Temel27-Sep-14 6:13
memberŞefik Temel27-Sep-14 6:13 
QuestionThanks Pin
Member 110620645-Sep-14 9:50
memberMember 110620645-Sep-14 9:50 
QuestionAnswer. Pin
Gyanesh Gouraw25-Aug-14 4:26
memberGyanesh Gouraw25-Aug-14 4:26 

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

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

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