代码之家 › 专栏 › 技术社区 › User3

正在尝试提高Sqlite中批量插入的速度。

sqlite android java

User3 · 技术社区 · 10 年前

我有一个databaseHelper类,其中有以下函数:

public SQLiteDatabase returnDB(){

        return DBHelper.getWritableDatabase(); 
    }

在我的主类(Async Task--onPreExecute)中:

            dbHelper = new DBAdapter(getApplicationContext());
            dbHelper.open(); 

            mDb = dbHelper.returnDB(); 
            mDb.beginTransaction();

在doInBackground中,我处理查询如下:

dbHelper.insert(dateToInsert, CONTACT_NAME, String.valueOf(days), String.valueOf(hou), CONTACT_IMAGE_URI, String.valueOf(min),String.valueOf(mon), String.valueOf(secon), CONTACT_ID, String.valueOf(weeks), String.valueOf(years));

然后在onPostExecute中:

mDb.endTransaction();
dbHelper.close();

但当我尝试在异步任务执行后立即将值提取到listview中时,我遇到了一个数据库锁定错误。下面是LogCat:

02-08 14:40:06.587: E/SQLiteLog(6394): (5) database is locked
02-08 14:40:06.588: D/SQLiteConnection(6394): executeForString took 2505ms - failed, sql="PRAGMA journal_mode", exception="database is locked (code 5): , while compiling: PRAGMA journal_mode"
02-08 14:40:06.602: E/SQLiteDatabase(6394): Failed to open database '/data/data/com.exa.birthdayrem/databases/Bdr'.
02-08 14:40:06.602: E/SQLiteDatabase(6394): android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:627)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:313)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:287)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:860)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at com.exa.birthdayrem.DBAdapter.returnDB(DBAdapter.java:156)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at com.exa.birthdayrem.LoaderClass$MagicCall.onPostExecute(LoaderClass.java:457)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at com.exa.birthdayrem.LoaderClass$MagicCall.onPostExecute(LoaderClass.java:1)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.os.AsyncTask.finish(AsyncTask.java:631)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.os.AsyncTask.access$600(AsyncTask.java:177)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:644)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.os.Handler.dispatchMessage(Handler.java:99)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.os.Looper.loop(Looper.java:153)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at android.app.ActivityThread.main(ActivityThread.java:5000)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at java.lang.reflect.Method.invokeNative(Native Method)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at java.lang.reflect.Method.invoke(Method.java:511)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:821)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:584)
02-08 14:40:06.602: E/SQLiteDatabase(6394):     at dalvik.system.NativeStart.main(Native Method)

我做得对吗?但我看不出有多大的性能提升?

1 回复 | 直到 10 年前

Flat Eric 10 年前

下面是一个如何在DatabaseHelper中处理事务的示例。您需要确保只创建一次Helper,例如通过将其设置为Singleton。为了在同时使用多个读卡器时获得更好的性能,可以将ReentrantLock替换为ReadWriteLock。

DbHelper helper = new DbHelper();

private void DoSimpleInsert()
{
    helper.InsertA(items);
}

private void DoComplexInsert()
{
    helper.BeginTransaction();
    helper.InsertA(items);
    helper.InsertB(items);
    helper.EndTransaction();
}

public class DbHelper
{
    private SQLiteDatabase db;
    private boolean isExpliciteTransaction;
    private ReentrantLock lock = new ReentrantLock();

    public void BeginTransaction()
    {
        isExpliciteTransaction = true;
        db.beginTransaction();
    }

    public void EndTransaction()
    {
        isExpliciteTransaction = false;
        db.endTransaction();
    }

    public void InsertA(ClassA[] items)
    {
        lock.lock();
        try
        {
            if (!isExpliciteTransaction)
            {
                db.beginTransaction();
            }

            // Do the inserts here

            if (!isExpliciteTransaction)
            {
                db.endTransaction();
            }
        }
        finally
        {
            lock.unlock();
        }
    }

    public void InsertB(ClassB[] items)
    {
        lock.lock();
        try
        {
            if (!isExpliciteTransaction)
            {
                db.beginTransaction();
            }

            // Do the inserts here

            if (!isExpliciteTransaction)
            {
                db.endTransaction();
            }
        }
        finally
        {
            lock.unlock();
        }
    }
}