The SQLiteDatabase class is the class that represents the actual database. The SQLiteOpenHelper class, however, is where most of the action takes place. This class will enable us to get access to a database and initialize an instance of SQLiteDatabase.

In addition, the SQLiteOpenHelper class, which we will extend in our forthcoming mini app, has two methods to override. First, it has an onCreate method, which is called the first time a database is used, and therefore it makes sense that we place our SQL in this method to create our table structure.

The other method we must override is onUpgrade, which, as you can probably guess, is called when we upgrade our database (ALTER its structure). Exactly how this works is best explained with a practical example, which we will see in a few chapter's time.

As our database structures get more complex and as our SQL knowledge grows, our SQL statements will get quite long and awkward. The potential for errors is high.

The way in which we will help overcome the problem of complexity is by building our queries from parts into a String. We can then pass this String to the method (we will see this soon) that will execute the query for us.

Furthermore, we will use final strings to represent things such as table and column names so that we can't get in a muddle with them.

For example, we could declare the following members that would represent the table names and column names from the earlier fictitious example. Note that we will also give the database itself a name and have a string for that too:

private static final String DB_NAME = "MyCollegeDB";
private static final String TABLE_S_AND_G = " StudentsAndGrades";

public static final String TABLE_ROW_ID = "_id";
public static final String TABLE_ROW_NAME = "name";
public static final String TABLE_ROW_SCORE = "score";

As you can see in the preceding code, we will benefit from accessing the string outside the class as well because we declare it public.

We could then build a query like this next example. The following example adds a new entry to our hypothetical database and incorporates our Java variables into the SQL statement:

String name = "Divij";
int score = 94;

// Add all the details to the table
String query = "INSERT INTO " + TABLE_S_AND_G + " (" +
    TABLE_ROW_NAME + ", " +
    TABLE_ROW_SCORE + ") " +
    "VALUES (" +
    "'" + name + "'" + ", " +
    score +
    ");"; 

Note that in the previous code, the regular Java variables name and score are highlighted. The previous String called query is now the SQL statement that is exactly equivalent to the following code:

INSERT INTO StudentsAndGrades (
  name, score)
  VALUES ('Divij',94);

Throughout the typing of the query, Android Studio prompts us the names of our variables, making the chances of an error much less likely even though it is more verbose than simply typing the query.

Now, we can use the classes that we introduced previously to execute the query:

// This is the actual database
private SQLiteDatabase db;

// Create an instance of our internal CustomSQLiteOpenHelper class
CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);

// Get a writable database
db = helper.getWritableDatabase();

// Run the query
db.execSQL(query);

When adding data to the database, we will use execSQL, as in the previous code, and when getting data from the database, we will use the rawQuery method, as shown in the next code snippet:

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

Note that the rawQuery method returns an object of the Cursor type.

In addition to the classes that give us access to the database and the methods that allow us to execute our queries, there is the issue of exactly how the results that we get back from our queries are formatted.

Fortunately, there is the Cursor class. All our database queries will return objects of the Cursor type. We can use the methods of the Cursor class to selectively access the data returned from the queries like we've used in this code:

Log.i(c.getString(1), c.getString(2)); 

The previous code would output to logcat the two values stored in the first two columns of the result that the query returned. It is the Cursor object itself that determines which row of our returned data we will currently read.

We can access a number of methods of the Cursor object including the moveToNext method, which unsurprisingly would move Cursor to the next row that is ready for reading:

c.moveToNext();

/*
  This same code now outputs the data in the
  first and second column of the returned 
  data but from the SECOND row.
*/

Log.i(c.getString(1), c.getString(2));

On some occasions, we will be able to bind Cursor to a part of our UI (such as ListView) and just leave everything to the Android API. We will see this when we add a database to the Where it's snap app in the next chapter.

There are many more useful methods of the Cursor class, some of which we will see soon.

Now, we can see how all this theory comes together and how we will structure our database code in an actual working mini app.