By Alvin Alexander. Last updated: January 23, 2017
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; } } }