Before we can learn how to use SQLite with Android, we need to first learn the basics of how to use SQLite in general, in a platform neutral context.
Let's look at some example SQL code that could be used on an SQLite database directly, without any Java or Android classes, and then we can more easily understand what our Java code is doing later on.
SQL has keywords, much like Java, that cause things to happen. Here is a flavor of some of the SQL keywords we will soon be using:
INSERT: Allows us to add data to the databaseDELETE: Allows us to remove data from the databaseSELECT: Allows us to read data from the databaseWHERE: Allows us to specify the parts of the database that match a specific criteria we want toINSERT,DELETE,orSELECTfromFROM: Used for specifying a table or column name in a database
Note
There are many more SQLite keywords than this and, for a comprehensive list, take a look at this link: https://sqlite.org/lang_keywords.html.
In addition to keywords, SQL has types. Some examples of SQL types are as follows:
- integer: Just what we need for storing whole numbers
- text: Perfect for storing a simple name or address
- real: For large floating point numbers
Note
There are many more SQLite types than this and, for a comprehensive list, take a look at this link: https://www.sqlite.org/datatype3.html.
Let's look at how we can combine those types with keywords to create tables, and add, remove, modify, and read data, using full SQLite statements.
It would be a perfectly decent question to ask why we don't first create a new database. The reason for this is that every app has access to a SQLite database by default. The database is private to that app. Here is the statement we would use to create a table within that database. I have highlighted a few parts to make the statement clearer:
create table StudentsAndGrades _ID integer primary key autoincrement not null, name text not null, score int;
The previous code creates a table called StudentsAndGrades with an integer row id that will be automatically increased (incremented) each time a row of data is added.
The table will also have a name column that will be of the text type and cannot be blank (not null).
It will also have a score column that will be of the int type. Also notice that the statement is completed by a semicolon.
Here is how we might insert a new row of data into that database:
INSERT INTO StudentsAndGrades
(name, score)
VALUES
("Bart", 23);The previous code added a row to the database. After the preceding statement, the database will have one entry with the values (1, "Bart", 23) for the columns (_ID, name, and score).
Here is how we might insert another new row of data into that database:
INSERT INTO StudentsAndGrades
(name, score)
VALUES
("Lisa", 100);The previous code added a new row of data with the values (2, "Lisa", 100) for the columns (_ID, name, and score).
Our spreadsheet-like structure would now look like the following diagram:
Here is how we would access all the rows and columns from our database;
SELECT * FROM StudentsAndGrades;
The previous code asks for every row and column. The * symbol can be read as all.
We can also be a little more selective, as the following code demonstrates:
SELECT score FROM StudentsAndGrades where name = "Lisa";
The previous code would only return 100, which, of course, is the score associated with the name Lisa.
We can even add new columns after the table has been created and the data added. This is simple as far as the SQL is concerned, but can cause some issues with regard to a user's data on already published apps. The next statement adds a new column called age that is of the int type:
ALTER TABLE StudentsAndGrades
ADD
age int;There are many more data types, keywords, and ways to use them than we have seen so far. Next, let's look at the Android SQLite API and we will begin to see how we can use our new SQLite skills.