Android SQLite Database Transaction learning, androidsqlite

Android SQLite Database Transaction learning, androidsqlite

SQLite is a lightweight relational database built into the Android system. It is fast in operation and occupies a small amount of resources. Generally, only a few hundred KB of memory is enough. SQLite not only supports standard SQL syntax, but also complies with ACID transactions of the database.

Simulate an Application Scenario: for a transfer operation, the bank will deduct the transfer amount from your account first, and then add the same amount to the recipient's account. It seems that there is no problem, but when the amount of your account has just been deducted, this is due to exceptions that cause the recipient to fail to collect (such as sudden power failure), this part of the money disappears out of thin air, of course, the bank will naturally consider this issue. It will ensure that the deduction and collection operations are either completed together or won't succeed, and the technology used is a thing.

Android provides a SQLiteOpenHelper helper class to facilitate database management. With this class, we can easily create and upgrade databases. Since SQLiteOpenHelper is an abstract class, we need to create a helper class to inherit it.

Create MyDatabaseHelper

package com.tonycheng.databasetest;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.widget.Toast;/** * Created by tonycheng on 2015/6/27. */public class MyDatabaseHelper extends SQLiteOpenHelper {    public static final String CREATE_BOOK = "create table book(" +            "id integer primary key autoincrement," +            "author text," +            "price real," +            "pages integer," +            "name text," +            "category_id integer)";    private Context mContext;    public MyDatabaseHelper(Context context, String name,                            SQLiteDatabase.CursorFactory factory, int version) {        super(context, name, factory, version);        mContext = context;    }    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL(CREATE_BOOK);        Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show();    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    }}

Write a simple XML layout file with two buttons, one for creating a database and the other for testing transaction operations.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"    android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    android:paddingBottom="@dimen/activity_vertical_margin"    android:orientation="vertical"    tools:context=".MainActivity">    <Button        android:id="@+id/create_database"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="Create Database"        />    <Button        android:id="@+id/replace_data"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="Replace data"        /></LinearLayout>

Finally, modify the code in MainActivity:

Step 1: Create a database:

package com.tonycheng.databasetest;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.support.v7.app.ActionBarActivity;import android.os.Bundle;import android.util.Log;import android.view.View;import android.widget.Button;public class MainActivity extends ActionBarActivity {    private MyDatabaseHelper dbHelper;    private Button btn_createDatabase;    private Button btn_raplaceData;    private static final String TAG = "MainActivity";    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        dbHelper = new MyDatabaseHelper(this,"BookStore.db",null,1);        btn_createDatabase = (Button) findViewById(R.id.create_database);        btn_raplaceData = (Button) findViewById(R.id.replace_data);        btn_createDatabase.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View v) {                dbHelper.getWritableDatabase();            }        });    }}

The onCreat () method constructs a MyDatabaseHelper object and specifies the database name as BookStore. db by using the constructor parameters. The version number is 1. Call the getWritableDatabase () method to create a database in the onClick event of the button.

Add a piece of Data to the BookStore. db database: Add a Data button

Btn_addData.setOnClickListener (new View. OnClickListener () {@ Override public void onClick (View v) {SQLiteDatabase db = dbHelper. getWritableDatabase ();
// Method 1: ContentValues values = new ContentValues (); // start to assemble the first data values. put ("name", "The Da Vinci Code"); values. put ("author", "Dan Brown"); values. put ("pages", 510); values. put ("price", 19.95); db. insert ("book", null, values );
// Method 2 // use SQL to insert data (similarly, it is used for other centralized operations) // you can use either of the two methods to add data. If you think the preceding method is too cumbersome, it is created using SQL statements. Their results are the same as those of db.exe cSQL ("insert into book (name, author, pages, price) values (?,?,?,?) ", New String [] {" The Da Vinci Code "," Dan Brown "," 510 "," 19.95 "});}});

In this way, there is a piece of data in the database. We always perform transaction operations: we delete this data from the book table, and then add a new data:

/*** Use a transaction to perform database operations. Either the two operations are completed or both fail (transaction) */btn_raplaceData.setOnClickListener (new View. onClickListener () {@ Override public void onClick (View v) {SQLiteDatabase db = dbHelper. getWritableDatabase (); db. beginTransaction (); // start transaction try {db. delete ("book", null, null); if (true) {// manually throw an exception here to make the transaction fail // throw new NullPointerException (); // because we manually throw an exception, the code for adding data cannot be executed, but the old data cannot be deleted due to the existence of the transaction} db.exe cSQL ("insert Into book (name, author, pages, price) values (?,?,?,?) ", New String [] {" android "," tonycheng "," 550 "," 79 "}); db. setTransactionSuccessful ();} finally {db. endTransaction ();}}});

Run the above Code and find that the data in the table has not been deleted. This is because we have enabled the transaction and deliberately throw an exception, so that the old data cannot be deleted. if the transaction is not enabled, the old data in the book table will be deleted. Due to an exception, the code for adding data cannot be executed. In some cases, there may be major problems. As a result, the importance of transactions is reflected. So far, a simple simulation of our transactions has been completed.