Android SQLiteOpenHelper example source code

Here’s an Android SQLite class I use in an Android app I wrote in 2014-2015. I’m sharing it here so I can easily find an Android SQLiteOpenHelper example:

package com.valleyprogramming.justbe;

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

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

public class QuotesDatabaseHelper extends SQLiteOpenHelper {

    private static final String TAG = "VPQuotesDatabaseHelper";

    private static final String DB_NAME = "quotes.sqlite";
    private static final int VERSION = 1;

    private static final String TABLE_QUOTES     = "quotes";
    private static final String COL_QUOTES_ID    = "_id";
            static final String COL_QUOTES_QUOTE = "quote";
    
    private static final String LONG_TAO_QUOTE =
            "The master, by residing in the Tao (the Way), sets an example for all beings.\n\n" +
            "Because he doesn’t display himself, people can see his light.\n\n" +
            "Because he has nothing to prove, people can trust his words.";

    private static final String LONG_TAO_QUOTE_2 = "Watch your thoughts, they become words.\n\n" +
            "Watch your words, they become actions.\n\n" +
            "Watch your actions, they become habits.\n\n" +
            "Watch your habits, they become character.\n\n" +
            "Watch your character, it becomes your destiny.\n\n" +
            "Lao Tzu";

    private static final String WHAT_TIME = "What time is it?\n" +
            "Now\n\n" +
            "Where am I?\n" +
            "Here";

    private static final String PEMA_BE_WITH = "In practicing meditation, we’re not trying to live up to some kind of ideal. Quite the opposite. We’re just being with our experience, whatever it is.\n\nPema Chodron";

    public QuotesDatabaseHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // create the "quotes" table
        db.execSQL("create table quotes (_id INTEGER PRIMARY KEY AUTOINCREMENT, quote TEXT UNIQUE)");
        // some sample data
        db.execSQL("insert into quotes (quote) values ('An attitude of gratitude makes the space for grace')");
        db.execSQL("insert into quotes (quote) values ('Be happy in the moment, that’s enough. Each moment is all we need, not more.\n\nMother Teresa')");
        db.execSQL("insert into quotes (quote) values ('Be here now\n\nRam Dass')");
        db.execSQL("insert into quotes (quote) values ('Be present\n\nBe aware\n\nBe calm')");
        db.execSQL("insert into quotes (quote) values ('Breath in, relax.\n\nBreathe out, release.')");
        db.execSQL("insert into quotes (quote) values ('Do every act of your life as though it were the very last act of your life.\n\nMarcus Aurelius')");
        db.execSQL("insert into quotes (quote) values ('Feelings come and go like clouds in a windy sky. Conscious breathing is my anchor.\n\nThich Nhat Hanh')");
        db.execSQL("insert into quotes (quote) values ('Go through your day as if you were the Dalai Lama undercover')");
        db.execSQL("insert into quotes (quote) values ('Just breathe')");
        db.execSQL("insert into quotes (quote) values ('Walk mindfully. Be aware of your steps, and your breathing.')");
        db.execSQL("insert into quotes (quote) values ('Walk as if you are kissing the Earth with your feet.\n\nThich Nhat Hanh')");
        db.execSQL("insert into quotes (quote) values ('Whatever the present moment contains, accept it as if you had chosen it.\n\nEckhart Tolle')");
        db.execSQL("insert into quotes (quote) values ('When breathing, just breathe')");
        db.execSQL("insert into quotes (quote) values ('You are the sky. Everything else, it’s just the weather.\n\nPema Chodron')");
        db.execSQL("insert into quotes (quote) values ('" + PEMA_BE_WITH + "')");
        db.execSQL("insert into quotes (quote) values ('" + WHAT_TIME + "')");
        db.execSQL("insert into quotes (quote) values ('" + LONG_TAO_QUOTE + "')");
        db.execSQL("insert into quotes (quote) values ('" + LONG_TAO_QUOTE_2 + "')");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // implement schema changes and data massage here when upgrading
    }

    /**
     * @throws java.sql.SQLException if the insert fails (typically because of a
     * "duplicate/unique" constraint.
     */
    public long insert(String quote) {
        ContentValues cv = new ContentValues();
        cv.put(COL_QUOTES_QUOTE, quote);
        return getWritableDatabase().insertOrThrow(TABLE_QUOTES, null, cv);
    }

    public QuoteCursor queryQuotes() {
        // equivalent to "select * from quotes order by quote asc"
        Cursor cursor = getReadableDatabase().query(TABLE_QUOTES,
                null, null, null, null, null, COL_QUOTES_QUOTE + " asc");
        return new QuoteCursor(cursor);
    }

//    // the following code comes from:
//    // http://hmkcode.com/android-simple-sqlite-database-tutorial/
//    // http://developer.android.com/training/basics/data-storage/databases.html
//
//    public ArrayList<String> selectAll() {
//        ArrayList<String> quotes = new ArrayList<>();
//        String query = "select * from " + TABLE_QUOTES + " order by quote asc";
//
//        SQLiteDatabase db = this.getReadableDatabase();
//        Cursor cursor = db.rawQuery(query, null);
//
//        if (cursor.moveToFirst()) {
//            do {
//                // 0 = id, 1 = quote
//                quotes.add(cursor.getString(1));
//            } while (cursor.moveToNext());
//        }
//
//        // TODO do i need to "close" the database connection
//        //db.close();
//
//        return quotes;
//    }

    public void delete(String quote) {
        SQLiteDatabase db = this.getWritableDatabase();
        String selection = COL_QUOTES_QUOTE + " = ?";
        String[] selectionArgs = { quote };
        db.delete(TABLE_QUOTES, selection, selectionArgs);
        //db.close();
    }

    public int update(String oldQuote, String newQuote) {
        SQLiteDatabase db = this.getWritableDatabase();

        String selection = COL_QUOTES_QUOTE + " = ?";
        String[] selectionArgs = { oldQuote };

        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_QUOTES_QUOTE, newQuote);

        int i = db.update(TABLE_QUOTES, contentValues, selection, selectionArgs);
        //db.close();

        return i;
    }

    /**
     * A convenience class to wrap a cursor that returns rows from the "quotes" table.
     * The getQuote() method will give you a Quote instance representing the current row.
     */
    public static class QuoteCursor extends CursorWrapper {

        private static final String TAG = "QuoteCursor";

        public QuoteCursor(Cursor c) {
            super(c);
        }

        /**
         * Returns a Quote object configured for the current row, or null if the current row is invalid.
         * (Converts the current Cursor to a Quote.)
         */
        public Quote getQuote() {
            if (isBeforeFirst() || isAfterLast()) {
                //Log.e(TAG, "ERROR: getQuote(): ABOUT TO RETURN NULL");
                return null;
            }
            Quote quote = new Quote();
            quote.setId(getLong(getColumnIndex(COL_QUOTES_ID)));
            quote.setQuote(getString(getColumnIndex(COL_QUOTES_QUOTE)));
            return quote;
        }
    }

}