Android SQLite Database Tutorial and Project

In this application, we will learn how to use SQLite database in android to save values and retrieve back from it. SQLite database does not support complex operators or function, so it does not support join, group by, no data type integrity etc. other wise it is same as other databases. There are three data types in SQLite: 

1) TEXT: to store any value as a text
2) Integer: to store integer type value
3) Real: to store floating type value 

But as we said it does not support data type integrity so we can use any data type and we can store any type of value in it. So let's start our project, create new project and here we are using two buttons, first button is used to take value from edit text boxes and save them to SQLite database, The second button is used to retrieve all data from database and display them on text view which is scrollable. The code of android XML file is given below:

<RelativeLayoutxmlns:android="http://schemas.android.com/apk/res/android"

   android:layout_width="fill_parent"

   android:layout_height="fill_parent"

   android:background="#abc" >

<EditText

   android:id="@+id/editText1"

   android:layout_width="wrap_content"

   android:layout_height="wrap_content"

   android:layout_alignParentTop="true"

   android:layout_centerHorizontal="true"

   android:maxLines="1"

   android:hint="Name"

   android:layout_marginTop="28dp"

   android:ems="10" >

  <requestFocus />

</EditText>

<EditText

   android:id="@+id/editText2"

   android:layout_width="wrap_content"

   android:layout_height="wrap_content"

   android:layout_alignLeft="@+id/editText1"

   android:layout_below="@+id/editText1"

   android:hint="Sur Name"

   android:maxLines="1"

   android:ems="10" />

<Button

   android:id="@+id/button1"

   android:layout_width="wrap_content"

   android:layout_height="wrap_content"

   android:layout_alignLeft="@+id/editText2"

   android:layout_alignRight="@+id/editText2"

   android:layout_below="@+id/editText2"

   android:text="Insert Values"

   android:onClick="insert"/>

 <Button

   android:id="@+id/button2"

   android:layout_width="wrap_content"

   android:layout_height="wrap_content"

   android:layout_alignLeft="@+id/button1"

   android:layout_alignRight="@+id/button1"

   android:layout_below="@+id/button1"

   android:onClick="display"

   android:text="Display all Values" />

<ScrollView

   android:id="@+id/scrollView1"

   android:layout_width="wrap_content"

   android:layout_height="wrap_content"

   android:layout_alignParentBottom="true"

   android:layout_alignParentLeft="true"

   android:layout_alignParentRight="true"

   android:layout_below="@+id/button2" >

  <LinearLayout

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:orientation="vertical" >

  <TextView android:id="@+id/textView1"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:textSize="20sp"/>

  </LinearLayout>

 </ScrollView>

</RelativeLayout>

Now open your Java file and initialize all objects.
-> To create new database or open existed database use: openOrCreataDatabase() and pass name of the database and open it in private mode.
-> To run any query except select query use: execSQL() 
-> To select values from database use: rawQuery() 
The code of android Java file is given below with explanation:

package com.smr; //your package name

import android.os.Bundle;

import android.view.View;

import android.widget.EditText;

import android.widget.TextView;

import android.widget.Toast;

import android.app.Activity;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity {

  SQLiteDatabase db;

  TextView tv;

  EditText et1,et2;

  @Override

  protected void onCreate(Bundle savedInstanceState) {

   super.onCreate(savedInstanceState);

   setContentView(R.layout.activity_main);

   //initialize all view objects

   tv=(TextView)findViewById(R.id.textView1);

   et1=(EditText)findViewById(R.id.editText1);

   et2=(EditText)findViewById(R.id.editText2);

   //create database if not already exist

   db= openOrCreateDatabase("Mydb", MODE_PRIVATE, null);

   //create new table if not already exist

   db.execSQL("create table if not exists mytable(name varchar, sur_name varchar)");

   }

   //This method will call on when we click on insert button

   public void insert(View v)

   {

    String name=et1.getText().toString();

    String sur_name=et2.getText().toString();

    et1.setText("");

    et2.setText("");

    //insert data into able

    db.execSQL("insert into mytable values('"+name+"','"+sur_name+"')");

   //display Toast

   Toast.makeText(this, "values inserted successfully.", Toast.LENGTH_LONG).show();

    }

   //This method will call when we click on display button

   public void display(View v)

   {

   //use cursor to keep all data

   //cursor can keep data of any data type

   Cursor c=db.rawQuery("select * from mytable", null);

   tv.setText("");

   //move cursor to first position

   c.moveToFirst();

   //fetch all data one by one

   do

   {

    //we can use c.getString(0) here

    //or we can get data using column index

    String name=c.getString(c.getColumnIndex("name"));

    String surname=c.getString(1);

    //display on text view

    tv.append("Name:"+name+" and SurName:"+surname+"\n");

    //move next position until end of the data

   }while(c.moveToNext());

  }

}

Now run your project and test this application.