Android 上 SQLite 的最佳实践是什么？

Question

提问by Vidar Vestnes

What would be considered the best practices when executing queries on an SQLite database within an Android app?

在 Android 应用程序中对 SQLite 数据库执行查询时，哪些被认为是最佳实践？

Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground? Or should I use the UI Thread? I suppose that database queries can be "heavy" and should not use the UI thread as it can lock up the app - resulting in an Application Not Responding(ANR).

从 AsyncTask 的 doInBackground 运行插入、删除和选择查询是否安全？还是应该使用 UI 线程？我认为数据库查询可能是“繁重的”，不应使用 UI 线程，因为它会锁定应用程序 - 导致应用程序无响应(ANR)。

If I have several AsyncTasks, should they share a connection or should they open a connection each?

如果我有多个 AsyncTask，它们应该共享一个连接还是每个都打开一个连接？

Are there any best practices for these scenarios?

是否有针对这些场景的最佳实践？

Answer 1

采纳答案by Kevin Galligan

Inserts, updates, deletes and reads are generally OK from multiple threads, but Brad's answeris not correct. You have to be careful with how you create your connections and use them. There are situations where your update calls will fail, even if your database doesn't get corrupted.

从多个线程插入、更新、删除和读取通常都可以，但 Brad 的回答不正确。您必须小心创建和使用连接的方式。在某些情况下，即使您的数据库没有损坏，您的更新调用也会失败。

The basic answer.

基本答案。

The SqliteOpenHelper object holds on to one database connection. It appears to offer you a read and write connection, but it really doesn't. Call the read-only, and you'll get the write database connection regardless.

SqliteOpenHelper 对象保持一个数据库连接。它似乎为您提供了读写连接，但实际上并没有。调用只读，无论如何您都会获得写入数据库连接。

So, one helper instance, one db connection. Even if you use it from multiple threads, one connection at a time. The SqliteDatabase object uses java locks to keep access serialized. So, if 100 threads have one db instance, calls to the actual on-disk database are serialized.

因此，一个助手实例，一个数据库连接。即使您从多个线程使用它，一次一个连接。SqliteDatabase 对象使用java 锁来保持访问序列化。因此，如果 100 个线程有一个 db 实例，则对实际磁盘数据库的调用将被序列化。

So, one helper, one db connection, which is serialized in java code. One thread, 1000 threads, if you use one helper instance shared between them, all of your db access code is serial. And life is good (ish).

所以，一个助手，一个数据库连接，在java代码中序列化。一个线程，1000个线程，如果你使用它们之间共享的一个辅助实例，那么你所有的数据库访问代码都是串行的。生活很好（ish）。

If you try to write to the database from actual distinct connections at the same time, one will fail. It will not wait till the first is done and then write. It will simply not write your change. Worse, if you don't call the right version of insert/update on the SQLiteDatabase, you won't get an exception. You'll just get a message in your LogCat, and that will be it.

如果您尝试同时从实际的不同连接写入数据库，则会失败。它不会等到第一个完成再写。它根本不会写入您的更改。更糟糕的是，如果您没有在 SQLiteDatabase 上调用正确版本的插入/更新，则不会出现异常。您只会在 LogCat 中收到一条消息，就这样。

So, multiple threads? Use one helper. Period. If you KNOW only one thread will be writing, you MAY be able to use multiple connections, and your reads will be faster, but buyer beware. I haven't tested that much.

那么，多线程？使用一名助手。时期。如果您知道只有一个线程正在写入，您可以使用多个连接，并且您的读取速度会更快，但买家要小心。我没有测试那么多。

Here's a blog post with far more detail and an example app.

这是一篇包含更多细节和示例应用程序的博客文章。

Android Sqlite Locking(Updated link 6/18/2012)
Android-Database-Locking-Collisions-Example by touchlabon GitHub

Android Sqlite 锁定（更新链接 6/18/2012）
Android-Database-Locking-Collisions-Example by touchlabon GitHub

Gray and I are actually wrapping up an ORM tool, based off of his Ormlite, that works natively with Android database implementations, and follows the safe creation/calling structure I describe in the blog post. That should be out very soon. Take a look.

Gray 和我实际上正在基于他的 Ormlite 开发一个 ORM 工具，该工具在本机与 Android 数据库实现一起工作，并遵循我在博客文章中描述的安全创建/调用结构。那应该很快就出来了。看一看。

In the meantime, there is a follow up blog post:

与此同时，有一篇后续博客文章：

Single SQLite connection

单个 SQLite 连接

Also checkout the fork by 2point0of the previously mentioned locking example:

还要通过前面提到的锁定示例的2point0来检查分叉：

Android-Database-Locking-Collisions-Example by 2point0on GitHub

Android-Database-Locking-Collision-Example by 2point0on GitHub

Answer 2

回答by Dmytro Danylyk

Concurrent Database Access

并发数据库访问

Same article on my blog(I like formatting more)

我博客上的同一篇文章（我更喜欢格式化）

I wrote small article which describe how to make access to your android database thread safe.

我写了一篇小文章，描述了如何安全地访问您的 android 数据库线程。

Assuming you have your own SQLiteOpenHelper.

假设您有自己的SQLiteOpenHelper。

public class DatabaseHelper extends SQLiteOpenHelper { ... }

Now you want to write data to database in separate threads.

现在您想在单独的线程中将数据写入数据库。

 // Thread 1
 Context context = getApplicationContext();
 DatabaseHelper helper = new DatabaseHelper(context);
 SQLiteDatabase database = helper.getWritableDatabase();
 database.insert(…);
 database.close();

 // Thread 2
 Context context = getApplicationContext();
 DatabaseHelper helper = new DatabaseHelper(context);
 SQLiteDatabase database = helper.getWritableDatabase();
 database.insert(…);
 database.close();

You will get following message in your logcat and one of your changes will not be written.

您将在 logcat 中收到以下消息，并且不会写入您的一项更改。

android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)

This is happening because every time you create new SQLiteOpenHelperobject you are actually making new database connection. If you try to write to the database from actual distinct connections at the same time, one will fail. (from answer above)

发生这种情况是因为每次创建新的SQLiteOpenHelper对象时，您实际上都是在建立新的数据库连接。如果您尝试同时从实际的不同连接写入数据库，则会失败。（来自上面的回答）

To use database with multiple threads we need to make sure we are using one database connection.

要在多个线程中使用数据库，我们需要确保使用一个数据库连接。

Let's make singleton class Database Managerwhich will hold and return single SQLiteOpenHelperobject.

让我们制作单例类数据库管理器，它将保存并返回单个SQLiteOpenHelper对象。

public class DatabaseManager {

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initialize(..) method first.");
        }

        return instance;
    }

    public SQLiteDatabase getDatabase() {
        return new mDatabaseHelper.getWritableDatabase();
    }

}

Updated code which write data to database in separate threads will look like this.

在单独的线程中将数据写入数据库的更新代码如下所示。

 // In your application class
 DatabaseManager.initializeInstance(new MySQLiteOpenHelper());
 // Thread 1
 DatabaseManager manager = DatabaseManager.getInstance();
 SQLiteDatabase database = manager.getDatabase()
 database.insert(…);
 database.close();

 // Thread 2
 DatabaseManager manager = DatabaseManager.getInstance();
 SQLiteDatabase database = manager.getDatabase()
 database.insert(…);
 database.close();

This will bring you another crash.

这会给你带来另一个崩溃。

java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase

Since we are using only one database connection, method getDatabase()return same instance of SQLiteDatabaseobject for Thread1and Thread2. What is happening, Thread1may close database, while Thread2is still using it. That's why we have IllegalStateExceptioncrash.

由于我们只使用一个数据库连接，方法getDatabase（）返回同一个实例SQLiteDatabase的对象线程1和线程2。发生了什么，Thread1可能会关闭数据库，而Thread2仍在使用它。这就是我们有IllegalStateException崩溃的原因。

We need to make sure no-one is using database and only then close it. Some folks on stackoveflow recommended to never close your SQLiteDatabase. This will result in following logcat message.

我们需要确保没有人在使用数据库，然后才关闭它。stackoveflow 上的一些人建议永远不要关闭您的SQLiteDatabase。这将导致以下 logcat 消息。

Leak found
Caused by: java.lang.IllegalStateException: SQLiteDatabase created and never closed

Working sample

工作样本

public class DatabaseManager {

    private int mOpenCounter;

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase mDatabase;

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initializeInstance(..) method first.");
        }

        return instance;
    }

    public synchronized SQLiteDatabase openDatabase() {
        mOpenCounter++;
        if(mOpenCounter == 1) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        return mDatabase;
    }

    public synchronized void closeDatabase() {
        mOpenCounter--;
        if(mOpenCounter == 0) {
            // Closing database
            mDatabase.close();

        }
    }

}

Use it as follows.

使用方法如下。

SQLiteDatabase database = DatabaseManager.getInstance().openDatabase();
database.insert(...);
// database.close(); Don't close it directly!
DatabaseManager.getInstance().closeDatabase(); // correct way

Every time you need database you should call openDatabase()method of DatabaseManagerclass. Inside this method, we have a counter, which indicate how many times database is opened. If it equals to one, it means we need to create new database connection, if not, database connection is already created.

每次需要数据库时，都应该调用DatabaseManager类的openDatabase()方法。在这个方法中，我们有一个计数器，它指示数据库打开了多少次。如果等于1，则表示需要创建新的数据库连接，如果不是，则表示已经创建了数据库连接。

The same happens in closeDatabase()method. Every time we call this method, counter is decreased, whenever it goes to zero, we are closing database connection.

closeDatabase()方法中也会发生同样的情况。每次我们调用这个方法时，计数器都会减少，每当它变为零时，我们就关闭数据库连接。

Now you should be able to use your database and be sure it's thread safe.

现在您应该能够使用您的数据库并确保它是线程安全的。

Answer 3

回答by Reed

Use a Threador AsyncTaskfor long-running operations (50ms+). Test your app to see where that is. Most operations (probably) don't require a thread, because most operations (probably) only involve a few rows. Use a thread for bulk operations.
Share one SQLiteDatabaseinstance for each DB on disk between threads and implement a counting system to keep track of open connections.

使用Thread或AsyncTask进行长时间运行的操作（50ms+）。测试您的应用程序以查看它的位置。大多数操作（可能）不需要线程，因为大多数操作（可能）只涉及几行。使用线程进行批量操作。
SQLiteDatabase在线程之间为磁盘上的每个 DB共享一个实例，并实现一个计数系统来跟踪打开的连接。

Are there any best practices for these scenarios?

是否有针对这些场景的最佳实践？

Share a static field between all your classes. I used to keep a singleton around for that and other things that need to be shared. A counting scheme (generally using AtomicInteger) also should be used to make sure you never close the database early or leave it open.

在所有类之间共享一个静态字段。我曾经为那个和其他需要共享的东西保留一个单身人士。还应该使用计数方案（通常使用 AtomicInteger）来确保您永远不会提前关闭数据库或让它保持打开状态。

My solution:

我的解决方案：

For the most current version, see https://github.com/JakarCo/databasemanagerbut I'll try to keep the code up to date here as well. If you want to understand my solution, look at the code and read my notes. My notes are usually pretty helpful.

对于最新版本，请参阅https://github.com/JakarCo/databasemanager，但我也会尽量在此处更新代码。如果你想了解我的解决方案，请查看代码并阅读我的笔记。我的笔记通常很有帮助。

copy/paste the code into a new file named DatabaseManager. (or download it from github)
extend DatabaseManagerand implement onCreateand onUpgradelike you normally would. You can create multiple subclasses of the one DatabaseManagerclass in order to have different databases on disk.
Instantiate your subclass and call getDb()to use the SQLiteDatabaseclass.
Call close()for each subclass you instantiated

将代码复制/粘贴到名为DatabaseManager. （或从github下载）
扩大DatabaseManager和落实onCreate，并onUpgrade像往常一样。您可以创建一个DatabaseManager类的多个子类，以便在磁盘上拥有不同的数据库。
实例化您的子类并调用getDb()以使用SQLiteDatabase该类。
调用close()您实例化的每个子类

The code to copy/paste:

要复制/粘贴的代码：

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import java.util.concurrent.ConcurrentHashMap;

/** Extend this class and use it as an SQLiteOpenHelper class
 *
 * DO NOT distribute, sell, or present this code as your own. 
 * for any distributing/selling, or whatever, see the info at the link below
 *
 * Distribution, attribution, legal stuff,
 * See https://github.com/JakarCo/databasemanager
 * 
 * If you ever need help with this code, contact me at [email protected] (or [email protected] )
 * 
 * Do not sell this. but use it as much as you want. There are no implied or express warranties with this code. 
 *
 * This is a simple database manager class which makes threading/synchronization super easy.
 *
 * Extend this class and use it like an SQLiteOpenHelper, but use it as follows:
 *  Instantiate this class once in each thread that uses the database. 
 *  Make sure to call {@link #close()} on every opened instance of this class
 *  If it is closed, then call {@link #open()} before using again.
 * 
 * Call {@link #getDb()} to get an instance of the underlying SQLiteDatabse class (which is synchronized)
 *
 * I also implement this system (well, it's very similar) in my <a href="http://androidslitelibrary.com">Android SQLite Libray</a> at http://androidslitelibrary.com
 * 
 *
 */
abstract public class DatabaseManager {

    /**See SQLiteOpenHelper documentation
    */
    abstract public void onCreate(SQLiteDatabase db);
    /**See SQLiteOpenHelper documentation
     */
    abstract public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);
    /**Optional.
     * *
     */
    public void onOpen(SQLiteDatabase db){}
    /**Optional.
     * 
     */
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
    /**Optional
     * 
     */
    public void onConfigure(SQLiteDatabase db){}



    /** The SQLiteOpenHelper class is not actually used by your application.
     *
     */
    static private class DBSQLiteOpenHelper extends SQLiteOpenHelper {

        DatabaseManager databaseManager;
        private AtomicInteger counter = new AtomicInteger(0);

        public DBSQLiteOpenHelper(Context context, String name, int version, DatabaseManager databaseManager) {
            super(context, name, null, version);
            this.databaseManager = databaseManager;
        }

        public void addConnection(){
            counter.incrementAndGet();
        }
        public void removeConnection(){
            counter.decrementAndGet();
        }
        public int getCounter() {
            return counter.get();
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            databaseManager.onCreate(db);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            databaseManager.onUpgrade(db, oldVersion, newVersion);
        }

        @Override
        public void onOpen(SQLiteDatabase db) {
            databaseManager.onOpen(db);
        }

        @Override
        public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            databaseManager.onDowngrade(db, oldVersion, newVersion);
        }

        @Override
        public void onConfigure(SQLiteDatabase db) {
            databaseManager.onConfigure(db);
        }
    }

    private static final ConcurrentHashMap<String,DBSQLiteOpenHelper> dbMap = new ConcurrentHashMap<String, DBSQLiteOpenHelper>();

    private static final Object lockObject = new Object();


    private DBSQLiteOpenHelper sqLiteOpenHelper;
    private SQLiteDatabase db;
    private Context context;

    /** Instantiate a new DB Helper. 
     * <br> SQLiteOpenHelpers are statically cached so they (and their internally cached SQLiteDatabases) will be reused for concurrency
     *
     * @param context Any {@link android.content.Context} belonging to your package.
     * @param name The database name. This may be anything you like. Adding a file extension is not required and any file extension you would like to use is fine.
     * @param version the database version.
     */
    public DatabaseManager(Context context, String name, int version) {
        String dbPath = context.getApplicationContext().getDatabasePath(name).getAbsolutePath();
        synchronized (lockObject) {
            sqLiteOpenHelper = dbMap.get(dbPath);
            if (sqLiteOpenHelper==null) {
                sqLiteOpenHelper = new DBSQLiteOpenHelper(context, name, version, this);
                dbMap.put(dbPath,sqLiteOpenHelper);
            }
            //SQLiteOpenHelper class caches the SQLiteDatabase, so this will be the same SQLiteDatabase object every time
            db = sqLiteOpenHelper.getWritableDatabase();
        }
        this.context = context.getApplicationContext();
    }
    /**Get the writable SQLiteDatabase
     */
    public SQLiteDatabase getDb(){
        return db;
    }

    /** Check if the underlying SQLiteDatabase is open
     *
     * @return whether the DB is open or not
     */
    public boolean isOpen(){
        return (db!=null&&db.isOpen());
    }


    /** Lowers the DB counter by 1 for any {@link DatabaseManager}s referencing the same DB on disk
     *  <br />If the new counter is 0, then the database will be closed.
     *  <br /><br />This needs to be called before application exit.
     * <br />If the counter is 0, then the underlying SQLiteDatabase is <b>null</b> until another DatabaseManager is instantiated or you call {@link #open()}
     *
     * @return true if the underlying {@link android.database.sqlite.SQLiteDatabase} is closed (counter is 0), and false otherwise (counter > 0)
     */
    public boolean close(){
        sqLiteOpenHelper.removeConnection();
        if (sqLiteOpenHelper.getCounter()==0){
            synchronized (lockObject){
                if (db.inTransaction())db.endTransaction();
                if (db.isOpen())db.close();
                db = null;
            }
            return true;
        }
        return false;
    }
    /** Increments the internal db counter by one and opens the db if needed
    *
    */
    public void open(){
        sqLiteOpenHelper.addConnection();
        if (db==null||!db.isOpen()){
                synchronized (lockObject){
                    db = sqLiteOpenHelper.getWritableDatabase();
                }
        } 
    }
}

Answer 4

回答by Brad Hein

The Database is very flexible with multi-threading. My apps hit their DBs from many different threads simultaneously and it does just fine. In some cases I have multiple processes hitting the DB simultaneously and that works fine too.

数据库在多线程方面非常灵活。我的应用程序同时从许多不同的线程访问他们的数据库，它做得很好。在某些情况下，我有多个进程同时访问数据库，这也很好用。

Your async tasks - use the same connection when you can, but if you have to, its OK to access the DB from different tasks.

您的异步任务 - 尽可能使用相同的连接，但如果必须，可以从不同的任务访问数据库。

Answer 5

回答by gonglong

Dmytro's answer works fine for my case. I think it's better to declare the function as synchronized. at least for my case, it would invoke null pointer exception otherwise, e.g. getWritableDatabase not yet returned in one thread and openDatabse called in another thread meantime.

Dmytro 的回答适用于我的情况。我认为最好将该函数声明为同步的。至少对于我的情况，否则它会调用空指针异常，例如 getWritableDatabase 尚未在一个线程中返回，同时在另一个线程中调用 openDatabse。

public synchronized SQLiteDatabase openDatabase() {
    if(mOpenCounter.incrementAndGet() == 1) {
        // Opening new database
        mDatabase = mDatabaseHelper.getWritableDatabase();
    }
    return mDatabase;
}

Answer 6

回答by dell116

after struggling with this for a couple of hours, I've found that you can only use one db helper object per db execution. For example,

在为此苦苦挣扎了几个小时后，我发现每次执行 db 只能使用一个 db helper 对象。例如，

for(int x = 0; x < someMaxValue; x++)
{
    db = new DBAdapter(this);
    try
    {

        db.addRow
        (
                NamesStringArray[i].toString(), 
                StartTimeStringArray[i].toString(),
                EndTimeStringArray[i].toString()
        );

    }
    catch (Exception e)
    {
        Log.e("Add Error", e.toString());
        e.printStackTrace();
    }
    db.close();
}

as apposed to:

如对：

db = new DBAdapter(this);
for(int x = 0; x < someMaxValue; x++)
{

    try
    {
        // ask the database manager to add a row given the two strings
        db.addRow
        (
                NamesStringArray[i].toString(), 
                StartTimeStringArray[i].toString(),
                EndTimeStringArray[i].toString()
        );

    }
    catch (Exception e)
    {
        Log.e("Add Error", e.toString());
        e.printStackTrace();
    }

}
db.close();

creating a new DBAdapter each time the loop iterates was the only way I could get my strings into a database through my helper class.

每次循环迭代时创建一个新的 DBAdapter 是我通过辅助类将字符串放入数据库的唯一方法。

Answer 7

回答by Zimbo Rodger

You can try to apply new architecture approach anouncedat Google I/O 2017.

您可以尝试应用在 Google I/O 2017 上宣布的新架构方法。

It also includes new ORM library called Room

它还包括名为Room 的新 ORM 库

It contains three main components: @Entity, @Dao and @Database

它包含三个主要组件：@Entity、@Dao 和@Database

User.java

用户.java

@Entity
public class User {
  @PrimaryKey
  private int uid;

  @ColumnInfo(name = "first_name")
  private String firstName;

  @ColumnInfo(name = "last_name")
  private String lastName;

  // Getters and setters are ignored for brevity,
  // but they're required for Room to work.
}

UserDao.java

用户道

@Dao
public interface UserDao {
  @Query("SELECT * FROM user")
  List<User> getAll();

  @Query("SELECT * FROM user WHERE uid IN (:userIds)")
  List<User> loadAllByIds(int[] userIds);

  @Query("SELECT * FROM user WHERE first_name LIKE :first AND "
       + "last_name LIKE :last LIMIT 1")
  User findByName(String first, String last);

  @Insert
  void insertAll(User... users);

  @Delete
  void delete(User user);
}

AppDatabase.java

应用数据库.java

@Database(entities = {User.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
  public abstract UserDao userDao();
}

Answer 8

回答by Swaroop

My understanding of SQLiteDatabase APIs is that in case you have a multi threaded application, you cannot afford to have more than a 1 SQLiteDatabase object pointing to a single database.

我对 SQLiteDatabase API 的理解是，如果您有一个多线程应用程序，您无法承受超过 1 个指向单个数据库的 SQLiteDatabase 对象。

The object definitely can be created but the inserts/updates fail if different threads/processes (too) start using different SQLiteDatabase objects (like how we use in JDBC Connection).

该对象肯定可以创建，但如果不同的线程/进程（也）开始使用不同的 SQLiteDatabase 对象（就像我们在 JDBC 连接中使用的方式），则插入/更新会失败。

The only solution here is to stick with 1 SQLiteDatabase objects and whenever a startTransaction() is used in more than 1 thread, Android manages the locking across different threads and allows only 1 thread at a time to have exclusive update access.

这里唯一的解决方案是坚持使用 1 个 SQLiteDatabase 对象，并且每当 startTransaction() 用于超过 1 个线程时，Android 管理跨不同线程的锁定，并且一次只允许 1 个线程具有独占更新访问权限。

Also you can do "Reads" from the database and use the same SQLiteDatabase object in a different thread (while another thread writes) and there would never be database corruption i.e "read thread" wouldn't read the data from the database till the "write thread" commits the data although both use the same SQLiteDatabase object.

您也可以从数据库中“读取”并在不同的线程中使用相同的 SQLiteDatabase 对象（而另一个线程写入）并且永远不会有数据库损坏，即“读取线程”不会从数据库读取数据直到“写入线程”提交数据，尽管两者都使用相同的 SQLiteDatabase 对象。

This is different from how connection object is in JDBC where if you pass around (use the same) the connection object between read and write threads then we would likely be printing uncommitted data too.

这与连接对象在 JDBC 中的方式不同，在 JDBC 中，如果您在读写线程之间传递（使用相同的）连接对象，那么我们也可能会打印未提交的数据。

In my enterprise application, I try to use conditional checks so that the UI Thread never have to wait, while the BG thread holds the SQLiteDatabase object (exclusively). I try to predict UI Actions and defer BG thread from running for 'x' seconds. Also one can maintain PriorityQueue to manage handing out SQLiteDatabase Connection objects so that the UI Thread gets it first.

在我的企业应用程序中，我尝试使用条件检查，以便 UI 线程永远不必等待，而 BG 线程持有 SQLiteDatabase 对象（独占）。我尝试预测 UI 操作并推迟 BG 线程运行“x”秒。也可以维护 PriorityQueue 来管理 SQLiteDatabase Connection 对象的分发，以便 UI 线程首先获取它。

Answer 9

回答by Ian Spencer

Having had some issues, I think I have understood why I have been going wrong.

遇到了一些问题，我想我已经明白为什么我会出错。

I had written a database wrapper class which included a close()which called the helper close as a mirror of open()which called getWriteableDatabase and then have migrated to a ContentProvider. The model for ContentProviderdoes not use SQLiteDatabase.close()which I think is a big clue as the code does use getWriteableDatabaseIn some instances I was still doing direct access (screen validation queries in the main so I migrated to a getWriteableDatabase/rawQuery model.

我编写了一个数据库包装器类，其中包含一个close()称为 helper close 作为其镜像的open()名为 getWriteableDatabase的镜像，然后迁移到ContentProvider. 模型ContentProvider没有使用SQLiteDatabase.close()，我认为这是一个很大的线索，因为代码确实使用了getWriteableDatabase在某些情况下，我仍在进行直接访问（主要的屏幕验证查询，所以我迁移到了 getWriteableDatabase/rawQuery 模型。

I use a singleton and there is the slightly ominous comment in the close documentation

我使用了一个单例，并且在关闭文档中有一点不祥的评论

Close anyopen database object

关闭任何打开的数据库对象

(my bolding).

（我的粗体）。

So I have had intermittent crashes where I use background threads to access the database and they run at the same time as foreground.

所以我遇到了间歇性崩溃，我使用后台线程访问数据库并且它们与前台同时运行。

So I think close()forces the database to close regardless of any other threads holding references - so close()itself is not simply undoing the matching getWriteableDatabasebut force closing anyopen requests. Most of the time this is not a problem as the code is single threading, but in multi-threaded cases there is always the chance of opening and closing out of sync.

所以我认为close()无论任何其他线程持有引用，都会强制关闭数据库 - 所以close()它本身不是简单地撤消匹配getWriteableDatabase而是强制关闭任何打开的请求。大多数时候这不是问题，因为代码是单线程的，但在多线程的情况下，总是有机会打开和关闭不同步。

Having read comments elsewhere that explains that the SqLiteDatabaseHelper code instance counts, then the only time you want a close is where you want the situation where you want to do a backup copy, and you want to force all connections to be closed and force SqLite to write away any cached stuff that might be loitering about - in other words stop all application database activity, close just in case the Helper has lost track, do any file level activity (backup/restore) then start all over again.

在其他地方阅读了解释 SqliteDatabaseHelper 代码实例计数的评论，那么您想要关闭的唯一时间是您想要进行备份副本的情况，并且您想要强制关闭所有连接并强制 Sqlite写掉任何可能徘徊的缓存内容 - 换句话说，停止所有应用程序数据库活动，关闭以防万一助手丢失，执行任何文件级活动（备份/恢复）然后重新开始。

Although it sounds like a good idea to try and close in a controlled fashion, the reality is that Android reserves the right to trash your VM so any closing is reducing the risk of cached updates not being written, but it cannot be guaranteed if the device is stressed, and if you have correctly freed your cursors and references to databases (which should not be static members) then the helper will have closed the database anyway.

虽然尝试以受控方式关闭听起来是个好主意，但实际情况是 Android 保留丢弃您的 VM 的权利，因此任何关闭操作都可以降低缓存更新不被写入的风险，但无法保证设备压力很大，如果您正确释放了游标和对数据库的引用（不应是静态成员），那么帮助程序无论如何都会关闭数据库。

So my take is that the approach is:

所以我认为方法是：

Use getWriteableDatabase to open from a singleton wrapper. (I used a derived application class to provide the application context from a static to resolve the need for a context).

使用 getWriteableDatabase 从单例包装器打开。（我使用派生的应用程序类从静态提供应用程序上下文来解决对上下文的需求）。

Never directly call close.

永远不要直接调用close。

Never store the resultant database in any object that does not have an obvious scope and rely on reference counting to trigger an implicit close().

永远不要将结果数据库存储在任何没有明显作用域的对象中，并且依赖引用计数来触发隐式 close()。

If doing file level handling, bring all database activity to a halt and then call close just in case there is a runaway thread on the assumption that you write proper transactions so the runaway thread will fail and the closed database will at least have proper transactions rather than potentially a file level copy of a partial transaction.

如果进行文件级处理，请停止所有数据库活动，然后调用 close 以防万一有一个失控线程，假设您编写了正确的事务，因此失控线程将失败，并且关闭的数据库至少会有正确的事务而不是可能是部分事务的文件级副本。

Answer 10

回答by Theo

I know that the response is late, but the best way to execute sqlite queries in android is through a custom content provider. In that way the UI is decoupled with the database class(the class that extends the SQLiteOpenHelper class). Also the queries are executed in a background thread(Cursor Loader).

我知道响应晚了，但在 android 中执行 sqlite 查询的最佳方法是通过自定义内容提供程序。通过这种方式，UI 与数据库类（扩展 SQLiteOpenHelper 类的类）分离。此外，查询在后台线程（Cursor Loader）中执行。

Android 上 SQLite 的最佳实践是什么？

提问by Vidar Vestnes

采纳答案by Kevin Galligan

回答by Dmytro Danylyk

Concurrent Database Access

并发数据库访问

Working sample

工作样本

回答by Reed

回答by Brad Hein

回答by gonglong

回答by dell116

回答by Zimbo Rodger

回答by Swaroop

回答by Ian Spencer

回答by Theo

相关推荐

最近更新

标签

Android 上 SQLite 的最佳实践是什么？

提问by Vidar Vestnes

采纳答案by Kevin Galligan

回答by Dmytro Danylyk

Concurrent Database Access

并发数据库访问

Working sample

工作样本

回答by Reed

回答by Brad Hein

回答by gonglong

回答by dell116

回答by Zimbo Rodger

回答by Swaroop

回答by Ian Spencer

回答by Theo

相关推荐

解组 Android Intent Parcelable 时找不到类

如何制作 Android 插页式广告？

keyPress 事件未在 Android 手机中触发

以编程方式获取Android手机的电话号码

相关推荐

最近更新

标签