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)
Ravi is hardcore Android programmer and Android programming has been his passion since he compiled his first hello-world program. Solving real problems of Android developers through tutorials has always been interesting part for him.
  • Pingback: Android SQLite Database Tutorial | AndroidHive | Tutorials, Games, Apps, Tips | Android Development for all | Scoop.it()

  • Pingback: Android SQLite Database Tutorial | Database | Syngu()

  • Pingback: Get acces to a login system, Android | PHP Developer Resource()

  • Pingback: Android | Pearltrees()

  • Pingback: Threading()

  • Arise Rayamangalam

    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

    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 ?

  • 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

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

      • madV

        just awsome man… ๐Ÿ™‚

  • anjali

    good.

  • anjali

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

  • Good example But how to add value like Email adress?

  • Giannis

    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

    • 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

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

  • Akheloes

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

  • me

    Can you give an example in Usage of how updateContact works

  • Noah

    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

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

  • kushi

    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

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

    • MAJK

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

      BTW. Very useful tutorial !

  • Tonyoh87

    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

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

  • Tonyoh87

    fixed, please ignore my request ๐Ÿ™‚

  • Tonyoh87

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

  • S

    how value in id Column are generated and inserted

  • Deepu S.A

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

  • Ali Fattahi

    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

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

  • Shan

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

    • vsdf

      Shoudl Should be Should

      • Guest

        Thanks for pointing that out too! ๐Ÿ™‚

      • Shan

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

  • yasith

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

  • Tejas

    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

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

  • anil

    Thanks for this tutorial ……………..

  • anil

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

  • majid

    thanks for sample and good exmple.

  • Allen

    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

    Hi Ravi,

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

  • Sanjay Mangroliya

    Very Nice Example………..

  • Zach

    Best tutorial for this I have seen, thanks!

  • Sandeep Saini

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

  • Sadegh ghanbari

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

  • Ryl

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

    • PrakashK Gowin

      Use Edittext attribute in your .xml file

      • ryl

        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

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

  • PrakashK Gowin

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

  • Saurabh Singh

    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

    Nice Tutorial……….. ๐Ÿ™‚

  • adnan

    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

    hi ravi.

    nice tutorial,

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

  • Thiago Borges

    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

    good tutorial… ๐Ÿ™‚

  • Akhil

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

  • Joseph

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

  • syang

    How are the “ids” generated?

  • Rowan

    Fantastic tutorial. Worked perfectly.

  • Dan

    Is it possible to view the tables in the database?

    • Jake

      yes it is. Download SQLite plug-in from Mozilla Firefox browser click tools>there you get the SQLite Manager. But download first. Thanks.

  • volume_8091

    And how can we save the table in a different file so that the user could save changes ?

  • raju

    BRO Hats off for ur work, really its superb……..
    the coding snippets and the way u explain the concepts in such an easy way and to the point its really admirable…………….

  • madV

    Awesome tut man.. ๐Ÿ™‚

  • Nilima Nandagavali

    Thanks for this…..
    Good Example!!!! :-))))

    • ASH

      i got this error in handler lass .how we can fix it?

  • ASH

    Multiple markers at this line
    – Contact cannot be resolved to a type
    – Contact cannot be resolved to a type
    – No enclosing instance of type contact is accessible. Must qualify the allocation with an enclosing instance
    of type contact (e.g. x.new A() where x is an instance of contact).

    HOW WE CAN OVERCOME THIS ERROR

  • Govind Rao

    Hi Sir,

    i have one table like Team (Team_ID primary key and Team_Name UNIQUE)
    when i start the my application i will work fine … first time … and i inserted value like {1, ‘TeamA’} {2,’TeamB’} the i close my application then .. again i try to insert same values … i am getting like”Error insertionting Team_Name = teamB”.
    please tell me the how cai solve the problem.

    • PrakashK Gowin

      You cannot insert the same values again, because you have created the column Team_ID as primary. So You need to give some other values for further insertion

  • arash ataafarin

    Just For More Clarification for newbie programmers,you should copy android_login_api folder in c:/wamp/www or c:/xamp/htdoc
    And if you use port 8080 to access xamp,wamp you should change links to :
    loginURL = “http://10.0.2.2:8080/android_login_api/”;
    registerURL = “http://10.0.2.2:8080/android_login_api/”;

  • Parth Sharma

    I did this:

    Contact contact = db.getContact(i.getExtras().getInt(“id”));

    db.deleteContact(contact);

    But still its not deleting anything but raising nullpointerexception(npe)

  • gds

    Thanks man…. was hitting my head in the walls to get a hand on Android databases ….. this was the best tutorial of all that came up on searching.

    Thanks again
    gds

  • yogesh

    Its nice tutorial.

    Using this i can i add and delete records in db but not able to update records.

    So please reply for the same as early as possible for updating record using this tutorial.

    Thanks in advance.

  • Techy

    Its worth read article. Another blog also posted the same Topic with breaking everything in parts. Getting started with DB in android

  • Lez-J

    Hello my friend! when I try to execute your getAllContacts() method I have an outOfMemory exception due to an infinite loop! but for people who have the same problem here is my way too fix it:

    public ArrayList getListContacts(SQLiteDatabase db){

    ArrayList listeContacts = new ArrayList();

    String query = “SELECT * FROM ” + TABLE_CONTACT;

    //SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    cursor.moveToFirst();

    for(int i=0;i<cursor.getCount();i++){

    Contact contact = new Contact();

    contact.setID(Integer.parseInt(cursor.getString(0)));

    banque.setName(cursor.getString(1));

    banque.setPhoneNumber(cursor.getString(2));

    listeContacts.add(contact);

    cursor.moveToNext();

    }

    cursor.close();

    return listeContacts;

    }

  • Lez-J

    sory for the previous post it’s about a project that I’m doing. but according with the object of this tutorial

    and sorry for my bad english I’m a french speaker
    public ArrayList getListContacts(SQLiteDatabase db){

    ArrayList listeContacts = new ArrayList();

    String query = “SELECT * FROM ” + TABLE_CONTACT;

    //SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    cursor.moveToFirst();

    for(int i=0;i<cursor.getCount();i++){

    Contact contact = new Contact();

    contact.setID(Integer.parseInt(cursor.getString(0)));

    contact.setName(cursor.getString(1));

    contact.setPhoneNumber(cursor.getString(2));

    listeContacts.add(contact);

    cursor.moveToNext();

    }
    cursor.close();

    return listeContacts;

    }

  • radha

    i need login activity with sqlite and sessions..can you please provide me link or any information

  • Amjad Mansour

    thank you very much working

  • ilksen

    I wanted to use this sample in my project. https://play.google.com/store/apps/details?id=izasoft.kurumsal.uygulama maybe you can use Xml Source an alternative… Thanks your tutorial.

  • Q: how big a table can you put in an Android cellphone? I have a 10GB table, and I am weary of starting down this road, if queries will take minutes instead of seconds.. Thanks for posting. Any advice appreciated!

    Jim Pruett
    Wikispeedia.org

    • If you have larger database, you should not keep it in the device. You have to maintain a server and make API calls from device and get the data.

    • Raja

      if your data 10gb put data on server side not android phone

    • Sasuke Uchia

      How did you make your table this big anyway. What it contains. There is no way you can create a table that big, if you are not inserting special data type like Image, audio, Video…etc.
      Anyway even if you will able to put it in your mobile phone. Do you know how much time it will take to search and load the data. Even i think a computer may start hanging.
      Anyway Good luck with that……(Why i suppose to care)

  • Harry May

    I like this totorial, you explain it cleary and simple, thanks !!!

  • Hhayf

    Hello.If someone could help me would be great.I installed this program,no errors,everything runs normal,but after I fill in a form and press “add user” nothing happens.Can someone help me point the problem?

    • A Friend from hiddle leaf

      Well, you can’t just copy and use this class. You have to modified it according to your need.
      Give me all the details about your layout what you want to save i will post a custom class for you.

  • Itachi Uchia

    Nice Tutorials

  • Sasuke Uchia

    Am i suppose to care. I am an avenger and i will avenge my clan

  • NarutoUzumaki of hidden leaf

    Don’t you think you should use readable database to read all the contacts.
    In getAllContacts

    SQLiteDatabase db = this.getWritableDatabase();

  • NarutoUzumaki of hidden leaf

    And what about the update query what will it update

  • Evgeniy Safronov

    // Empty constructor
    // setting id
    etc.
    Good tutorial but absolutely useless comments.

    • We are Titans….

      You have disrespected me, Now you will face the wrath of titans

    • Itachi Uchia

      Amaterasu – Burn in hell

    • Evgeniy Safronov

      Wh.. why? You don’t agree that this is a good tutorial? 0_o

      • Tobi

        Because Tobi is a good boy.

  • Yan Philippe

    Hi, I don’t knowhow to simply delete a contact, can please someone help me?

    • NarutoUzumaki of hidden leaf

      //tablename is your tablename from which you want to delete
      //KEY_ID is the column name
      //keyid is the value
      //Example you want to delete KEY_ID 20. (And remember you can also use any column you

      //want in place of KEY_ID).
      //Then keyid is 20 for your query

      SQLiteDatabase db = getWritableDatabase();
      String deleteQuery = “DELETE FROM ” + tablename +” WHERE “+KEY_ID+” =?”;
      db.execSQL(deleteQuery, new String[]{String.valueOf(keyId)});
      db.close();

      • suraj

        its not working and also what about updating?

        • NarutoUzumaki of hidden leaf

          Working perfectly in my app.
          By working what do you mean – giving Error, Exception, or wrong result.
          In wrong result case i think you might entering wrong KEY_ID. KEY_ID is unique, and used to delete a specific contact.

        • Tobi

          Load the data you want to update in an ArrayList of String then pass it to along with a userName ( any uniquely identified attribute ) to update a particular contact.
          Let say, you want to update user name, first name, middle name, last name, email id,mobile number, sex, and date of birth –
          Use following code –
          public int updateUser(String userName, ArrayList dataArray)
          {
          SQLiteDatabase db = this.getWritableDatabase();

          ContentValues values=new ContentValues();

          String newUserName = dataArray.get(0);

          values.put(KEY_USER_NAME, dataArray.get(0));

          values.put(KEY_FIRST_NAME, dataArray.get(1));

          values.put(KEY_MIDDLE_NAME, dataArray.get(2));

          values.put(KEY_LAST_NAME, dataArray.get(3));

          values.put(KEY_EMAIL_ID, dataArray.get(4));

          values.put(KEY_MOBILE_NUMBER, dataArray.get(5));

          values.put(KEY_SEX, dataArray.get(6));

          values.put(KEY_DATE_OF_BIRTH, dataArray.get(7));

          int index = db.update(TABLE_NAME, values , KEY_USER_NAME+”= ?”,new String[] {String.valueOf(userName)} );

          db.close();
          return index;

          }

  • amit

    its xml

  • Jon

    I am writing an app that queries an already created database (read only).
    How do I connect to the database via its file?
    All examples I have seen create the database on the fly.

    • Have you found the solution? I’ve the same problem ๐Ÿ™‚

      • Tobi

        Hi there

        Solution Founded

        My Boy Itachi found it.

        • jit

          so please tell us that solution..we want to access a already created database for readonly purpose..example-to make a contacts app,which show results on inserting names in searchbox..

          • Tobi

            Try this code :

            public class DataBaseHelper extends SQLiteOpenHelper {
            private Context mycontext;

            //private String DB_PATH = mycontext.getApplicationContext().getPackageName()+”/databases/”;
            private static String DB_NAME = “(datbasename).sqlite”;//the extension may be .sqlite or .db
            public SQLiteDatabase myDataBase;
            /*private String DB_PATH = “/data/data/”
            + mycontext.getApplicationContext().getPackageName()
            + “/databases/”;*/

            public DataBaseHelper(Context context) throws IOException {
            super(context,DB_NAME,null,1);
            this.mycontext=context;
            boolean dbexist = checkdatabase();
            if (dbexist) {
            //System.out.println(“Database exists”);
            opendatabase();
            } else {
            System.out.println(“Database doesn’t exist”);
            createdatabase();
            }
            }

            public void createdatabase() throws IOException {
            boolean dbexist = checkdatabase();
            if(dbexist) {
            //System.out.println(” Database exists.”);
            } else {
            this.getReadableDatabase();
            try {
            copydatabase();
            } catch(IOException e) {
            throw new Error(“Error copying database”);
            }
            }
            }

            private boolean checkdatabase() {
            //SQLiteDatabase checkdb = null;
            boolean checkdb = false;
            try {
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
            checkdb = dbfile.exists();
            } catch(SQLiteException e) {
            System.out.println(“Database doesn’t exist”);
            }
            return checkdb;
            }

            private void copydatabase() throws IOException {
            //Open your local db as the input stream
            InputStream myinput = mycontext.getAssets().open(DB_NAME);

            // Path to the just created empty db
            String outfilename = DB_PATH + DB_NAME;

            //Open the empty db as the output stream
            OutputStream myoutput = new FileOutputStream(“/data/data/(packagename)/databases /(datbasename).sqlite”);

            // transfer byte to inputfile to outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myinput.read(buffer))>0) {
            myoutput.write(buffer,0,length);
            }

            //Close the streams
            myoutput.flush();
            myoutput.close();
            myinput.close();
            }

            public void opendatabase() throws SQLException {
            //Open the database
            String mypath = DB_PATH + DB_NAME;
            myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);
            }

            public synchronized void close() {
            if(myDataBase != null) {
            myDataBase.close();
            }
            super.close();
            }

            }

    • Itachi Uchia

      There are several ways, Simplest one is :-
      Use “IF NOT EXISTS”

      @Override
      public void onCreate(SQLiteDatabase db) {
      String CREATE_TABLE = “CREATE TABLE IF NOT EXISTS “+TABLE_NAME+”(” + all your columns + “); ” ;

      db.execSQL(CREATE_TABLE);
      }

      If you still having troubles then let me know;

      • jit

        we want to access a already created database for readonly purpose..example-to make a contacts app,which show results on inserting names in searchbox..

    • Jon

      This is the solution, more complicated than I thought

      http://www.vogella.com/articles/AndroidSQLite/article.html

  • mich

    BEST TUTORIAL ! THANKS SO MUCH! ๐Ÿ˜€

  • Andrรฉ Kunde

    Nice!
    How can I use this List on a ListView component?

    • This is kinda tricky… ListView uses a layout to fill the list, hence you need an adapter… if using android.R.layout.simple_list_item_1 it accepts String and from (using the Contact example) the Contact you need to extract a String (Name probably) and load it into an ArrayList and this ArrayList load it to the Adapter and this Adapter set it to the ListView…

  • jose

    Great Tutorial, but can you tell me how can i show it in a ListView , and add images (icons)…
    ThankYou

    • Itachi Uchia

      First you need to learn about ListView – ( Use google to search, Open results, Prefer what suits you best ) .
      Second load the data from database into an ArrayList of type according to your need (Preferable String) (1d, 2d .. . depends on your need )
      Now load that data into a class and create objects, load that objects into an arrayList
      Then use this new ArrayList to populate the ListView.

      Finally – It is a big topic can’t be taught in a single comment.
      Search this topic in google.
      If you don’t know how to search in google then follow these links.

      1. developer.android.com/guide/topics/ui/layout/listview.html
      2. http://www.vogella.com/articles/AndroidListView/article.html
      3. http://www.mkyong.com/android/android-listview-example/
      4. http://www.youtube.com/watch?v=gaOsl2TtMHs

      If you are a noob then watch youtube video in 4th link. It shows the simplest way to create ListView with icons.

  • Vaibhav Luthra

    I downloaded this example and used on my phone however when I go to log cat for checking results it keeps running and I am unable to see any data..please suggest

    • Akamaru of Hidden Leaf

      What do you want to say exactly ?
      From your problem it looks like a infinite loop problem.

  • Can Gรถkdere

    Hello, this is nice tutorial but there is a thing I do not understand. When to close or not close the database and cursor? In this tutorial it seems random. Also, what happens if you close/do not close

    • Shikamaru of Hidden Leaf

      This is a nice question to ask,
      I would like to answer it as simple as possible

      First, When we need to open a database :-

      The answer is that – Whenever you want to access anything ( Tables, Views, Indexes . . . )
      from a database you have to open it, in other words you have to open the connection between your program code and database.

      You can do it easily by using SQLiteDatabase in Android like this,
      SQLiteDatabase db = this.getWritableDatabase(); //To write;
      SQLiteDatabase db = this.getReadableDatabase(); //To Read

      Any of those statements will open the default database for your app that is set by you in the constructor of your database class.

      So, if you want to access a database you have to open it ( In other words you have to open the connection b/w your app and database. This is obvious that first you have to create the connection. )

      Second, Why you have to close a Database connection.

      Remember that you must always close the Database connection if it is no longer needed for better performance of your app. If a connection is opened then it will use system resources which will reduce your app’s performance.

      In Android You will get an Exception if you forget to do that, However if you want multiple connections to a database through your app then you have to specify it clearly. In C# you do this by using MultipleActiveResultSets=True;
      If you forget to do that and you are accessing database concurrently ( In other words multiple connections are opened for some purpose say – You have to read from one table and write to another table in same database ) then you will get an Exception.

      At last,
      you should always:

      1) Open connections as late as possible

      2) Close connections as soon as possible

      The connection itself is returned to the connection pool. Connections
      are a limited resource. Any new connection you establish that has exactly the same connection string will be able to reuse the connection from the pool.

      Cursor are used to execute database queries in Android.

      Formal Definition is – cursor is a interface provides random read-write access to the result set returned by a database query.
      Cursor implementations are not required to be synchronized so code using a Cursor from multiple threads should perform its own synchronization when using the Cursor.

      It is recommended to close all cursors when you are not using they anymore.
      If you keep it opened, you will cause a memory leak on your application.

      • MMJQ

        Bump, as the question is a good one and wasn’t answered. In the example in this post, the author calls db.close() on insert and update operations but not on query retrieval operations. I’m particularly curious about this as I’m trying very similar operations and frequently getting database locked exceptions.

  • krunal

    hello guy,
    i develop application which have bulk inset record in database.
    i have two question…
    1) when record are inserted at time i can not read record, my query got in the queue, it is possible that access data and insert data at time…?
    2) what is the limit of sqlite data base..size…?

    • Itachi Uchia

      1.

      Simple answer for your question is – “IMPOSSIBLE”

      Even if you getting success to do so – Means you are getting wrong results.

      I think you should know the basics of Database –

      Why database is better then file handling and other data storage methods.

      Simple answer –

      You can’t perform W-W, W-R, R-W operations simultaneously.

      ( W – write, R- read )

      However you can execute infinite R-R operations at a same time.

      Just think about the online Banking system or Railway reservation system.

      In which there is a special feature of database is used which is Transaction.

      It follows ACID.

      which is Atomicity, Consistency, Isolation, Durability.

      Atomicity – Either complete or not at all.

      Consistency – After each transaction system will go from one consistent state to another consistent state.

      Isolation – Every transaction will executed in isolation of each other.

      ( Means if write query come first it will executed first ) There is just no way to give both write and read operation at the same time. Even there is a difference of nano second System will detect it. However if you got success to do so . db simply reject it or execute the operation which has higher priority.

      Durability – System must durable in time.

      –Maybe it is very broad than a simple database but it may be help you to understand.–

      2.

      An SQLite database file is organized as pages. The size of each
      page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default
      value for SQLITE_MAX_PAGE_SIZE is 32768.

      The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
      1073741823, is the maximum number of pages allowed in a single database
      file. An attempt to insert new data that would cause the database file
      to grow larger than this will return SQLITE_FULL.

      So we have 32768 * 1073741823, which is 35,184,372,056,064 (35 trillion bytes)!

      You can modify SQLITE_MAX_PAGE_COUNT or SQLITE_MAX_PAGE_SIZE
      in the source, but this of course will require a custom build of SQLite
      for your application. As far as I’m aware, there’s no way to set a
      limit programmatically other than at compile time (but I’d be happy to
      be proven wrong).

      • gdguradio@gmail.com

        please help me on this canyou show me how to solve this …..

        ” Contact getContact(int id) ”
        how to use this function please give sample on how to do it like i want to get names only so i want to check the column name only and get all the names that are john any sample on this?and how to use other functions as well..

  • Sandeep Pareek

    hi
    I have a question, Where did this database got created? on sdcard? or on phone memory? I wanna access that db file manually,

    just to add, can I create a folder in SD card with some name as “contact db folder” and save that db there in that folder on android?

    Thanks for the best tute on SQL basics.

  • Hasan Rahman Sawan

    Hi, I am getting this error:

    [2014-01-07 09:49:56 – Dex Loader] Unable to execute dex: java.nio.BufferOverflowException. Check the Eclipse log for stack trace.

    [2014-01-07 09:49:56 – AndroidSQLiteTutorial] Conversion to Dalvik format failed: Unable to execute dex: java.nio.BufferOverflowException. Check the Eclipse log for stack trace.

    [2014-01-07 09:53:09 – AndroidSQLiteTutorial] Dx

    trouble writing output: already prepared

    Please help. Thanks.

  • ZaidiSoft

    Thank you much for a very good and useful tutorial. Quick question though. Is it possible to have add contact and display all contacts in one activity. What I mean is, to have edit text and add new user button as well as list view of all contacts on the same UI.

  • AyemMadScientist

    Thanks for the tutorial. Btw any idea how can i load the data from database into gridView, or how can i pass the data to String[]?

  • wild974

    I am looking for some help on how to use this tutorial to load what i have in the database to a listView. Have been searching for days on how to do this with no luck. If someone could point me in the right direction. I can post my code if need be.

    • hแป“ng tฦฐฦกi nguyแป…n

      1. you read all columns of database by function getAllContect as above
      2.you need a custom listview by adapter , then you show your data in listview.

  • glenn

    Under AndroidSQLiteTutorialActivity,

    What does for (Contact cn : contacts){ does?
    1.Dont understand what is cn and where it comes from.
    2. What does : means?

    • Android@143

      Its a new looping mechanism; it recommend on working with multiple objects.
      Here,
      ‘cn’ is an object of Contact class.
      contacts is the collection of Contact class objects.
      This loop execute the total Contact objects present in the collection; and the checking is not needed.

      Only the Contact class objects are looped.

      • gdguradio@gmail.com

        ” Contact getContact(int id) ”
        how to use this function please give sample on how to do it like i want to get names only so i want to check the column name only and get all the names that are john any sample on this?

  • broskie

    So how would I add the database to a listView in another activity. I would like to click on a button that will take you to another activity and display the name and phone number of each contact in a listView with an onClick listener for each. Thanks for the help.

    • hแป“ng tฦฐฦกi nguyแป…n

      in activity you want to show databse :
      1. you read all columns of database by function getAllContect as above
      2.you need a custom listview by adapter , then you show your data in listview.

  • george

    Hello. I need someone
    to develop a simple app. will use java , php and sql. please reply to george.celsie@gmail.com
    if you can help me. I will share the
    credits :). Thanks

  • Nasif Ahmed

    Can you please tell me why you use(_) _id, _name, _number insted of id, name, number?

    • Karim

      I think it’s a coding style, to put _ before the name of local variables declared in a class.

  • // Getting contacts Count

    public int getContactsCount() {

    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(countQuery, null);

    cursor.close();

    // return count

    return cursor.getCount();

    }

    This function throws exception:

    java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteQuery: SELECT * FROM contacts

    You must get count before cursor.close();

    This is correct way:

    // Getting contacts Count

    public int getContactsCount() {

    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(countQuery, null);

    int count = cursor.getCount();

    cursor.close();

    return count;

    }

    • Joseph David

      Try this:
      .
      .
      .
      int count = cursor.getCount();

      cursor.close();

      // return count
      return count;

  • Shazwan

    I can’t find the database file. where does it saved?

    • Bhaskar

      Windows -> Show View -> Other.. -> Android -> File Explorer
      Data->Data->com.xxx.xxxx(Your package Name) ->Databases

  • LaLaRargh

    I’m just wondering, but how would you call the updateContact method in your activity class? I’m presuming it must be something like:

    contacts = db.updateContacts(); ?

    something along the lines of

    contacts = db.updateContacts(new Contact(); ?

    Thanks for any help.

  • Brian K. Trotter

    I notice that the onUpgrade() function wipes the previous database and starts over. While I could see some applications where that would be beneficial, I’d sure hate to lose a database with 100 contacts in it just because I decided to add a field for a 2nd phone number. Is there an easy way to upgrade a table to add a field without wiping the previous data?

  • if we use existing database and wants to upgrade database what should write in upgrade methods

  • Bharat Jiyani

    God Bless You ๐Ÿ™‚

  • Matthew N

    Thanks alot for a great tutorial! extremely helpful for a somewhat tricky topic for new android developers!

  • Anshuman

    Thanks for the codes and the idea. One question I had in mind was where to write this code?? Do I create a separate package for the database or should I write the code in MainActivity.java itself?? Please help
    Thank you

  • Shima Shirazi

    Hi,
    Thank you for Tutorial.
    I would be very thankful if you answer my question:
    Where can I see my database application in (android) phone?
    I rooted the phone, but I can not find folder such as /data/data/my_app/databade

    • Arsal

      data->data->see your package name-> databases->

  • Kamolporn Sanamlao

    I would like to thanks, this tutorial is helpful. ๐Ÿ™‚

  • Hendrik

    Hi, in the addContact method, can someone explain why only KEY_NAME and KEY_PH_NO are being added? Does KEY_ID not need to be added? Thanks

    • Noman

      because it is auto increment

  • Chrisantics

    hello! how do i want to view the database in sqLite browser? I can’t seem to find the .db file??

  • Shima Shirazi

    Somebody answer my question, plz

  • Also Ravi

    Never before have I gone through a tutorial like this and had it work on the first time. Ravi Tamada you are the man!

  • Abdullah Ben Nakhi

    How do you store images in the SQLite database ?

  • aref chegini

    Hello,
    Thank you for Tutorial.

    how do you store group data in the sqllite database and when im create a program in eclips and im install in the mobail database is void .

  • Hi,
    thanks for neat tutorial.
    I’m unable to see the created db files physically in my device. (/data/data//)
    do you have any idea why this happens?

  • Trevor L.

    Thanks, your tutorials are much appreciated.

  • Zahidul Islam

    good tutorial but one thing..data reinserted while run the tutorial again. i.e four rows inserted as 20 rows when run the tutorial 5 times. plz solve.

    • Faisal

      this is not a problem ~

  • Jagdeep

    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();

    Error here cannot access the cursor after closing it better put the count in some another variable and return that variable.

    • ramesh bs

      You should not close the cursor after u query, cursor is the one which hold data & other objects. So if u close, it wont give u count. Once all cursor operations is completed. close it

  • gdguradio@gmail.com

    ” Contact getContact(int id) ”
    how to use this function please give sample on how to do it like i want to get names only so i want to check the column name only and get all the names that are john any sample on this?because when i use it , it throw an exception like this
    ” throw new CursorIndexOutOfBoundsException(mPos, getCount());”
    and how to use
    List contacts = db.getAllContacts();

    without using this code

    for (Contact cn : contacts) {
    String log = “Id: “+cn.getID()+” ,Name: ” + cn.getName() + ” ,Phone: ” + cn.getPhoneNumber();
    // Writing Contacts to log
    Log.d(“Name: “, log);

    like if i want to use if to compare if i get same value from the name? like if i want to get all name of john from the list

  • Thanks, good tutorial. But may I ask why are you using:

    Integer.parseInt(cursor.getString(0));

    instead of:

    cursor.getInt(0);

    Thanks

    • Manoj

      it has store the data in string format

      • Manoj

        Check this one…db.addContact(new Contact(“Ravi”, “9100000000”));

        its storing data in string format…not stored in
        db.addContact(new Contact(“Ravi”, 9100000000));

  • piter09100

    Thank you, great tutorial ๐Ÿ™‚

    But I dont understand how you set IDs. My app gives ID=0 to every save and I dont know why??

    • David Doyle

      The definition for the I’d column using ‘Integer primary key’ should automatically increment on an insert. If that is not working try ‘integer primary key auto increment not null’.

      • piter09100

        Thanks, it works now, I’ve changed …+ KEY_ID + ” INTEGER PRIMARY KEY,”.. to …+ “KEY_ID INTEGER PRIMARY KEY,”…

        • Sarthak Majithia

          You need to change “INTEGER PRIMARY KEY” to “INTEGER PRIMARY KEY AUTO INCREMENT NOT NULL”

  • Mike

    Hi,

    For the AndroidSQLiteTutorialActivity class, i am facing an error where the code

    DatabaseHandler db = new DatabaseHandler(this);

    gives me an error saying: The constructor DatabaseHandler(SQLiteSubmit) is undefined

    Does anyone know how to solve it? Should the class extend SQLiteOpenHelper instead of Activity?

    • Sushreekant Mishra

      Try DatabaseHandler db = new DatabaseHandler(AndroidSQLiteTutorialActivity.this);

      • Mike

        Hi thanks for the help!

        I am new to android development and databases. Can i check whether data SQLite database is only accessible to 1 user/device?

        For example, if there is a Contacts database, and many users all over the world want to access this database to retrieve information, what kind of database should be used?

        • David Doyle

          This only allows you to store in a db accessible on the device to one application. To use a shared db, accessible from multiple devices, you’d need to call a web service (though you still might want to store a local copy of any information for caching purposes).

  • santosh

    how do i show this data in tablerow

  • how to save data from soap to sqlite database in android

    I am getting GetVehicles method of SOAP WSDL from SOAP web services and call that GetVehicles result in TextView when clicking on a Button event.

    when I run program , I want to store that result shown in TextView in Sqlite database?

    How can I do that?

    I have make a class getter Setter and databasehandler which extends SQLiteOpenHelper?

  • Jongsik

    Very useful article! Thank you ๐Ÿ˜€

    • You are welcome ๐Ÿ™‚

      • Umar Ashraf

        Plz tel me if I want to show the output in an Amulator in the form of Listview, plz send me the code…

  • junka

    Thank you very much for the tutorial. It worked very well except for the getContactsCount() function. It was giving me an error saying that I was trying to read a value from a closed object. I changed the code to:

    public int getContactsCount() {
    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = cursor.getCount(); //added line here
    cursor.close();

    return count;
    }

    After that everything was working smoothly. Thanks again.

    • Jomel

      how to use that getContactCount

  • tok

    Is there any possibility to add ArrayList as a type of row?

  • Anas

    Nice article, very useful!

  • NewtoAndroid

    The type java.lang.Object cannot be resolved. It is indirectly referenced from
    required .class files
    – The type java.lang.String cannot be resolved. It is indirectly referenced from
    required .class files

    Above error in DatabaseHandler.java file

  • NewtoAndroid

    for — package com.androidhive.androidsqlite;

  • Midomed

    what if I wanted to create a query to search for a particular id or a name?

  • Nika KirkitaZe

    Hi. Thank you for this tutorial, it is really helpful.

    Unfortunately my app gives ID = 0 to every time, i tried (INTEGER PRIMARY KEY),(‘_id’ INTEGER PRIMARY KEY AUTOINCREMENT), (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL),

    But i am getting same result always. what can i do?

  • FDM.Pro

    Why use static variables in contact class?

    • FDM.Pro

      Sorry in Handler not in contact.

  • Dilberius Biฤ Boลพji

    Does anyone know how to display database in textview or in anything like database? I would like to make a highscore but do not know the code to display strings in textview pls answer at dilberius@gmail.com

  • mukesh

    at logcat here elements add one by one when connection is generate………….
    THE PROBLEM HERE

    if (cursor.moveToFirst()) {

    do {
    Contact contact = new Contact();
    contact.setID(Integer.parseInt(cursor.getString(0)));
    contact.setName(cursor.getString(1));
    contact.setPhoneNumber(cursor.getString(2));
    // Adding contact to list
    contactList.add(contact);
    } while (cursor.moveToNext());
    }

    REDUCE IT……..

    THANKS WITH BEST REGARDS

  • baus

    where is the the ” ( ” in the CREATE_CONTACTS_TABLE-String?

  • Agilitis

    Hello everyone! I’m facing a pretty irritating problem… My app starts on my mobile but it shuts down without any errors, any ideas?

  • Maryea

    hyee ….
    i am facing database upgradation problem.. using a pre populated sqlite database… it works fine.. bt when i try to update it by inserting values in existing table using sqlite browser.. it never upgrades… and shows no such record..
    Any idea for solution???

  • Guest

    never Forget to close the Cursor in method getAllContact! ๐Ÿ˜‰

  • Russelius Ernestius

    some methods did not Close database or the Cursor… ๐Ÿ˜‰

  • Hi, How do I create a method to check for existing records? I don’t want to add duplicates for example. Thanks!

    • Kuev

      +1๐Ÿ‘ I was going to ask the same question… Anyone can help?

  • Ahmed Sobhy

    realy am very happy now i can say that i may anderstand sqlite database android thanks

  • Syed Ahmed Ali

    Awesome tutorial… got an idea on SQLite database

  • Thanks for perfect tutorial. I have two questions:
    1) How to prepare the SQLlite database and and fill it with the data before it is used in android app? All the tutorials I have red are creating the empty database directly in the dbHelper onCreate, but I already have the initial data, so I need to open the fully functional database in the app and not to create the empty one.

    2) How to dump the state of the database inside the phone onto the SD card and import the data back to database when needed. It would be very usefull in the case of database upgrade.

    Thanks for any info
    Jakub

    • 1. For the first question, you can insert the data in onCreate method of sqlite class or you can insert the data from the starting point of your app.

      2. For exporting the db to SDCard, you can try below link

      http://stackoverflow.com/questions/6540906/android-simple-export-and-import-of-sqlite-database

      • Thanks! It seems perfect. Exporting/importing also solves the first question, as I can create db and fill it with data in SQLite Manager (FireFox plugin), pack it into *.apk and than import it in database onCreate method.

        Thx
        Jakub

        • June

          Hi Jakub,
          Please have a look on the question I just asked to Ravi on the same context as of yours. May be you’ll also be able to help.

      • June

        Hi Ravi,
        Is it possible to have a ready-made database instead of populating one while running the app? I mean you just answred @jakuberch:disqus asking him to populate the data either of the two ways i.e. inserting in onCreate or inserting from the starting point. I want the database ready to use after installing the app only. How to achieve that? And how can I update the database from server in such case?

  • Duke

    Hi, Thanks for the Nice Tutorial.
    This line is throwing error.

    ” contact.setID(Integer.parseInt(cursor.getString(0))); ”
    Kindly advise.
    Also, getContactsCount() was throwing exceptions, due to closed cursor, so i removed cursor.close(). now its working fine.

  • Guest

    Thanks bro, its works

  • Guest

    Hi,
    Thanks alot for the tutorial. I have done all this.
    Can I know how to connect this sql database with eclipse?
    Can anyone help me please?

  • dr_ervina

    Thanks a lot ravi for your very simple useful code

  • wow thanks ravi, it’s very simple but useful tutorial

  • Hey Ravi Tamada,

    I have a database app that stores users’ names, phone numbers and email addresses, it works fine but I wanted it to do some extra task. I created an itemOnClickListener to handle clicking on saved contacts. Clicking on the listItems produces a popup menu with 3 options;

    1. Call selected person

    2. SMS this person

    3. Send email

    I created the pop up menu but now I want it to be in such a way that when I click on “Call selected person” option, it should get the respective phone number from database and call it. Please help.

    • numberFromDatabase = “111-333-222-4”;

      String uri = “tel:” + numberFromDatabase.trim() ;
      Intent intent = new Intent(Intent.ACTION_CALL);
      intent.setData(Uri.parse(uri));
      startActivity(intent);

  • name

    you did very well ………

  • Appkart In

    Hi Ravi , Some modification is needed in your code

    1.You should also add id of contact in ContentValues in addContact().

    2.Cursor can be null so that check null for whole code not only cursor.moveToFirst();

    if (cursor != null) {
    cursor.moveToFirst();
    Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
    cursor.getString(1), cursor.getString(2));

    }

  • swagat

    I did the same code, but app is throwing error- Unfortunately stopped. What is the reason??

  • bharat

    hey can u help? How to connect an externally created sqlite database in android studio??

  • Hรผseyin Mesecan

    in getContactsCount: you write curser.close(); and then return curser.getCount(); why?

  • wathmal

    why can’t we use cursor.getInt(0) instead of Integer.parseInt(cursor.getString(0)) ???

  • rootbee

    hi ravi.. i m going to build one my own app so could you please me.. bcz i m in confusion. if possible mail me in here rootbee2014@gmail.com

  • Twelve

    Useful tutorial, thx !

  • gourav

    How to insert Paragraph in database and retrive it as a paragraph???

  • David Bu

    Hi i am using ur code and by the way its the cleanest code ive seen ive watched your videos and glad to no your from a c sharp background your code matches entity framework basically if only their was an orm like that do you have an example of how to populate a listview using ur DBHandler class that is the point I am stuck on at the min using the GetAlLContacts I am wanting to populate a listview with it

  • Tejwinder

    you saved my day ๐Ÿ™‚

    thanks Ravi

  • WanderFilho

    Thank you very much for the explanation.
    I’ve been looking for something like this on the web and you made it very simple and incisive.

  • TheFiddle47

    What is the data type to add the photo of the contact? Its Byte[]?

    • yash

      you can store images with BLOB..!!

      • bilgee

        is BLOB is byte array ?

  • Chetan

    if I want to store data in sqlite file for permenate storage of data then how i connect to that file where i store that file SQLITE file

  • Gaurav

    when i write statement that give me the error

    List contacts = mDb.getAllStudentList();

    The method getAllStudentList() is undefined for the type SQLiteDatabase how to i solve

    • PrakashK Gowin

      Write this getAllStudentList() method in your DbHandler Class with public access.

      • Gaurav meghanathi

        actually i am trying to fetch data into sqlite-database in a list-view but this tutorial not much helpful..

  • mahboob gh

    how to update my contact database whenever android local contacts changed . I need to store contacts in database but I need to update it when a new contact is deleted or edited or inserted and update local contact when in my app a field changed,please help me.thank you

  • Gaurav meghanathi

    how to set string log to listview and display that object

    • Rรบben Diogo

      I had the same question and I used a simple adapter. Check my code:

      public class AndroidSQLiteTutorialActivity extends Activity {
      /** Called when the activity is first created. */
      ArrayList<HashMap> contactList;

      private static final String TAG_NAME = “name”;
      @Override
      public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.main);

      ListView test = (ListView) findViewById(R.id.listView1);
      DatabaseHandler db = new DatabaseHandler(this);

      /**
      * CRUD Operations
      * */
      // Inserting Contacts
      Log.d(“Insert: “, “Inserting ..”);
      db.addContact(new Contact(“Ravi”, “9100000000”));
      db.addContact(new Contact(“Srinivas”, “9199999999”));
      db.addContact(new Contact(“Tommy”, “9522222222”));
      db.addContact(new Contact(“Karthik”, “9533333333”));

      // Reading all contacts
      Log.d(“Reading: “, “Reading all contacts..”);
      List contacts = db.getAllContacts();

      contactList = new ArrayList < HashMap > ();

      for (Contact cn : contacts) {
      String log = “Id: “+cn.getID()+” ,Name: ” + cn.getName() + ” ,Phone: ” + cn.getPhoneNumber();
      // Writing Contacts to log
      Log.d(“Name: “, log);

      String name = “” + cn.getName();

      HashMap contact = new HashMap ();

      // adding each child node to HashMap key => value
      contact.put(TAG_NAME, name);

      contactList.add(contact);
      }

      SimpleAdapter adapter = new SimpleAdapter(this, contactList, android.R.layout.simple_list_item_1, new String[] { TAG_NAME }, new int[] { android.R.id.text1 });
      test.setAdapter(adapter);

      }

      }

  • vnshetty

    Hi , why we need SQLiteDatabase db = this.getWritableDatabase(); in getAllContacts() function?,where we are only fetching the data from db. Cant we use SQLiteDatabase db = this.getReadableDatabase(); ?

  • shivashish

    thanks a lot sir for your simple and extremely helpful code.

  • Basha

    Your tutorials are awesome yesterday I subscribed in your site. But still i didn’t get any confirmation email from you.

  • BLAH

    Can someone give me the example of deleting the user??

  • Abhay

    `this.getReadableDatabase(); makes the app to crash

  • Edward Lim

    Just wanted to give a huge shoutout to you and your work, Its all thanks to you that I finally have my app on the app store ๐Ÿ˜€ Although its just something simple I created, i created it because I found a need for it. Check it out if you guys are interested, again huge thanks to your work!!!

    https://play.google.com/store/apps/details?id=com.workoutlog.elcsgen.thesimpleworkoutlog

  • There is an error in your getContactsCount() function. The cursor object is closed before calling cursor.getCount().

  • JAMES

    The database name has to end with .db

  • Sanket Prabhu

    In future, can ORMLite/Realm takeover the SQlite? As a developer which is best as considering functionality?

  • Usman Ishrat

    Well elaborated… could be more helpful if you explain the parameters of the queries .

    • 8Farhan Shaikh

      if u understood program plz help ..i posted above my query plz help me out

  • Henrique Rosa

    How can i exclude the table, not the rows, but the intire table?

  • 8Farhan Shaikh

    i have try to do above program but i stuck at main class ……. when initiate() the DatabaseHandler like this DatabaseHandler db=new DatabaseHandler(this); in mainActivity class it says to impement arguments … but when i sae in your mainActivity there is no argument expect(this)

    please help me soon

  • Gergely Bindics

    Hi!

    Thank you for this tutorial, it was very useful.

    There was one problem with it if I am correct.

    Calling getContactsCount() method, my app crashed.

    The problem was that you closed cursor and then tried to call its getCount() method.

    So I stored cursor,getCount() int a variable, closed the cursor and then returned the variable.

    Cheers!

  • masoud seraj

    nice tutorial!

  • Sunil Singh

    very nice for beginners

  • Shindou Takuto

    where is code for update and delete contact? and i want to list it in list view.. thanks

    • Tim

      @shindoutakuto:disqus not to be rude, but you should read up on how android components work together to form an application. The code for updating and deleting is in the DatabaseHandler class, its up to you to try to figure out how to put it together. The author shows a good example of how to use it in the activity, just look by the “CRUD operations” comment. Great Job @ravi8x:disqus.

  • Homen

    Nice tutorial

  • Rhiedzal Brilliant Marz

    How showing database in layout?? please

  • AK

    I am making a small app which will just add and delete products but It is not able to print the database

    I want to print the database but it is not entering in the while loop which is made to move the cursor to access the product name

    Here is my code for products.java class

    package com.firstapp.sqliteexample;

    public class Products {
    private int _id;
    private String _productname;

    public Products(String productname) {
    this._productname = productname;
    }

    public void set_id(int _id) {
    this._id = _id;
    }

    public void set_productname(String _productname) {
    this._productname = _productname;
    }

    public int get_id() {
    return _id;
    }

    public String get_productname() {
    return _productname;
    }
    }

    Here is my code for MyDBHandler.java

    package com.firstapp.sqliteexample;

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

    public class MyDBHandler extends SQLiteOpenHelper{

    private static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = “products.db”;
    public static final String TABLE_PRODUCTS = “products”;
    public static final String COLUMN_ID = “_id”;
    public static final String COLUMN_PRODUCTNAME = “productname”;

    public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    String query = “CREATE TABLE ” + TABLE_PRODUCTS + “(” +
    COLUMN_ID + ” INTEGER PRIMARY KEY AUTOINCREMENT, ” +
    COLUMN_PRODUCTNAME + ” TEXT ” +
    “);”;
    db.execSQL(query);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(“DROP TABLE IF EXISTS ” + TABLE_PRODUCTS);
    onCreate(db);
    }

    //Add a new row to the database
    public void addProduct(Products product){
    ContentValues values = new ContentValues();
    values.put(COLUMN_PRODUCTNAME,product.get_productname());
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_PRODUCTS, null, values);
    Log.i(“database:”, String.valueOf(values));
    db.close();
    }

    //Delete a product from the database
    public void deleteProduct(String productname){
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL(“DELETE FROM ” + TABLE_PRODUCTS + ” WHERE ” + COLUMN_PRODUCTNAME + ” =” ” + productname + ” “;”);
    Log.i(“database:”, “yo”);
    }

    //Print out the the database as a string
    public String databasetoString(){
    String dbString=””;

    String query = “SELECT * FROM ” + TABLE_PRODUCTS;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    Log.i(“database:”, “print”);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
    Log.i(“database:”, “inif”);
    do {
    Log.i(“database:”, “inloop”);
    dbString += cursor.getString(cursor.getColumnIndex(“productname”));
    dbString += “n”;

    } while (cursor.moveToNext());
    }

    db.close();
    return dbString;
    }

    }

    Here is my MainActivity.java code

    package com.firstapp.sqliteexample;

    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.util.Log;
    import android.view.View;
    import android.widget.TextView;
    import android.widget.EditText;/*
    import android.view.Menu;
    import android.view.MenuItem;*/

    public class MainActivity extends AppCompatActivity {

    EditText buckysInput;
    TextView buckysText;
    MyDBHandler dbHandler;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    buckysInput = (EditText)findViewById(R.id.buckysInput);
    buckysText = (TextView)findViewById(R.id.buckysText);
    dbHandler = new MyDBHandler(this, null ,null ,1);
    printDatabase();
    }

    /*@Override
    public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.menu_main, menu);
    return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
    // Handle action bar item clicks here. The action bar will
    // automatically handle clicks on the Home/Up button, so long
    // as you specify a parent activity in AndroidManifest.xml.
    int id = item.getItemId();

    //noinspection SimplifiableIfStatement
    if (id == R.id.action_settings) {
    return true;
    }

    return super.onOptionsItemSelected(item);
    }
    */
    //Add a product to the database

    public void addButtonClick(View view){
    Products products = new Products(buckysInput.getText().toString());
    dbHandler.addProduct(products);
    printDatabase();
    }

    //Delete items
    public void deleteButtonClick(View view){
    String inputText = buckysInput.getText().toString();
    dbHandler.deleteProduct(inputText);
    printDatabase();
    }

    public void printDatabase(){
    Log.i(“db”, “entering database”);
    String dbString = dbHandler.databasetoString();
    buckysText.setText(dbString);
    buckysInput.setText(“”);
    Log.i(“db”, “exiting database”);
    Log.i(“db”, String.valueOf(dbHandler));
    }
    }

    XML code

  • Sathish Kumar

    how to save the datas from the server to sqlite database

  • roopa

    hi. iam new to sqlite.. and i need code from you sir.how to develop a program for name,phno, city,country fields give from the key board and store data in sqlite and display in another activity using list view.

  • roopa

    sorry stored data display from another activity.

  • joejava

    //MySQLiteHelper

    package com.egci392.qz0428;

    import android.content.Context;

    import android.database.sqlite.SQLiteDatabase;

    import android.database.sqlite.SQLiteOpenHelper;

    import android.util.Log;

    public class MySQLiteHelper extends SQLiteOpenHelper{

    public static final String TABLE_DATA = “data”;

    public static final String COLUMN_ID = “_id”;

    public static final String COLUMN_ROUTENAME = “routeName”;

    public static final String COLUMN_START_LATITUDE = “start_latitude”;

    public static final String COLUMN_START_LONGITUDE = “start_longitude”;

    public static final String COLUMN_STOP_LATITUDE = “stop_latitude”;

    public static final String COLUMN_STOP_LONGITUDE = “stop_longitude”;

    public static final String COLUMN_COLORFLAG = “color”;

    private static final String DATABASE_NAME = “data.db”;

    private static final int DATABASE_VERSION = 1;

    // Database creation sql statement

    private static final String DATABASE_CREATE = “create table ”

    + TABLE_DATA + “(” + COLUMN_ID + ” integer primary key autoincrement, ”

    + COLUMN_ROUTENAME + ” text not null,”

    + COLUMN_START_LATITUDE + ” real not null,”

    + COLUMN_START_LONGITUDE + ” real not null,”

    + COLUMN_STOP_LATITUDE + ” real not null,”

    + COLUMN_STOP_LONGITUDE + ” real not null,”

    + COLUMN_COLORFLAG + ” text not null);”;

    public MySQLiteHelper(Context context) {

    super(context,DATABASE_NAME,null,DATABASE_VERSION);

    }

    @Override

    public void onCreate(SQLiteDatabase database) {

    database.execSQL(DATABASE_CREATE);

    } //when the program runs will runs this command first and table will be created

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    Log.w(MySQLiteHelper.class.getName(),

    “Upgrading database from version ” + oldVersion + ” to ”

    + newVersion + “, which will destroy all old data”);

    db.execSQL(“DROP TABLE IF EXISTS ” + TABLE_DATA);

    onCreate(db);

    }

    }

  • Arpit

    Thanks for the tutorial ravi sir but i wanna a know how to add .sql file in to the our SQLite database Please help me suggest any link to do that and sorry for my english

  • Very nice tutorial. thanks for sharing

    • You are welcome ๐Ÿ™‚

      • Elashry

        how can match a new reading data with saved sqlite database

  • Kamaro Lambert

    Man I love your tutorials they are very simple to understand and codes are very clean.

    • Goodness Adewale

      “the codes are very clean” – very true

      • ๐Ÿ™‚

        • Lucas Ofend

          Please sir, i have an issue with the SQLite Database Restore button in c#.
          Can you guide me please on how to restore an SQLite database in c#??
          i’ll be very gratefull .

  • hezo

    thank you ! it very useful .

    • You are welcome!

      • Lucas Ofend

        Please sir, i have an issue with the SQLite Database Restore button in c#.
        Can you guide me please on how to restore an SQLite database in c#??
        i’ll be very gratefull

  • DroidTalker

    Hi,

    @ravi8x:disqus thank u for first of all.

    i need a question:
    In your opinion,
    how do i have to choose database for any android application?
    SQlite or PHP Mysql or else?
    What i need to suggest?

    Thank u very very much
    Regards,

  • Very nice tutorial

  • Shreedhar090

    Thanks a lot man…

  • Ahmad Muzzammil

    how can i make an app that function read database not create, edit or even delete it? so there is a search bar them if i insert an ID the result is detailview of the ID. Thank you

  • Ravi Godara

    Thanks a lot for the code ….
    A superb post for image upload & retrieve is also at
    http://godara4ravi.blogspot.in/2016/03/select-image-from-gallery-upload.html

  • Luthfi M Nabil

    Thanks for the Sample ๐Ÿ™‚

  • Thank you. Any reason why db.close() is not called in some of the CRUD operations?

    • Yeah, please add it before return. Also I suggest you create a singleton class to initiate the sqlite.

  • Thank You Soooo much !!!
    Great tutorial, well explained !

  • KC Raju Vysyaraju

    it’s great and simple.
    Thank you

  • florence cosmas

    This is awesome! Thanks

  • Thanks a lot!

  • ุงุฑุญู…

    i have error in DatabaseHandler ? why?

    • FaisalHyder

      As salam o alaikum.
      Error, where? which error? Be specific brother..

  • Gian Espinosa

    Thanks!

  • rogue_shadow

    Could you show how to use the update function? Thank you

  • AjayB

    I see you have used db.close() at only at one place and cursor.close() at another place. Will that not lead to issue of open db connections? Anyway I am looking for code that uses synchronized DB connection to ensure that open collection do not hang the mobile device. DO you know of any good example?

  • Ser

    Hi folks, I have a question. Why did we create three constructor at Contacts.java?

    • Dravit Lochan Gupta

      Not very sure, but maybe because if the developer puts id as auto incremental. empty constructor if the default constructor malfunctions or something like that.

  • raj

    it is ok. but how can we add a doc file or pdf file?

  • Neeraj Pant

    how can add contact add in emergency list class from contact list in phone

  • Jan

    Thanks a lot for this amazing tutorial!

    cursor.close();
    return cursor.getCount();

    Should be

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

    Because now it throws an java.lang.IllegalStateException: attempt to re-open an already-closed object when you call the getContactsCount() method.

  • Tushar

    Just Wow man … Loved it (y)

  • Muhammad Maqsood

    Tiger….!

  • Ahmed Aslam

    Assalam-o-Alaikum. #Ravi in getContact(). U used this.getReadableDatabase(); but in getAllContacts(). U used this.getWritableDatabase();. plz explain this. because both methods are reading data from data base. why this difference. and what is the reason of this?

    • It’s mistake. As we are not writing anything, the method should be readable db.

  • Dravit Lochan Gupta

    I have a doubt.!

    when an object of DatabaseHandler is made in AndroidSQLiteTutorialActivity, onCreate(), method of DatabaseHandler will be called. won’t it give an error if a table with same name is found in the system which was made at the time of previous execution of the application.

    • Dominic Claxton

      I’m pretty sure using the helper and this function it creates the database if it doesn’t exist and ignores this process if it already exists

      • Dravit Lochan Gupta

        Can you explain in detail?

    • Heba

      make sure that the constructor like this
      public DatabaseHandler(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

  • Sushant

    // In getting all contacts
    do{
    Contact contact = new Contact();
    Asking to pass the “int id, String name, String _phone_number “.
    What to write in this constructor?
    Please help.

    • vishwa

      Don’t write anything, Empty constructor will handle it.

      while adding the contact you should fill something in that.

    • Hitesh Danidhariya

      make an empty constructor

  • Great you articles are really useful for me , i am a beginner for developing android app. your website is so useful for me.

  • Adedara Klever Olanrewaju

    Ravi, how do I set the results to a list view

    • Jeffy

      //your listview
      ListView list;
      list = (ListView )findViewById(R.id.listView);

      List contacts = db.getAllContacts();

      ArrayAdapter adapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,contacts);
      list.setAdapter(adapter)

      //this would work

      • ospfkpoes

        Its showing the list in hexadecimal values.. cant figure out what to do!!plz Help!!

  • Suhail Parvez

    Hey Ravi , how do we store an Arraylist into the database table ?
    Didn’t get much help in StackOverflow.
    I know we have to split the arraylist and insert it one by one.
    BUT, how do i store it in the table ?

    • Can you give me the sample data in your ArrayList?

      • Suhail Parvez

        Thie is the JSON coming in
        “staff”: [
        “John Doe”,
        “2”,
        “a”
        ],
        or
        “departments”: [
        “Waste Management”,
        “Medical Gases”,
        “Hospital Wards”,
        “Waste Compound”,
        “BIOCHEMISTRY CHEMICALS”,
        “Biochemistry Lab”,
        “Catering and Household”,
        “CDU”,
        “Clinical Engineering”,
        “Derrycourt Cleaning Services”,
        “EMBU Lab”,
        “Emergency”,
        “Haematology/ Blood transfusion”,
        “Histology”,
        “Histology Dyes”,
        “Household Services”,
        “IMMRL”,
        “Industrial Gases”,
        “Medical Gases”,
        “Metabolic”,
        “Microbiology”,
        “Mortuary”,
        “Neurology”,
        “Newborn Screening Lab”,
        “Pharmacy”,
        “Technical Services”
        ]

  • wazaa20003

    Ravi, how to select query using List?

  • Shiven Singh

    Amazing tutorial..Really appreciate the effort you put in to make all these sweet tutorials
    Thankyou from the whole community.

  • Leo

    My good sir might I ask where is the xml file or how can i connect the database and xml file

  • ajaxkm

    xml?

    • Kebaa Marush

      remember is this example you don’t use any view but rather work with the Logcat,

  • George Zhao

    Hi, thanks for your sharing. It helps me a lot.
    But I have a question, I do not understand why you do not add the id into the value in the method addContact().

  • shashank tiwari

    Sir again needed your help i wanted to drop my sqlite tables but not able to call onupgrade method .is there any alternate solution to dropping my sqlite tables and then recreating them.

  • Burak Cakir

    Great tutorial Ravi, thanks.

    But there is one mistake that you try to re-open the cursor which is already closed in getContactsCount() method.

    it should be fixed like this :

    public int getContactsCount() {
    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = cursor.getCount();

    cursor.close();
    db.close();

    return count ;
    }

  • Shameem Ahsan

    hi sir if u dont mind give me ur number , need help

  • james

    How do you do it with getContactsById? sorry I’m new to this.

  • John Kimson Magat

    Thanks for this great tutorial ๐Ÿ˜€

    but please someone help me with my problem
    how do i put the result in ListView? i already tried it but it only show the last record

    • Whats the code you are trying?

      • Keshav Tiwari

        @ravi8x:disqus Sir I have the same prob. Its my first time with databases. I have a custom object and an adapter. Should I edit my object as Contact ? Should I send you the code?

  • Sgsh

    How do I create the database in one class and then reference it from other classes?

  • Saathwik Nalige

    This tutorial is awesome!!! Really helpful for beginners… have you devoloped any project based on retrofit???
    and im getting value of phone number as name(name = ravi,phone_num = ravi) instead of the number

  • Chris Ryce

    Thanks for this amazing tutorial. I’m a beginner in android development and just by your simple steps of explaining this complex topic of maintaining SQL database in android, I’ve learnt so much.
    There is a minor correction in getContactsCount() function. There shouldn’t be cursor.close() line before the cursor.getCount(). So I think it should be like this, correct me if i’m wrong..
    {
    //rest of the code…..
    int count=cursor.getCount();
    cursor.close();
    return count;
    }

  • Chris Ryce

    Can anyone tell me how to use this updateContact() function. And what integer value it is returning?

  • Chris Ryce

    I have one more question. I have successfully applied the deleteContact() function but when I further viewed the database, there is no update in the KEY_ID like-
    ID NAME PHONE_NO
    1 Chris 15654654
    2 Andrew 11546586
    3 Will 54556585

    After deletion of “Andrew” record-
    ID NAME PHONE_NO
    1 Chris 15654654
    3 Will 54556585

    See, no change in the key_id like “Will” record should have id=2.
    Is this limitations of SQLite or I’m doing something wrong?

    • Marius Zimta

      public void onCreate(SQLiteDatabase db) {
      String CREATE_CONTACTS_TABLE = “CREATE TABLE ” + TABLE_CONTACTS + “(”
      + KEY_ID + ” INTEGER PRIMARY KEY,” + KEY_NAME + ” TEXT,”
      + KEY_PH_NO + ” TEXT” + “)”;
      db.execSQL(CREATE_CONTACTS_TABLE);
      }

      in this function key_id is declared as primary key(that means, that the key must be unique). if you delete your entry you are deleting also the unique key. in this example the primary key is autoincremented.

  • Peter

    put the internet permission outside permission tags

    • Peter Amit

      application tags

  • Yogesh

    Thank you very much for the code.. ๐Ÿ™‚

  • Lor’themar Theron

    Thanks for your guide, but when i run app, its giving me error at three lanes

    1. SQLiteDatabase db = this.getWritableDatabase(); // Updating single contact
    2. SQLiteDatabase db = this.getWritableDatabase(); //Getting all contact
    3. db.execSQL(CREATE_CONTACTS_TABLE); // creating table
    What must i do?? When i tried others database examples, they r giving me same error on getWritableDatabase, db.execSQL methods.

  • Dimas Prasetio

    hey ravi

    i wanna ask about this code :

    public List getAllContacts() {
    List contactList = new ArrayList();
    // Select All Query
    String selectQuery = “SELECT * FROM ” + TABLE_CONTACTS;

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

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
    do {
    Contact contact = new Contact();
    contact.setID(Integer.parseInt(cursor.getString(0)));
    contact.setName(cursor.getString(1));
    contact.setPhoneNumber(cursor.getString(2));
    // Adding contact to list
    contactList.add(contact);
    } while (cursor.moveToNext());
    }

    // return contact list
    return contactList;
    }

    if adding new value to sqlite and show into arraylist, the previous data not clear.
    example :

    i insert db.addContact(new Contact(“1”, “1”));
    and the output will be :
    1,1

    and then i insert new one
    i insert db.addContact(new Contact(“2”, “2”));
    and the output will be :
    1,1
    2,2

    how to change output only showing 2,2 ?
    i try put fotolist.clear() before add but not working

  • whoisnva

    If I instantiate the database in the MainActivity and want to access the data inside of a different Fragment how do I achieve this to make a getAllContacts(); call?

    • whoisnva

      Do I have to pass JSON to SQLlite or can I pass arraylist object direct?

  • gtm

    i need to user search the data in android application using sqlite .only search option

  • Shahzaib Dahani

    Sir You are doing a great job for beginners. I dont find any good startup tutorial than yours.
    I have some questions that i have ask. i have implemented login registration app and i just wanna know.. I mean for now we are using Wamp server l and in code we gave our machine ip address and it wont run on my mobile or on any other mobile too so whats the procedure if i have my own server and it should run on all networks not only on my PC.

  • Abdul moiz

    Sir its great to see your tutorials but i will suggest you that if you can make a list of tutorials so that beginners can start from top to bottom step by step.. maybe beginners will find hard if they start implementing REST API tutorials so you know if you can make a list in which all your tutorials from beginners to advanced. I hope you can understand as i am just a beginner .

    • Yup, I have this in mind. I’ll keep them after few days. Website is getting new look.

  • Rughetto

    hello and thanks for your tutorials! I want to bring to your attention a library that I made for Android to work with persistence through SQLite, SharedPreference, JSON, XML, and other formats. Its name is Krypton and is located at the following URL: https://github.com/xcesco/kripton/.

    With my library, to achieve the same CRUD operations of your tutorial, I would have to write a class contact:

    — Contact.java
    @BindType
    public class Contact {
    //private variables
    long id;
    String name;
    String phoneNumber;
    // same of original class

    }

    And two interfaces to define the SQLite and a DAO database to work with the Contact.

    — ContactsDataSource.java
    @BindDataSource(dao = {DaoContacts.class}, fileName = “contacts.db”)
    public interface ContactsDataSource {
    }

    — DaoContacts.java
    @BindDao(Contact.class)
    public interface DaoContacts {

    // Adding new contact
    @BindSqlInsert
    void addContact(Contact contact);

    // Getting single contact
    @BindSqlSelect(where=”id=${id}”)
    Contact getContact(long id);

    // Getting All Contacts
    @BindSqlSelect
    List getAllContacts();

    // Getting contacts Count
    @BindSqlSelect(value=”count(*)”)
    int getContactsCount();

    // Updating single contact
    @BindSqlUpdate(where=”id=${contact.id}”)
    int updateContact(Contact contact);

    // Deleting single contact
    @BindSqlDelete(where=”id=${contact.id}”)
    void deleteContact(Contact contact);
    }

    So, to perform CRUD operations you can simply write:

    // init library
    KriptonLibrary.init(this);

    // open database and get dao
    BindContactsDataSource dataSource=BindContactsDataSource.open();
    DaoContactsImpl daoContacts = dataSource.getDaoContacts();

    Contact bean=new Contact();
    bean.setName(“name”);
    bean.setPhoneNumber(“123456”);

    // insert a contact
    daoContacts.addContact(bean);

    // update a contact by id
    bean.setPhoneNumber(“654321”);
    daoContacts.updateContact(bean);

    // select all contact
    daoContacts.getAllContacts();

    // select by id
    Contact result=daoContacts.getContact(bean.getId());

    // delete by id
    daoContacts.deleteContact(result);

    // close database
    dataSource.close();

  • Kiruthika Meena

    Sir,
    how to view the table information in list view..?? please explain me sir

  • Beck

    Sir, I have a question about your code to create database. Will each user who launches this program share the same database, or each user has his own SQLite database?

  • jaya

    hi ravi
    am highly impressed with this article or yours
    but want to ask u if we have any app which can actually get contact.db file from ur phone
    bcoz am trying to help my mom to get her contacts back from her old phn
    she cant do all this coding stuff

  • Hosein Mirian

    Dear Ravi. Thanks for your beautiful Tutorial

  • Simon T

    what parameter do i pass to method deleteContact()
    to remove contacts?

    • zheer

      an instant of Contact class but before that make sure you set all variables on the instant by setter method

  • Nurul Musaffa

    Sangat membantu Ravi, terimakasih dari Indonesia

  • Michael Nguyen

    i insert data in sqlite but listview not freshing?
    i’ using notifyDataSetChanged but not freshing?

    please help me.
    Thank you https://uploads.disquscdn.com/images/38519a4a5f06b2c9ad991cadde40f7a1377ca8dd256411586452b6ba9f2aeed7.png

    • notifyDataSetChanged() will work only when you change the data in the arraylist or the data associated with adapter. If you want to see the changes after inserting in SQLite, add the newly inserted items to arraylistsv and call notifyDataSetChanged.

      Also there might be another problem with this line of code
      arraylistsv = db.getListSVAL() as you will get a reference problem.

  • Joy

    Hi…Ravi,
    I would like to want an example of SQLiteDatabase using ContentProvider with more than one URL content.

  • nilisha

    Hello Ravi, I am just started learning android and I have just copied your code as it is to understand sqlite connection in android bt still it is not working in emulator. Can you please personally guide me for this application from the beginning.

    • shashi patil

      problem??

  • fromeroh2009

    Hi Ravi
    I have a problem!
    i dont see the /data/data/xxxxxxxxxxx/databases folder
    when i open the Android Monitor and click File Explorer Tab the data folder is empty???
    please advise
    i tried to do it by ADB but when i execute the “ls” command it says: not permission
    please advise

    • Jamie O’neill

      you can use Stetho to view local db’s and shared preferences.
      add compile ‘com.facebook.stetho:stetho:1.4.2’ to your gradle and Stetho.initializeWithDefaults(this); in the onCreate of your MainActivity.
      Once you run your app connected via usb you can go to “chrome://inspect/#devices” in the chrome browser to examine the database folders.
      More info can be found here http://facebook.github.io/stetho/

  • Raghavan

    hi sir how to add daily reports of point of sale systems in SQLiteDatabase in android plz guide me sir

  • lukas

    Thanks a lot for the great tutorial, really helped me!

  • syed shahid

    How i update a specific contact and override it i call update in MainActivity and Pas new value to update plz anyone can help me
    ContentValues contentValues = new ContentValues();
    contentValues.put(“name”,”Shiraz”);
    contentValues.put(“phone_number”,”30159008182″);
    such value we want to put as an update value

    • yusuf khan

      DatabaseHandler db = new DatabaseHandler(this);

      db.updateContact(new Contact(1, “Shiraz”, “30159008182”));

      1 is the id where you want to update. Revert if you have any doubts.

  • venkat

    //Login
    public class LoginActivity extends Activity {
    Button Login;
    EditText USERNAME, USERPASS;
    String username, userpass;
    Context CTX = this;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.login_layout);
    Login = (Button) findViewById(R.id.b_login);
    USERNAME = (EditText) findViewById(R.id.user_name);
    USERPASS = (EditText) findViewById(R.id.user_pass);
    Login.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View arg0) {
    Bundle b = getIntent().getExtras();
    int status = b.getInt(“status”);
    if (status == 1) {
    Toast.makeText(getApplicationContext(), “Please wait…”, Toast.LENGTH_LONG).show();
    username = USERNAME.getText().toString();
    userpass = USERPASS.getText().toString();
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    Cursor CR = DOP.getInformation(DOP);
    CR.moveToFirst();
    boolean loginstatus = false;
    String NAME = “”;
    do {
    if (username.equals(CR.getString(0)) && (userpass.equals(CR.getString(1)))) {
    loginstatus = true;
    NAME = CR.getString(0);
    }

    } while (CR.moveToNext());
    if (loginstatus) {
    Toast.makeText(getApplicationContext(), “Login Success—-n Welcome ” + NAME, Toast.LENGTH_LONG).show();
    finish();
    } else {
    Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show();
    finish();
    }

    } else if (status == 2) {
    Toast.makeText(getApplicationContext(), “Please wait…”, Toast.LENGTH_LONG).show();
    username = USERNAME.getText().toString();
    userpass = USERPASS.getText().toString();
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    Cursor CR = DOP.getInformation(DOP);
    CR.moveToFirst();
    boolean loginstatus = false;
    String NAME = “”;
    do {
    if (username.equals(CR.getString(0)) && (userpass.equals(CR.getString(1)))) {
    loginstatus = true;
    NAME = CR.getString(0);
    }

    } while (CR.moveToNext());
    if (loginstatus) {
    Toast.makeText(getApplicationContext(), “Login Success—-n Welcome ” + NAME, Toast.LENGTH_LONG).show();
    Intent i = new Intent(LoginActivity.this, DeleteActivity.class);
    Bundle BN = new Bundle();
    BN.putString(“user_name”, NAME);
    BN.putString(“user_pass”, userpass);
    i.putExtras(BN);
    startActivity(i);
    finish();
    } else {
    Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show();
    finish();
    }

    } else if (status == 3) {
    Toast.makeText(getApplicationContext(), “Please wait…”, Toast.LENGTH_LONG).show();
    username = USERNAME.getText().toString();
    userpass = USERPASS.getText().toString();
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    Cursor CR = DOP.getInformation(DOP);
    CR.moveToFirst();
    boolean loginstatus = false;
    String NAME = “”;
    do {
    if (username.equals(CR.getString(0)) && (userpass.equals(CR.getString(1)))) {
    loginstatus = true;
    NAME = CR.getString(0);
    }

    } while (CR.moveToNext());
    if (loginstatus) {
    Toast.makeText(getApplicationContext(), “Login Success—-n Welcome ” + NAME, Toast.LENGTH_LONG).show();
    Intent i = new Intent(“delete_filter”);
    Bundle B = new Bundle();
    B.putString(“user_name”, NAME);
    i.putExtras(B);
    startActivity(i);

    finish();
    } else {
    Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show();
    finish();
    }

    //Intent i = new Intent(“delete_filter”);
    //startActivity(i);
    }

    }
    });
    }

    }

    //Registration

    REG.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View v) {
    user_name = USER_NAME.getText().toString();
    user_pass = USER_PASS.getText().toString();
    con_pass = CON_PASS.getText().toString();

    if(!(user_pass.equals(con_pass)))
    {
    Toast.makeText(getApplicationContext(),”Passwords are not matching”, Toast.LENGTH_LONG).show();
    USER_NAME.setText(“”);
    USER_PASS.setText(“”);
    CON_PASS.setText(“”);
    }
    else
    {
    DatabaseOperations DB = new DatabaseOperations(ctx);
    DB.putInformation(DB, user_name, user_pass);
    Toast.makeText(getApplicationContext(), “Registration success”, Toast.LENGTH_LONG).show();
    finish();

    }

    }
    });
    //Update

    b_update.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View v) {
    user_name = username.getText().toString();
    user_pass = password.getText().toString();
    New_user_name = newuser.getText().toString();
    DOP = new DatabaseOperations(CTX);

    Cursor CR = DOP.getUserPass(DOP, user_name);
    CR.moveToFirst();
    boolean login_status = false;
    if (CR.getCount() > 0) {
    DOP.updateUserInfo(DOP, user_name, user_pass, New_user_name);
    Toast.makeText(getApplicationContext(), “Updation Success…..”, Toast.LENGTH_LONG).show();
    finish();

    } else {
    Toast.makeText(getApplicationContext(), “Invalid user…..Try later”, Toast.LENGTH_LONG).show();

    }
    }
    });

    //DO
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    // Drop older table if existed
    db.execSQL(“DROP TABLE IF EXISTS ” + TableData.TableInfo.TABLE_NAME);
    onCreate(db);// Create tables again

    }

    public void putInformation(DatabaseOperations dop, String name, String pass)

    {
    SQLiteDatabase SQ = dop.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(TableData.TableInfo.USER_NAME, name);
    cv.put(TableData.TableInfo.USER_PASS, pass);
    long k = SQ.insert(TableData.TableInfo.TABLE_NAME, null, cv);
    Log.d(“Database operations”, “One raw inserted”);

    }

    public Cursor getInformation(DatabaseOperations dop) {
    SQLiteDatabase SQ = dop.getReadableDatabase();
    String[] coloumns = {TableData.TableInfo.USER_NAME, TableData.TableInfo.USER_PASS};
    Cursor CR = SQ.query(TableData.TableInfo.TABLE_NAME, coloumns, null, null, null, null, null);
    return CR;

    }

    public Cursor getUserPass(DatabaseOperations DOP, String user) {
    SQLiteDatabase SQ = DOP.getReadableDatabase();
    String selection = TableData.TableInfo.USER_NAME + ” LIKE ?”;
    String coloumns[] = {TableData.TableInfo.USER_PASS};
    String args[] = {user};
    Cursor CR = SQ.query(TableData.TableInfo.TABLE_NAME, coloumns, selection, args, null, null, null);
    return CR;

    }

    public void deleteUser(DatabaseOperations DOP, String user, String pass) {
    String selection = TableData.TableInfo.USER_NAME + ” LIKE ? AND ” + TableData.TableInfo.USER_PASS + ” LIKE ?”;
    //String coloumns[] = {TableData.TableInfo.USER_PASS};
    String args[] = {user, pass};
    SQLiteDatabase SQ = DOP.getWritableDatabase();
    SQ.delete(TableData.TableInfo.TABLE_NAME, selection, args);

    }

    public void updateUserInfo(DatabaseOperations DOP, String user_name, String user_pass, String new_user_name) {
    SQLiteDatabase SQ = DOP.getWritableDatabase();
    String selection = TableData.TableInfo.USER_NAME + ” LIKE ? AND ” + TableData.TableInfo.USER_PASS + ” LIKE ?”;
    String args[] = {user_name, user_pass};
    ContentValues values = new ContentValues();
    values.put(TableData.TableInfo.USER_NAME, new_user_name);
    SQ.update(TableData.TableInfo.TABLE_NAME, values, selection, args);

    }

  • Kinjal Makwana

    how to get database file ?
    i see the /data/ folder blank

    • Elbert

      you must root the phone to be able to acccess system files

      • Kinjal Makwana

        its already rooted

  • Karan galgat

    how can i call the DatabaseHandler methods from activities other than MainActivity class, if the object is made in MainActivity class ?
    Also there is an error in DatabaseHandler class, in getContactsCount method due to which app crashes.(cursor.close(); should not be written since you are using cursor.getCount(); to return value )

    • Karan galgat

      …??

      • moussa maanga

        the error that you close the cursor then call him try this code it work for me

        public int getContactsCount() {
        String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int c=cursor.getCount();
        cursor.close();

        // return count
        return c;
        }

  • annie

    you didnt db.close() for some functions such as getAllContacts

    • If it’s missing, please add it yourself.

  • Ram Venkatraman

    I could execute this program and it worked out. Can you help me where this SQLite table is stored and how do i view this ?
    Thanks in advance.

  • JU quite old

    Thank you for the first tutorial I understood and success follows

  • Nikita Gaba

    Thanks for the tutorial Ravi, could u please help with the location of Databases stored on phone, i looked inside /data/data/com.my.package/ directory , i could not find the database files.

  • Nikitha

    I would like to open database with sqlitedatabase.opendatabase. Do you have any example. How can we give path there.

  • Shadow Walker

    Hi Ravi, could u please help with how i store image in SQLITE and get that image in imageview

    • Inter Lock (Alluka)

      you don’t exactly store the image in the database, you just store its filepath

  • Sachin Gupta

    Hello Ravi, thanks for this Tutorial.
    what should i do if i want to to sort database in alpabetical order by using name.
    (i mean which query should i run when i am getting all contact)

    • BunnyFiscuit

      SELECT * FROM tableName ORDER BY name

  • sandypatel

    great tutorial Ravi , Really Helpful for me.

  • So easy, that’s what i wanted to see in my simple app! Thanks a lot!

  • Parimal Debbarma

    hello sir,It is very nice and clear tutorial.I have a question how to get multiple checkbox click value in place of phone number ? please help.

  • Praful Dhabekar

    contact.set_id(Integer.parseInt(cursor.getString(0)));

    I am getting number format exception here. can you please help me ?

  • hamza abusabra

    thx alot for this tutorial . i have a question can i make 2 table in the database ?

    • You can have any number of tables. Follow the below article to see working with multiple tables.
      https://www.androidhive.info/2013/09/android-sqlite-database-with-multiple-tables/

      • hamza abusabra

        many thx for you ^-^

      • Stajah Lee Hoeflich

        @ravi8x:disqus Thank you so much for these tutorials! I am a student and we are implementing an Android App for our Senior Design Project (it will be an Android mobile device application – bluetooth paired with an RFID tag reading device – that takes/maintains inventory). I have never created an Android App before (school mostly writes programs in C++, Python, etc and not a full application with UI, etc). These tutorials are so extremely helpful! Since the date on this is several years ago, will that impact the code or anything with the libraries as I move forward trying to work with my team to make our app?

  • Yury Oskin

    Hello, i have one question, can i have access to List contacts from another activity, besides intent?
    if contact is static, we have access from any activity, but we can not do this List contacts = db.getAllContacts() because “non static field cannot be referenced from a static context”

  • SSKWEB Android

    thanks a lot… your code is crystal clear

  • Tiago

    Error – reusing cursor after closing
    cursor.close();

    // return count
    return cursor.getCount();
    ————————————————–
    int count = cursor.getCount();
    cursor.close();

    // return count
    return count;

  • Nishant Lokhande

    is this database universal? if i have two users A & B, if A creates a new entry, will that be accessible or visible to user B?(i want it to be that way)
    my english is not good and I’m a noob, hope you understand what i said

    • It’s not Universal that means other apps can’t access your app database. But within the app you can any thing. A can access of B, but that depends how you implement the logic.

  • Vivek

    Great Tutorial! I have a question. What theme are you using for the editor ? I want to apply it to my Android Studio Editor. Please let me know.

    • I use Darcula theme. But this article was written in Eclipse.

  • Pari Gojariya

    Anyone about complete tutorial on SQLite because i’m new in it.
    If yes then please suggest me.I really need it.

  • Kaushik Reddi

    can you give information about this for xml i need to create a app for suggest me a interface please @ravi8x:disqus

    • LaLa

      i need that too

  • Tom M-W

    Perhaps I doing something wrong (I’ve only been doing this Android Studio/Java thing for 2 days), but I think the ‘getContactsCount’ function didn’t work for me. I think this is because it is trying to return with a method that doesn’t exist (it gets closed). I solved it by storing the cursor.getCount() result in an integer, closing the cursor and then returning the integer. Otherwise, it’s worked great, thank you for the great tutorials!

  • LALIT

    Nice tutorial… May you please make a tutorial on Google drive api. I want to fetch data from private drive.

  • Akashh

    How is ID field getting updated ?? like I can see you are not inserting ID or doing anything like auto-increment ?

    • The ID field is declared as INTEGER PRIMARY KEY which doesn’t need an AUTOINCREMENT.

      Refer this doc
      The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
      https://sqlite.org/autoinc.html

  • Ibrahim

    Nice and well organized tutorial. I m new to android development but I known more SQL. I was wondering if there is a way to directly use sql commands for certain actions instead of using methods. Eg “select count(*) from contacts” to get the total count. Thank u

    • I suggest Room database once you are good with this.

  • Arafat Bagalkot

    where is main activity code?

    • You can consider AndroidSQLiteTutorialActivity.class as main activity.

      • Arafat Bagalkot

        extend Activity???i cant see Activity

  • shyam

    hello ravi what about the xml. file?? why u didint used xml file??

  • Paul

    Works well

  • Paul

    A tutorial on using this code with edittexts and buttons (UI) would help.

  • Kevin Fernandes

    the logcat doesnt display the results… instead keeps running…

    11-27 20:51:27.043 2530-2530/? I/BatteryInfoReceiver: ACTION_BATTERY_CHANGED
    11-27 20:51:27.058 1587-1790/? D/LockBoost: tid=1790, prevPriority=0
    11-27 20:51:27.058 1587-1790/? D/LockBoost: thread tid=1790 priority is boosted to -2
    11-27 20:51:27.063 1587-1790/? D/LockBoost: thread tid=1790 priority is reset to 0
    11-27 20:51:27.064 2530-12078/? I/PowerCenterProvider: call METHOD_GET_POWER_SUPPLY_INFO
    11-27 20:51:27.067 1587-1790/? D/LockBoost: tid=1790, prevPriority=0
    11-27 20:51:27.067 1587-1790/? D/LockBoost: thread tid=1790 priority is boosted to -2
    11-27 20:51:27.070 1849-1849/? D/StatusBar: onNotificationPosted: Key: 0|com.pextor.batterychargeralarm|1|null|10235 GroupKey: 0|com.pextor.batterychargeralarm|1|null|10235 Connected: true
    11-27 20:51:27.070 1849-1849/? D/StatusBar: GroupChild: false isContains: false isAutoBundleGroup: false IsUpdate: true IsGroupSummary: false hasIcon: true
    11-27 20:51:27.070 1587-1790/? D/LockBoost: thread tid=1790 priority is reset to 0
    11-27 20:51:27.071 1849-1849/? V/ExpandedNotification: push score:0.0, local score:0.0, pkg:com.pextor.batterychargeralarm
    11-27 20:51:27.071 1849-1849/? D/PhoneStatusBar: updateNotification pkg=com.pextor.batterychargeralarm;id=1

  • Saqib Atiq

    Why there is a db.close() statement at the end of each altering (changing) query while not at the end of a simple query getting data from database? Actually I am facing db close exceptions in my other application, I would love to hear from you about db management in multi threading.

    • Yeah that a bug in my code. The connection has to be closed after all operations.

  • shyam

    How to view the contacts in the edit text field in which we are entering input data??

  • LALIT

    can you tell me when will the onCreate method of SQLite class will be called?

  • PP

    How to look Data like Database Structure? (SQL Database, Oracle Database) NOT Logcat

  • Bob Gordon

    I like it. Do you have as simple code as this for leveraging ContentProviders?

  • Techy

    Thanks for the article Ravi:)

  • Zita

    Thank you

  • nuzha jeetun

    Hi,
    I’ve been doing a project work using android studio. It consists of a simple login and registration. I have to create a Database. Can I just use the Android SQLite Database or should I be using WAMP ( as in and Android Login and Registration with Php, Mysql, and Sqlite tutorial)for my whole project?

    • SQLite is local to one mobile device. Usually login / registration will be used to authenticate a user on any device. Thats why we store the user details on a centralized server (PHP, MySQL) and connect the REST services from mobile device. So that the user details (created from one device) will be available to another device.

      If your requirement is login user on any device, you should consider building REST api using PHP and MySQL.

  • Akshay

    Great article. Easy to follow. Keep up.

  • tugcekolcu

    Hi,

    cursor.moveToFirst() always returns false. What can the problem be ?

    Regards,

    • Have you checked your Query? Print the query in Log. and see whether it is proper or not.

  • Slimen Tunis

    Nice article well done
    But I get this error each time I run the app:
    no such table
    I did exactly as you described here
    what can be the cause of this exception ?!!

    • You are modifying the SQLite create table statements, you need to uninstall and install the app again to see the new tables created.

  • David Richard

    Thank you very much for such a tutorial,I have followed it and it work perfectly for my app,however, I can not save any pictures in my database.If you can help me saving my app images in my database it will great..
    Thanks in advance.

    • We shouldn’t save images into Database, instead they should be stored in Gallery and the image name / path can be saved to database. When retrieving, you can form the path again and show the image from Gallery.

      • Justinas Vijeikis

        I trying to read other column and when i print value into screen i get text null, maybe i mist something. Please help im trying to do this all day

  • Sethu mekala

    please do a video tutorial about this.

  • Vratislav Jindra

    Hi, you’re not closing the Cursor object in getContact and getAllContacts methods. Also you’re not closing the SQLiteDatabase object in getContact, getAllContacts, updateContact and getContactsCount methods. Isn’t that an issue?

    I’m asking because I have a bit more complex app and database, and now I’m starting to get a few crashes because I had some leaked Cursor objects. So I’m now closing all Cursors after I’m done with them. But now I also tried enabling strict mode for my app, and it reveals that I’m not closing the SQLiteDatabase objects and they can also leak. So should I perhaps keep only one SQLiteDatabase object in the DatabaseHandler class?

    Also, I’m using instances of DatabaseHandler in multiple Fragments (and Activities). In each Fragment or Activity, there is only one DatabaseHandler object – but isn’t that also an issue? Should I for example keep only one DatabaseHandler object in memory and pass this object between all my Fragments and Activities? Or keep the only one DatabaseHandler object accessible via a static method in Application class?

    Last but not least – the SQLiteOpenHelper class (and therefore also the DatabaseHandler class) have a close() method. Should I for example close the DatabaseHandler object in onPause or onStop in Fragments/Activites to prevent memory leaks?

    Those single Activity examples are really nice to get started, but once things get more complicated, there is not enough sources and examples on how we should handle these things.

    • Sorry mate, I missed out your valuable comment. I got your points. I just updated the article with an example app. Please check once.

      • Vratislav Jindra

        Thanks for the update. I already stopped using SQLiteDatabase in favor of Firebase DB (I needed real-time sync), but I’ll definitely check that update out to learn something new.

        • Great:)

        • Fatima Mirza

          Is there anyway to connect the this sql database to firebase database?

          • Vratislav Jindra

            No. Firebase is a NoSQL database. It doesn’t have tables and relationships – it’s just a JSON file. The nature of NoSQL databases goes against best practices used in SQL – for example duplicate data can be a good thing here. I had to scratch 90 % of my app and start over when I was moving from SQLite to Firebase. But with that I also went for MVP design pattern, and now that I’m almost finished with it, I’d say it was worth it. Firebase offers much better sync options than I’ve ebev dreamed of, and although querying data can sometimes be a pain, I don’t miss SQLite at all.

          • Fatima Mirza

            :D. Haha, thank you. I am trying to find a way to sync the offline capabilities for Firebase. However, it simply isn’t working (no errors, just not working. Must be making a mistake somewhere, can’t figure it out). Have you worked on the offline capabilities of Firebase?

          • Vratislav Jindra

            Nah, I haven’t looked into that yet. I only enabled cache, and that seems to work fine.

          • Fatima Mirza

            I have figured it out btw. I have used android hive’s code itself.

  • Sumaira Ansari

    https://uploads.disquscdn.com/images/737e31bd1ba1ea454eb612263e6315fd2f4de285cf3d85cceb4fdfe2bb21d9cd.png
    //////
    Hey!
    Kindly help me to solve out this problem…
    As I open the Data folder and then again the sub Data folder then there is no file of my project as there is no error in the program, the only problem of mine is to show my file in the Data folder .
    i have run Emulator of API 24 also API 19 , but still the same issue .
    kindly help me soon

    • muhammadjawwad rohani

      just download api level 21 it will show you,face same problem..

  • Shagun Choudhary

    where is this contactsManager.db database stored in project usually we store it in assets folder ?

    • Here, but you can’t access with without root access /data/data/your.app.package/databases/contactsManager.db

      • Gunjan Sharma

        Sir i am facing the same problem. I can not see the package name under device option.please tell me how can i access the root of the device.

  • Vurjana Govinda

    What is the use of
    if (cursor != null)
    cursor.moveToFirst();
    in the getContact() method.

  • Amjad

    thank you for your tutorial
    but after executed i go to Tools->Android->Android Device Monitor ,
    I don’t find my table in data

  • hiren gavit

    how to access same sqlite database from different activities ?

  • Omar Beshary

    Easy and into the point , thank you .

  • Gurpreet Kaur

    Sir look into getContactsCount().. And I think u should remove this statement…….cursor.close()… Some time it make lots of trouble while copy n pasting ur code in production… U know what i mean…..

    • The article is updated. Please check once.

  • BM

    thank you for this useful article, as i was trying to create a datebase to store my data. my app consists of have live score for football matches. Is it possible to have a save button on every live match that is occurring, when the button is pressed it automatically saves that specific match in an another activity called bookmark? do you have any article on that?

    • You won’t find the code depending on your requirements. Always you have to understand the concepts and build the modules on your own.

  • AbelardoLG

    @ravi8x:disqus , could you show the date of publishing of your posts? Also, you might show the minimum Android OS necessary to run your code. It would be very useful to know this information for us.

    Thanks in advance. ๐Ÿ™‚ Best regards.

    • Hi Abelardo

      All the articles will work with little modifications in case of any problem. I need to spend at least a month to update them all. Are you facing any problem in following this article?

      • AbelardoLG

        Hi Ravi,

        No, I hadn’t got any issue with this article. Thanks. ๐Ÿ™‚

        My last comment was a suggestion in order to avoid most of questions set out here. ๐Ÿ™‚

  • Maximo

    where are RecyclerTouchListener and MyDividerItemDecoration?

  • Dilshod Rakhmanov

    Where can I find the old version of this tutorial? The page was opened, but when browser was updated, there is new version.

  • ahmed mohamed

    can’t query replace raw query !!

  • Jeff

    How you get row, you always getAll

  • Barkha Choithani

    Can you please share styles.xml

    • Pls get it from downloaded code. I forgot to add it.

  • eko hendratno

    please request this using fragment on TabLayout with notifDataChange

  • Gecing Gecingweb

    the time not mach, please anyone help me to make time get from the Calendar