Learn about the business of the Android SQLite database

SQLite is a lightweight relational database built into the Android system that is computationally fast and resource-intensive, usually requiring only hundreds of K of memory. SQLite not only supports standard SQL syntax, it also follows the ACID transaction of the database.

Simulate a scenario: once a transfer is made, the bank will deduct the amount of the transfer from your account before adding an equal amount to the account of the receiving party. There seems to be no problem, but when the amount of your account has just been deducted, this is due to some unusual reasons causing the other party to fail (such as a sudden power loss), this part of the money is lost in thin air, of course, the bank will naturally take into account this problem, it will ensure that the deduction and collection of operations are either completed together, And the technology used is the thing.

Android in order to make it easier for us to manage the database, dedicated to provide a Sqliteopenhelper helper class, with this class we can easily create and upgrade the database. Since Sqliteopenhelper is an abstract class, we need to create an auxiliary class to inherit him.

Create Mydatabasehelper

 Packagecom.tonycheng.databasetest;ImportAndroid.content.Context;Importandroid.database.sqlite.SQLiteDatabase;ImportAndroid.database.sqlite.SQLiteOpenHelper;ImportAndroid.widget.Toast;/*** Created by Tonycheng on 2015/6/27.*/ Public classMydatabasehelperextendsSqliteopenhelper { Public Static FinalString create_book = "CREATE table book (" + "ID integer primary key autoincrement," + "author text," + "Price real," + "pages integer," + "Name text," + "category_id Integer)"; PrivateContext Mcontext;  PublicMydatabasehelper (Context context, String name, Sqlitedatabase.cursorfactory factory,intversion) {        Super(context, name, Factory, version); Mcontext=context; } @Override Public voidonCreate (Sqlitedatabase db) {db.execsql (Create_book); Toast.maketext (Mcontext,"Create succeeded", Toast.length_short). Show (); } @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {    }}

Write a simple XML layout file, two buttons, one database, one to test the operation of things.

<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:

The first step is to create a database:

 Packagecom.tonycheng.databasetest;Importandroid.content.ContentValues;ImportAndroid.database.Cursor;Importandroid.database.sqlite.SQLiteDatabase;Importandroid.support.v7.app.ActionBarActivity;ImportAndroid.os.Bundle;ImportAndroid.util.Log;ImportAndroid.view.View;ImportAndroid.widget.Button; Public classMainactivityextendsactionbaractivity {PrivateMydatabasehelper DBHelper; PrivateButton btn_createdatabase; PrivateButton Btn_raplacedata; Private Static FinalString TAG = "Mainactivity"; @Overrideprotected voidonCreate (Bundle savedinstancestate) {Super. OnCreate (savedinstancestate);        Setcontentview (R.layout.activity_main); DBHelper=NewMydatabasehelper ( This, "Bookstore.db",NULL, 1); Btn_createdatabase=(Button) Findviewbyid (r.id.create_database); Btn_raplacedata=(Button) Findviewbyid (r.id.replace_data); Btn_createdatabase.setonclicklistener (NewView.onclicklistener () {@Override Public voidOnClick (View v) {dbhelper.getwritabledatabase ();    }        }); }}

A Mydatabasehelper object is constructed in the Oncreat () method, and the database name is specified as bookstore.db by the parameters of the constructor, and the version number is specified as 1. Then call the getwritabledatabase () method in the OnClick event of the button to create the database.

Add a piece of data to the BOOKSTORE.DB database: Adding an Add Data button

Btn_adddata.setonclicklistener (NewView.onclicklistener () {@Override Public voidOnClick (View v) {sqlitedatabase db=dbhelper.getwritabledatabase ();
The first method: Contentvalues values=Newcontentvalues (); //start assembling the first piece of dataValues.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);
The second method of//inserting data Using SQL (similarly to other centralized operations)//two ways to add data, if you think the above method is too cumbersome, is to use SQL statements to create, they have the same effectDb.execsql ("INSERT into book (Name,author,pages,price) VALUES (?,?,?,?)",Newstring[]{"The Da Vinci Code", "Dan Brown", "510", "19.95"                }); }        });

So there's a piece of data in the database, and we're always doing things: we delete this data from the Book table and add a new piece of data:

 /*** Use things to perform database operations, either of them complete, or both fail (things)*/Btn_raplacedata.setonclicklistener (NewView.onclicklistener () {@Override Public voidOnClick (View v) {sqlitedatabase db=dbhelper.getwritabledatabase (); Db.begintransaction ();//Open Things                Try{db.delete ("Book",NULL,NULL); if(true){                        //here, manually throw an exception to make things fail//throw new NullPointerException ();//since we have manually thrown an exception, the code to add the data cannot be executed, but the old data cannot be deleted because of the existence of the thing.} db.execsql (Insert into book (Name,author,pages,price) VALUES (?,?,?,?),Newstring[]{"Android", "Tonycheng", "550", "79"                    });                Db.settransactionsuccessful (); }finally{db.endtransaction (); }            }        });

Run the above code and find that the data in the table is not deleted, because we enabled the transaction, deliberately manually thrown an exception, causing the old data can not be deleted, if the transaction is not enabled, the old data in the Book table will be deleted, and because of the exception, the code to add data cannot be executed. And that's going to be a big problem in some situations. As a result, the importance of business is reflected. At this point, a simple simulation of our business is done.

Learning about the transactions of the Android SQLite database