ORMLite在Android上将表从一个数据库插入到另一个数据库的问题 [英] ORMLite Issue with inserting tables from one database to another database on Android

问题描述

所以我会解释问题出现的方式：

So I´ll explain the way the problem appears:

我从服务器下载一个SQLite数据库文件到用户的Android设备。
下载后，我们将下载的数据库中的一些表插入或替换到本地数据库。
我们使用ORMLite 4.47来处理这些函数。

I download a SQLite database file from a server to the Android device of the user. After the download we insert or replace some tables from the downloaded database to the local database. We use ORMLite 4.47 for these function.

首先我们将数据库文件附加到DatabaseConnection：

First we attach the database files to the DatabaseConnection:

DatabaseConnection con = null;
con = conSrc.getReadWriteConnection();
con.executeStatement("attach database '" + localDatabase.getAbsolutePath() + "' as '" + localDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS);
con.executeStatement("attach database '" + downloadedDatabase.getAbsolutePath() + "' as '" + remoteDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS);

附加数据库后，我们创建以下查询以从下载的数据库（remoteDb）复制到本地database（localDb）：

After attaching the database we create the following query to copy from the downloaded database (remoteDb) to the local database (localDb):

INSERT OR REPLACE INTO localDb.table_items (createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6) SELECT createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6 FROM remoteDb.table_items

b $ b

以下函数来自ORMLite：

And the following function from ORMLite:

con.executeStatement(query, flags); 

此功能在Android 4.1版本（使用SQLite 3.7.11）及更高版本上正常工作。但是我们在Android版本4.0和4.0.3（使用SQLite 3.7.4）上得到以下错误：

The function works fine on Android version 4.1 (with SQLite 3.7.11) and higher. But we get the following Error on Android version 4.0 and 4.0.3 (with SQLite 3.7.4):

09-13 15:51:19.852: E/com.example.controller(1028): java.sql.SQLException: Problems executing INSERT OR REPLACE INTO localDb.table_items (createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6) SELECT createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6 FROM remoteDb.table_items Android statement: INSERT OR REPLACE INTO localDb.table_items (createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6) SELECT createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6 FROM remoteDb.table_items
09-13 15:51:19.852: E/com.example.controller(1028):     at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.j256.ormlite.android.AndroidCompiledStatement.execSql(AndroidCompiledStatement.java:185)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.j256.ormlite.android.AndroidDatabaseConnection.executeStatement(AndroidDatabaseConnection.java:134)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller$DatabaseTransferHandler.executeDatabaseTransfers(contoller.java:763)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller$DatabaseTransferHandler.access$0(contoller.java:740)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller.transferTables(contoller.java:494)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller.addElement(contoller.java:132)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller.addOrRemoveElements(contoller.java:109)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller.addElements(contoller.java:147)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller.download(contoller.java:254)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller.access$3(controller.java:204)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller$2.doInBackground(contoller.java:468)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.example.controller$2.doInBackground(contoller.java:1)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.os.AsyncTask$2.call(AsyncTask.java:264)
09-13 15:51:19.852: E/com.example.controller(1028):     at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
09-13 15:51:19.852: E/com.example.controller(1028):     at java.util.concurrent.FutureTask.run(FutureTask.java:137)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:208)
09-13 15:51:19.852: E/com.example.controller(1028):     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076)
09-13 15:51:19.852: E/com.example.controller(1028):     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569)
09-13 15:51:19.852: E/com.example.controller(1028):     at java.lang.Thread.run(Thread.java:856)
09-13 15:51:19.852: E/com.example.controller(1028): Caused by: android.database.sqlite.SQLiteDatabaseLockedException: database is locked
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteStatement.native_executeSql(Native Method)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:90)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1839)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:661)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteDatabase.beginTransactionNonExclusive(SQLiteDatabase.java:576)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:247)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:84)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899)
09-13 15:51:19.852: E/com.example.controller(1028):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1889)
09-13 15:51:19.852: E/com.example.controller(1028):     at com.j256.ormlite.android.AndroidCompiledStatement.execSql(AndroidCompiledStatement.java:183)
09-13 15:51:19.852: E/com.example.controller(1028):     ... 18 more

我们如何解决这个问题？

How can we solve this problem? Is there perhaps a better way to download large amounts of information to a device?

推荐答案

我是Kenny Seyffarth的同事，这个问题的作者。我设法找到一种方法来避免这个问题，我想我会在这里张贴，以防其他人绊倒同一个问题。

I'm a coworker of Kenny Seyffarth, the author of this question. I managed to find a way to avoid this problem, and I figured I'd post about it here in case anyone else is stumbling across the same issue.

Android设备这个非常特定的操作系统版本似乎有这个序列的Ormlite命令的问题：

Android devices with that very specific OS version seemed to have a problem with this sequence of Ormlite commands:

DatabaseConnection con = null;
con = conSrc.getReadWriteConnection();
con.executeStatement("attach database '" + localDatabase.getAbsolutePath() + "' as '" + localDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS);
con.executeStatement("attach database '" + downloadedDatabase.getAbsolutePath() + "' as '" + remoteDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS);

我不知道这是否是由预装在这些设备上的SQLite3的版本，

I don't know if this is caused by the version of SQLite3 that comes preinstalled on these devices, or by something else.

现在我们可以避免这种情况，因为我们可以（幸运地）避免在这种情况下使用Ormlite。 SQLite语句ATTACH DATABASE不需要在DatabaseConnection实例上执行 - 它可以在本地数据库文件上执行。因此，不是前面的块，只需要这个语句：

Now we could avoid this because we could (luckily) avoid using Ormlite in this situation altogether. The SQLite statement "ATTACH DATABASE" does not need to be executed on a DatabaseConnection instance - it can be executed on the local database file. So instead of the former block, only this statement is necessary:

db.execSQL("ATTACH DATABASE '" + tempDb.getAbsolutePath() + "' AS '" + remoteDb + "'");

其中 db 是 SQLiteDatabase 本地数据库的实例。
SQLite文档说明：

where db is the SQLiteDatabase instance of the local database. The SQLite documentation states that:

ATTACH DATABASE语句会向当前数据库连接添加另一个数据库文件。

"The ATTACH DATABASE statement adds another database file to the current database connection."

这就是为什么不需要 DatabaseConnection 实例。

which is why no DatabaseConnection instance is needed.

这篇关于ORMLite在Android上将表从一个数据库插入到另一个数据库的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！