This lesson:

- using transactions.

I think there is no need to explain what is a database transaction. It is used when working with data on the principle of "all or nothing." I.e., for example, you need to insert a data package. But you need to insert it so that either everything is inserted or nothing is inserted. And, if a half of the records passed in the process, and the other is not - there should be an opportunity to cancel changes.

Let's write a simple application and explore the possibilities of SQLite in this question.

Create a project:

Project name: P0381_SQLiteTransaction
Build Target: Android 2.3.3
Application name: SQLiteTransaction
Package name: ru.startandroid.develop.p0381sqlitetransaction
Create Activity: MainActivity

Let’s open MainActivity.java and code:

package ru.startandroid.develop.p0381sqlitetransaction;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends Activity {

  final String LOG_TAG = "myLogs";

  DBHelper dbh;
  SQLiteDatabase db;

  /** Called when the activity is first created. */
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    Log.d(LOG_TAG, "--- onCreate Activity ---");
    dbh = new DBHelper(this);
    myActions();
  }

  void myActions() {
    db = dbh.getWritableDatabase();
    delete(db, "mytable");
    insert(db, "mytable", "val1");
    read(db, "mytable");
    dbh.close();
  }

  void insert(SQLiteDatabase db, String table, String value) {
    Log.d(LOG_TAG, "Insert in table " + table + " value = " + value);
    ContentValues cv = new ContentValues();
    cv.put("val", value);
    db.insert(table, null, cv);
  }

  void read(SQLiteDatabase db, String table) {
    Log.d(LOG_TAG, "Read table " + table);
    Cursor c = db.query(table, null, null, null, null, null, null);
    if (c != null) {
      Log.d(LOG_TAG, "Records count = " + c.getCount());
      if (c.moveToFirst()) {
        do {
          Log.d(LOG_TAG, c.getString(c.getColumnIndex("val")));
        } while (c.moveToNext());
      }
      c.close();
    }
  }

  void delete(SQLiteDatabase db, String table) {
    Log.d(LOG_TAG, "Delete all from table " + table);
    db.delete(table, null, null);
  }

  // a class for database interaction
  class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
      super(context, "myDB", null, 1);
    }

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, "--- onCreate database ---");

      db.execSQL("create table mytable ("
          + "id integer primary key autoincrement," 
          + "val text"
          + ");");
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
  }

}

Let’s go through the code. I have created several methods to group operations within them:
insert - set of operations for row inserting, read - all data reading, delete - all data deleting. DBHelper is a class for database management. myActions method is what we are interested in. We connect to database, clear mytable, insert row with val1 value, display all table data in logs and close connection within it.

Let’s save and launch the app:

--- onCreate Activity ---
--- onCreate database ---
Delete all from table mytable
Insert in table mytable value = val1
Read table mytable
Records count = 1
val1

Everything looks good, the row was inserted and displayed.

Now let’s try to use transaction. Replace myActions code with this:

  void myActions() {
    db = dbh.getWritableDatabase();
    delete(db, "mytable");
    db.beginTransaction();
    insert(db, "mytable", "val1");
    db.endTransaction();
    insert(db, "mytable", "val2");
    read(db, "mytable");
    dbh.close();
  }

We connect to database, clear the table, and open transaction with beginTransaction method, insert val1, close transaction with endTransaction, insert val2, display content in logs and close the connection. Let’s save the project, run it and look in logs:

--- onCreate Activity ---
Delete all from table mytable
Insert in table mytable value = val1
Insert in table mytable value = val2
Read table mytable
Records count = 1
val2

From logs we can see, that we have inserted two rows, but only the second one has passed. That one which was in the transaction hasn’t pass. It happened because we did not explicitly indicate that the transaction should be successfully closed. If this is not done, then when the transaction is closed, all operations will be canceled. Let's get better and rewrite myActions:

  void myActions() {
    db = dbh.getWritableDatabase();
    delete(db, "mytable");
    db.beginTransaction();
    insert(db, "mytable", "val1");
    db.setTransactionSuccessful();
    insert(db, "mytable", "val2");
    db.endTransaction();
    insert(db, "mytable", "val3");
    read(db, "mytable");
    dbh.close();
  }

--- onCreate Activity ---
Delete all from table mytable
Insert in table mytable value = val1
Insert in table mytable value = val2
Insert in table mytable value = val3
Read table mytable
Records count = 3
val1
val2
val3

All tree rows were inserted. Note, although val2 was inserted after transaction success confirming, the was inserted within this transaction. But operation’s processing after confirming the transaction is not recommended by the documentation.

The Transaction blocks database when opens. Let us verify this, we will try to create a new database connection while transaction will be processing. Let’s rewrite myActions:

  void myActions() {
    try {
      db = dbh.getWritableDatabase();
      delete(db, "mytable");

      db.beginTransaction();
      insert(db, "mytable", "val1");

      Log.d(LOG_TAG, "create DBHelper");
      DBHelper dbh2 = new DBHelper(this);
      Log.d(LOG_TAG, "get db");
      SQLiteDatabase db2 = dbh2.getWritableDatabase();
      read(db2, "mytable");
      dbh2.close();

      db.setTransactionSuccessful();
      db.endTransaction();

      read(db, "mytable");
      dbh.close();

    } catch (Exception ex) {
      Log.d(LOG_TAG, ex.getClass() + " error: " + ex.getMessage());
    }
  }

We connect to the database, clear the table, open the transaction, insert the row, create a new database connection db2, get data with the second connection, close it, successfully close the transaction, get data with the first connection and close it.

Let’s save the project and look in logs:

--- onCreate Activity ---
Delete all from table mytable
Insert in table mytable value = val1
create DBHelper
get db
class android.database.sqlite.SQLiteException error: database is locked

We can see that we got SQLiteException while we were trying to create the second connection because the database was blocked with the opened transaction. Now if you commit or delete the code rows which rule the transaction and launch the code execution again, it will run successfully, because there is no blocks any more.

For sure, you have some questions on this topic. I’ll try to answer them here.

close

SQLiteDatabase and SQLiteOpenHelper both have close() method. But what’s the difference and which of them should be used for connection closing?

One tip should be understood: SQLiteOpenHelper always provides only one connection. I’ll try to explain you this mechanism. SQLiteOpenHelper object have an inner attribute SQLiteDatabase mDatabase. When we call getWritableDatabase method, SQLiteOpenHelper SQLiteOpenHelper checks if mDatabase is not null and is not closed, and returns it.

Otherwise SQLiteOpenHelper connects to database, creates new SQLiteDatabase object within mDatabase and returns it. I.e. getWritableDatabase method either returns an existing database connection, or creates a new one in case of its absence. While close method is processing for SQLiteOpenHelper, mDatabase’s close method is called and mDatabase gets being null.

Consider the example. Let’s rewrite myActions again:

  void myActions() {
    db = dbh.getWritableDatabase();
    SQLiteDatabase db2 = dbh.getWritableDatabase();
    Log.d(LOG_TAG, "db = db2 - " + db.equals(db2));
    Log.d(LOG_TAG, "db open - " + db.isOpen() + ", db2 open - " + db2.isOpen());
    db2.close();
    Log.d(LOG_TAG, "db open - " + db.isOpen() + ", db2 open - " + db2.isOpen());
  }

--- onCreate Activity ---
db = db2 – true
db open - true, db2 open – true
db open - false, db2 open - false

We see that equals method returned us true. Then we see, that db and db2 are opened, and after closing db2 we see, that both objects are closed. All because both these objects reference to the same object.

In case we change db2.close() with dbh.close() within the code, we will get the same result. dbh will call close() method for mDatabase and reset its value to null, and db and db2 will refer to closed SQLiteDatabase object.

As for me, calling close for SQLiteOpenHelper will be more correctly than for SQLiteDatabase. Because this way the current opened connection will be guaranteed closed and the inner object reference will be reset.

You have to create a new instance of DBHelper class and call its getWritableDatabase method if you need to get a second database connection. We it did this way within transaction block example.

read write

What’s the difference between getWritableDatabase and getReadableDatabase? According to help, both methods return the same in a common situation. And both allows to read and change database. But in case of no free space trouble on the device, getReadableDatabase will return database only for reading, and getWritableDatabase will throw an error.

_id, as a name of identifier field

_id is used for database interaction in different sources instead of simple id. Why? The answer was found in Cursor adapter’s README. Quote: "The Cursor must include a column named "_id" or this class will not work". I.e. you are going to use Cursor adapter, it’s necessary for table to contain _id field, otherwise adapter won’t work.

Blocking

The transaction opening method beginTransaction puts a block with EXCLUSIVE mode. I.e. the database is locked for reading and changing for other connections. There is beginTransactionNonExclusive method in SDK higher than 2.3.3 which puts block in IMMEDIATE mode. I suppose, it will allow other connections to read data.

You can follow this link to learn more.

Syntax

By the way, this is a recommended form for transaction usage:

    db.beginTransaction();
    try {
      ...
      db.setTransactionSuccessful();
    } finally {
      db.endTransaction();
    }

It’s very important! I.e. you opened a transaction and didn’t close after some action, all operations will be considered as unsuccessful and changes won’t be applied. That’s why transaction must be closed and finally guarantees it.

The next lesson:

- change version and update database structure within onUpgrade