Android SQLite 事務處理Operation SQLite

在寫藍牙Contacts的時候，遇到一個問題，多聯系人(最少1000+)插入導致應用程序死掉，因為我們寫的藍牙不是設備的標准藍

牙，自己焊接的，協議都是不同的，數據傳遞雖然都是一樣的方式，但是驅動和連接協議有別於標准藍牙，這裡就不細說了，

直接切入主題，解決這個藍牙跟車機設備聯系人同步的問題

 

先前有幾個客戶反饋說，在測試我們藍牙應用的時候，說聯系人超1000+的時候，或者超了300+，都會導致程序死掉，或者上傳很慢，因為先前藍牙應用

是其他同事寫的，不知道這個情況，之後一個朋友(袁工)說建議采用事物處理，我今天親測了，確實事物跟單事物插入的效果真是天差地別啊！好明

顯！！

首先說說事物，直截了當的說吧，每一個語句就是一個事物，然後頻繁的操作 SQLite 會導致應用緩不過來，而我們可以把要做的操作全部歸納到一個事

物裡面去，需要我們調用三個函數

SQLiteDatabase db:

db.beginTransaction(); // 開啟事務

db.setTransactionSuccessful();// 設置事物標志為成功，當結束事物時就會提交事物

db.endTransaction();//事物結束

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";
	public static final String CREATE_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;
    }

	@Override
	public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE);
        Log.d("BlueTooth_Navi_Call", "SQLite Database Table is Create...");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		
	}
	public void insertContacts(){
		Log.d("BlueTooth_Navi_Call", "SQLite Database Start is insert...");
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();// 開啟事務
		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();// 設置事物標志為成功，當結束事物時就會提交事物
		} finally{
			db.endTransaction();
		}
		db.close();
		Log.d("BlueTooth_Navi_Call", "SQLite Database end for 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 mPhoneBookeHelper;
	@Override
	protected 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);
	}
	@Override
	public 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 (code) {
			case 0:
				mPhoneBookeHelper.insertContacts();
				break;
			}
		}
	};
}

大家需要注意的是，在Activity裡新建線程的時候，它是不能直接訪問Activity裡的UI組件的，需要用Handler或者AsynTask來處理，否者會拋出Exception:

 

java.lang.RuntimeException: Can't create handler inside thread that has not called Looper.prepare()

所以我在按鈕事件裡調用sendEmptyMessage()函數，執行插入操作

經測試，插入1000+， 耗時0.9s，而單事物耗時將近7s

10000+，耗時12左右，單事物2分09s

所以效果是很明顯的，下面看看實測對比圖：

 

單事物插入，1000+測試：

 

		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+"******************************");
		}

 

 

歸納事物，1000+測試：

 

			db.beginTransaction();// 開啟事務
		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();// 設置事物標志為成功，當結束事物時就會提交事物
		} finally{
			db.endTransaction();
		}
		db.close();
		Log.d("BlueTooth_Navi_Call", "SQLite Database end for insert...");