java - 在移动设备上找不到 sqlite db，但可以在模拟器上运行

Question

我创建了一个由表组成的 sqlite 数据库，数据库中填充了大约 2000 行。

我能够查询此表，并且当我通过模拟器执行此操作时一切正常。

但是当我在移动设备上测试它时，它会抛出以下错误:

android.database.sqlite.SQLiteException: no such table: person (code 1): , while compiling: SELECT name from person WHERE rowid = 292

当我在设备上尝试时，我希望我填充的 ​​sqlite 数据库在我的设备上自动可用，但根据上述错误，情况似乎并非如此。

I did have a look through past issues and they do not match my issue.

1. I did not set my own path for the db storage location.

   When I looked under Device File Explorer, the path for the database is /data/data/com.somepackage.myappname/databases/person

2. 我已经尝试卸载该应用程序并再次重新安装，但没有任何区别。

我的 sdk 设置详细信息(如果相关)。

minSdkVersion 16

targetSdkVersion 27

Mobile device: Using Android version 8.0.0

请告知如何让数据库在安装时自动随应用程序一起提供(当我在 Android Studio 上单击运行时)。

这就是我将数据加载到数据库中的方式。这只运行了一次，我目前已经将这段代码注释掉了。

    try {
        SQLiteDatabase database = this.openOrCreateDatabase("person", MODE_PRIVATE, null);
        database.execSQL("DROP TABLE IF EXISTS person");
        database.execSQL("CREATE TABLE IF NOT EXISTS person (name VARCHAR, name_size INT(8))");

        BufferedReader br = new BufferedReader(new InputStreamReader(getAssets().open("person.txt")));
        String line;
        while ((line = br.readLine()) != null) {
            String sql = "INSERT INTO person (name, name_size) VALUES ('" + line + "', " + line.length() + ")";
            database.execSQL(sql);
        }database.close();
    }
    catch (Exception e){
        e.printStackTrace();
    }  

我在 onCreate 方法下初始化数据库。

database = this.openOrCreateDatabase("person", MODE_PRIVATE, null);

单击按钮时，将执行以下方法。错误发生在该方法的第一行。

private String retrieveNextPerson(int randomIndex){
        //error on this raw query
        Cursor cursor = database.rawQuery("SELECT name from person WHERE rowid = " + randomIndex, null);
        int wordIndex = cursor.getColumnIndex("name");
        cursor.moveToFirst();
        return cursor.getString(wordIndex);
    }

Answer 1

假设您没有错误地认为在模拟器(或任何设备)上运行应用程序会更改包，以便分发将包含填充的数据库

• 分发预填充的数据库涉及

  • a) 填充数据库(通常使用 SQLite 管理工具)，
  • b) 将此(文件作为数据库)复制到 Assets 文件夹中，然后:-

  • c) 从 Assets 文件夹中检索它。

    • 使用SQLiteAssetHelper使这变得简单，注意使用 SQLiteAssethelper 数据库文件需要存在于数据库文件夹中(您很可能需要创建它))。

然后我怀疑你过早地调用了:-

 database = this.openOrCreateDatabase("person", MODE_PRIVATE, null);

这样做会创建没有人员表的人员数据库，从而导致您所描述的失败。

您需要在使用上述行之前加载数据。

或者，如果您在 database = this.openOrCreateDatabase("person", MODE_PRIVATE, null); 之后立即添加以下代码

Cursor csr = database.query("sqlite_master",null,"name='person'",null,null,null,null);
if (csr.getCount() < 1) {
    database.execSQL("CREATE TABLE IF NOT EXISTS person (name VARCHAR, name_size INT(8))");
........ rest of the code that inserts data from the person.txt asset
}

表格将被创建。但是，它将是空的(您可以在此处复制 person.txt Assets 中的数据)

补充评论:-

Thanks for reply. Not sure if you missed the part where I mentioned the database creation is already done and that part has been commented out. I invoked the db creation once and loaded it with data and the db is just sitting in the app now (at least for the emulator). The initialisation you mentioned should open an existing db with an existing table thus I don't see why that would be premature.

以下是对您的代码进行的非常可靠的修改，它将解决我认为最有可能出现的潜在问题:-

public class MainActivity extends AppCompatActivity {

    public static final String DBNAME = "person";
    public static final String TBNAME = "person";
    public static final String COL_NAME = "name";
    public static final String COL_NAME_SIZE = "name_size";
    public static final String ASSET_FILENAME = "person.txt";

    public static final String SQLITE_MASTER_TABLE = "sqlite_master";
    public static final String COL_SQLITE_MATSER_NAME = "name";

    static final int MIMINUM_ROWS_IN_PERSONTABLE = 1;

    SQLiteDatabase db;
    BufferedReader br;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        checkPersonTable(); // Notes sets db either way

        // FOR TESTING
        long rows_in_person_table = DatabaseUtils.queryNumEntries(db,TBNAME);
        Log.d(
                "PERSON ROW COUNT",
                "The number of rows in the " +
                        TBNAME +
                        " table is " +
                        String.valueOf(rows_in_person_table)
        );
    }


    private void checkPersonTable() {

        db = this.openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
        // Database will now exist but it may or may not contain the person table so check sqlite_master
        Cursor csr = db.query(
                SQLITE_MASTER_TABLE,
                new String[]{COL_SQLITE_MATSER_NAME},
                        COL_SQLITE_MATSER_NAME + "=?",
                        new String[]{TBNAME},
                        null,null,null
        );
        // Cursor will contain 1 row if the person table exists so check count
        int person_table_count = csr.getCount();
        csr.close();
        // Before attemtping to create the Person table ensure that the assets file exists
        // If not then throw a RunTime exception

        if (person_table_count < 1) {
            try {
                if (!Arrays.asList(getResources().getAssets().list("")).contains(ASSET_FILENAME)) {
                    StringBuilder sb = new StringBuilder();
                    throw new RuntimeException("Asset file " +
                            ASSET_FILENAME +
                            " not found in the assets folder." +
                            " The following assets were found" +
                            sb
                    );
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        if (person_table_count < 1) {
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TBNAME +
                    "(" +
                    COL_NAME + " TEXT," +
                    COL_NAME_SIZE + " INTEGER" +
                    ")"
            );
            loadPersonsFromAssets();
        } else {
            // <<<<<<<<<< NOTE Optional will load data from assets if miminum nuber of rows
            //                 aren't in the person table
            if (DatabaseUtils.queryNumEntries(db,TBNAME) < MIMINUM_ROWS_IN_PERSONTABLE) {
                loadPersonsFromAssets();
            }
        }
    }

    // Load the person table from the Assets File
    private void loadPersonsFromAssets() {
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(getAssets().open(ASSET_FILENAME)));
            String line, sql;
            int lines_read = 0;
            db.beginTransaction();
            while ((line = br.readLine()) != null) {
                sql = "INSERT INTO " + TBNAME + " VALUES('" + line + "'," + String.valueOf(line.length()) + ")";
                db.execSQL(sql);
                lines_read++;
            }
            db.setTransactionSuccessful();
            db.endTransaction();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Asset File " + ASSET_FILENAME + " not found in the assets folder.");
        }
    }
}

这将:-

• 仅尝试在设置 SQliteDatabase 对象后打开数据库。
• 将创建person 数据库(如果不存在)，然后创建 person 表。
• 即使数据库存在，它也会继续检查表是否存在，如果不存在，将创建它。
• 一个异常(exception)是，如果 Assets 文件不存在于 Assets 文件夹中，在这种情况下，将引发运行时异常(就好像它丢失了，这是一个严重的错误，因为它应该始终存在，因为它是包)。
• 它还会在创建表时填充表，或者如果有两行则填充表(取决于 MIMINUM_ROWS_IN_PERSONTABLE 的值)。

如果 Assets 文件 person.txt 不存在，那么您将得到类似于以下的异常:-

06-10 03:58:43.503 3097-3097/personthing.so50777840 E/AndroidRuntime: FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo{personthing.so50777840/personthing.so50777840.MainActivity}: java.lang.RuntimeException: Asset file person.txt not found in the assets folder. The following assets were found
         found asset file :- images
         found asset file :- notperson.txt
         found asset file :- sounds
         found asset file :- webkit
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)
     Caused by: java.lang.RuntimeException: Asset file person.txt not found in the assets folder. The following assets were found
         found asset file :- images
         found asset file :- notperson.txt
         found asset file :- sounds
         found asset file :- webkit
        at personthing.so50777840.MainActivity.checkPersonTable(MainActivity.java:83)
        at personthing.so50777840.MainActivity.onCreate(MainActivity.java:39)
        at android.app.Activity.performCreate(Activity.java:5008)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
            ... 11 more

• 注意 可以看出，person.txt 被“错误地”命名为notperson.txt 以强制错误。

请注意，在此阶段数据库将存在(由于 openOrCreateDatabase)并且它将包含两个表(sqlite_master 和 android_metadata)但不包含 person 表，例如:-

但是，创建正确的 Assets 文件 person.txt(将 notperson.txt 重命名为 person.text)将导致创建表并加载数据:-

例如如果 person.txt 是:-

然后运行该应用程序将导致日志包含:-

06-10 04:39:04.277 3325-3325/? D/PERSON ROW COUNT: The number of rows in the person table is 11