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.
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.
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("•")); // 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.
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 🙂
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.
[…] Android SQLite Database Tutorial | AndroidHive | Tutorials, Games, Apps, Tips Androidhive.info is a blog maintained by Ravi Tamada focused on Android tutorials… Source: http://www.androidhive.info […]
[…] Android tutorial about handling with SQLite database operations with an example. Database Read the original post on DZone… […]
[…] Database Tutorial […]
[…] actually of the defined type, e.g. you can write an integer into a string column and vice versa. Android SQLite Database Tutorial This tutorial introduces a variety of widgets that are useful when creating forms, such as image […]
[…] Reference: Android SQLite Database Tutorial GD Star Ratingloading… […]
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
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
you r right ,,he will give Some half half code,,so we confuse that data is going where
just awsome man… 🙂
good.
it is good. but i have one doubt. how to creae multiple tables
Good example But how to add value like Email adress?
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.
having same problem, says the column doesnt exist, why?
Just a benign thought : isn’t that the whole purpose of a database ?
Can you give an example in Usage of how updateContact works
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.
Nice Tutorial i am new in android its help me a lot ….thank you !!!!!
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
what is path of DB? where can i found my SQLite file here?
Yeah, what is the path of database? Please answer as soon as possible.
BTW. Very useful tutorial !
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 ?
how to use “getAllContacts()” to display all user contacts in listview i m facing difficulty to do this
fixed, please ignore my request 🙂
stupid question but how do we use the database ? I’m looking to use it for making a language application
how value in id Column are generated and inserted
This is what a tutorial should be. I have nothing more to say. just perfect!.
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
a small correction : onUpgrage() shoudl be onUpgrade()
Great post ! A small correction : onUpgrage() shoudl be onUpgrade()
Shoudl Should be Should
Thanks for pointing that out too! 🙂
Thanks for pointing that out too! It should be ‘should’ not ‘Should’ 🙂
This helped me 🙂 Thanks for writing the post.
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.
Expecting content provider tutorial as good as this one soon sir…
Thanks for this tutorial ……………..
plz tell me How to display data from sqlite in textview.
thanks for sample and good exmple.
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!
Hi Ravi,
can you please help me on coding of how to tag a photo which is in image view
Very Nice Example………..
Best tutorial for this I have seen, thanks!
how to update the table with modified values…..????
and where to pass the ID…..??
Nice example ,thank you.
http://www.AndroidSoftware.ir
Hello
I am new with Android development..may i Know exactly how to write the output to textview ?
Use Edittext attribute in your .xml file
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?
Hi Ravi, I would like to ask how can i share the contact database details in another activities?
Hi Ravi, Could You plz update us with some Apache Cordova (Phone Gap)Examples..
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
…………………….
Nice Tutorial……….. 🙂
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).
hi ravi.
nice tutorial,
wondering how to get Id of the record. am getting error
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
good tutorial… 🙂
Hi…Can we use on Database Handler class for more than one table
Nice tutorial, Only have a question: Where’s saved the SqLite db ? I want to take out for processing
How are the “ids” generated?