Configuring the SQLite Database Properties

Now that you have a valid SQLiteDatabase instance, it’s time to configure it. Some important database configuration options include version, locale, and the thread-safe locking feature. import java.util.Locale; mDatabase.setLocale(Locale.getDefault()); mDatabase.setLockingEnabled(true); mDatabase.setVersion(1);

Creating Tables and Other SQLite Schema Objects

Creating tables and other SQLite schema objects is as simple as forming proper SQLite statements and executing them.The following is a valid CREATE TABLE SQL statement. This statement creates a table called tbl_authors.The table has three fields: a unique id number, which auto-increments with each record and acts as our primary key, and firstname and lastname text fields: CREATE TABLE tbl_authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT); You can encapsulate this CREATE TABLE SQL statement in a static final String variable (called CREATE_AUTHOR_TABLE) and then execute it on your database using the execSQL() method: mDatabase.execSQL(CREATE_AUTHOR_TABLE); The execSQL() method works for nonqueries.You can use it to execute any valid SQLite SQL statement. For example, you can use it to create, update, and delete tables, views, triggers, and other common SQL objects. In our application,we add another table called tbl_books.The schema for tbl_books looks like this: CREATE TABLE tbl_books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT,dateadded DATE, authorid INTEGER NOT NULL CONSTRAINT authorid REFERENCES tbl_authors(id) ON DELETE CASCADE); Unfortunately, SQLite does not enforce foreign key constraints. Instead,we must enforce them ourselves using custom SQL triggers. So we create triggers, such as this one that enforces that books have valid authors:SELECT RAISE(ROLLBACK, ‘insert on table \”tbl_books\” violates foreign key constraint \”fk_authorid\”’) WHERE (SELECT id FROM tbl_authors WHERE id = NEW.authorid) IS NULL; END;”; We can then create the trigger simply by executing the CREATE TRIGGER SQL statement: mDatabase.execSQL(CREATE_TRIGGER_ADD); We need to add several more triggers to help enforce our link between the author and book tables, one for updating tbl_books and one for deleting records from tbl_authors.

Creating, Updating, and Deleting Database Records

Now that we have a database set up,we need to create some data.The SQLiteDatabase class includes three convenience methods to do that.They are, as you might expect, insert(), update(), and delete(). Inserting Records We use the insert() method to add new data to our tables.We use the ContentValues object to pair the column names to the column values for the record we want to insert. For example, here we insert a record into tbl_authors for J.K. Rowling: import android.content.ContentValues; ContentValues valuesnew ContentValues(); values.put(“firstname”, “J.K.”); values.put(“lastname”, “Rowling”); ong newAuthorID = mDatabase.insert(“tbl_authors”, null, values); The insert() method returns the id of the newly created record.We use this author id to create book records for this author. Tip There is also another helpful method called insertOrThrow(), which does the same thing as the insert() method but throws a SQLException on failure, which can be helpful, especially if your inserts are not working and you’d really like to know why. You might want to create simple classes (that is, class Author and class Book) to encapsulate your application record data when it is used programmatically.

Passing null to the WHERE clause modifies all records within the table, which can be useful for making sweeping changes to your database. Most of the time, we want to modify individual records by their unique identifier. The following function takes two parameters: an updated book title and a bookId.We find the record in the table called tbl_books that corresponds with the id and update that book’s title. Again, we use the ContentValues object to bind our column names to our data values:Passing null to the WHERE clause deletes all records within the table. For example, this function call deletes all records within the table called tbl_authors: mDatabase.delete(“tbl_authors”, null, null); Most of the time, though,we want to delete individual records by their unique identifiers. The following function takes a parameter bookId and deletes the record corresponding to that unique id (primary key) within the table called tbl_books.Often you have multiple database operations you want to happen all together or not at all. You can use SQL Transactions to group operations together; if any of the operations fails, you can handle the error and either recover or roll back all operations. If the operations all succeed, you can then commit them. Here we have the basic structure.