Decode: Creating and using database in Android

Android has many ways to store your apps data, databases being one of them. In this post I will show you how to create database and how to store and retrieve data from it. I assume you are familiar with database and this post will only be on getting started with SQLite database in Android. 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.

Table Structure

Lets take a simple table where you store contacts(name and number) which has 3 columns - id , name , number.

Field	Data Type	Key
id	INT	PK Auto Inc
name	TEXT
number	INT

Defining a contract class

Although it is not compulsory to create this class I would recommend it as it is really helpful. 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

public class CONTACTContract {  
   
    
 /*  Inner class that defines the table contents of the contacts table.  
   
   Create similar classes for every table that the database will have  
   
   
   
   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.  
   
     
  */  public static final class CONTACTSENTRY implements BaseColumns {  
   
   
     //this defines the name of the table in which we will store contacts  
     public static final String TABLE_NAME = "contacts";  
   
     //name of the columns  
     public static final String COLUMN_CONTACT_NAME = "name";  
   
     public static final String COLUMN_CONTACT_NUMBER = "number";  
   
   
   }  
   
 }

Now create a Helper class that create and maintain database and tables

import android.content.Context;  
 import android.database.sqlite.SQLiteDatabase;  
 import android.database.sqlite.SQLiteOpenHelper;  
   
 //this is the contract class we just created  
 import com.example.siddharth.AppName.Contact.CONTACTSENTRY;  
 /** * Manages a local database for Contact data. */public class ContactDbHelper extends SQLiteOpenHelper {  
   
   // If you change the database schema, you must increment the database version.  private static final int DATABASE_VERSION = 2;  
   
   static final String DATABASE_NAME = "contact.db";  
   
   public WeatherDbHelper(Context context) {  
     super(context, DATABASE_NAME, null, DATABASE_VERSION);  
   }  
   
   @Override  public void onConfigure(SQLiteDatabase db) {  
     super.onConfigure(db);  
     //use this line if you are creating table with foreign key and want to turn on   
     //foreign key constraint uncomment this  
     //db.execSQL("PRAGMA foreign_keys=ON;");  
   }  
   
   @Override  public void onCreate(SQLiteDatabase sqLiteDatabase) {  
   
   
   
     //change the not null constraint depending on your requirement  
     final String SQL_CREATE_CONTACT_TABLE = "CREATE TABLE " + CONTACTSENTRY.TABLE_NAME + " ( " +  
         CONTACTSENTRY._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"+  
         CONTACTSENTRY.COLUMN_CONTACT_NAME + " TEXT NOT NULL, " +  
         CONTACTSENTRY.COLUMN_CONTACT_NUMBER + " INTEGER NOT NULL); " ;  
   
     sqLiteDatabase.execSQL(SQL_CREATE_CONTACT_TABLE);  
   
   }  
   
   @Override  public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {  
   
     // tThis only fires if you change the version number for your database.    // It does NOT depend on the version number for your application.    // If you want to update the schema without wiping data, commenting out the next line    // should be your top priority before modifying this method.    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + CONTACTSENTRY.TABLE_NAME);  
   
     onCreate(sqLiteDatabase);  
   }  
 }

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 all getWritableDatabase() orgetReadableDatabase(). This class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary.

Inserting Data

 //To access your database, instantiate your subclass of SQLiteOpenHelper  
 SQLiteDatabase db = new ContactDbHelper(this).getWritableDatabase();  
 ContentValues values = new ContentValues();  
 values.put(CONTACTContract.CONTACTSENTRY.COLUMN_CONTACT_NAME, "siddharth");  
 values.put(CONTACTContract.CONTACTSENTRY.COLUMN_CONTACT_NUMBER, 1234567890);  
   
 //insert operaation returs the rowId of the inserted row  
 //if rowId is -1 after this line it means the insertion failed  
 Long rowId = db.insert(CONTACTContract.CONTACTSENTRY.TABLE_NAME, null, values);  
   
 db.close();

Parameters of insert function:

Parameters
`table`	`String`: the table to insert the row into
`nullColumnHack`	`String`: optional; may be `null`. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided `values` is empty, no column names are known and an empty row can't be inserted. If not set to null, the `nullColumnHack` parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your `values` is empty.
`values`	`ContentValues`: this map contains the initial column values for the row. The keys should be the column names and the values the column values

Bulk Insert

When we want insert a large number of rows in one or multiple tables we can use the mehtod of bulk insert . It is a lot faster than inserting it one by one.

db.beginTransaction();
int returnCount = 0;
try {
    for (ContentValues value : values) {
        normalizeDate(value);
        long _id = db.insert(WeatherContract.WeatherEntry.TABLE_NAME, null, value);
        //if _id == -1 , this means insert failed

        if (_id != -1) {
            returnCount++;
        }
    }
    db.setTransactionSuccessful();
} finally {

    //if db.setTransactionSuccessful() is not called before this all the transactions will be rolled back
    db.endTransaction();
}

Read data from database

To read data from the database we use the query function which returns a Cursor over the result set

Parameters
`table`	`String`: The table name to compile the query against.
`columns`	`String`: A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
`selection`	`String`: A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
`selectionArgs`	`String`: You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
`groupBy`	`String`: A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
`having`	`String`: A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
`orderBy`	`String`: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.

Code

SQLiteDatabase db = new ContactDbHelper(this).getWritableDatabase();  
 Cursor c = db.query(CONTACTContract.CONTACTSENTRY.TABLE_NAME,  
           null,  
           null,  
           null,  
           null,  
           null,  
           null);  
 //use this to see how many rows are there in the result   
 //c.getCount()  
 //if c.getCount() == 0 no rows are in in the result  
 //if c.getCount() !=0  
 //move to first row of the result  
 c.moveToFirst();  
 do{  
 Log.d("name = ", c.getString(c.getColumnIndex(CONTACTContract.CONTACTSENTRY.COLUMN_CONTACT_NAME)));  
 }while (c.moveToNext());  
 db.close();  
 c.close();

The above query function generates the query "SELECT * FROM contact" which return all the rows in the contact database.

Selecting rows from the database:

Cursor c = db.query(CONTACTContract.CONTACTSENTRY.TABLE_NAME,  
           null,  
           CONTACTContract.CONTACTSENTRY._ID+"=?",            new String[] {"1"},  
           null,  
           null,  
           null);

The above query function generates the query "SELECT * FROM location WHERE _id=1"

Deleting data from database

To delete rows from a table, you need to provide selection criteria that identify the rows.

// Define 'where' part of query.  
 String selection = CONTACTContract.CONTACTSENTRY._ID + " LIKE ?";

 // Specify arguments in placeholder order.  
 String[] selectionArgs = { String.valueOf(rowId) };  
 // Issue SQL statement.  
 db.delete(table_name, selection, selectionArgs);

Sources:

Decode

Monday, 28 March 2016

Creating and using database in Android