Android sqlite Transaction processing Operation SQLite

When writing Bluetooth contacts, encountered a problem, multiple contacts (minimum 1000+) insertion causes the application to die, because we write Bluetooth is not the standard blue device

Teeth, welding themselves, the protocol is different, although the data transmission is the same way, but the driver and the connection protocol is different from the standard Bluetooth, here is not elaborate,

directly cut into the topic, to solve this Bluetooth and car equipment contact synchronization problem

Several previous customer feedback said that when testing our Bluetooth application, said the contact person Super 1000 + time, or Super 300+, will cause the program to die, or upload very slow, because the previous Bluetooth application

is written by other colleagues, do not know this situation, after a friend (Shang) said that the proposed use of things to deal with, I tested today, it is true that things and single things inserted effect is really poor to do not AH! Good Ming

Display!!

First of all, say things, straight to the point, each statement is a thing, and then the frequent operation of SQLite will cause the application is slow, and we can be done to sum up the operation of a thing

Inside the object, we need to call three functions.

Sqlitedatabase DB:

Db.begintransaction (); Open transaction

Db.settransactionsuccessful ();//Set a thing to be a success, when the end of the thing will be submitted

Db.endtransaction ();//End of thing

Sqliteopenhelper Demo:

Package Com.example.uploadphonebook;import Android.content.context;import android.database.sqlite.SQLiteDatabase; Import Android.database.sqlite.sqliteopenhelper;import Android.util.log;public class Phonebookehelper extends    sqliteopenhelper{@SuppressWarnings ("unused") private Context mcontext;    private static Phonebookehelper Mphonebookehelper;    public static final String db_name = "Phonebook";    public static final String ID = "_id";    public static final String name = "Name";    public static final String number = "Number"; private static final int VERSION = 1;private static final String book_table_name = "Contacts";p ublic static final string C reate_table = String.Format ("Create TABLE if not exists%s (%s integer primary key AutoIncrement,%s text,%s text)", Book_    TABLE_NAME, id,name, number);        Private Phonebookehelper (Context context) {Super (context, db_name, NULL, VERSION);        This.mcontext = context; LOG.D ("Bluetooth_navi_call", "Sqliteatabase is CreAte ... "); public static Phonebookehelper Getphonebook (context context) {if (mphonebookehelper==null) {mphonebookehelper =    New Phonebookehelper (context);    LOG.D ("Bluetooth_navi_call", "Mphonebookehelper is null ...");    } log.d ("Bluetooth_navi_call", "mphonebookehelper is not null ...");    return mphonebookehelper;        } @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (create_table); LOG.D ("Bluetooth_navi_call", "SQLite Database Table is Create ..."); @Overridepublic void Onupgrade (sqlitedatabase arg0, int arg1, int arg2) {}public void insertcontacts () {LOG.D ("Bluetooth_ Navi_call "," SQLite Database Start is insert ... "); Sqlitedatabase db = Getwritabledatabase ();d b.begintransaction ();//Open transaction try {for (int i = 0;i<=10000;i++) {String name = "Tom" +i; String number = "185" +math.random () *100000000; String url = "INSERT into Contacts (Name,number) VALUES (?,?)"; Db.execsql (URL, new String[]{name,number}); LOG.D ("Bluetooth_navi_call", "**********************"+name +" "+ number+" ****************************** ");} Db.settransactionsuccessful ();//Set the thing flag to be successful, when the end of the thing will commit the thing} finally{db.endtransaction ();} Db.close (); LOG.D ("Bluetooth_navi_call", "SQLite Database end for insert ...");}}

Insert Demo:

Package Com.example.uploadphonebook;import Android.os.bundle;import Android.os.handler;import android.util.Log; Import Android.view.view;import Android.view.view.onclicklistener;import Android.widget.button;import Android.app.activity;public class Mainactivity extends Activity implements onclicklistener{private Phonebookehelper MP Honebookehelper; @Overrideprotected void OnCreate (Bundle savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (R.layout.activity_main); Button upload = (button) Findviewbyid (r.id.upload); Upload.setonclicklistener (this); mphonebookehelper = Phonebookehelper.getphonebook (this);} @Overridepublic void OnClick (view view) {LOG.D ("Bluetooth_navi_call", "Uploadphonebook is Start ..."); Mhandler.sendemptymessage (0);//new Thread (New Runnable () {//@Override//public void Run () {// Mphonebookehelper.insertcontacts ();//LOG.D ("Bluetooth_navi_call", "Uploadphonebook is successful");// Toast.maketext (Getapplicationcontext (), "Uploadphonebook is successful", Toast.leNgth_short). Show ();//}//}). Start (); Private Handler Mhandler = new Handler () {public void Handlemessage (Android.os.Message msg) {int code = Msg.what;switch (CO DE) {case 0:mphonebookehelper.insertcontacts (); break;}};}

It is important to note that when you create a new thread in the activity, it is not directly accessible to the UI components in the activity, it needs to be handled with handler or Asyntask, and no one throws exception:

Java.lang.RuntimeException:Can ' t create handler inside thread that have not called looper.prepare ()

So I call the sendemptymessage () function in the button event to perform the insert Operation

Tested to insert 1000+, time consuming 0.9s, and single thing time will be nearly 7s

10000+, time consuming 12 or so, single thing 2 minutes 09s

So the effect is very obvious, the following look at the actual comparison chart:

Single thing insert, 1000+ test:

for (int i = 0;i<=1000;i++) {String name = "Tom" +i; String number = "185" +math.random () *100000000; String url = "INSERT into Contacts (Name,number) VALUES (?,?)"; Db.execsql (URL, new String[]{name,number}); LOG.D ("Bluetooth_navi_call", "******************************" +name + "" + number+ "******************************");}

Summarize things, 1000+ test:

Db.begintransaction ();//Open transaction try {for (int i = 0;i<=1000;i++) {String name = "Tom" +i; String number = "185" +math.random () *100000000; String url = "INSERT into Contacts (Name,number) VALUES (?,?)"; Db.execsql (URL, new String[]{name,number}); LOG.D ("Bluetooth_navi_call", "******************************" +name + "" + number+ "******************************");} Db.settransactionsuccessful ();//Set the thing flag to be successful, when the end of the thing will commit the thing} finally{db.endtransaction ();} Db.close (); LOG.D ("Bluetooth_navi_call", "SQLite Database end for insert ...");

1000+ Time-Comparison chart:

10000+ test code above, is more than a 0, time-consuming comparison chart:

The effect is not very obvious? The error is not very big estimate also about 1s ~

Android sqlite Transaction processing Operation SQLite