SQLite is a very popular embedded database, as it combines a clean SQL interface with a very small memory footprint and decent speed.

Moreover, it is a public domain, so everyone can use it.

Lots of firms (Adobe, Apple, Google, Symbian) and open source projects (Mozilla, PHP, Python) all ship products with SQLite.

• For Android, SQLite is "baked into" into the Android runtime, so every Android application can create SQLite databases.
• Since SQLite uses a SQL interface, it is fairly straightforward to use for people with experience in other SQL-based databases.
• However, its native API is not JDBC, and JDBC might be too much overhead for a memory-limited device like a phone, anyway.

• SQLite is a typical relational database, containing tables (themselves consisting of rows and columns), indexes and so on.
• Your application will need its own set of tables and so forth for holding whatever data you wish to hold.
• This structure is generally referred to as a "schema".
   
• It is likely that your schema will need to change over time.
• You might add new tables or columns in support of new features.
• Or, you might significantly reorganize your data structure and wind up dropping some tables while moving the data into new ones.
   
• Android has facilities to assist with handling changing database schemes, mostly centered around the SQLiteOpenHelper class.

• SQLiteOpenHelper is designed to consolidate your code related to two very common problems:
  1. What happens the very first time when your app is run on a device after it is installed?
     • At this point, we do not know yet have a database, and so you will need to create your tables, indexes, starter data, and so on.
  2. What happens the very first time when an upgraded version of your app is run on a device, where the upgraded version is expecting a newer database schema?
     • Your database will still be on the old schema for the older version of the app.
     • You will need to have a chance to alter the database schema to match the needs of the rest of your app.
        
• You will need to create a custom subclass of SQLiteOpenHelper, implementing three methods at minimum:

1. The constructor

• Chaining upward to the SQLOpenHelper constructor.
• This takes the:
  • (1) Context (e.g., an Activity).
  • (2) The name of the database.
  • (3) An optional cursor factory (typically, just pass null).
  • (4) An integer representing the version of the database schema you are using (typically start at 1 and increment from there).

package com.mariamaged.android.sqlite;  
  
import android.content.ContentValues;  
import android.content.Context;  
import android.database.sqlite.SQLiteDatabase;  
import android.database.sqlite.SQLiteOpenHelper;  
import android.hardware.SensorManager;  
  
public class DatabaseHelper extends SQLiteOpenHelper {  
    private static final String DATABASE_NAME = "constants.db";  
    private static final int SCHEMA = 1;  
    static final String TITLE = "title";  
    static final String VALUE = "value";  
    static final String TABLE = "constants";  
  
    public DatabaseHelper(Context context) {  
        super(context, DATABASE_NAME, null, SCHEMA);  
    }

2. onCreate()

• Called when there is no database and your app needs one.
• Which passes you a SQLiteDatabase object, pointing at a newly created database, that you use to populate with tables and initial data, as appropriate.

@Override  
public void onCreate(SQLiteDatabase db) {  
    db.execSQL("CREATE TABLE " + TABLE + " (title TEXT, value REAL)");  
  
    ContentValues cv = new ContentValues();  
  
    cv.put(TITLE, "Gravity, DEATH Star I");  
    cv.put(VALUE, SensorManager.GRAVITY_DEATH_STAR_I);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, MARS");  
    cv.put(VALUE, SensorManager.GRAVITY_MARS);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Mercury");  
    cv.put(VALUE, SensorManager.GRAVITY_MERCURY);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Moon");  
    cv.put(VALUE, SensorManager.GRAVITY_NEPTUNE);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Neptune");  
    cv.put(VALUE, SensorManager.GRAVITY_NEPTUNE);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Pluto");  
    cv.put(VALUE, SensorManager.GRAVITY_PLUTO);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Saturn");  
    cv.put(VALUE, SensorManager.GRAVITY_SATURN);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Sun");  
    cv.put(VALUE, SensorManager.GRAVITY_SUN);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, The Island");  
    cv.put(VALUE, SensorManager.GRAVITY_THE_ISLAND);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Uranus");  
    cv.put(VALUE, SensorManager.GRAVITY_URANUS);  
    db.insert(TABLE, TITLE, cv);  
  
    cv.put(TITLE, "Gravity, Venus");  
    cv.put(VALUE, SensorManager.GRAVITY_VENUS);  
    db.insert(TABLE, TITLE, cv);  
  
}

3. onUpgrade()

• Called when the schema version you are seeking does not match the schema version of the database.
• Which passes you a SQLiteDatabase object and the old and new version numbers, so you can figure out how best to convert the database from the old schema to the new one.
• We are passed the old and new schema versions, so we know what needs to be upgraded.
• Bear in mind that users do not necessarily have to take on each of your application updates, and so you might find that a user skipped a schema version:
   

4. onOpen() [Optional]

• To get control when somebody opens this database.
• Usually, this is not required.

5. onDowngrade() [Optional]

• Android 3.0 introduced onDowngrade(), which will be called if the code requests an older schema than what is in the database presently.
• This is the converse of onUpgrade().
• If your version numbers differ, one of the two methods will be invoked.
• Since normally you are moving forward with updates, you can usually skip onDowngrade().

• To use your SQLiteOpenHelper class, create and hold onto an instance of it.
• Then, when you need a SQLiteDatabase object to do queries or data modification, ask your SQLiteOpenHelper to getReadableDatabase() or getWritableDatabase(), depending on whether or not you will be changing its contents.
   

import android.database.Cursor;  
import android.os.AsyncTask;  
import android.os.Bundle;  
import android.view.View;  
  
import androidx.fragment.app.ListFragment;  
  
import com.mariamaged.android.sqlite.DatabaseHelper;  
  
public class ConstantsFragment extends ListFragment {  
    private DatabaseHelper db = null;  
    private Cursor current = null;  
    private AsyncTask task = null;  
  
    @Override  
  public void onCreate(Bundle savedInstanceState) {  
        super.onCreate(savedInstanceState);  
  
        setHasOptionsMenu(true);  
        setRetainInstance(true);  
    }

@Override  
public void onViewCreated(View v, Bundle savedInstanceState) {  
    super.onViewCreated(v, savedInstanceState);  
  
    db = new DatabaseHelper(getActivity());  
}

• When you are done with database (e.g., your activity is being closed), simply call close() on your SQLiteOpenHelper to release your connection, as ConstantsFragment does (among other things) in onDestroy():

@Override  
public void onDestroy() {  
    super.onDestroy();  
    
    db.close();  
}

• For trivial appas, like the one profiled in this chapter, holding a SQLiteOpenHelper in a data member of your one-and-only activity is fine.
   
• If you, however, you have multiple components - such as multiple activities- all needing to use the database, you are much better served having a singleton instance of your SQLiteOpenHelper, compared to having each activity have its own instance.
  • The reason is threading.
     
• You really should do your database I/O on background threads.
• Opening a database is cheap.
• But working with it (queries, inserts, etc.) is not.
• The SQLiteDatabase object managed by the SQLiteOpenHelper is thread safe.
• So long as all threads are using the same instance.
   

For singleton objects that depend on a Context like SQliteOpenHelper, rather than create the object using a garden-variety Context like an Activity subclass, you really should create it with an instance of a Context, in the form of an Application subclass, created in your process moments after it is started.

• You can retrieve this singleton by calling getApplicationContext() on any other Context.

Advantage of using application subclass instance.

Memory leaks.

• If you put a SQLiteOpenHelper, and use, say, an Activity to create it, then the Activity might not be able to be garbage-collected, because the SQLiteOpenHelper keeps a strong reference to it.
• Since Application is itself a singleton (and, hence, pre-leaked, so to speak), the risks of memory leak diminish significantly.
   
• So, instead of:

db = new DatabaseHelper(getActivity());

in a fragment, as a data member, you might have:

db = new DatabaseHelper(getActivity().getApplicationContext());

with db as a static data member, shared by multiple activities or other components.

• In most cases, your simplest option for executing a query is to call rawQuery() on SQLiteDatabase.
• This takes two parameters:
  1. A SQL SELECT statement.
     • Optionally with ? characters in the WHERE clause.
     • Or ORDER BY.
     • Or similar clauses.
     • Representing parameters to be bound at runtime.
  2. An option String array of parameters to be used to replace the ? characters in the query.
• If you do not use the ? position parameters syntax in your query, you are welcome to pass null as the second parameter to rawQuery().
   
• There are two other query options- query() and SQLiteQueryBuilder.
• These both build a SQL SELECT statement from its component parts.
• These are more cumbersome to use, particularly with complex SELECT statements.
• Mostly, they would be used in cases where, for one reason or another, you do no know the precise query at compile time and find it easier to use these facilities to construct the query from parts at runtime.

private abstract class BaseTask<T> extends AsyncTask<T, Void, Cursor> {  
    @Override  
  public void onPostExecute(Cursor result) {  
        ((CursorAdapter)getListAdapter()).changeCursor(result);  
        current = result;  
        task = null;  
    }  
  
    Cursor doQuery() {  
        Cursor result = db  
                .getReadableDatabase()  
                .query(DatabaseHelper.TABLE,  
               new String[]{"ROWID AS _id", DatabaseHelper.TITLE, DatabaseHelper.VALUE}  
               ,null,null,null,null, DatabaseHelper.TITLE);  
        result.getCount();  
  
        return result;  
    }  
}  
  
private class LoadCursorTask extends BaseTask<Void> {  
    @Override  
  protected Cursor doInBackground(Void... params) {  
        return(doQuery());  
    }  
}  
  
private class InsertTask extends BaseTask<ContentValues> {  
    @Override  
  protected Cursor doInBackground(ContentValues... values) {  
        db.getWritableDatabase()
        .insert(DatabaseHelper.TABLE, DatabaseHelper.TITLE, values[0]);  
  
        return(doQuery());  
    }  
}

Ideally, queries are done on a background thread, as they may take some time.

One approach for doing that is to use an AsyncTask.\

• query() return the Cursor.
• The query is not actually executed until we do something that needs the results set.
• This also holds true for rawQuery().
• Which is why we need to make sure to "touch" the Cursor while we are on the background thread.
   
• onPostExecute() then uses changeCursor() to replace the Cursor in the SimpleCursorAdapter with the results.
• This way the UI will not be frozen while the query is being executed.
• Yet, we only update the UI from the main application thread.
   
• Notice that the first time we try using the SQLiteOpenHelper is in our background thread.
• SQLiteOpenHelper will not try creating our database (e.g., for a new app install) until we call getReadableDatabase() or getWritableDatabase().
• Hence, onCreate() or onUpdate() will end up will wind up being called on the background thread as well, meaning that the time spent creating or updating the database also does not freeze the UI.
   
• Also, note that in onDestroy(), we call cancel() on the AsyncTask if it is not null.
• If the task is still running, calling cancel() will prevent onPostExecute() from being invoked, and we will not have to worry about updating our UI after the fragment has been destroyed.

• Cursor represents the entire result set of the query- all the rows and all the columns that the query returned.

If the cursor result is over 1 MB, it actually only holds a "window" on the data.

• As a close() the Cursor when you are done with it, to free up the heap space it consumes and make that memory available to the rest of your application.

1. Find out how many rows are in the result set via getCount().
2. Iterate over the rows via moveToFirst(), moveToNext(), and isAfterLast().
3. Find out the names of the columns via getColumnNames(), convert these column numbers via getColumnIndex(), and get values for the current row for a given column via methods like getString(), getInt().

Cursor result = db.rawQuery("SELECT _id, name, inventory FROM widgets", null);

while(result.moveToNext()) {
int id = result.getInt(0);
String name = result.getString(1);
int inventory = result.getInt(2);


// do something useful with this.
}
result.close();

• Another way to use a cursor is to wrap it in a CursorAdapter.

Just as ArrayAdapter adapts arrays, CursorAdapter adapts cursor objects, making their data available to an AdapterView like ListView.

• The easiest way to set one of these up is to use SimpleCursorAdapter, which extends CursorAdapter and provides some boilerplate logic for taking values of columns and putting them into row View objects for a ListView.

@Override

public void onViewCreated(View view, Bundle savedInstanceState) {

super.onViewCreated(view, savedInstanceState);

SimpleCursorAdapter adapter=
new SimpleCursorAdapter(
getActivity(), R.layout.row, current, new String[] 
{
DatabaseHelper.TITLE,
DatabaseHelper.VALUE 
},
new int[]
{
R.id.title,
R.id.value
},
0);

setListAdapter(adapter);

if (current==null) {
db = new DatabaseHelper(getActivity());
task = new LoadCursorTask().execute();
}
}

• Here, we are telling SimpleCursorAdapter to take rows out of a Cursor named current, turning each into an inflated Row.layout.row ViewGroup.
• In this case, a RelativeLayout holding a pair of TextView widgets.
   
• For each row in the Cursor, the columns named title and value (represented by TITLE and VALUE constants on the database helper) are to be poured into their respective TextView widgets (R.id.title and R.id.value).

Note, though, that if you are going to use CursorAdapter or its subclasses (like SimpleCursorAdapter), your result set of your query must contain and integer column named _id that is unique for the result set.

Notice that this requirement is on the result set in the Cursor, so if you have a suitable column in a table that is not named _id, you can rename it in your query (e.g., SELECT key AS _id).

However, if you want, you can use the built-in ROWID.

• This "id" value is then supplied to methods like onListItemClick(), to identify what item the user clicked upon in the AdapterView.
   

• Also, note that you cannot close the Cursor used by a CursorAdapter until you no longer need the CursorAdapter.
• That is why we do not close the Cursor until onDestroy() of the fragment.

@Override  
public void onDestroy() {  
    if(task!=null) task.cancel(false);  
  
    ((CursorAdapter)getListAdapter()).getCursor().close();  
    db.close();  
      
    super.onDestroy();  
}