[Android Development Tutorial (1)]--saving Data in SQL Databases

Saving data to a database are ideal for repeating or structured data, such as contact information. This class assumes is 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 is available in the package android.database.sqlite .

Define a Schema and contract

One of the main principles of SQL databases is the schema:a formal declaration of what the database is organized. The schema is reflected in the SQL statements so you use to create your database. You could find it helpful to create a companion class, known as a contract class, which explicitly specifies the LA Yout of your schema in a systematic and self-documenting.

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

A good to organize a contract class was to put definitions that was global to your whole database at the root level of The class. Then create a inner class for each table, enumerates its columns.

Note: BaseColumnsby implementing the interface, your inner class can inherit a primary key field called that _ID some Android class Es such as cursor adaptors would expect it to has. It's not a 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 Feed Entry 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 has defined how your database looks, you should implement methods that create and maintain the database and TABL Es. Here is some typical statements that create and delete a table:

private static final String text_type = "TEXT";p rivate static final String comma_sep = ",";p rivate static final string SQ L_create_entries =    "CREATE TABLE" + Feedentry.table_name + "(" +    feedentry._id + "INTEGER PRIMARY KEY," +    F eedentry.column_name_entry_id + text_type + comma_sep +    Feedentry.column_name_title + text_type + COMMA_SEP +    The    rivate static final String sql_delete_entries =    "DROP TABLE" for the "./" and "+ options for the CREATE command") ";p IF EXISTS "+ feedentry.table_name;

Just like files--save on the device's internal storage, Android stores your database in private disk space that ' s Associated application. Your data is secure, and because by default this area is not accessible to other applications.

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

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

To use SQLiteOpenHelper , create a subclass onCreate() that overrides the, onUpgrade() and onOpen() callback methods. Also want onDowngrade() to implement, but it's not required.

For example, here's a implementation of Sqliteopenhelper that uses some of the commands shown above:

public class Feedreaderdbhelper extends Sqliteopenhelper {//If you change the DA    Tabase schema, you must increment the database version.    public static final int database_version = 1;    public static final String database_name = "FEEDREADER.DB";    Public Feedreaderdbhelper (Context context) {Super (context, database_name, NULL, database_version);    } public void OnCreate (Sqlitedatabase db) {db.execsql (sql_create_entries); } public void Onupgrade (sqlitedatabase db, int oldversion, int. newversion) {//This database is only a cache fo R online data, so it upgrade policy is//to simply to discard the data and start over Db.execsql (Sql_delet        E_entries);    OnCreate (DB); } public void Ondowngrade (sqlitedatabase db, int oldversion, int newversion) {onupgrade (db, Oldversion, newvers    ION); }}

To access your database, instantiate your subclass of SQLiteOpenHelper :

Feedreaderdbhelper mdbhelper = new Feedreaderdbhelper (GetContext ());

Put information into a Database

Insert data into the database by passing a ContentValues object to the insert() method:

Gets the data repository in write Modesqlitedatabase db = Mdbhelper.getwritabledatabase ();//Create a new map of values , where column names is the keyscontentvalues values = new Contentvalues (); Values.put (feedentry.column_name_entry_id, ID ); Values.put (Feedentry.column_name_title, TITLE); Values.put (Feedentry.column_name_content, CONTENT);//Insert the New row, returning the primary key value of the new Rowlong Newrowid;newrowid = Db.insert (         feedentry.table_name,         F Eedentry.column_name_nullable,         values);

The first argument for is insert() simply the table name. The second argument provides the name of a column in which the framework can insert NULL in the event that's the IS ContentValues em Pty (if you instead set this "null" to and then the framework would not insert a row when there is no values).

Read information from a Database

To read from a database, use query() the method, passing it your selection criteria and desired columns. The method combines elements insert() update() of and, except the column list defines the data you want to fetch, rather than th e data to insert. The results of the query is returned to your in a Cursor object.

sqlitedatabase db = Mdbhelper.getreadabledatabase ();//Define a projection that Specifies which columns from the database//you'll actually use after this query.  string[] projection = {feedentry._id, feedentry.column_name_title, feedentry.column_name_updated, ...};// How to want the results sorted in the resulting cursorstring SortOrder = feedentry.column_name_updated + "DESC"; Cursor C = db.query (Feedentry.table_name,//the TABLE to query projection,//the C                            Olumns to return selection,//The columns for the WHERE clause Selectionargs, The values for the WHERE clause null,//don ' t group the RO                                 WS NULL,//don ' t filter by row groups SortOrder the sort order); 

To look at a row in the cursor, use one Cursor of the move methods, which you must always call before you begin reading Val UEs. Generally, should start by calling moveToFirst() , which places the ' read position ' on the first entry in the results. For each row, you can read a column ' s value by calling one Cursor of the Get methods, such as getString() or getLong() . For each of the Get methods, you must pass the index position of the "column you Desire", which you can get by calling getColumnIndex() or getColumnIndexOrThrow() . For example:

Cursor.movetofirst (); Long itemId = Cursor.getlong (    cursor.getcolumnindexorthrow (feedentry._id));

Delete information from a Database

To delete rows from a table, you need to provide selection criteria for that identify the rows. The database API provides a mechanism for creating selection criteria, that protects against SQL injection. The mechanism divides the selection specification into a selection clause and selection arguments. The clause defines the columns to look at, and also allows you to combine column tests. The arguments is values to test against that is bound into the clause. Because the result isn ' t handled the same as a regular SQL statement, it's immune to SQL injection.

Define ' where ' part of query. String selection = feedentry.column_name_entry_id + "like?"; /Specify arguments in placeholder order. String[] Selectionargs = {string.valueof (rowId)};//Issue SQL statement.db.delete (table_name, selection, Selectionargs );

Update a Database

When you need to modify a subset of your database values, use the update() method.

Updating The table combines the content values syntax of with the insert() where syntax of delete() .

Sqlitedatabase db = Mdbhelper.getreadabledatabase ();//new value for one columncontentvalues values = New contentvalues (); Values.put (Feedentry.column_name_title, TITLE);//which row to update, based on the idstring selection = Feedentry.column_ name_entry_id + "like?"; String[] Selectionargs = {string.valueof (rowId)};int count = Db.update (    FeedReaderDbHelper.FeedEntry.TABLE_NAME,    values,    selection,    Selectionargs);

(from developer.androind.com)