In Android, there are several ways to store persistent data. SQLite is one way of storing app data. It is very lightweight database that comes with Android OS. In Android, integrating SQLite is a tedious task as it needs writing lot of boilerplate code to store simple data. Consider SQLite when your app needs to store simple data objects. Alternatively you can consider Room Persistence Library for better APIs and easier integration.

In this article we are going to learn basics of SQLite database with a realtime example of Notes App.

1. The Notes App

We are going to create a simple Notes App with SQLite as database storage. The app will be very minimal and will have only one screen to manage the notes.

Below are the screenshots of the app.

android-sqlite-notes-app
android-sqlite-notes-app-edit-note

Now let’s start by creating new project in Android Studio.

2. Creating New Project

1. Create a new project in Android Studio from File β‡’ New Project and select Basic Activity from the templates.

2. Open build.gradle under app directory and add RecyclerView dependency. The RecyclerView will be used to display the Notes in list manner.

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])
    // ..

    implementation 'com.android.support:recyclerview-v7:26.1.0'
}

3. Add the below resources to colors.xml, dimens.xml and strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <color name="colorPrimary">#455399</color>
    <color name="colorPrimaryDark">#455399</color>
    <color name="colorAccent">#00c6ae</color>
    <color name="msg_no_notes">#999</color>
    <color name="hint_enter_note">#89c3c3c3</color>
    <color name="timestamp">#858585</color>
    <color name="note_list_text">#232323</color>
</resources>
<resources>
    <dimen name="fab_margin">16dp</dimen>
    <dimen name="activity_margin">16dp</dimen>
    <dimen name="dot_margin_right">10dp</dimen>
    <dimen name="msg_no_notes">26sp</dimen>
    <dimen name="margin_top_no_notes">120dp</dimen>
    <dimen name="lbl_new_note_title">20sp</dimen>
    <dimen name="dimen_10">10dp</dimen>
    <dimen name="input_new_note">20sp</dimen>
    <dimen name="dot_height">30dp</dimen>
    <dimen name="dot_text_size">40sp</dimen>
    <dimen name="timestamp">14sp</dimen>
    <dimen name="note_list_text">18sp</dimen>
</resources>
<resources>
    <string name="app_name">Notes</string>
    <string name="action_settings">Settings</string>
    <string name="activity_title_home">Notes</string>
    <string name="msg_no_notes">No notes found!</string>
    <string name="lbl_new_note_title">New Note</string>
    <string name="lbl_edit_note_title">Edit Note</string>
    <string name="hint_enter_note">Enter your note!</string>
</resources>

4. Quickly create few packages named database, database/model, utils and view. Below is the final project structure and files we gonna need.

android-sqlite-notes-app-project-structure

2.1 Writing SQLite Helper Class

We need to create a class that extends from SQLiteOpenHelper. This class perform CRUD operations (Create, Read, Update and Delete) on the database.

We also need a model class to create Note objects to manage the notes easily.

5. Under database/model package, create a class named Note.java. In this class we define the SQLite table name, column names and create table SQL query along with getter / setter methods.

  • The `notes` table needs three columns i.e `id`, `note` and `timestamp`.
  • Column `id` is defined as Primary Key and Auto Increment which means each note will be uniquely identified by its id.
  • Column `note` stores the actual note text.
  • Column `timestamp` stores the date and time of the note that is created.
public class Note {
    public static final String TABLE_NAME = "notes";

    public static final String COLUMN_ID = "id";
    public static final String COLUMN_NOTE = "note";
    public static final String COLUMN_TIMESTAMP = "timestamp";

    private int id;
    private String note;
    private String timestamp;


    // Create table SQL query
    public static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + COLUMN_NOTE + " TEXT,"
                    + COLUMN_TIMESTAMP + " DATETIME DEFAULT CURRENT_TIMESTAMP"
                    + ")";

    public Note() {
    }

    public Note(int id, String note, String timestamp) {
        this.id = id;
        this.note = note;
        this.timestamp = timestamp;
    }

    public int getId() {
        return id;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public String getTimestamp() {
        return timestamp;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setTimestamp(String timestamp) {
        this.timestamp = timestamp;
    }
}

6. Under database package, create a class named DatabaseHelper.java and extend the class from SQLiteOpenHelper. This class holds the database related methods to perform the CRUD operations.

  • onCreate() will be called only once when the app is installed. In this method, we execute the create table sql statements to create necessary tables.
  • onUpgrade() called when an update is released. You need to modify the DATABASE_VERSION in order to execute this method. You have to take care of database migrations here without loosing the older data if necessary. For now, we just drop the older tables and recreate them again.
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;
import java.util.List;

import info.androidhive.sqlite.database.model.Note;

/**
 * Created by ravi on 15/03/18.
 */

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "notes_db";


    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        // create notes table
        db.execSQL(Note.CREATE_TABLE);
    }

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

        // Create tables again
        onCreate(db);
    }
}

Now we’ll see the methods required to store or retrieve the notes. Add the following methods to same class.

a. Inserting Note

Inserting data requires getting writable instance (getReadableDatabase()) on database. Below, we are inserting new note in database.

  • ContentValues() is used to define the column name and its data to be stored. Here, we are just setting the note value only ignoring `id` and `timestamp` as these two will be inserted automatically.
  • Every time the database connection has to be closed once you are done with database access. Calling db.close() closes the connection.
  • Once the note is inserted, the `id` of newly inserted note will be returned.
    public long insertNote(String note) {
        // get writable database as we want to write data
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        // `id` and `timestamp` will be inserted automatically.
        // no need to add them
        values.put(Note.COLUMN_NOTE, note);

        // insert row
        long id = db.insert(Note.TABLE_NAME, null, values);

        // close db connection
        db.close();

        // return newly inserted row id
        return id;
    }

b. Reading Notes

Reading data requires only read access (getReadableDatabase()) on the database.

  • getNote() takes already existed note `id` and fetches the note object.
  • getAllNotes() fetches all the notes in descending order by timestamp.
  • getNotesCount() returns the count of notes stored in database.
    public Note getNote(long id) {
        // get readable database as we are not inserting anything
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(Note.TABLE_NAME,
                new String[]{Note.COLUMN_ID, Note.COLUMN_NOTE, Note.COLUMN_TIMESTAMP},
                Note.COLUMN_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);

        if (cursor != null)
            cursor.moveToFirst();

        // prepare note object
        Note note = new Note(
                cursor.getInt(cursor.getColumnIndex(Note.COLUMN_ID)),
                cursor.getString(cursor.getColumnIndex(Note.COLUMN_NOTE)),
                cursor.getString(cursor.getColumnIndex(Note.COLUMN_TIMESTAMP)));

        // close the db connection
        cursor.close();

        return note;
    }

    public List<Note> getAllNotes() {
        List<Note> notes = new ArrayList<>();

        // Select All Query
        String selectQuery = "SELECT  * FROM " + Note.TABLE_NAME + " ORDER BY " +
                Note.COLUMN_TIMESTAMP + " DESC";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getInt(cursor.getColumnIndex(Note.COLUMN_ID)));
                note.setNote(cursor.getString(cursor.getColumnIndex(Note.COLUMN_NOTE)));
                note.setTimestamp(cursor.getString(cursor.getColumnIndex(Note.COLUMN_TIMESTAMP)));

                notes.add(note);
            } while (cursor.moveToNext());
        }

        // close db connection
        db.close();

        // return notes list
        return notes;
    }

    public int getNotesCount() {
        String countQuery = "SELECT  * FROM " + Note.TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = cursor.getCount();
        cursor.close();


        // return count
        return count;
    }

c. Updating Note

Updating data again requires writable access. Below the note is updated by its `id`.

    public int updateNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Note.COLUMN_NOTE, note.getNote());

        // updating row
        return db.update(Note.TABLE_NAME, values, Note.COLUMN_ID + " = ?",
                new String[]{String.valueOf(note.getId())});
    }

d. Deleting Note

Deleting data also requires writable access. Below method deletes a note by finding its `id`.

    public void deleteNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(Note.TABLE_NAME, Note.COLUMN_ID + " = ?",
                new String[]{String.valueOf(note.getId())});
        db.close();
    }

After adding all the methods, the DatabaseHelper.java class should be like this.

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;
import java.util.List;

import info.androidhive.sqlite.database.model.Note;

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "notes_db";


    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        // create notes table
        db.execSQL(Note.CREATE_TABLE);
    }

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

        // Create tables again
        onCreate(db);
    }

    public long insertNote(String note) {
        // get writable database as we want to write data
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        // `id` and `timestamp` will be inserted automatically.
        // no need to add them
        values.put(Note.COLUMN_NOTE, note);

        // insert row
        long id = db.insert(Note.TABLE_NAME, null, values);

        // close db connection
        db.close();

        // return newly inserted row id
        return id;
    }

    public Note getNote(long id) {
        // get readable database as we are not inserting anything
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(Note.TABLE_NAME,
                new String[]{Note.COLUMN_ID, Note.COLUMN_NOTE, Note.COLUMN_TIMESTAMP},
                Note.COLUMN_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);

        if (cursor != null)
            cursor.moveToFirst();

        // prepare note object
        Note note = new Note(
                cursor.getInt(cursor.getColumnIndex(Note.COLUMN_ID)),
                cursor.getString(cursor.getColumnIndex(Note.COLUMN_NOTE)),
                cursor.getString(cursor.getColumnIndex(Note.COLUMN_TIMESTAMP)));

        // close the db connection
        cursor.close();

        return note;
    }

    public List<Note> getAllNotes() {
        List<Note> notes = new ArrayList<>();

        // Select All Query
        String selectQuery = "SELECT  * FROM " + Note.TABLE_NAME + " ORDER BY " +
                Note.COLUMN_TIMESTAMP + " DESC";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Note note = new Note();
                note.setId(cursor.getInt(cursor.getColumnIndex(Note.COLUMN_ID)));
                note.setNote(cursor.getString(cursor.getColumnIndex(Note.COLUMN_NOTE)));
                note.setTimestamp(cursor.getString(cursor.getColumnIndex(Note.COLUMN_TIMESTAMP)));

                notes.add(note);
            } while (cursor.moveToNext());
        }

        // close db connection
        db.close();

        // return notes list
        return notes;
    }

    public int getNotesCount() {
        String countQuery = "SELECT  * FROM " + Note.TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = cursor.getCount();
        cursor.close();


        // return count
        return count;
    }

    public int updateNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Note.COLUMN_NOTE, note.getNote());

        // updating row
        return db.update(Note.TABLE_NAME, values, Note.COLUMN_ID + " = ?",
                new String[]{String.valueOf(note.getId())});
    }

    public void deleteNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(Note.TABLE_NAME, Note.COLUMN_ID + " = ?",
                new String[]{String.valueOf(note.getId())});
        db.close();
    }
}

7. Under utils package, create two classes named RecyclerTouchListener.java and MyDividerItemDecoration.java

  • RecyclerTouchListener class adds touch event to RecyclerView row.
  • MyDividerItemDecoration class adds divider line between rows.
import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.GestureDetector;
import android.view.MotionEvent;
import android.view.View;

/**
 * Created by ravi on 21/02/18.
 */

public class RecyclerTouchListener implements RecyclerView.OnItemTouchListener {

    private ClickListener clicklistener;
    private GestureDetector gestureDetector;

    public RecyclerTouchListener(Context context, final RecyclerView recycleView, final ClickListener clicklistener) {

        this.clicklistener = clicklistener;
        gestureDetector = new GestureDetector(context, new GestureDetector.SimpleOnGestureListener() {
            @Override
            public boolean onSingleTapUp(MotionEvent e) {
                return true;
            }

            @Override
            public void onLongPress(MotionEvent e) {
                View child = recycleView.findChildViewUnder(e.getX(), e.getY());
                if (child != null && clicklistener != null) {
                    clicklistener.onLongClick(child, recycleView.getChildAdapterPosition(child));
                }
            }
        });
    }

    @Override
    public boolean onInterceptTouchEvent(RecyclerView rv, MotionEvent e) {
        View child = rv.findChildViewUnder(e.getX(), e.getY());
        if (child != null && clicklistener != null && gestureDetector.onTouchEvent(e)) {
            clicklistener.onClick(child, rv.getChildAdapterPosition(child));
        }

        return false;
    }

    @Override
    public void onTouchEvent(RecyclerView rv, MotionEvent e) {

    }

    @Override
    public void onRequestDisallowInterceptTouchEvent(boolean disallowIntercept) {

    }

    public interface ClickListener {
        void onClick(View view, int position);

        void onLongClick(View view, int position);
    }
}
import android.content.Context;
import android.content.res.Resources;
import android.content.res.TypedArray;
import android.graphics.Canvas;
import android.graphics.Rect;
import android.graphics.drawable.Drawable;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.util.TypedValue;
import android.view.View;

public class MyDividerItemDecoration extends RecyclerView.ItemDecoration {

    private static final int[] ATTRS = new int[]{
            android.R.attr.listDivider
    };

    public static final int HORIZONTAL_LIST = LinearLayoutManager.HORIZONTAL;
    public static final int VERTICAL_LIST = LinearLayoutManager.VERTICAL;

    private Drawable mDivider;
    private int mOrientation;
    private Context context;
    private int margin;

    public MyDividerItemDecoration(Context context, int orientation, int margin) {
        this.context = context;
        this.margin = margin;
        final TypedArray a = context.obtainStyledAttributes(ATTRS);
        mDivider = a.getDrawable(0);
        a.recycle();
        setOrientation(orientation);
    }

    public void setOrientation(int orientation) {
        if (orientation != HORIZONTAL_LIST && orientation != VERTICAL_LIST) {
            throw new IllegalArgumentException("invalid orientation");
        }
        mOrientation = orientation;
    }

    @Override
    public void onDrawOver(Canvas c, RecyclerView parent, RecyclerView.State state) {
        if (mOrientation == VERTICAL_LIST) {
            drawVertical(c, parent);
        } else {
            drawHorizontal(c, parent);
        }
    }

    public void drawVertical(Canvas c, RecyclerView parent) {
        final int left = parent.getPaddingLeft();
        final int right = parent.getWidth() - parent.getPaddingRight();

        final int childCount = parent.getChildCount();
        for (int i = 0; i < childCount; i++) {
            final View child = parent.getChildAt(i);
            final RecyclerView.LayoutParams params = (RecyclerView.LayoutParams) child
                    .getLayoutParams();
            final int top = child.getBottom() + params.bottomMargin;
            final int bottom = top + mDivider.getIntrinsicHeight();
            mDivider.setBounds(left + dpToPx(margin), top, right - dpToPx(margin), bottom);
            mDivider.draw(c);
        }
    }

    public void drawHorizontal(Canvas c, RecyclerView parent) {
        final int top = parent.getPaddingTop();
        final int bottom = parent.getHeight() - parent.getPaddingBottom();

        final int childCount = parent.getChildCount();
        for (int i = 0; i < childCount; i++) {
            final View child = parent.getChildAt(i);
            final RecyclerView.LayoutParams params = (RecyclerView.LayoutParams) child
                    .getLayoutParams();
            final int left = child.getRight() + params.rightMargin;
            final int right = left + mDivider.getIntrinsicHeight();
            mDivider.setBounds(left, top + dpToPx(margin), right, bottom - dpToPx(margin));
            mDivider.draw(c);
        }
    }

    @Override
    public void getItemOffsets(Rect outRect, View view, RecyclerView parent, RecyclerView.State state) {
        if (mOrientation == VERTICAL_LIST) {
            outRect.set(0, 0, 0, mDivider.getIntrinsicHeight());
        } else {
            outRect.set(0, 0, mDivider.getIntrinsicWidth(), 0);
        }
    }

    private int dpToPx(int dp) {
        Resources r = context.getResources();
        return Math.round(TypedValue.applyDimension(TypedValue.COMPLEX_UNIT_DIP, dp, r.getDisplayMetrics()));
    }
}

3. Adding Notes UI

Now we have the database helper class ready. Let’s quickly build the main interface and integrate it with the database.

First we need an adapter to display the notes in list manner. For this, we need a layout file and Adapter class.

8. Create new xml layout named note_list_row.xml. This layout holds the design of single note item in the list.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:clickable="true"
    android:foreground="?attr/selectableItemBackground"
    android:paddingBottom="@dimen/dimen_10"
    android:paddingLeft="@dimen/activity_margin"
    android:paddingRight="@dimen/activity_margin"
    android:paddingTop="@dimen/dimen_10">

    <TextView
        android:id="@+id/dot"
        android:layout_width="wrap_content"
        android:layout_height="@dimen/dot_height"
        android:layout_marginRight="@dimen/dot_margin_right"
        android:layout_marginTop="@dimen/dimen_10"
        android:includeFontPadding="false"
        android:textColor="@color/colorAccent"
        android:lineSpacingExtra="0dp"
        android:textSize="@dimen/dot_text_size" />

    <TextView
        android:id="@+id/timestamp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_toRightOf="@id/dot"
        android:textColor="@color/timestamp"
        android:textSize="@dimen/timestamp" />

    <TextView
        android:id="@+id/note"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@id/timestamp"
        android:layout_toRightOf="@id/dot"
        android:textColor="@color/note_list_text"
        android:textSize="@dimen/note_list_text" />

</RelativeLayout>

9. Under view package, create a class named NotesAdapter.java. This adapter class renders the RecyclerView with defined layout and data set.

import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.text.Html;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import info.androidhive.sqlite.R;
import info.androidhive.sqlite.database.model.Note;

public class NotesAdapter extends RecyclerView.Adapter<NotesAdapter.MyViewHolder> {

    private Context context;
    private List<Note> notesList;

    public class MyViewHolder extends RecyclerView.ViewHolder {
        public TextView note;
        public TextView dot;
        public TextView timestamp;

        public MyViewHolder(View view) {
            super(view);
            note = view.findViewById(R.id.note);
            dot = view.findViewById(R.id.dot);
            timestamp = view.findViewById(R.id.timestamp);
        }
    }


    public NotesAdapter(Context context, List<Note> notesList) {
        this.context = context;
        this.notesList = notesList;
    }

    @Override
    public MyViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        View itemView = LayoutInflater.from(parent.getContext())
                .inflate(R.layout.note_list_row, parent, false);

        return new MyViewHolder(itemView);
    }

    @Override
    public void onBindViewHolder(MyViewHolder holder, int position) {
        Note note = notesList.get(position);

        holder.note.setText(note.getNote());

        // Displaying dot from HTML character code
        holder.dot.setText(Html.fromHtml("&#8226;"));

        // Formatting and displaying timestamp
        holder.timestamp.setText(formatDate(note.getTimestamp()));
    }

    @Override
    public int getItemCount() {
        return notesList.size();
    }

    /**
     * Formatting timestamp to `MMM d` format
     * Input: 2018-02-21 00:15:42
     * Output: Feb 21
     */
    private String formatDate(String dateStr) {
        try {
            SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date = fmt.parse(dateStr);
            SimpleDateFormat fmtOut = new SimpleDateFormat("MMM d");
            return fmtOut.format(date);
        } catch (ParseException e) {

        }

        return "";
    }
}

3.1 Adding Create / Edit Note Dialog

If you observe the app design, a note is created or updated using a Dialog. So, we need to create a custom layout with EditText input and inflate it in AlertDialog.

10. Create another layout named note_dialog.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:paddingLeft="@dimen/activity_margin"
    android:paddingRight="@dimen/activity_margin"
    android:paddingTop="@dimen/activity_margin">

    <TextView android:id="@+id/dialog_title"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="@dimen/dimen_10"
        android:fontFamily="sans-serif-medium"
        android:lineSpacingExtra="8sp"
        android:text="@string/lbl_new_note_title"
        android:textColor="@color/colorAccent"
        android:textSize="@dimen/lbl_new_note_title"
        android:textStyle="normal" />

    <EditText
        android:id="@+id/note"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@android:color/transparent"
        android:gravity="top"
        android:hint="@string/hint_enter_note"
        android:inputType="textCapSentences|textMultiLine"
        android:lines="4"
        android:textColorHint="@color/hint_enter_note"
        android:textSize="@dimen/input_new_note" />

</LinearLayout>

11. Open the layout files of main activity (activity_main.xml and content_main.xml) and add RecyclerView widget. I am also changing the icon of FAB here.

<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/coordinator_layout"
    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="info.androidhive.sqlite.view.MainActivity">

    <android.support.design.widget.AppBarLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:theme="@style/AppTheme.AppBarOverlay">

        <android.support.v7.widget.Toolbar
            android:id="@+id/toolbar"
            android:layout_width="match_parent"
            android:layout_height="?attr/actionBarSize"
            android:background="?attr/colorPrimary"
            app:popupTheme="@style/AppTheme.PopupOverlay" />

    </android.support.design.widget.AppBarLayout>

    <include layout="@layout/content_main" />

    <android.support.design.widget.FloatingActionButton
        android:id="@+id/fab"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="bottom|end"
        android:layout_margin="@dimen/fab_margin"
        app:srcCompat="@drawable/ic_add_white_24dp" />

</android.support.design.widget.CoordinatorLayout>
<?xml version="1.0" encoding="utf-8"?>
<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"
    app:layout_behavior="@string/appbar_scrolling_view_behavior"
    tools:context="info.androidhive.sqlite.view.MainActivity"
    tools:showIn="@layout/activity_main">

    <android.support.v7.widget.RecyclerView
        android:id="@+id/recycler_view"
        android:layout_width="match_parent"
        android:layout_height="match_parent" />

    <TextView
        android:id="@+id/empty_notes_view"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="@dimen/margin_top_no_notes"
        android:fontFamily="sans-serif-light"
        android:text="@string/msg_no_notes"
        android:textColor="@color/msg_no_notes"
        android:textSize="@dimen/msg_no_notes" />

</RelativeLayout>

12. Finally open MainActivity.java and do the below changes.

  • showNoteDialog() open the alert dialog to create new note. This dialog will be shown by tapping FAB.
  • createNote() inserts new note in database and adds the newly inserted note in RecyclerView list.
  • showActionsDialog() shows a dialog with Edit and Delete options. This dialog can be invoked by long pressing the note in the list.
  • Selecting Edit, opens the update note dialog with already existed note text. You can modify the note text and update it in database by calling updateNote() method.
  • deleteNote() deletes a note from database. The deleted note is again removed from list by calling notifyItemRemoved() on adapter.
  • toggleEmptyNotes() toggles the visibility of notes and empty note view depending on the count (db.getNotesCount() > 0) of notes.
import android.content.DialogInterface;
import android.os.Bundle;
import android.support.design.widget.CoordinatorLayout;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.DefaultItemAnimator;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.support.v7.widget.Toolbar;
import android.text.TextUtils;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

import info.androidhive.sqlite.R;
import info.androidhive.sqlite.database.DatabaseHelper;
import info.androidhive.sqlite.database.model.Note;
import info.androidhive.sqlite.utils.MyDividerItemDecoration;
import info.androidhive.sqlite.utils.RecyclerTouchListener;

public class MainActivity extends AppCompatActivity {
    private NotesAdapter mAdapter;
    private List<Note> notesList = new ArrayList<>();
    private CoordinatorLayout coordinatorLayout;
    private RecyclerView recyclerView;
    private TextView noNotesView;

    private DatabaseHelper db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        coordinatorLayout = findViewById(R.id.coordinator_layout);
        recyclerView = findViewById(R.id.recycler_view);
        noNotesView = findViewById(R.id.empty_notes_view);

        db = new DatabaseHelper(this);

        notesList.addAll(db.getAllNotes());

        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                showNoteDialog(false, null, -1);
            }
        });

        mAdapter = new NotesAdapter(this, notesList);
        RecyclerView.LayoutManager mLayoutManager = new LinearLayoutManager(getApplicationContext());
        recyclerView.setLayoutManager(mLayoutManager);
        recyclerView.setItemAnimator(new DefaultItemAnimator());
        recyclerView.addItemDecoration(new MyDividerItemDecoration(this, LinearLayoutManager.VERTICAL, 16));
        recyclerView.setAdapter(mAdapter);

        toggleEmptyNotes();

        /**
         * On long press on RecyclerView item, open alert dialog
         * with options to choose
         * Edit and Delete
         * */
        recyclerView.addOnItemTouchListener(new RecyclerTouchListener(this,
                recyclerView, new RecyclerTouchListener.ClickListener() {
            @Override
            public void onClick(View view, final int position) {
            }

            @Override
            public void onLongClick(View view, int position) {
                showActionsDialog(position);
            }
        }));
    }

    /**
     * Inserting new note in db
     * and refreshing the list
     */
    private void createNote(String note) {
        // inserting note in db and getting
        // newly inserted note id
        long id = db.insertNote(note);

        // get the newly inserted note from db
        Note n = db.getNote(id);

        if (n != null) {
            // adding new note to array list at 0 position
            notesList.add(0, n);

            // refreshing the list
            mAdapter.notifyDataSetChanged();

            toggleEmptyNotes();
        }
    }

    /**
     * Updating note in db and updating
     * item in the list by its position
     */
    private void updateNote(String note, int position) {
        Note n = notesList.get(position);
        // updating note text
        n.setNote(note);

        // updating note in db
        db.updateNote(n);

        // refreshing the list
        notesList.set(position, n);
        mAdapter.notifyItemChanged(position);

        toggleEmptyNotes();
    }

    /**
     * Deleting note from SQLite and removing the
     * item from the list by its position
     */
    private void deleteNote(int position) {
        // deleting the note from db
        db.deleteNote(notesList.get(position));

        // removing the note from the list
        notesList.remove(position);
        mAdapter.notifyItemRemoved(position);

        toggleEmptyNotes();
    }

    /**
     * Opens dialog with Edit - Delete options
     * Edit - 0
     * Delete - 0
     */
    private void showActionsDialog(final int position) {
        CharSequence colors[] = new CharSequence[]{"Edit", "Delete"};

        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setTitle("Choose option");
        builder.setItems(colors, new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                if (which == 0) {
                    showNoteDialog(true, notesList.get(position), position);
                } else {
                    deleteNote(position);
                }
            }
        });
        builder.show();
    }

    /**
     * Shows alert dialog with EditText options to enter / edit
     * a note.
     * when shouldUpdate=true, it automatically displays old note and changes the
     * button text to UPDATE
     */
    private void showNoteDialog(final boolean shouldUpdate, final Note note, final int position) {
        LayoutInflater layoutInflaterAndroid = LayoutInflater.from(getApplicationContext());
        View view = layoutInflaterAndroid.inflate(R.layout.note_dialog, null);

        AlertDialog.Builder alertDialogBuilderUserInput = new AlertDialog.Builder(MainActivity.this);
        alertDialogBuilderUserInput.setView(view);

        final EditText inputNote = view.findViewById(R.id.note);
        TextView dialogTitle = view.findViewById(R.id.dialog_title);
        dialogTitle.setText(!shouldUpdate ? getString(R.string.lbl_new_note_title) : getString(R.string.lbl_edit_note_title));

        if (shouldUpdate && note != null) {
            inputNote.setText(note.getNote());
        }
        alertDialogBuilderUserInput
                .setCancelable(false)
                .setPositiveButton(shouldUpdate ? "update" : "save", new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialogBox, int id) {

                    }
                })
                .setNegativeButton("cancel",
                        new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialogBox, int id) {
                                dialogBox.cancel();
                            }
                        });

        final AlertDialog alertDialog = alertDialogBuilderUserInput.create();
        alertDialog.show();

        alertDialog.getButton(AlertDialog.BUTTON_POSITIVE).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // Show toast message when no text is entered
                if (TextUtils.isEmpty(inputNote.getText().toString())) {
                    Toast.makeText(MainActivity.this, "Enter note!", Toast.LENGTH_SHORT).show();
                    return;
                } else {
                    alertDialog.dismiss();
                }

                // check if user updating note
                if (shouldUpdate && note != null) {
                    // update note by it's id
                    updateNote(inputNote.getText().toString(), position);
                } else {
                    // create new note
                    createNote(inputNote.getText().toString());
                }
            }
        });
    }

    /**
     * Toggling list and empty notes view
     */
    private void toggleEmptyNotes() {
        // you can check notesList.size() > 0

        if (db.getNotesCount() > 0) {
            noNotesView.setVisibility(View.GONE);
        } else {
            noNotesView.setVisibility(View.VISIBLE);
        }
    }
}

If you have followed the article carefully, you can see the app running very smoothly as shown in the video demo.

android-sqlite-notes-app

5. Further Reading

Once you are comfortable with SQLite, check out Android SQLite Database with Multiple Tables that explains how to handle SQLite when your app needs more than one table.

Happy Coding πŸ™‚

Change Log

Updated On 16th Mar 2018 (Updated code and introduced Notes App)
Subscribe
Notify of
guest
791 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback

[…] Android SQLite Database Tutorial | AndroidHive | Tutorials, Games, Apps, Tips Androidhive.info is a blog maintained by Ravi Tamada focused on Android tutorials… Source: http://www.androidhive.info […]

trackback

[…] Android tutorial about handling with SQLite database operations with an example.    Database Read the original post on DZone… […]

trackback

[…] Database Tutorial […]

trackback
8 years ago

[…] actually of the defined type, e.g. you can write an integer into a string column and vice versa. Android SQLite Database Tutorial This tutorial introduces a variety of widgets that are useful when creating forms, such as image […]

trackback
8 years ago

[…] Reference:Β Android SQLite Database Tutorial GD Star Ratingloading… […]

Arise Rayamangalam
Arise Rayamangalam
7 years ago

A problem with this type of method is if you want to add data say 100 nos then your code look shabby and it will be very difficult tot enter the data. You can use another method by which we can enter the data using a Database Manager and later attach it to the Application. A simple tutorial with source code is given in the following link
http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html

Guest
Guest
7 years ago

Could I use this database to store dynamic fields such as latitude and longitude (for my app) which gets refreshed every 10 sec. Is there a way to retrieve this data on a web server ?

Mohammad Alhobayyeb
7 years ago

Your tutorial is simple and clear.

I tried Vogella’s tutorial about SQLite but I get lost with it, although he is a good tutor in other parts, but you beated him in SQLite part. πŸ™‚

Thank you

Nirmal
Nirmal
7 years ago

you r right ,,he will give Some half half code,,so we confuse that data is going where

madV
madV
7 years ago
Reply to  Nirmal

just awsome man… πŸ™‚

anjali
anjali
7 years ago

good.

anjali
anjali
7 years ago

it is good. but i have one doubt. how to creae multiple tables

Asif Hasan
7 years ago

Good example But how to add value like Email adress?

Giannis
Giannis
7 years ago

Hi i get an error: table contacts has no column named phone, i run it in API level 15 i am really stack and need ASAP help..please if anyone know asnwer me here or in my email: gveron@gmail.com

david molina
7 years ago
Reply to  Giannis

Did you make sure that you are creating that column in your table and is the exact name(spelling, capitals, etc) I run into a similar problem but mine was resolved by adding onCreate before manipulating the data, that way I make sure that if for any reason the table does not exists it will be crated first and I will not get any errors.

Garry Hickey
Garry Hickey
7 years ago
Reply to  Giannis

having same problem, says the column doesnt exist, why?

Akheloes
Akheloes
7 years ago

Just a benign thought : isn’t that the whole purpose of a database ?

me
me
7 years ago

Can you give an example in Usage of how updateContact works

Noah
Noah
7 years ago

Nice! However you can simplify the somewhat awkward

if (cursor.moveToFirst()) { do … while (cursor.moveToNext()); }

with a much cleaner

while (cursor.moveToNext()) { … }

Since the query / rawQuery moves the cursor before the first entry.

Shahil Modan
Shahil Modan
7 years ago

Nice Tutorial i am new in android its help me a lot ….thank you !!!!!

kushi
kushi
7 years ago

helloo sir,

it has helped me very much thank you.but i do have got a problem please resolve my problem..

we r getting a o/p in logcat but i want to display data in listview that what ever the data stored in sqlite database? please help me
thanks in advance

A guest
A guest
7 years ago

what is path of DB? where can i found my SQLite file here?

MAJK
MAJK
7 years ago
Reply to  A guest

Yeah, what is the path of database? Please answer as soon as possible.

BTW. Very useful tutorial !

Tonyoh87
Tonyoh87
7 years ago

Hey great tutorial.

I imported the project but I get the following errors:

[2013-05-05 17:34:18 – DemoActivity] Application package ‘AndroidManifest.xml’ must have a minimum of 2 segments.

[2013-05-05 17:37:58 – Database] Error in an XML file: aborting build.

[2013-05-05 18:18:19 – Database] W/ResourceType( 8832): Bad XML block: header size 92 or total size 0 is larger than data size 0

[2013-05-05 18:18:19 – Database] C:UsersTonyoh87workspaceDatabasereslayoutactivity_main.xml:6: error: Error: No resource found that matches the given name (at ‘text’ with value ‘@string/hello_world’).

[2013-05-05 18:18:19 – Database] C:UsersTonyoh87workspaceDatabaseresmenuactivity_main.xml:2: error: Error: No resource found that matches the given name (at ‘title’ with value ‘@string/menu_settings’).

[2013-05-05 18:19:28 – Database] W/ResourceType( 3340): Bad XML block: header size 119 or total size 0 is larger than data size 0

[2013-05-05 18:19:28 – Database] C:UsersTonyoh87workspaceDatabasereslayoutactivity_main.xml:6: error: Error: No resource found that matches the given name (at ‘text’ with value ‘@string/hello_world’).

[2013-05-05 18:19:28 – Database] C:UsersTonyoh87workspaceDatabaseresmenuactivity_main.xml:2: error: Error: No resource found that matches the given name (at ‘title’ with value ‘@string/menu_settings’).

[2013-05-05 18:29:12 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

[2013-05-05 18:30:24 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

[2013-05-05 18:31:51 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

[2013-05-05 18:34:04 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

[2013-05-05 18:38:10 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

Can you help me about this ?

nida
nida
7 years ago
Reply to  Tonyoh87

how to use “getAllContacts()” to display all user contacts in listview i m facing difficulty to do this

Tonyoh87
Tonyoh87
7 years ago

fixed, please ignore my request πŸ™‚

Tonyoh87
Tonyoh87
7 years ago

stupid question but how do we use the database ? I’m looking to use it for making a language application

S
S
7 years ago

how value in id Column are generated and inserted

Deepu S.A
Deepu S.A
7 years ago

This is what a tutorial should be. I have nothing more to say. just perfect!.

Ali Fattahi
Ali Fattahi
7 years ago

Hello

Thank you for your useful post , i have a question aboud sqlite

if I already have a sqlite database file how can I use in my project ?

Best Regards
Ali

Guest
Guest
7 years ago

a small correction : onUpgrage() shoudl be onUpgrade()

Shan
Shan
7 years ago

Great post ! A small correction : onUpgrage() shoudl be onUpgrade()

vsdf
vsdf
7 years ago
Reply to  Shan

Shoudl Should be Should

Guest
Guest
7 years ago
Reply to  vsdf

Thanks for pointing that out too! πŸ™‚

Shan
Shan
7 years ago
Reply to  vsdf

Thanks for pointing that out too! It should be ‘should’ not ‘Should’ πŸ™‚

yasith
yasith
7 years ago

This helped me πŸ™‚ Thanks for writing the post.

Tejas
Tejas
7 years ago

does any body know about maximum data base file size in(1.2 mb ) Assets folder.
will it cause any problem. What if it increases more than 1 mb. I know how to implement this. but is there Any disadvantages of using it.

Sanjeev
Sanjeev
7 years ago

Expecting content provider tutorial as good as this one soon sir…

anil
anil
7 years ago

Thanks for this tutorial ……………..

anil
anil
7 years ago

plz tell me How to display data from sqlite in textview.

majid
majid
7 years ago

thanks for sample and good exmple.

Allen
Allen
7 years ago

Ravi,

Your getContact(int id) method will crash if there are no rows for the specified id. The returned Cursor will never be null, but it could be empty. Cursor.moveToFirst() returns false if the Cursor is empty, so the if condition should be:

if (Cursor.moveToFirst()) {}

instead of:

if (cursor != null) {}

Thanks for the tutorial, Ravi!

Milana
Milana
7 years ago

Hi Ravi,

can you please help me on coding of how to tag a photo which is in image view

Sanjay Mangroliya
Sanjay Mangroliya
7 years ago

Very Nice Example………..

Zach
Zach
7 years ago

Best tutorial for this I have seen, thanks!

Sandeep Saini
Sandeep Saini
7 years ago

how to update the table with modified values…..????
and where to pass the ID…..??

Sadegh ghanbari
Sadegh ghanbari
7 years ago

Nice example ,thank you.
http://www.AndroidSoftware.ir

Ryl
Ryl
7 years ago

Hello
I am new with Android development..may i Know exactly how to write the output to textview ?

PrakashK Gowin
PrakashK Gowin
7 years ago
Reply to  Ryl

Use Edittext attribute in your .xml file

ryl
ryl
7 years ago
Reply to  PrakashK Gowin

Solution by Ravi in previous comments
// setting name in textview

TextView txtName = (TextView) findViewById(R.id.txtName);

// display name
txtName.setText(person.getName());

Do you knw how to share this contact database detail in another activity?

ryl
ryl
7 years ago

Hi Ravi, I would like to ask how can i share the contact database details in another activities?

PrakashK Gowin
PrakashK Gowin
7 years ago

Hi Ravi, Could You plz update us with some Apache Cordova (Phone Gap)Examples..

Saurabh Singh
Saurabh Singh
7 years ago

nice example of data base–
but when i am doing some changes. on this code……..
like giving data from EditText
I am heaving a meney problem
…………………….

polash
polash
7 years ago

Nice Tutorial……….. πŸ™‚

adnan
adnan
7 years ago

hi, good tutorial but i have got a problem. i am just adding and reading data but when i run it event log doesn’t show something and emulator shows “Closed un expectedly” (i made some xml that why m telling about emulator).

Moorthy
Moorthy
7 years ago

hi ravi.

nice tutorial,

wondering how to get Id of the record. am getting error

Thiago Borges
Thiago Borges
7 years ago

It doesn’t work for me. I’m getting:

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.androidhive.androidsqlite/com.androidhive.androidsqlite.AndroidSQLiteTutorialActivity}: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.androidhive.androidsqlite/databases/contactsManager

android fan
android fan
7 years ago

good tutorial… πŸ™‚

Akhil
Akhil
7 years ago

Hi…Can we use on Database Handler class for more than one table

Joseph
Joseph
7 years ago

Nice tutorial, Only have a question: Where’s saved the SqLite db ? I want to take out for processing

syang
syang
7 years ago

How are the “ids” generated?

791
0
Would love your thoughts, please comment.x
()
x