What is SQLite

SQLite is a programming library which implements a relational database management system.It is in built in android. So you do not have to do any extra work for SQLite in android.

SQLite is used to store the persistant data in android.

SQLite is serverless. Most SQL database engines are implemented as a separate server process. Programs that want to access the database communicate with the server to send requests to the server and to receive back results. SQLite does not work this way. With SQLite you can directly read and write from database file on disk.

It take much less momory.All the mobiles uses this to store the data.

SQLite is open source.

In my program i will take a simple example to write and read the data from SQLite database step by step.When you click on submit button it will insert the record into sqlite table and when you click on the display button it will display the whole record from sqlite database table.


SQLite:.

In this section i am going to show the SQLite program with practical example.

First open the android studio and create a project and give the name of your project like SQLiteDemo.

The screenshots of the program is as follows:

sqlite image sqlite image

Drag and drop the two Button , three TextView and three editText from the pallet. Open the activity_main.xml file and code look like this


<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="com.example.niitpc.optionmenudemo.MainActivity">


    
<TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="30dp"
        android:layout_marginTop="80dp"
        android:text="Enter The Name" />

    
<EditText
        android:id="@+id/editText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentEnd="true"
        android:layout_alignParentTop="true"
        android:layout_marginEnd="13dp"
        android:layout_marginTop="63dp"
        android:ems="10"
        android:inputType="textPersonName"
         />

    
<TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignStart="@+id/textView"
        android:layout_below="@+id/editText"
        android:layout_marginTop="41dp"
        android:text="Enter Surname" />

    
<EditText
        android:id="@+id/editText2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/textView2"
        android:layout_alignStart="@+id/editText"
        android:ems="10"
        android:inputType="textPersonName"
         />

    
<Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignStart="@+id/textView2"
        android:layout_below="@+id/textView2"
        android:layout_marginTop="128dp"
        android:onClick="submit"
        android:text="Submit" />

    
<Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/button"
        android:layout_marginStart="48dp"
        android:layout_toEndOf="@+id/button"
        android:onClick="display"
        android:text="Display" />

    
<TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignStart="@+id/textView2"
        android:layout_below="@+id/textView2"
        android:layout_marginTop="49dp"
        android:text="Enter Marks" />

    
<EditText
        android:id="@+id/editText3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/textView3"
        android:layout_alignEnd="@+id/editText2"
        android:ems="10"
        android:inputType="textPersonName"
         />

</RelativeLayout>


       

Explore the java folder and right click on the MainActivity.java file package and take a class give the name as DatabaseHelper.

The code is look like this.


package com.example.niitpc.optionmenudemo;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by niitpc on 6/10/2018.
 */

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String database_name="student.db";
    public static final String table_name="student_table";
    public static final String col_1="ID";
    public static final String col_2="NAME";
    public static final String col_3="SURNAME";
    public static final String col_4="MARKS";
    public DatabaseHelper(Context context) {
        super(context, database_name, null, 1);
        SQLiteDatabase db=this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table student_table(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,SURNAME TEXT,MARKS INTEGER)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS student_table");
        onCreate(db);

    }
    public  boolean InserData(String name,String surname,String marks)
    {
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues contentValues=new ContentValues();
        contentValues.put(col_2,name);
        contentValues.put(col_3,surname);
        contentValues.put(col_4,marks);
        long result=db.insert(table_name,null,contentValues);
        if(result==-1) {
            return false;
        }
        else
        {
        return  true;
        }

    }
    public Cursor getdataAll()
    {
        SQLiteDatabase db=this.getWritableDatabase();
        Cursor res=db.rawQuery("select *from student_table",null);
        return res;

    }
}


       

Explanation:

The DatabaseHelper class inherit the SQLiteOpenHelper for sqlite database implementation.

public static final String database_name="student.db":Here i have declared a variable database_name String type and assign the database name as student.db you can write any databse name here.

public static final String table_name="student_table":Here i have declared a variable table_name and assign the table name as student_table you can write any table name

public static final String col_1="ID";:Here i have declared a variable col_1 and assign the ID it is the column name of table.

public static final String col_2="NAME";:Here i have declared a variable col_2 assign the NAME it is the column name of table.

public static final String col_3="SURNAME";:Here i have declared a variable col_3 and assign the SURNAME it is the column name of table.

public static final String col_4="MARKS";:Here i have declared a variable col_4 assign the MARKS it is the column name of table.

public DatabaseHelper(Context context):.In this statement i have created the constructor of DatabaseHelper class.

super(context, database_name, null, 1):This statement call the constructor of the SQLiteOpenHelper class.

SQLiteDatabase db=this.getWritableDatabase():This statement initialized the db object of the SQLiteDatabase class with the help of getWritableDatabase().

getWritableDatabase():This method is used to create/open the database and that will be used for reading and writing.

The method declaration is :public SQLiteDatabase getWritableDatabase ().

public void onCreate(SQLiteDatabase db):Here i have override the onCreate().

db.execSQL("create table student_table(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,SURNAME TEXT,MARKS INTEGER)"); The execSQL() is used to execute the query in database.In my program i have create table student_table and col ID as Integer and autoincrement , NAME as TEXT ,SURNAME as TEXT,MARKS as INTEGER.

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion):This statement is used to upgrade the database like you want to drop the datbase alter the table etc.

public boolean InserData(String name,String surname,String marks):Here i have created the InsertData() that take 3 argument name surname and marks.

SQLiteDatabase db=this.getWritableDatabase():Here we initialized the db object of SQLiteDatabase with the help of getWritableDatabase().

ContentValues contentValues=new ContentValues(): The ContentValues class is used to store a set of values.In my program i have created the object of Contentvalues class that contains the empty set of row.

contentValues.put(col_2,name):The put() take two argument first the col_2 that is column name(NAME) and second is method parameter as name of my own method InsertData().

same for other column.

long result=db.insert(table_name,null,contentValues):Here we call the insert() with the help of db object and the method take three argument first is table name and second is null and third is contentValues object of ContentValues class. The insert() return -1 if the record is not inserted.

public Cursor getdataAll():Here i have created the method getdataAll that will return all the data from database table of SQLite.

SQLiteDatabase db=this.getWritableDatabase():you are well known this statement.

Cursor res=db.rawQuery("select *from student_table",null):Here the rawQuery() take two argument first is query and second is null.It returns value will store int res object of Cursor.


Open the MainActivity.java file and the code is look like this.


package com.example.niitpc.optionmenudemo;

import android.app.AlarmManager;
import android.app.AlertDialog;
import android.app.PendingIntent;
import android.content.Intent;
import android.database.Cursor;
import android.os.Vibrator;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.PopupMenu;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

EditText txt_name,txt_surname,txt_marks;
DatabaseHelper db;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db=new DatabaseHelper(this);
        txt_name=(EditText)findViewById(R.id.editText);
        txt_surname=(EditText)findViewById(R.id.editText2);
        txt_marks=(EditText)findViewById(R.id.editText3);


    }
    public void submit(View v)
    {
        String name=txt_name.getText().toString();
        String surname=txt_surname.getText().toString();
        String marks=txt_marks.getText().toString();
        boolean result=db.InserData(name,surname,marks);
        if(result==true)
        {
            Toast.makeText(getApplicationContext(),"Record inserted",Toast.LENGTH_LONG).show();

        }
        else
        {
            Toast.makeText(getApplicationContext(),"not inserted",Toast.LENGTH_LONG).show();

        }

    }
    public  void display(View v)
    {


        Cursor res=db.getdataAll();
        if(res.getCount()==0)
        {
            showMessage("error","nothing");
            return;
        }
        StringBuffer buffer=new StringBuffer();
        while (res.moveToNext())
        {
            buffer.append("ID"+" "+res.getString(0)+"\n"+"Name"+" "+res.getString(1)+"\n"+"Surname"+" "+res.getString(2)+"\n"+"Surname"+" "+res.getString(3));
        }
        showMessage("data",buffer.toString());
    }

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



}
     

EditText txt_name,txt_surname,txt_marks:This statement create the three object txt_name,txt_surname,txt_marks of EditText.

DatabaseHelper db;This statement declare the db object of the DatabaseHelper class.

db=new DatabaseHelper(this):Here i have initialized the db object and pass this into DatabaseHelper constructor.

txt_name=(EditText)findViewById(R.id.editText);:This statement initialized the txt_name object.

txt_surname=(EditText)findViewById(R.id.editText);:This statement initialized the txt_surname object.

txt_marks=(EditText)findViewById(R.id.editText);:This statement initialized the txt_marks object.

public void submit(View v):I have created the submit() for insertion of record.

String name=txt_name.getText().toString();:Here we store the name into name variable with the help of getText() of txt_name object.

String surname=txt_surname.getText().toString(): we store the surname into surname variable with the help of getText() of txt_surname object.

String marks=txt_marks.getText().toString(): we store the marks into marks variable with the help of getText() of txt_marks.

boolean result=db.InserData(name,surname,marks):Here we have to call the InsertData() that take the three argument name ,surname and marks this method is we have to created in DatabaseHelper class. It will return boolean result true or false

if(result==true)This statement check the result as true if true than Toast display the message as Record inserted.

elseif the condition is false then the else part execute and display the message record not inserted in Toast.

public void display(View v):I have create another method display that will display all the data from database table.

Cursor res=db.getdataAll():Here i have called the getdataAll() that is available in DatabaseHelper class and return the data into res object of the Cursor.

public void display(View v):I have create another method display that will display all the data from database table.

Cursor res=db.getdataAll():Here i have called the getdataAll() that is available in DatabaseHelper class and return the data into res object of the Cursor.

if(res.getCount()==0):Here we have to given the condition the res object call the getCount() that return the no of record if the getCount return 0 means no record than we call the showMessage()method this method is defined below the display method and passing nothing and error two argument that will display in alert dialog box nothing and error.

StringBuffer buffer=new StringBuffer():This statement create the buffer object of the StringBuffer class.

while (res.moveToNext()):Here i am using the loop to move the record next. The moveToNext() continue until the record is end.With in the body of loop we append the ID ,NAME, SURNAME,MARKS in buffer object. When the loop is end we call the showMessage() passing two argument data and buffer.

public void showMessage(String title,String message):We have to created the showMessage() that take two argument title and message.With in body i am using alert dialog to display the title and message.

Do not forget to assign the submit() on onClick property of submit button and display() on onClick property of display button.

What is SQLite.

what is SQLite for video click here

back image                     next image