Android Sqlite数据库—并发篇

最近在优化之前的客户端软件-号码通，应用是基于数据库查询的，数据库是单独分开发布的，数据库除了查询操作之外，还有添加、更新、删除操作，而Sqlite 数据库对多线程并发不支持（至少不够好，多线程去操作容易出问题），故需要对 数据库的读写进行同步以保证线程安全。

DBHelper.java 

package com.example.sqlitetest;

import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReadWriteLock;
import java.util.concurrent.locks.ReentrantReadWriteLock;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

public class DBHelper {

	//数据库路径
	private static final String DATABASE_PATH = Environment
			.getExternalStorageDirectory().getAbsolutePath() + "/yulore/";

	private ReadWriteLock lock = new ReentrantReadWriteLock(true);
	private Lock readLock  = lock.readLock();
	private Lock writeLock   = lock.writeLock();
	
	/**
	 * 获取用于进行 读操作的 SQLiteDatabase
	 * @param databaseFileName	数据库名称
	 * @return
	 */
	public SQLiteDatabase getReadableDataBase(String databaseFileName) {

		readLock.lock();
		
		try {
			// 得到数据库的完整路径名
			String databasePath = DATABASE_PATH + databaseFileName;

			return SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		}finally{
			readLock.unlock();
		}
	}
	
	/**
	 * 获取用于进行 写操作的 SQLiteDatabase
	 * @param databaseFileName
	 * @return
	 */
	public SQLiteDatabase getWritableDataBase(String databaseFileName) {
		
		writeLock.lock();
		
		try {
			// 得到数据库的完整路径名
			String databasePath = DATABASE_PATH + databaseFileName;

			return SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		} finally{
			writeLock.unlock();
		}
	}
}

使用ReadWriteLock对Sqlite 数据库的读写操作进行同步，保证不会因为一个线程写 Sqlite 数据库导致其他读线程因为Sqlite 数据库被锁定而出错的情况。

模拟读线程 ReadThread.java

package com.example.sqlitetest.thread;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.example.sqlitetest.DBHelper;

public class ReadThread implements Runnable {

	private DBHelper helper;
	
	public ReadThread(DBHelper helper) {
		this.helper = helper;
	}

	@Override
	public void run() {
		
		queryDataBase();
	}

	private void queryDataBase() {
		
		SQLiteDatabase db = helper.getReadableDataBase("ypdata");
		
		int count = 0;

		Cursor cursor = null;
		try {
			if (db.isOpen()) {
				String sql = "SELECT count(*)  FROM DialHistory";

				try {
					Thread.sleep(200);	//模拟等待
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				
				cursor = db.rawQuery(sql, null);
				if (cursor.moveToNext()) {

					count = cursor.getInt(0);

					Log.e("ReadThread", Thread.currentThread().getName()+" query table DialHistory count = " + count);
				}
				
			}
		} finally {
			if (cursor != null) {
				try {
					cursor.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			db.close();
		}
	}
//	private void queryDataBase() {
//		
//		SQLiteDatabase db = helper.getReadableDataBase("ypdata");
//		
//		int count = 0;
//		
//		Cursor cursor = null;
//		try {
//			if (db.isOpen()) {
//				String sql = "SELECT count(*)  FROM City WHERE selected =1";
//				cursor = db.rawQuery(sql, null);
//				if (cursor.moveToNext()) {
//					
//					count = cursor.getInt(0);
//					
//					Log.e("ReadThread", "download city count = " + count);
//				}
//				
//				try {
//					Thread.sleep(200);	//模拟等待
//				} catch (InterruptedException e) {
//					e.printStackTrace();
//				}
//			}
//		} finally {
//			if (cursor != null) {
//				try {
//					cursor.close();
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//			}
//			db.close();
//		}
//	}

}

模拟写线程 WriteThread.java

package com.example.sqlitetest.thread;

import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.sqlitetest.DBHelper;

public class WriteThread implements Runnable {
	private DBHelper helper;
	private int sequence;
	
	public WriteThread(DBHelper helper,int sequence) {
		this.helper = helper;
		this.sequence = sequence;
	}

	@Override
	public void run() {
		
		updateDataBase();
	}

	private void updateDataBase() {
		
		SQLiteDatabase db = helper.getWritableDataBase("ypdata");
		try {
			if (db.isOpen()) {

				db.execSQL("INSERT INTO DialHistory (City,DialType,ShopID,TelNumberID,LastDial)"
						+ " VALUES ("
						+ sequence
						+ ","
						+ sequence
						+ ","
						+ 123456
						+ ","
						+ 123456
						+ ","
						+ "DATETIME('now','localtime'))");
				
				try {
					Thread.sleep(200);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				
				Log.e("WriteThread", Thread.currentThread().getName()+" add table DialHistory success..."+sequence);
			}
		} finally {
			db.close();
		}
	}

}

测试demo

package com.example.sqlitetest;

import com.example.sqlitetest.thread.ReadThread;
import com.example.sqlitetest.thread.WriteThread;

import android.os.Bundle;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class MainActivity extends Activity implements OnClickListener {

    private Button bt_read_write;
	private DBHelper helper;

	@Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        findViewById();
        initDB();
        setListener();
    }

    private void initDB() {
		helper = new DBHelper();
	}

	private void setListener() {
    	bt_read_write.setOnClickListener(this);
	}


	private void findViewById() {
		bt_read_write = (Button) findViewById(R.id.bt_read_write);
	}


	@Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }


	@Override
	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.bt_read_write:
			
			Log.i("MainActivity", "开始测试 DB 并发性...");
			
			//4个读线程
			new Thread(new ReadThread(helper)).start();
			new Thread(new ReadThread(helper)).start();
			new Thread(new ReadThread(helper)).start();
			new Thread(new ReadThread(helper)).start();
			
			//3个写线程
			new Thread(new WriteThread(helper,1)).start();
			new Thread(new WriteThread(helper,2)).start();
			new Thread(new WriteThread(helper,3)).start();
			
			break;

		default:
			break;
		}
	}
}

布局文件activity_main.xml

<RelativeLayout 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"
    tools:context=".MainActivity" >

    <Button
        android:id="@+id/bt_read_write"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/test_read_write" />

</RelativeLayout>

如果应用中使用 SqliteOpenHelper来操作数据导致的android.database.sqlite.SQLiteException: database is locked这样的异常，请参看这篇文章

http://bbs.51cto.com/thread-990260-1.html

个人能力有限，程序有不对的地方还请大家多多指教，谢谢！