Lecture
In this lesson:
- we use transactions when working with the database
I think it is not necessary to explain what a DB transaction is . It is used when working with data on the " all or nothing " principle. Ie, for example, you need to insert a packet of data. But it is necessary to insert so that either everything is inserted or nothing is inserted. And if in the process half of the records have passed, and the other is not, it should be possible to roll back the changes.
Let's write a simple application and explore the possibilities of SQLite in this regard.
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
Open MainActivity.java and write:
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);
}
// класс для работы с БД
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) {
}
}
} Parse the code. I created several methods where I grouped the operations: insert - a set of operations to insert a record, read - read all records, delete - delete all records. DBHelper class - for database management. We will be interested in the myActions method. Now in it, we connect to the database, clear the table mytable, insert a row with the value val1 , output all records from the table to the log, and disconnect.
All save, run the application. We look at the log:
--- onCreate Activity ---
--- onCreate database ---
Delete all from table mytable
Insert in table mytable value = val1
Read table mytable
Records count = 1
val1
That's right, the record is inserted and displayed.
Now we will try to use transaction. Let's fix myActions code for 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 the database, clean the table, open a transaction with the beginTransaction method, insert val1 , close the transaction with the endTransaction method, insert val2 , output the contents to the log and disconnect. We save everything, run and watch the log:
--- 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
The log shows that we inserted two records, but only the second one passed. The one that was in the transaction - did not sign up. This happened because we did not explicitly indicate that the transaction should be successfully closed . If this is not done, then when closing the transaction, all operations will be canceled . Let's fix it. Let's 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();
} We connect to the database, clean the table, open the transaction, insert val1 , confirm the success of the transaction with the setTransactionSuccessful method, insert val2 , close the transaction, insert val3 , display the contents and disconnect.
Save, run, look at the log:
--- 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
Pasted all three records. Pay attention - in spite of the fact that we inserted val2 after confirming the success of the transaction, the record was inserted, entered into this transaction. But to carry out operations after confirming the transaction is not recommended by help.
Transaction when opening puts lock on base. Let us make sure of this, try to create a new connection to the database during the transaction. 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, clean the table, open a transaction, insert a record, create a new connection to the database - db2 , read the contents of the second connection, close the second connection, successfully close the transaction, read the contents of the first connection, close the first connection.
All save and run. We look at the log:
--- 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 see that when trying to create a second connection to the database, an error SQLiteException occurred - the database was locked by an open transaction. If you comment or delete the transaction management lines and run the code again, then everything will be successful, because there will be no locks.
Surely there are some questions on this topic. I will try to answer some here.
The SQLiteDatabase and SQLiteOpenHelper have a close method. What is the difference between them? Which one to use to close the connection?
Here you need to understand one thing - the SQLiteOpenHelper object always provides only one connection. I will try to explain this mechanism. The SQLiteOpenHelper object has an internal mDatabase attribute of type SQLiteDatabase . When we call the getWritableDatabase method, the SQLiteOpenHelper object checks: if mDatabase is not null and not closed, then it goes as a return . Otherwise, SQLiteOpenHelper connects to the database, writes a new SQLiteDatabase object to the mDatabase and returns it to us. Those. The getWritableDatabase method either returns an existing database connection or creates a new one if there is no connection. When the close method for SQLiteOpenHelper is executed , the close call for mDatabase is called and the code mDatabase = null is executed.
Consider an example. Change the myActions method 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());
} Then from the same dbh we get db2 . dbh again checks its internal mDatabse , sees that it is no longer null and is not closed , and returns it to our db2 . As a result, db and db2 are equal and refer to the same object. We check this with the equals method. Next, check that db and db2 are open . Then we close only db2 , and once again check for openness of both objects.
Save, run, look at the log:
--- onCreate Activity ---
db = db2 - true
db open - true, db2 open - true
db open - false, db2 open - false
We see that equals returned true . Then you can see that db and db2 are open . And after closing db2, we see that both objects are closed. All because “both objects” are just two references to one object .
If the code instead of db2.close () put dbh.close () - the effect will be the same. dbh will call the close method for mDatabase and reset it - mDatabase = null . And db and db2 will refer to the closed SQLiteDatabase .
I think that it is more correct to call close for SQLiteOpenHelper , and not for SQLiteDatabase . Because guaranteed to close the current open connection and reset the internal link to the object.
If you need to get a second open connection to the database, then you need to create a new instance of DBHelper and call getWritableDatabase . We did it a little higher in the example with the transaction lock.
What is the difference between getWritableDatabase and getReadableDatabase ? Judging by the help, in the usual situation both methods return the same thing . And both will allow you to read and change the database. In the case of, for example, the problem of lack of free space on the device, the getReadableDatabase method returns a read- only database, and getWritableDatabase will give an error .
In various sources, when working with a database, not just id, but _id are used as the name of the identifier field in the table. Why?
The answer was in the docks on the Cursor-adapters. Quote: "The Cursor must include a column named" _id "or this class will not work.". Those. if you plan to use Cursor adapters, it is necessary that the table contain the _id field, otherwise the adapter will not work.
The openTransaction transaction open method locks in EXCLUSIVE mode. Those. The database is blocked for both reading and writing for other connections. In the Android SDK version older than 2.3.3, the beginTransactionNonExclusive method has appeared , which puts the lock in the IMMEDIATE mode. I suspect that this will allow other connections to be read.
If there is a desire to dive into the topic in more detail, go here.
And by the way, the recommended form for using transactions is:
db.beginTransaction();
try {
...
db.setTransactionSuccessful();
} finally {
db.endTransaction();
} It is very important! Those. if you have opened a transaction, performed any actions and did not close the transaction, then all operations will be considered unsuccessful and the changes will not be made to the database. Therefore, closing a transaction must be performed and finally it guarantees us.
In the next lesson:
- we change the version and update the database structure in onUpgrade
Comments
To leave a comment
Mobile Programming (Android IOs)
Terms: Mobile Programming (Android IOs)