Saving Data In Android Apps- Part 3

In this tutorial you will learn to work with SQL databases. SQL databases are mostly used or repeating or structured data. The APIs required for this are available in android.database.sqlite package. Here’s a link to other parts of the tutorial.

Saving Data In Android Apps- Part 1
Saving Data In Android Apps- Part 2

Using SQL Databases

The initial thing that you need to do for a SQL database is define its schema: that is the formal declaration of the database. Which is the names of the columns and the name of the table.

Creating a Schema

The best way to define the schema is to create a class and putting the column names as String variables. It is advised to implement BaseColumns class for such a class though its not necessary.
Implementing BaseColumns adds a column _ID which is again not necessary but advisable to use.

The following code creates a class SQLCols that defines the schema of the database named mytable with two columns sno & title:

  public static abstract class SQLCols implements BaseColumns{
    public static final String TABLE_NAME = “mytable”;
    public static final String COLUMN_NAME_SNO = “sno”;
    public static final String COLUMN_NAME_TITLE = “title”;
}

Create a Database Using SQL Helper

Now once the schema is created you can use SQL queries to create and maintain the database.
You can store SQL queries such as for creating and deleting the table in String variables and pass the string to method execSQL() to execute such queries.

For creating and deleting following String variables can be declared:

//storing sql query to create the table as String
private static final String SQL_CREATE_TABLE = “CREATE TABLE” + SQLCols.TABLE_NAME + “ (“ + SQLCols._ID + “ INTEGER PRIMARY KEY,” + SQLCols.COLUMN_NAME_SNO + “ INTEGER,” + SQLCols.COLUMN_NAME_TITLE + “ TEXT)”;

//storing sql query to drop the table as a String
private static final String SQL_DROP_TABLE = “DROP TABLE IF EXISTS ” + SQLCols.TABLE_NAME;

Now that you have stored the general queries to create and drop table. You can use available APIs to create, drop and manipulate SQL Databases. The class SQLiteOpenHelper can be used to do all the above mentioned task. To use the SQLiteOpenHelper , you need to create a subclass of this class and override the onCreate() ,onUpgrade() and onOpen() callback methods. You can also override the onDowngrade() method, but it’s not required.
For Example:

  public class DatabaseHelper extends SQLiteOpenHelper{
    // If you change the database schema, you must increment the database version.
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = “MyDatabase.db" ;

    public DatabaseHelper(Context context){
        super(context, DATABASE_NAME , null , DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase sqlDb){
        sqlDb.execSQL(SQL_CREATE_TABLE);
    }

    public void onUpgrade(SQLiteDatabase sqlDb, int oldVersion , int newVersion){
        //the following code drops the old table and creates new one
        //if the need be you can use previous table and just manipulate that.
        sqlDb.execSQL(SQL_DROP_TABLE);
        onCreate(sqlDb);
    }

    public void onDowngrade(SQLiteDatabase sqlDb, int oldVersion, int newVersion) {
        onUpgrade(sqlDb, oldVersion, newVersion);
    }
} 

Putting Information into a Database

To access your database you have to just create an instance of your SQLiteOpenHelper subclass that you created.

DatabaseHelper dbHelper = new DatabaseHelper(getContext());

Now that creation of database is done and you can access your database through the instance you created, you can start putting information in it. You can do that by passing a ContentValues object to the insert() method. The following sample code shows how to do that:

// Gets the data repository in write mode
SQLiteDatabase db = dbHelper.getWritableDatabase();

// Create a new map of values, where column names are the keys
ContentValues values = new ContentValues();
values.put(SQLCols.COLUMN_NAME_SNO, sno);
values.put(SQLCols.COLUMN_NAME_TITLE, title);

// Insert the new row, returning the primary key value of the new row
long newRowId;
newRowId = db.insert(
         SQLCols.TABLE_NAME,
         “null”,
         values);

The second argument provides the name of a column in which the framework can insert NULL in the event that the ContentValues is empty (if you instead set this to “null”, then the framework will not insert a row when there are no values).

Read Information from the Database

To read from the database you can use the query() method, passing it the columns you want to read. The results of the query() method are returned to you in a Cursor object.
The following sample code shows how to read from the database :

SQLiteDatabase db = dbHelper.getReadableDatabase();

// define all the columns that you want to read
String[] selectedCols = {
    SQLCols._ID, SQLCols.COLUMN_NAME_SNO , SQLCols.COLUMN_NAME_TITLE};

// define the sorting order of the data to be returned 
String sortOrder = SQLCols.COLUMN_NAME_SNO + “ DESC”;

Cursor cursor = db.query(
    SQLCols.TABLE_NAME,     // Table name 
    selectedCols,           // The columns to be selected
    selection,          // The columns for WHERE clause
    selectionArgs,          // The values for the WHERE clause
    null,               // Don’t group the rows
    null,               // Don’t filter by row groups
    sortOrder           // The sorting order
    );

To look at a row in the cursor, use one of the Cursor move methods, which you must always call before you begin reading values. Generally, you 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 of the Cursor 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(SQLCols._ID));

Deleting Information from the database

The following sample code shows how to selectively delete rows of the database:

// Define 'where' part of query.
// the “?” is replaced by the values from selectionArgs
String selection = SQLCols.COLUMN_NAME_SNO + " LIKE ? ” ;
// Specify arguments in placeholder order.
String[] selectionArgs = { “1”,”3” };
// Issue SQL statement.
db.delete(table_name, selection, selectionArgs);

Updating the Database

To update the database you can use the method update().
The following code shows how to do this:

SQLiteDatabase db = dbHelper.getReadableDatabase();

// New value for one column
ContentValues values = new ContentValues();
values.put(SQLCols.COLUMN_NAME_TITLE, title);

// Which row to update, based on the SNO
// the “?” is replaced by the values from selectionArgs
String selection = FeedEntry.COLUMN_NAME_SNO+ " LIKE ?";
String[] selectionArgs = { “1”,”6” };

int count = db.update(
    SQLCols.TABLE_NAME,
    values,
    selection,
    selectionArgs);

This concludes the three part tutorial on how to save data in android apps.

This content has been helpful to you?

Thanks for contributing!

Yes No