Android SQLite 事务处理 Operation SQLite

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/jspping/article/details/42804541

在写蓝牙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...");



1000+ 耗时-对比图:



10000+ 测试代码同上,就是多了一个0而已,耗时对比图:



效果是不是很明显?误差不是很大估计也就1s左右~ 


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页