SQLite Database Example
Android Sample Codes

SQLite Database example in android

What is SQLite database?

SQLite database is open source relational database management system in android. It is self-contained, serverless, zero configuration and transactional SQL database engine.

SQLite is a SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation. SQLite is a lightweight database, it does not require any server configuration like other database. SQLite is available as embedded in the android by default, so requirements of a server or installation not required.

Why SQLite?

SQLite Database Tutorial

The main package is android.database.sqlite that contains the classes to manage your own databases. The methods used to connect the SQLite databse is shown below.

openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags, DatabaseErrorHandler errorHandler)

openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags)

String path : This is the database name. It cannot be null.

SQLiteDatabase.CursorFactory factory : Used to allow returning sub-classes of Cursor when calling query. This value may be null.

int flags : Open the database according to the flags OPEN_READWRITE,  OPEN_READONLY, CREATE_IF_NECESSARY.

DatabaseErrorHandler errorHandler :  This is defined to take an action when database corruption is detected. This value may be null.

Another method to establish a connection with SQLite database is openOrCreateDatabase() . It not only opens but creates the database if it not exists. The syntax is

public static SQLiteDatabase openOrCreateDatabase (String path, SQLiteDatabase.CursorFactory factory, DatabaseErrorHandler errorHandler)

  • path String This value must never be null.
  • factory SQLiteDatabase.CursorFactory This value may be null.
  • errorHandler DatabaseErrorHandler This value may be null.

The tutorial on SQLite database discussing about students record handling. Here there is an option for adding students , Delete student record based on roll number, modify student details based on roll number, view students details based on roll number and we can view all students details in the database .

Layout for handling student record is as shown below.

SQLite Database Example

The layout design of the above layout is as follows.

activity_main.xml
<?xml version="1.0" encoding="utf-8"?>

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        android:id="@+id/l1"
        android:orientation="horizontal">
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Students Record Handling"
            android:gravity="center"
            android:textSize="20sp"
            android:textColor="@color/colorAccent"
            android:textAlignment="center"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="60dp"
        android:id="@+id/l2"
         android:orientation="horizontal"
        android:layout_below="@+id/l1">
        <TextView
            android:layout_width="200dp"
            android:layout_height="50dp"
            android:text="Enter Student Roll No:"
            android:textSize="20sp"/>
        <EditText
            android:layout_width="200dp"
            android:layout_height="50dp"
            android:id="@+id/e1"
            />

    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="60dp"
        android:id="@+id/l3"

        android:orientation="horizontal"
        android:layout_below="@+id/l2">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Enter Student Name:"
            android:textSize="20sp"
            />
        <EditText
            android:layout_width="200dp"
            android:layout_height="50dp"
            android:id="@+id/e2"/>
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="60dp"
        android:id="@+id/l4"
    android:layout_below="@+id/l3"
        android:orientation="horizontal">
        <TextView
            android:layout_width="200dp"
            android:layout_height="50dp"
            android:text="Enter Student Mark:"
            android:textSize="20sp"/>
        <EditText
            android:layout_width="200dp"
            android:layout_height="50dp"
            android:id="@+id/e3"/>
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="80dp"
    android:layout_below="@+id/l4"
        android:id="@+id/l5"
        android:orientation="horizontal"
        android:gravity="center">
        <Button
            android:layout_width="100dp"
            android:layout_height="40dp"
            android:id="@+id/b1"
            android:text="Add"
            />
        <Button
            android:layout_width="100dp"
            android:layout_height="40dp"
            android:id="@+id/b2"
            android:text="Delete"
            />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="80dp"

        android:id="@+id/l6"
        android:orientation="horizontal"
        android:gravity="center"
        android:layout_below="@+id/l5">
        <Button
            android:layout_width="100dp"
            android:layout_height="40dp"
            android:id="@+id/b3"
            android:text="Modify"
            />
        <Button
            android:layout_width="100dp"
            android:layout_height="40dp"
            android:id="@+id/b4"
            android:text="View"
            />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="80dp"
    android:layout_below="@+id/l6"
        android:id="@+id/l7"
        android:orientation="horizontal"
        android:gravity="center">
        <Button
            android:layout_width="100dp"
            android:layout_height="40dp"
            android:id="@+id/b5"
            android:text="ViewAll"
            />
        <Button
            android:layout_width="100dp"
            android:layout_height="40dp"
            android:id="@+id/b6"
            android:text="SQLite Details"
            />
    </LinearLayout>

</RelativeLayout>

 MainActivity.java

package com.example.sqliteexample1;

import android.app.AlertDialog;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends AppCompatActivity {
   EditText editRollno;
   EditText editName;
   EditText editMarks;
    Button btnAdd;
    Button btnDelete;
    Button btnModify;
    Button btnView;
    Button btnViewAll;
    Button btnShowInfo;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

// Initializing controls

        editRollno = (EditText) findViewById(R.id.e1);
        editName = (EditText) findViewById(R.id.e2);
       editMarks = (EditText) findViewById(R.id.e3);
        btnAdd = (Button) findViewById(R.id.b1);
        btnDelete = (Button) findViewById(R.id.b2);
         btnModify = (Button) findViewById(R.id.b3);
        btnView = (Button) findViewById(R.id.b4);
         btnViewAll = (Button) findViewById(R.id.b5);
        btnShowInfo = (Button) findViewById(R.id.b6);

        // Creating database and table

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


// Registering event handlers

        btnAdd.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {

                // 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();
            }
        });
        btnView.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
                // 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())
                {
                    editName.setText(c.getString(1));
                    editMarks.setText(c.getString(2));
                }
                else
                {
                    showMessage("Error", "Invalid Rollno");
                    clearText();
                }


            }
        });

        btnDelete.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {


                // Checking empty roll number
                if(editRollno.getText().toString().trim().length()==0)
                {
                    showMessage("Error", "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();

            }



       });
       btnModify.setOnClickListener(new View.OnClickListener() {
           public void onClick(View v) {
                // Code here executes on main thread after user presses button
               if(editRollno.getText().toString().trim().length()==0)
               {
                   showMessage("Error", "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();


            }
        });

        btnViewAll.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
                // Code here executes on main thread after user presses button
                // Checking empty roll number
                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());
            }
        });
       btnShowInfo.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
                showMessage("Students Information Handling", "https://codeunplug.com/");

           }
       });

    }
    public void showMessage(String title,String message)
    {
        AlertDialog.Builder builder=new AlertDialog.Builder(this);
        builder.setCancelable(true);
        builder.setTitle(title);
        builder.setMessage(message);
        builder.show();
    }
    public void clearText()
    {
        editRollno.setText("");
        editName.setText("");
        editMarks.setText("");
        editRollno.requestFocus();
    }

}
AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.sqliteexample1" >

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme" >
        <activity android:name=".MainActivity" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>
Output :

SQLite Database example

Leave a Reply

Your email address will not be published.