lessons 6.3 Saving Data in SQL Databases

Saving Data in SQL Databases


THIS LESSON TEACHES YOU TO

Define a Schema and Contract
Create a Database Using a SQL Helper
Put Information into a Database
Read Information from a Database
Delete Information from a Database
Update a Database
YOU SHOULD ALSO READ

Using Databases
Saving data to a database is ideal for repeating or structured data, such as contact information. This class assumes that you are familiar with SQL databases in general and helps you get started with SQLite databases on Android. The APIs you'll need to use a database on Android are available in the android.database.sqlite package.

Define a Schema and Contract
One of the main principles of SQL databases is the schema: a formal declaration of how the database is organized. The schema is reflected in the SQL statements that you use to create your database. You may find it helpful to create a companion class, known as a contract class, which explicitly specifies the layout of your schema in a systematic and self-documenting way.

A contract class is a container for constants that define names for URIs, tables, and columns. The contract class allows you to use the same constants across all the other classes in the same package. This lets you change a column name in one place and have it propagate throughout your code.

A good way to organize a contract class is to put definitions that are global to your whole database in the root level of the class. Then create an inner class for each table that enumerates its columns.

Note: By implementing the BaseColumns interface, your inner class can inherit a primary key field called _ID that some Android classes such as cursor adaptors will expect it to have. It's not required, but this can help your database work harmoniously with the Android framework.

For example, this snippet defines the table name and column names for a single table:

public final class FeedReaderContract {
// To prevent someone from accidentally instantiating the contract class,
// give it an empty constructor.
public FeedReaderContract() {}

/* Inner class that defines the table contents */
public static abstract class FeedEntry implements BaseColumns {
public static final String TABLE_NAME = "entry";
public static final String COLUMN_NAME_ENTRY_ID = "entryid";
public static final String COLUMN_NAME_TITLE = "title";
public static final String COLUMN_NAME_SUBTITLE = "subtitle";
...
}
}
Create a Database Using a SQL Helper
Once you have defined how your database looks, you should implement methods that create and maintain the database and tables. Here are some typical statements that create and delete a table:

private static final String TEXT_TYPE = " TEXT";
private static final String COMMA_SEP = ",";
private static final String SQL_CREATE_ENTRIES =
"CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
FeedEntry._ID + " INTEGER PRIMARY KEY," +
FeedEntry.COLUMN_NAME_ENTRY_ID + TEXT_TYPE + COMMA_SEP +
FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
... // Any other options for the CREATE command
" )";

private static final String SQL_DELETE_ENTRIES =
"DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;
Just like files that you save on the device's internal storage, Android stores your database in private disk space that's associated application. Your data is secure, because by default this area is not accessible to other applications.

A useful set of APIs is available in the SQLiteOpenHelper class. When you use this class to obtain references to your database, the system performs the potentially long-running operations of creating and updating the database only when needed and not during app startup. All you need to do is call getWritableDatabase() or getReadableDatabase().

Note: Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread, such as with AsyncTask or IntentService.

To use SQLiteOpenHelper, create a subclass that overrides the onCreate(), onUpgrade() and onOp