Unit - 5 Storing Structured Data using SQLite Databases

Lesson Plan: Unit - 05
Subject: P15A2AAD - Android Application Development
Topic of Study: Working with SQLite Database
Grade/Level: Master of Computer Applications
Objective: To understand the mechanism of Storing Structured Data using SQLite Databases
Time Allotment: 55 Minutes

• Storing Structured Data using SQLite Databases
• Android file system includes support for application-specific relational databases using SQLite.
• SQLite databases are lightweight and file-based, making them ideally suited for embedded devices.
• These databases and the data within them are private to the application.
• To share application data with other applications, you must expose the data you want to share by making your application a content provider.
• Many of these classes are found in the android.database.sqlite package.
• The package also includes specialized Cursor objects for iterating query results.
• Operations…
• Creating a SQLite Database 
• Finding the Application’s Database File
• Configuring the SQLite Database Properties.
• Creating Tables and Other SQLite Schema Objects.
• Creating a SQLite Database Instance Using the Application Context:
• The simplest way to create a new SQLiteDatabase instance for your application is to use the openOrCreateDatabase() method of your application Context, like this:
• Exa.
  
• How to View Database:
• Exa.
  
• Finding the Application’s Database File on the Device File System:
• Exa.
  
• 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.
• Exa.
  
• Creating Tables and Other SQLite Schema Objects:
• 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
• firstname and lastname text fields.
• Exa.
  
• 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.
• Exa.
  
• How to Insert Record in Table?
• insert() method to add new data to our tables.
• The insert() method returns the id of the newly created record.
• Exa.
  
• How to Edit / Modify Record in Table?:
• You can modify records in the database using the update() method.
• The update() method takes four arguments:
• The table to update records
• A ContentValues object with the modified fields to update
• An optional WHERE clause, in which ? identifies a WHERE clause argument
• An array of WHERE clause arguments, each of which is substituted in place of the ?’s from the second parameter
• Passing null to the WHERE clause modifies all records within the table, which can be useful for making sweeping changes to your database.
• How to Delete record in Table?
• You can remove records from the database using the remove() method.
• The remove() method takes three arguments:
• The table to delete the record from
• An optional WHERE clause, in which ? identifies a WHERE clause argument
• An array of WHERE clause arguments, each of which is substituted in place of the ?’s from the second parameter Passing null to the WHERE clause deletes all records within the table. 
• Exa. This function call deletes all records within the table called tbl_authors: 
• mDatabase.delete(“tbl_authors”, null, null);
• Deleting Tables and Other SQLite Objects:
• You delete tables and other SQLite objects in exactly the same way you create them. 
• Format the appropriate SQLite statements and execute them. For example, to drop our tables and triggers, we can execute three SQL statements
• Exa.
• mDatabase.execSQL(“DROP TABLE tbl_books;”);
• mDatabase.execSQL(“DROP TABLE tbl_authors;”);
• mDatabase.execSQL(“DROP TRIGGER IF EXISTS fk_insert_book;”);
• Closing/Deleting a SQLite Database:
• You should close your database when you are not using it.
• You can close the database using the close() method of your SQLiteDatabase instance, like this:
• mDatabase.close();
• The simplest way to delete a SQLiteDatabase is to use the deleteDatabase() method of your application Context.
• You delete databases by name and the deletion is permanent. 
• You lose all data and schema information.
• deleteDatabase(“my_sqlite_database.db”);