Android 发送带有数据库的应用程序

Ship an application with a database

提问by Heikki Toivonen

If your application requires a database and it comes with built in data, what is the best way to ship that application? Should I:

如果您的应用程序需要一个数据库并且它带有内置数据，那么发布该应用程序的最佳方式是什么？我是不是该：

1. Precreate the SQLite database and include it in the .apk?

2. Include the SQL commands with the application and have it create the database and insert the data on first use?

1. 预先创建 SQLite 数据库并将其包含在.apk?

2. 在应用程序中包含 SQL 命令并让它创建数据库并在第一次使用时插入数据？

The drawbacks I see are:

我看到的缺点是：

1. Possible SQLite version mismatches might cause problems and I currently don't know where the database should go and how to access it.

2. It may take a really long time to create and populate the database on the device.

1. 可能的 SQLite 版本不匹配可能会导致问题，我目前不知道数据库应该去哪里以及如何访问它。

2. 在设备上创建和填充数据库可能需要很长时间。

Any suggestions? Pointers to the documentation regarding any issues would be greatly appreciated.

有什么建议？将不胜感激指向有关任何问题的文档。

回答by Danny Remington - OMS

There are two options for creating and updating databases.

创建和更新数据库有两个选项。

One is to create a database externally, then place it in the assets folder of the project and then copy the entire database from there. This is much quicker if the database has a lot of tables and other components. Upgrades are triggered by changing the database version number in the res/values/strings.xml file.Upgrades would then be accomplished by creating a new database externally, replacing the old database in the assets folder with the new database, saving the old database in internal storage under another name, copying the new database from the assets folder into internal storage, transferring all of the data from the old database (that was renamed earlier) into the new database and finally deleting the old database. You can create a database originally by using the SQLite Manager FireFox pluginto execute your creation sql statements.

一种是在外部创建一个数据库，然后将其放在项目的assets文件夹中，然后从那里复制整个数据库。如果数据库有很多表和其他组件，这会快得多。 通过更改 res/values/strings.xml 文件中的数据库版本号来触发升级。然后通过在外部创建一个新数据库，用新数据库替换资产文件夹中的旧数据库，以另一个名称将旧数据库保存在内部存储中，将新数据库从资产文件夹复制到内部存储中，转移所有将旧数据库（之前重命名的）中的数据转移到新数据库中，最后删除旧数据库。您最初可以使用以下命令创建数据库SQLite Manager FireFox 插件来执行您创建的 sql 语句。

The other option is to create a database internally from a sql file. This is not as quick but the delay would probably be unnoticeable to the users if the database has only a few tables. Upgrades are triggered by changing the database version number in the res/values/strings.xml file.Upgrades would then be accomplished by processing an upgrade sql file. The data in the database will remain unchanged except when its container is removed, for example dropping a table.

另一种选择是从 sql 文件内部创建数据库。这不是那么快，但如果数据库只有几个表，那么延迟可能不会被用户注意到。通过更改 res/values/strings.xml 文件中的数据库版本号来触发升级。然后将通过处理升级 sql 文件来完成升级。数据库中的数据将保持不变，除非删除其容器，例如删除表。

The example below demonstrates how to use either method.

下面的示例演示了如何使用任一方法。

Here is a sample create_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice the comment about the table required by Android.)

这是一个示例 create_database.sql 文件。内部方法要放在项目的assets文件夹中，或者复制到SQLite Manager的“Execute SQL”中，为外部方法创建数据库。 （注意：注意Android所需表的注释。）

--Android requires a table named 'android_metadata' with a 'locale' column
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO "android_metadata" VALUES ('en_US');

CREATE TABLE "kitchen_table";
CREATE TABLE "coffee_table";
CREATE TABLE "pool_table";
CREATE TABLE "dining_room_table";
CREATE TABLE "card_table"; 

Here is a sample update_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice that all three types of SQL comments will be ignored by the sql parser that is included in this example.)

这是一个示例 update_database.sql 文件。内部方法要放在项目的assets文件夹中，或者复制到SQLite Manager的“执行SQL”中，为外部方法创建数据库。 （注意：三种SQL注释都会被忽略通过本示例中包含的 sql 解析器。）

--CREATE TABLE "kitchen_table";  This is one type of comment in sql.  It is ignored by parseSql.
/*
 * CREATE TABLE "coffee_table"; This is a second type of comment in sql.  It is ignored by parseSql.
 */
{
CREATE TABLE "pool_table";  This is a third type of comment in sql.  It is ignored by parseSql.
}
/* CREATE TABLE "dining_room_table"; This is a second type of comment in sql.  It is ignored by parseSql. */
{ CREATE TABLE "card_table"; This is a third type of comment in sql.  It is ignored by parseSql. }

--DROP TABLE "picnic_table"; Uncomment this if picnic table was previously created and now is being replaced.
CREATE TABLE "picnic_table" ("plates" TEXT);
INSERT INTO "picnic_table" VALUES ('paper');

Here is an entry to add to the /res/values/strings.xml file for the database version number.

这是要添加到 /res/values/strings.xml 文件的数据库版本号条目。

<item type="string" name="databaseVersion" format="integer">1</item>

Here is an activity that accesses the database and then uses it. (Note: You might want to run the database code in a separate thread if it uses a lot of resources.)

这是一个访问数据库然后使用它的活动。（注意：如果数据库代码使用大量资源，您可能希望在单独的线程中运行它。）

package android.example;

import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;

/**
 * @author Danny Remington - MacroSolve
 * 
 *         Activity for demonstrating how to use a sqlite database.
 */
public class Database extends Activity {
     /** Called when the activity is first created. */
     @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        DatabaseHelper myDbHelper;
        SQLiteDatabase myDb = null;

        myDbHelper = new DatabaseHelper(this);
        /*
         * Database must be initialized before it can be used. This will ensure
         * that the database exists and is the current version.
         */
         myDbHelper.initializeDataBase();

         try {
            // A reference to the database can be obtained after initialization.
            myDb = myDbHelper.getWritableDatabase();
            /*
             * Place code to use database here.
             */
         } catch (Exception ex) {
            ex.printStackTrace();
         } finally {
            try {
                myDbHelper.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                myDb.close();
            }
        }

    }
}

Here is the database helper class where the database is created or updated if necessary. (NOTE: Android requires that you create a class that extends SQLiteOpenHelper in order to work with a Sqlite database.)

这是数据库助手类，如果需要，可以在其中创建或更新数据库。 （注意：Android 要求您创建一个扩展 SQLiteOpenHelper 的类，以便使用 Sqlite 数据库。）

package android.example;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

/**
 * @author Danny Remington - MacroSolve
 * 
 *         Helper class for sqlite database.
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    /*
     * The Android's default system path of the application database in internal
     * storage. The package of the application is part of the path of the
     * directory.
     */
    private static String DB_DIR = "/data/data/android.example/databases/";
    private static String DB_NAME = "database.sqlite";
    private static String DB_PATH = DB_DIR + DB_NAME;
    private static String OLD_DB_PATH = DB_DIR + "old_" + DB_NAME;

    private final Context myContext;

    private boolean createDatabase = false;
    private boolean upgradeDatabase = false;

    /**
     * Constructor Takes and keeps a reference of the passed context in order to
     * access to the application assets and resources.
     * 
     * @param context
     */
    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, context.getResources().getInteger(
                R.string.databaseVersion));
        myContext = context;
        // Get the path of the database that is based on the context.
        DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath();
    }

    /**
     * Upgrade the database in internal storage if it exists but is not current. 
     * Create a new empty database in internal storage if it does not exist.
     */
    public void initializeDataBase() {
        /*
         * Creates or updates the database in internal storage if it is needed
         * before opening the database. In all cases opening the database copies
         * the database in internal storage to the cache.
         */
        getWritableDatabase();

        if (createDatabase) {
            /*
             * If the database is created by the copy method, then the creation
             * code needs to go here. This method consists of copying the new
             * database from assets into internal storage and then caching it.
             */
            try {
                /*
                 * Write over the empty data that was created in internal
                 * storage with the one in assets and then cache it.
                 */
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        } else if (upgradeDatabase) {
            /*
             * If the database is upgraded by the copy and reload method, then
             * the upgrade code needs to go here. This method consists of
             * renaming the old database in internal storage, create an empty
             * new database in internal storage, copying the database from
             * assets to the new database in internal storage, caching the new
             * database from internal storage, loading the data from the old
             * database into the new database in the cache and then deleting the
             * old database from internal storage.
             */
            try {
                FileHelper.copyFile(DB_PATH, OLD_DB_PATH);
                copyDataBase();
                SQLiteDatabase old_db = SQLiteDatabase.openDatabase(OLD_DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);
                SQLiteDatabase new_db = SQLiteDatabase.openDatabase(DB_PATH,null, SQLiteDatabase.OPEN_READWRITE);
                /*
                 * Add code to load data into the new database from the old
                 * database and then delete the old database from internal
                 * storage after all data has been transferred.
                 */
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }

    }

    /**
     * Copies your database from your local assets-folder to the just created
     * empty database in the system folder, from where it can be accessed and
     * handled. This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException {
        /*
         * Close SQLiteOpenHelper so it will commit the created empty database
         * to internal storage.
         */
        close();

        /*
         * Open the database in the assets folder as the input stream.
         */
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        /*
         * Open the empty db in interal storage as the output stream.
         */
        OutputStream myOutput = new FileOutputStream(DB_PATH);

        /*
         * Copy over the empty db in internal storage with the database in the
         * assets folder.
         */
        FileHelper.copyFile(myInput, myOutput);

        /*
         * Access the copied database so SQLiteHelper will cache it and mark it
         * as created.
         */
        getWritableDatabase().close();
    }

    /*
     * This is where the creation of tables and the initial population of the
     * tables should happen, if a database is being created from scratch instead
     * of being copied from the application package assets. Copying a database
     * from the application package assets to internal storage inside this
     * method will result in a corrupted database.
     * <P>
     * NOTE: This method is normally only called when a database has not already
     * been created. When the database has been copied, then this method is
     * called the first time a reference to the database is retrieved after the
     * database is copied since the database last cached by SQLiteOpenHelper is
     * different than the database in internal storage.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        /*
         * Signal that a new database needs to be copied. The copy process must
         * be performed after the database in the cache has been closed causing
         * it to be committed to internal storage. Otherwise the database in
         * internal storage will not have the same creation timestamp as the one
         * in the cache causing the database in internal storage to be marked as
         * corrupted.
         */
        createDatabase = true;

        /*
         * This will create by reading a sql file and executing the commands in
         * it.
         */
            // try {
            // InputStream is = myContext.getResources().getAssets().open(
            // "create_database.sql");
            //
            // String[] statements = FileHelper.parseSqlFile(is);
            //
            // for (String statement : statements) {
            // db.execSQL(statement);
            // }
            // } catch (Exception ex) {
            // ex.printStackTrace();
            // }
    }

    /**
     * Called only if version number was changed and the database has already
     * been created. Copying a database from the application package assets to
     * the internal data system inside this method will result in a corrupted
     * database in the internal data system.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        /*
         * Signal that the database needs to be upgraded for the copy method of
         * creation. The copy process must be performed after the database has
         * been opened or the database will be corrupted.
         */
        upgradeDatabase = true;

        /*
         * Code to update the database via execution of sql statements goes
         * here.
         */

        /*
         * This will upgrade by reading a sql file and executing the commands in
         * it.
         */
        // try {
        // InputStream is = myContext.getResources().getAssets().open(
        // "upgrade_database.sql");
        //
        // String[] statements = FileHelper.parseSqlFile(is);
        //
        // for (String statement : statements) {
        // db.execSQL(statement);
        // }
        // } catch (Exception ex) {
        // ex.printStackTrace();
        // }
    }

    /**
     * Called everytime the database is opened by getReadableDatabase or
     * getWritableDatabase. This is called after onCreate or onUpgrade is
     * called.
     */
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }

    /*
     * Add your public helper methods to access and get content from the
     * database. You could return cursors by doing
     * "return myDataBase.query(....)" so it'd be easy to you to create adapters
     * for your views.
     */

}

Here's the FileHelper class that contains methods for byte stream copying files and parsing sql files.

这是 FileHelper 类，它包含字节流复制文件和解析 sql 文件的方法。

package android.example;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.nio.channels.FileChannel;

/**
 * @author Danny Remington - MacroSolve
 * 
 *         Helper class for common tasks using files.
 * 
 */
public class FileHelper {
    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - InputStream for the file to copy from.
     * @param toFile
     *            - InputStream for the file to copy to.
     */
    public static void copyFile(InputStream fromFile, OutputStream toFile) throws IOException {
        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;

        try {
            while ((length = fromFile.read(buffer)) > 0) {
                toFile.write(buffer, 0, length);
            }
        }
        // Close the streams
        finally {
            try {
                if (toFile != null) {
                    try {
                        toFile.flush();
                    } finally {
                        toFile.close();
                    }
            }
            } finally {
                if (fromFile != null) {
                    fromFile.close();
                }
            }
        }
    }

    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - String specifying the path of the file to copy from.
     * @param toFile
     *            - String specifying the path of the file to copy to.
     */
    public static void copyFile(String fromFile, String toFile) throws IOException {
        copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
    }

    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - File for the file to copy from.
     * @param toFile
     *            - File for the file to copy to.
     */
    public static void copyFile(File fromFile, File toFile) throws IOException {
        copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
    }

    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - FileInputStream for the file to copy from.
     * @param toFile
     *            - FileInputStream for the file to copy to.
     */
    public static void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
        FileChannel fromChannel = fromFile.getChannel();
        FileChannel toChannel = toFile.getChannel();

        try {
            fromChannel.transferTo(0, fromChannel.size(), toChannel);
        } finally {
            try {
                if (fromChannel != null) {
                    fromChannel.close();
                }
            } finally {
                if (toChannel != null) {
                    toChannel.close();
                }
            }
        }
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - String containing the path for the file that contains sql
     *            statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(String sqlFile) throws IOException {
        return parseSqlFile(new BufferedReader(new FileReader(sqlFile)));
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - InputStream for the file that contains sql statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(InputStream sqlFile) throws IOException {
        return parseSqlFile(new BufferedReader(new InputStreamReader(sqlFile)));
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - Reader for the file that contains sql statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(Reader sqlFile) throws IOException {
        return parseSqlFile(new BufferedReader(sqlFile));
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - BufferedReader for the file that contains sql statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(BufferedReader sqlFile) throws IOException {
        String line;
        StringBuilder sql = new StringBuilder();
        String multiLineComment = null;

        while ((line = sqlFile.readLine()) != null) {
            line = line.trim();

            // Check for start of multi-line comment
            if (multiLineComment == null) {
                // Check for first multi-line comment type
                if (line.startsWith("/*")) {
                    if (!line.endsWith("}")) {
                        multiLineComment = "/*";
                    }
                // Check for second multi-line comment type
                } else if (line.startsWith("{")) {
                    if (!line.endsWith("}")) {
                        multiLineComment = "{";
                }
                // Append line if line is not empty or a single line comment
                } else if (!line.startsWith("--") && !line.equals("")) {
                    sql.append(line);
                } // Check for matching end comment
            } else if (multiLineComment.equals("/*")) {
                if (line.endsWith("*/")) {
                    multiLineComment = null;
                }
            // Check for matching end comment
            } else if (multiLineComment.equals("{")) {
                if (line.endsWith("}")) {
                    multiLineComment = null;
                }
            }

        }

        sqlFile.close();

        return sql.toString().split(";");
    }

}

回答by DavidEG

The SQLiteAssetHelperlibrary makes this task really simple.

该SQLiteAssetHelper库使这项任务变得非常简单。

It's easy to add as a gradle dependency (but a Jar is also available for Ant/Eclipse), and together with the documentation it can be found at:
https://github.com/jgilfelt/android-sqlite-asset-helper

很容易将其添加为 gradle 依赖项（但 Jar 也可用于 Ant/Eclipse），并且可以在以下位置找到它的文档：https:
//github.com/jgilfelt/android-sqlite-asset-helper

Note:This project is no longer maintained as stated on above Github link.

注意：该项目不再按照上述 Github 链接进行维护。

As explained in documentation:

如文档中所述：

1. Add the dependency to your module's gradle build file:

   dependencies {
       compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
   }
   

2. Copy the database into the assets directory, in a subdirectory called assets/databases. For instance:
   assets/databases/my_database.db

   (Optionally, you may compress the database in a zip file such as assets/databases/my_database.zip. This isn't needed, since the APK is compressed as a whole already.)

3. Create a class, for example:

   public class MyDatabase extends SQLiteAssetHelper {
   
       private static final String DATABASE_NAME = "my_database.db";
       private static final int DATABASE_VERSION = 1;
   
       public MyDatabase(Context context) {
           super(context, DATABASE_NAME, null, DATABASE_VERSION);
       }
   }
   

1. 将依赖项添加到模块的 gradle 构建文件中：

   dependencies {
       compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
   }
   

2. 将数据库复制到 assets 目录中名为assets/databases. 例如：
   assets/databases/my_database.db

   （或者，您可以将数据库压缩为 zip 文件，例如assets/databases/my_database.zip。这不是必需的，因为 APK 已经作为一个整体进行了压缩。）

3. 创建一个类，例如：

   public class MyDatabase extends SQLiteAssetHelper {
   
       private static final String DATABASE_NAME = "my_database.db";
       private static final int DATABASE_VERSION = 1;
   
       public MyDatabase(Context context) {
           super(context, DATABASE_NAME, null, DATABASE_VERSION);
       }
   }
   

回答by Vaishak Nair

My solution neither uses any third-party library nor forces you to call custom methods on SQLiteOpenHelpersubclass to initialize the database on creation. It also takes care of database upgrades as well. All that needs to be done is to subclass SQLiteOpenHelper.

我的解决方案既不使用任何第三方库，也不强制您在SQLiteOpenHelper子类上调用自定义方法以在创建时初始化数据库。它还负责数据库升级。所有需要做的就是子类化SQLiteOpenHelper.

Prerequisite:

先决条件：

1. The database that you wish to ship with the app. It should containa 1x1 table named android_metadatawith an attribute localehaving the value en_USin addition to the tables unique to your app.

1. 您希望随应用程序一起提供的数据库。它应该包含一个 1x1 表android_metadata，该表命名为一个属性locale，en_US除了您的应用程序独有的表之外，该属性还具有该值。

Subclassing SQLiteOpenHelper:

子类化SQLiteOpenHelper：

1. Subclass SQLiteOpenHelper.
2. Create a privatemethod within the SQLiteOpenHelpersubclass. This method contains the logic to copy database contents from the database file in the 'assets' folder to the database created in the application package context.
3. Override onCreate, onUpgradeandonOpenmethods of SQLiteOpenHelper.

1. 子类SQLiteOpenHelper。
2. private在SQLiteOpenHelper子类中创建一个方法。此方法包含将数据库内容从“assets”文件夹中的数据库文件复制到在应用程序包上下文中创建的数据库的逻辑。
3. 覆盖onCreate，onUpgrade和onOpen方法SQLiteOpenHelper。

Enough said. Here goes the SQLiteOpenHelpersubclass:

说够了。这是SQLiteOpenHelper子类：

public class PlanDetailsSQLiteOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = "SQLiteOpenHelper";

    private final Context context;
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "my_custom_db";

    private boolean createDb = false, upgradeDb = false;

    public PlanDetailsSQLiteOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    /**
     * Copy packaged database from assets folder to the database created in the
     * application package context.
     * 
     * @param db
     *            The target database in the application package context.
     */
    private void copyDatabaseFromAssets(SQLiteDatabase db) {
        Log.i(TAG, "copyDatabase");
        InputStream myInput = null;
        OutputStream myOutput = null;
        try {
            // Open db packaged as asset as the input stream
            myInput = context.getAssets().open("path/to/shipped/db/file");

            // Open the db in the application package context:
            myOutput = new FileOutputStream(db.getPath());

            // Transfer db file contents:
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }
            myOutput.flush();

            // Set the version of the copied database to the current
            // version:
            SQLiteDatabase copiedDb = context.openOrCreateDatabase(
                DATABASE_NAME, 0, null);
            copiedDb.execSQL("PRAGMA user_version = " + DATABASE_VERSION);
            copiedDb.close();

        } catch (IOException e) {
            e.printStackTrace();
            throw new Error(TAG + " Error copying database");
        } finally {
            // Close the streams
            try {
                if (myOutput != null) {
                    myOutput.close();
                }
                if (myInput != null) {
                    myInput.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new Error(TAG + " Error closing streams");
            }
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "onCreate db");
        createDb = true;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "onUpgrade db");
        upgradeDb = true;
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        Log.i(TAG, "onOpen db");
        if (createDb) {// The db in the application package
            // context is being created.
            // So copy the contents from the db
            // file packaged in the assets
            // folder:
            createDb = false;
            copyDatabaseFromAssets(db);

        }
        if (upgradeDb) {// The db in the application package
            // context is being upgraded from a lower to a higher version.
            upgradeDb = false;
            // Your db upgrade logic here:
        }
    }
}

Finally, to get a database connection, just call getReadableDatabase()or getWritableDatabase()on the SQLiteOpenHelpersubclass and it will take care of creating a db, copying db contents from the specified file in the 'assets' folder, if the database does not exist.

最后，要获得数据库连接，只需在子类上调用getReadableDatabase()或，它就会负责创建数据库，如果数据库不存在，则从“资产”文件夹中的指定文件复制数据库内容。getWritableDatabase()SQLiteOpenHelper

In short, you can use the SQLiteOpenHelpersubclass to access the db shipped in the assets folder just as you would use for a database that is initialized using SQL queries in the onCreate()method.

简而言之，您可以使用SQLiteOpenHelper子类访问资产文件夹中提供的数据库，就像您在onCreate()方法中使用 SQL 查询初始化的数据库一样。

回答by Fangming

Shipping the app with a database file, in Android Studio 3.0

在 Android Studio 3.0 中使用数据库文件传送应用程序

Shipping the app with a database file is a good idea for me. The advantage is that you don't need to do a complex initialization, which sometimes costs lots of time, if your data set is huge.

将应用程序与数据库文件一起发送对我来说是个好主意。优点是你不需要做复杂的初始化，如果你的数据集很大，这有时会花费很多时间。

Step 1: Prepare database file

第一步：准备数据库文件

Have your database file ready. It can be either a .db file or a .sqlite file. If you use a .sqlite file, all you need to do is to change file extension names. The steps are the same.

准备好数据库文件。它可以是 .db 文件或 .sqlite 文件。如果您使用 .sqlite 文件，您需要做的就是更改文件扩展名。步骤是一样的。

In this example, I prepared a file called testDB.db. It has one table and some sample data in it like this

在这个例子中，我准备了一个名为 testDB.db 的文件。它有一张表和一些示例数据，如下所示

Step 2: Import the file into your project

第 2 步：将文件导入到您的项目中

Create the assets folder if you haven't had one. Then copy and paste the database file into this folder

如果您还没有，请创建资产文件夹。然后将数据库文件复制并粘贴到此文件夹中

Step 3: Copy the file to the app's data folder

第 3 步：将文件复制到应用程序的数据文件夹

You need to copy the database file to the app's data folder in order to do further interaction with it. This is a one time action (initialization) to copy the database file. If you call this code multiple times, the database file in data folder will be overwritten by the one in assets folder. This overwrite process is useful when you want to update the database in future during the app update.

您需要将数据库文件复制到应用程序的数据文件夹中，以便与它进行进一步的交互。这是复制数据库文件的一次性操作（初始化）。如果多次调用此代码，data 文件夹中的数据库文件将被 assets 文件夹中的数据库文件覆盖。当您希望在应用程序更新期间更新数据库时，此覆盖过程非常有用。

Note that during app update, this database file will not be changed in the app's data folder. Only uninstall will delete it.

请注意，在应用更新期间，该数据库文件不会在应用的数据文件夹中更改。只有卸载才会删除它。

The database file needs to be copied to /databasesfolder. Open Device File Explorer. Enter data/data/<YourAppName>/location. This is the app's default data folder mentioned above. And by default, the database file will be place in another folder called databases under this directory

需要将数据库文件复制到/databases文件夹中。打开设备文件资源管理器。输入data/data/<YourAppName>/位置。这是上面提到的应用程序的默认数据文件夹。默认情况下，数据库文件将放置在该目录下另一个名为 databases 的文件夹中

Now, the copy file process is pretty much like the what Java is doing. Use the following code to do the copy paste. This is the initiation code. It can also be used to update(by overwriting) the database file in future.

现在，复制文件过程与 Java 所做的非常相似。使用以下代码进行复制粘贴。这是启动代码。它还可以用于将来更新（通过覆盖）数据库文件。

//get context by calling "this" in activity or getActivity() in fragment
//call this if API level is lower than 17  String appDataPath = "/data/data/" + context.getPackageName() + "/databases/"
String appDataPath = context.getApplicationInfo().dataDir;

File dbFolder = new File(appDataPath + "/databases");//Make sure the /databases folder exists
dbFolder.mkdir();//This can be called multiple times.

File dbFilePath = new File(appDataPath + "/databases/testDB.db");

try {
    InputStream inputStream = context.getAssets().open("testDB.db");
    OutputStream outputStream = new FileOutputStream(dbFilePath);
    byte[] buffer = new byte[1024];
    int length;
    while ((length = inputStream.read(buffer))>0)
    {
        outputStream.write(buffer, 0, length);
    }
    outputStream.flush();
    outputStream.close();
    inputStream.close();
} catch (IOException e){
    //handle
}

Then refresh the folder to verify the copy process

然后刷新文件夹以验证复制过程

Step 4: Create database open helper

第四步：创建数据库打开助手

Create a subclass for SQLiteOpenHelper, with connect, close, path, etc. I named it DatabaseOpenHelper

为SQLiteOpenHelper，使用连接、关闭、路径等创建一个子类。我将它命名为DatabaseOpenHelper

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

public class DatabaseOpenHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "testDB.db";
    public static final String DB_SUB_PATH = "/databases/" + DB_NAME;
    private static String APP_DATA_PATH = "";
    private SQLiteDatabase dataBase;
    private final Context context;

    public DatabaseOpenHelper(Context context){
        super(context, DB_NAME, null, 1);
        APP_DATA_PATH = context.getApplicationInfo().dataDir;
        this.context = context;
    }

    public boolean openDataBase() throws SQLException{
        String mPath = APP_DATA_PATH + DB_SUB_PATH;
        //Note that this method assumes that the db file is already copied in place
        dataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READWRITE);
        return dataBase != null;
    }

    @Override
    public synchronized void close(){
        if(dataBase != null) {dataBase.close();}
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

Step 5: Create top level class to interact with the database

第 5 步：创建顶级类以与数据库交互

This will be the class that read & write your database file. Also there is a sample query to print out the value in the database.

这将是读取和写入数据库文件的类。还有一个示例查询可以打印出数据库中的值。

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class Database {
    private final Context context;
    private SQLiteDatabase database;
    private DatabaseOpenHelper dbHelper;

    public Database(Context context){
        this.context = context;
        dbHelper = new DatabaseOpenHelper(context);
    }

    public Database open() throws SQLException
    {
        dbHelper.openDataBase();
        dbHelper.close();
        database = dbHelper.getReadableDatabase();
        return this;
    }

    public void close()
    {
        dbHelper.close();
    }

    public void test(){
        try{
            String query ="SELECT value FROM test1";
            Cursor cursor = database.rawQuery(query, null);
            if (cursor.moveToFirst()){
                do{
                    String value = cursor.getString(0);
                    Log.d("db", value);
                }while (cursor.moveToNext());
            }
            cursor.close();
        } catch (SQLException e) {
            //handle
        }
    }
}

Step 6: Test running

第六步：试运行

Test the code by running the following lines of codes.

通过运行以下代码行来测试代码。

Database db = new Database(context);
db.open();
db.test();
db.close();

Hit the run button and cheer!

按下运行按钮，欢呼吧！

回答by LordRaydenMK

In November 2017 Google released the Room Persistence Library.

2017 年 11 月，Google 发布了Room Persistence Library。

From the documentation:

从文档：

The Room persistence library provides an abstraction layer over SQstrong textLite to allow fluent database access while harnessing the full power of SQLite.

The library helps you create a cache of your app's data on a device that's running your app. This cache, which serves as your app's single source of truth, allows users to view a consistent copy of the key information within your app, regardless of whether users have an internet connection.

Room 持久性库在 SQ强文本Lite 上提供了一个抽象层，以允许流畅的数据库访问，同时利用SQLite的全部功能 。

该库可帮助您在运行您的应用程序的设备上创建应用程序数据的缓存。此缓存作为您应用的唯一真实来源，允许用户查看您应用内关键信息的一致副本，无论用户是否有互联网连接。

The Room database has a callback when the database is first created or opened. You can use the create callback to populate your database.

Room 数据库在第一次创建或打开数据库时有一个回调。您可以使用 create 回调来填充您的数据库。

Room.databaseBuilder(context.applicationContext,
        DataDatabase::class.java, "Sample.db")
        // prepopulate the database after onCreate was called
        .addCallback(object : Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                // moving to a new thread
                ioThread {
                    getInstance(context).dataDao()
                                        .insert(PREPOPULATE_DATA)
                }
            }
        })
        .build()

Code from this blog post.

来自这篇博文的代码。

回答by masfenix

From what I've seen you should be be shipping a database that already has the tables setup and data. However if you want (and depending on the type of application you have) you can allow "upgrade database option". Then what you do is download the latest sqlite version, get the latest Insert/Create statements of a textfile hosted online, execute the statements and do a data transfer from the old db to the new one.

从我所看到的，你应该发送一个已经有表设置和数据的数据库。但是，如果您愿意（并且取决于您拥有的应用程序类型），您可以允许“升级数据库选项”。然后你要做的是下载最新的sqlite版本，获取在线托管的文本文件的最新插入/创建语句，执行这些语句并将数据从旧数据库传输到新数据库。

回答by afsane

Finally I did it!! I have used this link help Using your own SQLite database in Android applications, but had to change it a little bit.

我终于做到了！！我已经使用了这个链接帮助在 Android 应用程序中使用你自己的 SQLite 数据库，但不得不稍微改变它。

1. If you have many packages you should put the master package name here:

   private static String DB_PATH = "data/data/masterPakageName/databases";

2. I changed the method which copies the database from local folder to emulator folder! It had some problem when that folder didn't exist. So first of all, it should check the path and if it's not there, it should create the folder.

3. In the previous code, the copyDatabasemethod was never called when the database didn't exist and the checkDataBasemethod caused exception. so I changed the code a little bit.

4. If your database does not have a file extension, don't use the file name with one.

1. 如果你有很多包，你应该把主包名放在这里：

   private static String DB_PATH = "data/data/masterPakageName/databases";

2. 我更改了将数据库从本地文件夹复制到模拟器文件夹的方法！当该文件夹不存在时，它有一些问题。所以首先，它应该检查路径，如果它不存在，它应该创建文件夹。

3. 在前面的代码中，copyDatabase当数据库不存在并且该checkDataBase方法导致异常时，从未调用该方法。所以我稍微改变了代码。

4. 如果您的数据库没有文件扩展名，请不要将文件名与一个一起使用。

it works nice for me , i hope it whould be usefull for u too

它对我有用，我希望它对你也有用

    package farhangsarasIntroduction;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;

import android.content.Context;
import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;


    public class DataBaseHelper extends SQLiteOpenHelper{

    //The Android's default system path of your application database.
    private static String DB_PATH = "data/data/com.example.sample/databases";

    private static String DB_NAME = "farhangsaraDb";

    private SQLiteDatabase myDataBase;

    private final Context myContext;

    /**
      * Constructor
      * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
      * @param context
      */
    public DataBaseHelper(Context context) {

        super(context, DB_NAME, null, 1);
            this.myContext = context;

    }   

    /**
      * Creates a empty database on the system and rewrites it with your own database.
      * */
    public void createDataBase() {

        boolean dbExist;
        try {

             dbExist = checkDataBase();


        } catch (SQLiteException e) {

            e.printStackTrace();
            throw new Error("database dose not exist");

        }

        if(dbExist){
        //do nothing - database already exist
        }else{

            try {

                copyDataBase();


            } catch (IOException e) {

                e.printStackTrace();
                throw new Error("Error copying database");

            }
    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();


    }

    }

    /**
      * Check if the database already exist to avoid re-copying the file each time you open the application.
      * @return true if it exists, false if it doesn't
      */
    private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
        String myPath = DB_PATH +"/"+ DB_NAME;

        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }catch(SQLiteException e){

    //database does't exist yet.
        throw new Error("database does't exist yet.");

    }

    if(checkDB != null){

    checkDB.close();

    }

    return checkDB != null ? true : false;
    }

    /**
      * Copies your database from your local assets-folder to the just created empty database in the
      * system folder, from where it can be accessed and handled.
      * This is done by transfering bytestream.
      * */
    private void copyDataBase() throws IOException{



            //copyDataBase();
            //Open your local db as the input stream
            InputStream myInput = myContext.getAssets().open(DB_NAME);

            // Path to the just created empty db
            String outFileName = DB_PATH +"/"+ DB_NAME;
            File databaseFile = new File( DB_PATH);
             // check if databases folder exists, if not create one and its subfolders
            if (!databaseFile.exists()){
                databaseFile.mkdir();
            }

            //Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(outFileName);

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

            //Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();



    }



    @Override
    public synchronized void close() {

        if(myDataBase != null)
        myDataBase.close();

        super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }



    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

     you to create adapters for your views.

}

回答by Hiep

Shipping the database inside the apk and then copying it to /data/data/...will double the size of the database (1 in apk, 1 in data/data/...), and will increase the apk size (of course). So your database should not be too big.

将数据库传送到 apk 中，然后将其复制到/data/data/...将数据库的大小加倍（apk 中的 1 个，apk 中的 1 个data/data/...），并且会增加 apk 的大小（当然）。所以你的数据库不应该太大。

回答by Will

Currently there is no way to precreate an SQLite database to ship with your apk. The best you can do is save the appropriate SQL as a resource and run them from your application. Yes, this leads to duplication of data (same information exists as a resrouce and as a database) but there is no other way right now. The only mitigating factor is the apk file is compressed. My experience is 908KB compresses to less than 268KB.

目前没有办法预先创建一个 SQLite 数据库来与您的 apk 一起提供。您能做的最好的事情是将适当的 SQL 保存为资源并从您的应用程序中运行它们。是的，这会导致数据重复（相同的信息作为资源和数据库存在），但目前没有其他方法。唯一的缓解因素是 apk 文件被压缩。我的经验是 908KB 压缩到小于 268KB。

The thread below has the best discussion/solution I have found with good sample code.

下面的线程具有我发现的最佳示例代码的最佳讨论/解决方案。

http://groups.google.com/group/android-developers/msg/9f455ae93a1cf152

http://groups.google.com/group/android-developers/msg/9f455ae93a1cf152

I stored my CREATE statement as a string resource to be read with Context.getString() and ran it with SQLiteDatabse.execSQL().

我将我的 CREATE 语句存储为一个字符串资源，以便使用 Context.getString() 读取并使用 SQLiteDatabse.execSQL() 运行它。

I stored the data for my inserts in res/raw/inserts.sql (I created the sql file, 7000+ lines). Using the technique from the link above I entered a loop, read the file line by line and concactenated the data onto "INSERT INTO tbl VALUE " and did another SQLiteDatabase.execSQL(). No sense in saving 7000 "INSERT INTO tbl VALUE "s when they can just be concactenated on.

我将插入的数据存储在 res/raw/inserts.sql 中（我创建了 sql 文件，7000 多行）。使用上面链接中的技术，我进入了一个循环，逐行读取文件并将数据合并到“INSERT INTO tbl VALUE”上，然后执行另一个 SQLiteDatabase.execSQL()。当它们可以连接时，节省 7000 个“INSERT INTO tbl VALUE”是没有意义的。

It takes about twenty seconds on the emulator, I do not know how long this would take on a real phone, but it only happens once, when the user first starts the application.

在模拟器上大约需要 20 秒，我不知道这在真正的手机上需要多长时间，但它只发生一次，当用户第一次启动应用程序时。

回答by Jaco

If the required data is not too large (limits I don′t know, would depend on a lot of things), you might also download the data (in XML, JSON, whatever) from a website/webapp. AFter receiving, execute the SQL statements using the received data creating your tables and inserting the data.

如果所需的数据不是太大（我不知道限制，将取决于很多事情），您还可以从网站/webapp 下载数据（XML、JSON 等）。接收后，使用接收到的数据执行 SQL 语句，创建您的表并插入数据。

If your mobile app contains lots of data, it might be easier later on to update the data in the installed apps with more accurate data or changes.

如果您的移动应用程序包含大量数据，那么稍后使用更准确的数据或更改来更新已安装应用程序中的数据可能会更容易。