首页 > 代码库 > Android多线程操作sqlite（Sqlite解决database locked问题）
Android多线程操作sqlite（Sqlite解决database locked问题）
参考http://blog.csdn.net/sdsxleon/article/details/18259973 很好
https://github.com/2point0/Android-Database-Locking-Collisions-Example 示例
http://www.eoeandroid.com/forum.php?mod=viewthread&tid=333473
http://bbs.51cto.com/thread-990260-1.html
用事务，速度会很会
方案1：
SQLite实质上是将数据写入一个文件，通常情况下，在应用的包名下面都能找到xxx.db的文件，拥有root权限的手机，可以通过adb shell，看到data/data/packagename/databases/xxx.db这样的文件。
我们可以得知SQLite是文件级别的锁：多个线程可以同时读，但是同时只能有一个线程写。Android提供了SqliteOpenHelper类，加入Java的锁机制以便调用。
如果多线程同时读写（这里的指不同的线程用使用的是不同的Helper实例），后面的就会遇到android.database.sqlite.SQLiteException: database is locked这样的异常。
对于这样的问题，解决的办法就是keep single sqlite connection，保持单个SqliteOpenHelper实例，同时对所有数据库操作的方法添加synchronized关键字。
完美解决sqlite的 database locked 或者是 error 5: database locked 问题
/** * @FileName : DatabaseHelper.java * @ProjectName : SqlitePractice * @PakageName : com.sqlitepractice.database * @Author : Brant * @CreateDate : 2012-12-16 */package com.sqlitepractice.database;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.provider.BaseColumns;import android.util.Log;/** * @Module : 隶属模块名 * @Comments : 描述 * @Author : Brant * @CreateDate : 2012-12-16 * @ModifiedBy : Brant * @ModifiedDate: 2012-12-16 * @Modified: * 2012-12-16: 实现基本功能 */public class DatabaseHelper extends SQLiteOpenHelper { public static final String TAG = "DatabaseHelper"; private static final String DB_NAME = "practice.db"; private final static String DB_TABLE_APP = "app"; private static final int DB_VERSION = 1; private static final String DB_CREATE_TABLE_APP = "CREATE TABLE " + DB_TABLE_APP + "(" + APP_COLUMNS._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + APP_COLUMNS.PACKAGE_NAME + " NTEXT NOT NULL," + APP_COLUMNS.APP_NAME + " NTEXT NOT NULL," + APP_COLUMNS.APP_NAME_PINYIN + " NTEXT," + APP_COLUMNS.CLASS_NAME + " NTEXT NOT NULL);"; private static DatabaseHelper mInstance; protected DatabaseHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } public synchronized static DatabaseHelper getInstance(Context context) { if (mInstance == null) { mInstance = new DatabaseHelper(context); } return mInstance; } public synchronized static void destoryInstance() { if (mInstance != null) { mInstance.close(); } } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(DB_CREATE_TABLE_APP); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } public synchronized int getCount() { Log.d(TAG, "getCount"); int count = -1; Cursor c = getReadableDatabase().query(DB_TABLE_APP, null, null, null, null, null, null); if (c.moveToFirst()) { count = c.getCount(); } c.close(); c = null; return count; } public synchronized void insert(String packageName, String appLabel, String pinyin, String className) { Log.d(TAG, "insert"); ContentValues values = new ContentValues(4); values.put(APP_COLUMNS.PACKAGE_NAME, packageName); values.put(APP_COLUMNS.APP_NAME, appLabel); values.put(APP_COLUMNS.APP_NAME_PINYIN, pinyin); values.put(APP_COLUMNS.CLASS_NAME, className); getWritableDatabase().insert(DB_TABLE_APP, null, values); } public static class APP_COLUMNS implements BaseColumns { public static final String PACKAGE_NAME = "package_name"; public static final String APP_NAME = "app_name"; public static final String APP_NAME_PINYIN = "app_name_pinyin"; public static final String CLASS_NAME = "class_name"; public static final int ID_INDEX = 0; public static final int PACKAGE_NAME_INDEX = 1; public static final int APP_NAME_INDEX = 2; public static final int APP_NAME_PINYIN_INDEX = 3; public static final int CLASS_NAME_INDEX = 4; }}
package com.sqlitepractice;import java.util.ArrayList;import java.util.List;import android.app.Activity;import android.os.Bundle;import android.util.Log;import android.view.Menu;import com.sqlitepractice.database.DatabaseHelper;public class MainActivity extends Activity { private static final String TAG = "MainActivity"; private static int sThreadCounter = 0; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); final int threadCount = 8; final List<Thread> allThreads = new ArrayList<Thread>(threadCount); DatabaseHelper helper = DatabaseHelper.getInstance(this); for (int i = 0; i < threadCount; i++) { allThreads.add(new DbInsertThread(helper, 50, sThreadCounter++)); } for (int i = 0; i < threadCount; i++) { allThreads.add(new FastSelectThread(helper, sThreadCounter++, 50)); } for (Thread thread : allThreads) { thread.start(); } // Wait for all threads to complete before running for (Thread thread : allThreads) { try { thread.join(); Log.i(thread.getName(), "collected"); } catch (InterruptedException e) { Log.e(TAG, "Interrupted", e); } } Log.i(TAG, "All threads finished!"); } @Override public boolean onCreateOptionsMenu(Menu menu) { getMenuInflater().inflate(R.menu.activity_main, menu); return true; } class DbInsertThread extends Thread { private final String TAG = DbInsertThread.class.getSimpleName(); private final DatabaseHelper mDbHelper; private int mRunCount; DbInsertThread(DatabaseHelper helper, int runCount, int id) { setName(String.format("%1$s-%2$d", TAG, id)); mDbHelper = helper; mRunCount = runCount; Log.i(getName(), helper.toString()); } @Override public void run() { for (int i = 0; i < mRunCount; i++) { try { mDbHelper.insert("com.xx.xxxx", "测试", "ceshi", "MainActivity"); } catch (Exception e) { Log.e(getName(), "Insert failed!!!, stopping writes", e); break; } } Log.i(getName(), "finished!"); } } class FastSelectThread extends Thread { private final String TAG = FastSelectThread.class.getSimpleName(); private final DatabaseHelper mHelper; private final int mCount; FastSelectThread(DatabaseHelper helper, int id, int selectCount) { setName(String.format("%1$s-%2$d", TAG, id)); mHelper = helper; mCount = selectCount > 0 ? selectCount : 50; Log.i(getName(), helper.toString()); } @Override public void run() { int count = 0; while (count < mCount) { mHelper.getCount(); Log.i(getName(), "start wait"); try { Thread.sleep(200); } catch (InterruptedException e) { e.printStackTrace(); } Log.i(getName(), "end wait"); count++; } Log.i(getName(), "finished!"); } }}
Android多线程操作sqlite（Sqlite解决database locked问题）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。