在Android中预先填充数据库的最快，最有效的方法-Fastestandmostefficientwaytopre-populatedatabaseinAndroid

5  

I suggest the following:

我建议如下：

1. Wrap all of your INSERT logic into a transaction (BEGIN... COMMIT, or via the beginTransaction()... endTransaction() APIs)
2. 将所有INSERT逻辑包装到事务中（BEGIN ... COMMIT，或通过beginTransaction（）... endTransaction（）API）
3. As already suggested, utilize the bind APIs and recycle objects.
4. 如前所述，使用绑定API和回收对象。
5. Don't create any indexes until after this bulk insert is complete.
6. 在批量插入完成之前，不要创建任何索引。

Additionally take a look at Faster bulk inserts in sqlite3?

另外看一下sqlite3中的Faster批量插入？

4  

Your question states, that you want the fastest way - but you don't like the way it's done in the article - you don't want to manually replace the DB file (even though, it may be actually faster than filling empty DB with queries).

你的问题表明，你想要最快的方式 - 但你不喜欢文章中的方式 - 你不想手动替换数据库文件（尽管它实际上可能比填充空数据库更快）查询）。

I had exaclty the same thoughts - and I figured out, that populating via SQL statements and prepopulating can both be the best solution - but it depends on the way you will use the DB.

我有同样的想法 - 我想通过SQL语句填充和预填充都可以是最好的解决方案 - 但这取决于你使用数据库的方式。

In my application I need to have about 2600 rows (with 4 columns) in DB at the very first run - it's the data for autocompletion and few other things. It will be modified quite rarely (users can add custom records, but most of the time - they don't need to) and is quite big. Populating it from SQL statements takes not only significantly more time, but more space in the APK (assuming I would store data inside it, alternatively I could download it from the internet).

在我的应用程序中，我需要在第一次运行时在DB中有大约2600行（有4列） - 它是自动完成的数据和其他一些东西。它将很少被修改（用户可以添加自定义记录，但大部分时间 - 他们不需要）并且非常大。从SQL语句填充它不仅需要更多的时间，而且还需要更多的空间（假设我将数据存储在其中，或者我可以从互联网上下载）。

This is the very simple case (the "Big" insert can take place only once and only at first startup) and I decided to go with copying prepopulated DB file. Sure, it may not be the nicest way - but it's faster. I want my users to be able to use the app as quickly as it's possible and treat speed as a priority - and they really like it. On the contrary, I doubt they would be glad when app would slow down because I thought that slower and nicer solution is actually better.

这是一个非常简单的情况（“大”插入只能在第一次启动时发生一次）我决定使用复制预先填充的DB文件。当然，它可能不是最好的方式 - 但它更快。我希望我的用户能够尽可能快地使用应用程序并将速度视为优先事项 - 他们非常喜欢它。相反，我怀疑当应用程序减速时他们会很高兴，因为我认为更慢更好的解决方案实际上更好。

If instead of 2600 my table would have initially ~50 rows, I would go with SQL statements, since speed and size difference wouldn't be so big.

如果不是2600我的表最初会有~50行，我会使用SQL语句，因为速度和大小差异不会那么大。

You have to decide which solution fits your case better. If you foresee any problems that may arise from using "prepopulated db" option - don't use it. If you are not sure about these problems - ask, providing more details on how you will use (and eventually, upgrade) contents of the DB. If you aren't quite sure which solution will be faster - benchmark it. And don't be afraid of that copying file method - it can work really well, if used wisely.

您必须更好地决定哪种解决方案适合您的情况。如果您预见到使用“预填充db”选项可能引起的任何问题 - 请勿使用它。如果您不确定这些问题 - 请询问，提供有关如何使用（并最终升级）数据库内容的更多详细信息。如果你不确定哪种解决方案会更快 - 那么就要对它进行基准测试。并且不要害怕复制文件方法 - 如果明智地使用它可以很好地工作。

2  

You can have your cake and eat it too. Here is a solution that can both respect the use of your db adapter and also use a simple (and much faster) copy process for a pre-populated database.

你可以吃蛋糕，也可以吃。这是一个解决方案，它既可以尊重数据库适配器的使用，也可以对预先填充的数据库使用简单（且速度更快）的复制过程。

I'm using a db adapter based on one of Google's examples. It includes an internal class dbHelper() that extends Android's SQLiteOpenHelper() class. The trick is to override it's onCreate() method. This method is only called when the helper can't find the DB you are referencing and it has to create the DB for you. This should only happen the first time it is called on any given device installation, which is the only time you want to copy the DB. So override it like this -

我正在使用基于Google的一个示例的数据库适配器。它包含一个内部类dbHelper（），它扩展了Android的SQLiteOpenHelper（）类。诀窍是覆盖它的onCreate（）方法。只有在帮助程序找不到您正在引用的数据库且必须为您创建数据库时才会调用此方法。这应该仅在第一次在任何给定的设备安装上调用时发生，这是您唯一要复制数据库的时间。所以像这样覆盖它 -

    @Override
    public void onCreate(SQLiteDatabase db) {
        mNeedToCopyDb = true;
    }

Of course make sure you have first declared and initialized this flag in the DbHelper -

当然要确保你在DbHelper中首先声明并初始化了这个标志 -

        private Boolean mNeedToCopyDb = false;

Now, in your dbAdapter's open() method you can test to see if you need to copy the DB. If you do then close the helper, copy the DB and then finally open a new helper (see below code). All future attempts to open the db using the db adapter will find your (copied) DB and therefor the onCreate() method of the internal DbHelper class will not be called and the flag mNeedToCopyDb will remain false.

现在，在dbAdapter的open（）方法中，您可以测试是否需要复制数据库。如果你这样做，然后关闭帮助程序，复制数据库，然后最后打开一个新的帮助程序（见下面的代码）。将来使用db适配器打开数据库的所有尝试都将找到您的（复制的）DB，因此不会调用内部DbHelper类的onCreate（）方法，并且标志mNeedToCopyDb将保持为false。

    /**
 * Open the database using the adapter. If it cannot be opened, try to
 * create a new instance of the database. If it cannot be created,
 * throw an exception to signal the failure.
 * 
 * @return this (self reference, allowing this to be chained in an
 *         initialization call)
 * @throws SQLException if the database could neither be opened nor created
 */
public MyDbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getReadableDatabase();

    if (mDbHelper.mNeedToCopyDb == true){
        mDbHelper.close();
        try {
            copyDatabase();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            mDbHelper = new DatabaseHelper(mCtx);
            mDb = mDbHelper.getReadableDatabase();
        }
    }
    return this;
}

Just place some code to do your database copy inside of your db adapter in a method named copyDatabase() as used above. You can use the value of mDb that was updated by the first instance of DbHelper (when it created the stub DB) to get the path to use for your output stream when you do the copy. Construct your input stream like this

只需放置一些代码，就可以在上面使用的名为copyDatabase（）的方法中在db适配器内部进行数据库复制。您可以使用由DbHelper的第一个实例（创建存根数据库时）更新的mDb值，以便在执行复制时获取用于输出流的路径。像这样构造输入流

dbInputStream = mCtx.getResources().openRawResource(R.raw.mydatabase);

[note: If your DB file is too large to copy in one gulp then just break it up into a few pieces.]

[注意：如果你的数据库文件太大而无法一次性复制，那么就把它分成几块。]

This works very fast and puts all of the db access code (including the copying of the DB if needed) into your db adapter.

这非常快，并将所有数据库访问代码（包括数据库的复制，如果需要）放入数据库适配器。

2  

I wrote a DbUtils class similar to the previous answer. It is part of the ORM tool greenDAO and is available on github. The difference is that it will try to find statement boundaries using a simple regular expression, not just line endings. If you have to rely on a SQL file, I doubt that there's a faster way.

我写了一个类似于上一个答案的DbUtils类。它是ORM工具greenDAO的一部分，可以在github上找到。不同之处在于它将尝试使用简单的正则表达式来查找语句边界，而不仅仅是行结尾。如果你必须依赖SQL文件，我怀疑有更快的方法。

But, if you can supply the data in another format, it should be significantly faster than using a SQL script. The trick is to use a compiled statement. For each data row, you bind the parsed values to the statement and execute the statement. And, of course, you need to do this inside a transaction. I would recommend a simple delimiter separated file format (for example CSV) because it can be parsed faster than XML or JSON.

但是，如果您可以以其他格式提供数据，那么它应该比使用SQL脚本快得多。诀窍是使用编译语句。对于每个数据行，将解析后的值绑定到语句并执行该语句。当然，您需要在事务中执行此操作。我建议使用简单的分隔符分隔文件格式（例如CSV），因为它可以比XML或JSON更快地解析。

We did some performance tests for greenDAO. For our test data, we had insert rates of about 5000 rows per second. And for some reason, the rate dropped to half with Android 4.0.

我们为greenDAO做了一些性能测试。对于我们的测试数据，我们的插入速率约为每秒5000行。出于某种原因，Android 4.0的速度降至一半。

0  

ye, the assets maybe has size limit, so if bigger than the limit, you can cut to more files.

是的，资产可能有大小限制，所以如果大于限制，你可以削减更多的文件。

and exesql support more sql sentence, here give you a example:

和exesql支持更多的sql语句，这里举个例子：

    BufferedReader br = null;
    try {
        br = new BufferedReader(new InputStreamReader(asManager.open(INIT_FILE)), 1024 * 4);
        String line = null;
        db.beginTransaction();
        while ((line = br.readLine()) != null) {
            db.execSQL(line);
        }
        db.setTransactionSuccessful();
    } catch (IOException e) {
        FLog.e(LOG_TAG, "read database init file error");
    } finally {
        db.endTransaction();
        if (br != null) {
            try {
                br.close();
            } catch (IOException e) {
                FLog.e(LOG_TAG, "buffer reader close error");
            }
        }
    }

above example require the INIT_FILE need every line is a sql sentence.

上面的例子要求INIT_FILE需要每一行都是一个sql语句。

Also, if your sql sentences file is big, you can create the database out site of android(sqlite support for windows, linux, so you can create the database in your os, and copy the database file to your assets folder, if big, you can zip it)

另外，如果您的sql语句文件很大，您可以创建android的数据库站点（sqlite支持windows，linux，因此您可以在您的操作系统中创建数据库，并将数据库文件复制到您的资源文件夹，如果大，你可以压缩它）

when your application run, you can get the database file from assets, directed to save to your application's database folder (if you zip it, you can unzip to the application's database folder)

当您的应用程序运行时，您可以从资产中获取数据库文件，并将其保存到应用程序的数据库文件夹中（如果您将其压缩，则可以解压缩到应用程序的数据库文件夹）

hope can help you -):

希望可以帮到你 - ）：

0  

I used this method. First create your sqlite database there are a few programs you can use I like SqliteBrowser. Then copy your database file into your assets folder. Then you can use this code in the constructor of SQLiteOpenHelper.

我用过这种方法。首先创建你的sqlite数据库你可以使用一些程序我喜欢SqliteBrowser。然后将数据库文件复制到assets文件夹中。然后，您可以在SQLiteOpenHelper的构造函数中使用此代码。

final String outFileName = DB_PATH + NAME;

        if(! new File(outFileName).exists()){
            this.getWritableDatabase().close();
            //Open your local db as the input stream
            final InputStream myInput = ctx.getAssets().open(NAME, Context.MODE_PRIVATE);

            //Open the empty db as the output stream
            final OutputStream myOutput = new FileOutputStream(outFileName);
            //final FileOutputStream myOutput = context.openFileOutput(outFileName, Context.MODE_PRIVATE);

            //transfer bytes from the inputfile to the outputfile
            final byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer))>0){
                myOutput.write(buffer, 0, length);
            }

            //Close the streams
            myOutput.flush();
            ((FileOutputStream) myOutput).getFD().sync();
            myOutput.close();
            myInput.close();
        }
        } catch (final Exception e) {
            // TODO: handle exception
        }

DB_PATH is something like /data/data/com.mypackage.myapp/databases/

DB_PATH类似于/data/data/com.mypackage.myapp/databases/

NAME is whatever database name you choose "mydatabase.db"

NAME是您选择的任何数据库名称“mydatabase.db”

I know there are many improvements on this code but it worked so well and is VERY FAST. So I left it alone. Like this might be even better in the onCreate() method. Also checking if the file exists every time is probably not the best. Anyway like I said it works, it's fast and reliable.

我知道这个代码有很多改进但它运行得很好并且非常快。所以我一个人待着。像这样在onCreate（）方法中可能会更好。另外，每次检查文件是否存在可能不是最好的。无论如何，就像我说它有效，它快速可靠。

0  

If the data is not private then simply host it on your website then download it on first run. That way you can keep it up to date. So long as you remember to take app version into account when you upload it to your webserver.

如果数据不是私有的，那么只需将其托管在您的网站上，然后在首次运行时下载。这样你可以保持最新。只要您记得在将应用版本上传到网络服务器时将其考虑在内。