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

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).

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

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

如果我有几个异步任务，它们应该共享一个连接，还是应该每个都打开一个连接?

Are there any best practices for these scenarios?

对于这些场景，有什么最佳实践吗?

10 个解决方案

#1

Inserts, updates, deletes and reads are generally OK from multiple threads, but Brad's answer is 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.

一个助手实例，一个db连接。即使您在多个线程中使用它，也要同时使用一个连接。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).

一个助手，一个db连接，用java代码序列化。一个线程，1000个线程，如果在它们之间共享一个助手实例，那么所有的db访问代码都是串行的。生活是美好的。

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上不调用正确的插入/更新版本，就不会出现异常。你会在你的日志里得到一个信息，那就是它。

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 Sqlite锁定(更新链接6/18/2012)
• Android-Database-Locking-Collisions-Example by touchlab on GitHub
• android -数据库-锁定-碰撞-例子由触摸实验室在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和我正在完成一个ORM工具，基于他的Ormlite，这个工具可以在Android数据库实现中正常工作，并且遵循我在博客文章中描述的安全创建/调用结构。那应该很快就会出来。看一看。

---

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

与此同时，还有一篇后续博文:

• Single SQLite connection
• 单一SQLite连接

Also checkout the fork by 2point0 of the previously mentioned locking example:

还可以通过前面提到的锁示例的2点0检查fork:

• Android-Database-Locking-Collisions-Example by 2point0 on GitHub
• android数据库-锁-碰撞- GitHub上2点0的例子

#2

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 SQLiteOpenHelper object 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 Manager which will hold and return single SQLiteOpenHelper object.

让我们创建单例类数据库管理器，它将保存并返回单个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 SQLiteDatabase object for Thread1 and Thread2. What is happening, Thread1 may close database, while Thread2 is still using it. That’s why we have IllegalStateException crash.

由于我们只使用一个数据库连接，方法getDatabase()为Thread1和Thread2返回SQLiteDatabase对象的相同实例。发生的情况是，Thread1可能会关闭数据库，而Thread2仍在使用它。这就是为什么我们有非法州例外崩溃的原因。

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. It not only sounds stupid but also honor you with 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 DatabaseManager class. 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()方法也是如此。每次调用这个方法，计数器就会减少，当计数器变为0时，就会关闭数据库连接。

---

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

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

#3

• Use a Thread or AsyncTask for 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.
• 对长时间运行的操作使用线程或AsyncTask (50ms+)。测试你的应用，看看它在哪里。大多数操作(可能)不需要线程，因为大多数操作(可能)只涉及几行。使用线程进行批量操作。
• Share one SQLiteDatabase instance for each DB on disk between threads and implement a counting system to keep track of open connections.
• 在线程之间为磁盘上的每个DB共享一个SQLiteDatabase实例，并实现一个计数系统来跟踪打开的连接。

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/databasemanager but 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，但我也会尽量在这里更新代码。如果您想了解我的解决方案，请查看代码并阅读我的说明。我的笔记通常很有用。

1. copy/paste the code into a new file named DatabaseManager. (or download it from github)
2. 将代码复制/粘贴到名为DatabaseManager的新文件中。(或从github上下载)
3. extend DatabaseManager and implement onCreate and onUpgrade like you normally would. You can create multiple subclasses of the one DatabaseManager class in order to have different databases on disk.
4. 像往常一样扩展DatabaseManager并实现onCreate和onUpgrade。您可以创建一个DatabaseManager类的多个子类，以便在磁盘上拥有不同的数据库。
5. Instantiate your subclass and call getDb() to use the SQLiteDatabase class.
6. 实例化您的子类并调用getDb()来使用SQLiteDatabase类。
7. Call close() for each subclass you instantiated
8. 为实例化的每个子类调用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 [e-mail] (or [e-mail] )
 * 
 * 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();
                }
        } 
    }
}

#4

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.

数据库非常灵活，具有多线程。我的应用程序同时从许多不同的线程击中他们的DBs，而且它做得很好。在某些情况下，我有多个进程同时命中DB，这也很好。

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

异步任务——尽可能使用相同的连接，但是如果必须这样做，可以从不同的任务访问DB。

#5

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的答案对我来说很适用。我认为最好将函数声明为synchronized。至少在我的例子中，它会调用空指针异常，例如getWritableDatabase还没有在一个线程中返回，而openDatabse在另一个线程中调用。

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

#6

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 Connection中使用的方式)，那么插入/更新就会失败。

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.

这里唯一的解决方案是坚持使用一个SQLiteDatabase对象，并且无论何时在超过一个线程中使用startTransaction()， Android都会管理跨不同线程的锁定，并且每次只允许一个线程拥有独占的更新访问。

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对象(而另一个线程则写入)，并且不会出现数据库损坏i。e“read thread”在“write thread”提交数据之前不会从数据库中读取数据，尽管它们都使用相同的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中连接对象的方式不同，如果您在读和写线程之间传递(使用相同的)连接对象，那么我们可能也会打印未提交的数据。

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连接对象，以便UI线程首先获得它。

#7

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，这是通过helper类将字符串放入数据库的惟一方法。

#8

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 ContentProvider does not use SQLiteDatabase.close() which I think is a big clue as the code does use getWriteableDatabase In 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 any open 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 getWriteableDatabase but force closing any open 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.

从来没有直接调用关闭。

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.

如果做文件处理水平,把所有数据库活动停止,然后调用关闭以防有失控线程假设你写正确的交易失控线程失败和关闭数据库至少会有适当的事务,而不是潜在的部分事务的文件级副本。

#9

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

您可以尝试在2017年谷歌I/O上应用新的体系结构方法。

It also includes new ORM library called Room

它还包括一个叫做Room的新的ORM图书馆

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

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

User.java

User.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

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

AppDatabase.java

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

#10

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类的类)解耦了。查询也在后台线程(游标加载程序)中执行。