There are a number of different ways in which the Android API makes it fairly easy to use our app's database. The first class we need to get familiar with is SQLiteOpenHelper
.
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,
which we will extend in our Age Database app, has two methods to override. First, it has an onCreate
method, which is called the first time a database is used, and it therefore makes sense that we would incorporate our SQL in which to create our table structure.
The other method we must override is onUpgrade,
which, you can probably guess, is called when we upgrade our database (ALTER
its structure).
As our database structures become 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 we will help overcome the problem of complexity is to build our queries from parts into a String. We can then pass that 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 we can't get in a muddle with them.
For example, we could declare the following members, which would represent the table name and column names from the fictitious example from earlier. 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";
Notice that in the preceding code, where we will benefit from accessing the String outside the class as well, we declare it public
.
We could then build a query like this in the next example. The following example adds a new entry to our hypothetical database and incorporates Java variables into the SQL statement:
String name = "Onkar"; int score = 95; // Add all the details to the table String query = "INSERT INTO " + TABLE_S_AND_G + " (" + TABLE_ROW_NAME + ", " + TABLE_ROW_SCORE + ") " + "VALUES (" + "'" + name + "'" + ", " + score + ");";
Notice that in the previous code, the regular Java variables, name
and score
, are
highlighted. The previous String called query
is now the SQL statement, exactly equivalent to this:
INSERT INTO StudentsAndGrades ( name, score) VALUES ('Onkar',95);
It is not essential to completely grasp the previous two blocks of code in order to proceed with learning Android programming. But if you want to build your own apps and construct SQL statements that do exactly what you need, it will help to do so. Why not study the previous two blocks of code in order to discern the difference between the pairs of double quote marks "
that are the parts of the String joined together with +
, the pairs of single quote marks '
that are part of the SQL syntax, the regular Java variables, and the distinct semicolons from the SQL statement in the String and Java.
Throughout the typing of the query, Android Studio prompts us as to 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 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, demonstrated as follows:
Cursor c = db.rawQuery(query, null);
Notice that the rawQuery
method returns an object of type Cursor
.
There are several different ways in which we can interact with SQLite, and they each have their advantages and disadvantages. We have chosen to use raw SQL statements as it is entirely transparent as to what we are doing, at the same time as reinforcing our knowledge of the SQL language. Refer to the next tip if you want to know more.
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 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, as in the following 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 are currently reading.
We can access a number of methods of the Cursor
object, including the moveToNext
method, which, unsurprisingly, would move the Cursor
to the next row 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 certain occasions, we will be able to bind a Cursor
to a part of our UI (like RecyclerView
), as we did with an ArrayList
in the Note to Self app, and just leave everything to the Android API.
There are many more useful methods in 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 the Age Database app.
3.133.149.168