Android SQLite Database Tutorial

This article is about android sqlite database tutorial.

There are several storage options available in android like shared preferences, internal and external storage, sqlite, etc. Here we will see how to use sqlite database as a storage system in android to perform CRUD operations.

SQLite is light weight open source database that stores data in text files. Android already comes with built in sqlite database.

Also Read: Java SQLite Tutorial

 

Android SQLite Database Tutorial

The database table that I will use in this tutorial has following structure.

Table Name: record

Field	Type
id	integer, primary key, autoincrement
name	text

 

• The android.database.sqlite package contains sqlite specific classes.
• The SQLiteOpenHelper class provides all the functionality for sqlite database.
• The SQLiteDatabase class provides various methods to perform create, read, update and delete operations.

 

Create Database

Before working with SQLite database we have to first extend SQLiteOpenHelper class. The example that I have used here contains DBHelper class that extends SQLiteOpenHelper class and perform all database related operations.

For creating the database we will call constructor of SQLiteOpenHelper class using super().

public DBHandler(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
}

 

onCreate() and onUpgrade()

We have to override two methods onCreate() and onUpgrade().

The code required to create table will be written inside onCreate() method.

public void onCreate(SQLiteDatabase db) {
        String query="CREATE TABLE "+TABLE_NAME+" ("+ID_COL+" INTEGER PRIMARY KEY AUTOINCREMENT,"+NAME_COL+" TEXT)";
        db.execSQL(query);
}

 

onUpgrade() method contains the code required to update the database.

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

        // Create table again
        onCreate(db);
}

 

Insert

In this example the insert operation is handled by insertRecord() method. It takes name as a string argument and insert it into table. We have to first add all the values in ContentValues object and then finally insert into table using insert() method of SQLiteDatabase class.

public void insertRecord(String name){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();

        values.put(NAME_COL,name);
        db.insert(TABLE_NAME,null,values);
        db.close();
}

 

Read

For reading from table we just execute our select query using rawQuery() method of SQLiteDatabase class. This method returns Cursor object that will be used to fetch the records one by one.

public String getRecords(){
        String query="SELECT * FROM "+TABLE_NAME;
        String result="";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(query,null);

        cursor.moveToFirst();
        while(cursor.isAfterLast()==false){
            result+=cursor.getString(0)+" "+cursor.getString(1)+"\n";
            cursor.moveToNext();
        }

        db.close();
        return result;
}

 

Update

The update() method of SQLiteDatabase class is used to perform update operation according to a primary key. In this example id column is the primary key.

public void updateRecord(String id,String name){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(NAME_COL,name);

        db.update(TABLE_NAME,values,"id=?",new String[]{id});
        db.close();
}

 

Delete

The delete operation is performed by delete() method of SQLiteDatabase class according to primary key.

public void deleteRecord(String id){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(TABLE_NAME,"id=?",new String[]{id});

        db.close();
}

 

Android SQLite Database Example

First create a new project with name AndroidSQLite and package name com.androidsqlite.

Add following code in respective files and run the project.

MainActivity.java

package com.androidsqlite;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;


public class MainActivity extends Activity {
    EditText id,name;
    Button insert,view,update,delete;
    TextView text;
    DBHandler db;

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

        id=(EditText)findViewById(R.id.id);
        name=(EditText)findViewById((R.id.name));
        insert=(Button)findViewById(R.id.insert);
        view=(Button)findViewById(R.id.view);
        update=(Button)findViewById(R.id.update);
        delete=(Button)findViewById(R.id.delete);
        text=(TextView)findViewById(R.id.text);

        db=new DBHandler(getApplicationContext());
    }

    public void buttonAction(View view){
        switch (view.getId()){
            case R.id.insert:
                db.insertRecord(name.getText().toString());
                Toast.makeText(getApplicationContext(),"record inserted",Toast.LENGTH_LONG).show();
                break;
            case R.id.view:
                text.setText(db.getRecords());
                break;
            case R.id.update:
                db.updateRecord(id.getText().toString(),name.getText().toString());
                Toast.makeText(getApplicationContext(),"record updated",Toast.LENGTH_LONG).show();
                break;
            case R.id.delete:
                db.deleteRecord(id.getText().toString());
                Toast.makeText(getApplicationContext(),"record deleted",Toast.LENGTH_LONG).show();
                break;
        }
    }
}

 

DBHandler.java

package com.androidsqlite;

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

import java.util.ArrayList;

public class DBHandler extends SQLiteOpenHelper{
    private static final String DB_NAME="demodb";
    private static final int DB_VERSION=1;
    private static final String TABLE_NAME="record";
    private static final String ID_COL="id";
    private static final String NAME_COL="name";

    public DBHandler(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query="CREATE TABLE "+TABLE_NAME+" ("+ID_COL+" INTEGER PRIMARY KEY AUTOINCREMENT,"+NAME_COL+" TEXT)";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

        // Create table again
        onCreate(db);
    }

    public void insertRecord(String name){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();

        values.put(NAME_COL,name);
        db.insert(TABLE_NAME,null,values);
        db.close();
    }

    public String getRecords(){
        String query="SELECT * FROM "+TABLE_NAME;
        String result="";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(query,null);

        cursor.moveToFirst();
        while(cursor.isAfterLast()==false){
            result+=cursor.getString(0)+" "+cursor.getString(1)+"\n";
            cursor.moveToNext();
        }

        db.close();
        return result;
    }

    public void updateRecord(String id,String name){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(NAME_COL,name);

        db.update(TABLE_NAME,values,"id=?",new String[]{id});
        db.close();
    }

    public void deleteRecord(String id){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(TABLE_NAME,"id=?",new String[]{id});

        db.close();
    }
}

 

activity_main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
    android:layout_height="match_parent" android:paddingLeft="15dp"
    android:paddingRight="15dp"
    android:paddingTop="15dp"
    android:paddingBottom="15dp" tools:context=".MainActivity">

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Enter id to update or delete"
        android:id="@+id/id"
        android:onClick="buttonAction"/>

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Enter name to insert or update"
        android:id="@+id/name"
        android:layout_below="@+id/id"
        android:layout_marginTop="10dp"/>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/name"
        android:id="@+id/layout1"
        android:orientation="vertical">
        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/insert"
            android:text="Insert"
            android:onClick="buttonAction"/>

        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/view"
            android:text="View"
            android:onClick="buttonAction"
            android:layout_below="@+id/name"/>

        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Update"
            android:onClick="buttonAction"
            android:id="@+id/update"
            android:layout_below="@+id/name"/>

        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Delete"
            android:onClick="buttonAction"
            android:id="@+id/delete" />

    </LinearLayout>

    <TextView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/layout1"
        android:layout_marginTop="10dp"
        android:id="@+id/text"/>
</RelativeLayout>

 

Output

 

If you are facing any problem related to above android sqlite database tutorial then feel free to comment below. I will try my best to solve your problem.